[TOC] #### 1. 索引的作用 --- MySQL 索引是数据库中用于加速数据检索核心数据结构，其作用类似于书籍的目录，能帮助数据库快速定位到目标数据行，避免全表扫描，显著提升查询效率。简单来说，索引的作用就是提高 SQL 查询效率。 索引的核心作用： + 提升查询速度：没有索引的时需要逐行扫描来匹配条件；而有了索引后，可通过高效的数据结构快速定位目标记录 + 优化排序与分组：当使用排序或分组时，若字段已建立索引，数据库可直接利用索引的有序性，减少额外排序开销 + 加速表连接：在 `JOIN` 操作中，对关联字段建立索引可大幅提升连接效率，尤其在大表关联时效果显著 #### 2. 索引的类型 --- ##### 按照数据结构分类 这是从索引的底层实现方式来划分的，决定了索引的查询效率和适用场景 | 索引类型 | 核心特点 | 适用场景 | | ------------ | ------------ | ------------ | | B+树索引 | 最主流、默认的索引类型。支持等值查询、范围查询和排序 | 绝大多数场景，如 主键索引、普通索引 | | 哈希索引 | 基于哈希表实现，等值查询速度极快，但不支持范围查询和排序 | 特定场景，如 InnoDB 自适应哈希 | ##### 按照物理存储分类 这主要针对 `InnoDB` 存储引擎，决定了数据和索引是如何存储在磁盘上的 聚簇索引（Clustered Index）： + 定义：索引和数据存储在一起。在 InnoDB 中，主键索引就是聚簇索引 + 特点：其叶子节点直接存储了完整的行数据。因此，通过主键查询速度最快，无需回表。一张表有且仅有一个聚簇索引 非聚簇索引（Clustered Index）： + 定义：也叫二级索引，除了聚簇索引之外的索引都属于此类 + 特点：其叶子节点存储的是索引列的值和对应的主键值，而非完整数据。查询时，通常需要先找到主键值，再通过主键值去聚簇索引中查询查找完整数据，这个过程叫做回表 ##### 按照逻辑功能分类 按照逻辑功能分类：普通索引、主键索引、唯一索引、联合索引、全文索引、空间索引 + 普通索引：最基础的索引，允许重复和空值，加速 where、join、order by 查询 + 主键索引：指定 primary key 的字段，主键索引是一种唯一性索引 + 唯一索引：列的内容具有唯一性，只有出现一次，但是空（null）除外 + 联合索引：在多个字段上创建的索引，如 `index(a,b,c)`。多字段联合查询，遵循最左前缀原则 + 全文索引：用于大文本字段的关键词检索。文章、评论、商品描述等内容的搜索 + 空间索引：用于地理空间数据类型。地图应用、位置服务等 #### 3. 索引的弊端 --- 一、加索引之后会生成索引文件，这个文件还比较大，所以加索引会占用一些磁盘空间。 二、当进行增删改（insert，delete，update）操作时，会重新构建索引树，所以，加索引后增删改操作效率会略慢一些 补充：加索引后增删改操作会变慢，这个问题其实没有必要担心，因为一个系统的查询操作频率是远大于增删改操作的 #### 4. 索引的使用场景 ---- 针对项目中常见的手机号和性别字段，是否应该建立索引 ？ 手机号字段：推荐使用 `varchar(11)` 或 `varchar(20)`，坚决不用 `int` 或 `bigint`。为什么不用数字类型 ？ + 长度溢出风险：int 类型最大只能存到 21亿（10位），虽然 bigint 能存下，但没必要 + 前导零丢失：如果未来业务设计国际号码，数字类型会自动去掉前面的零，导致数据错误 + 无计算需求：手机号本质上是字符串标识，不会对手机号进行算术运算，用数字类型没有意义 性别字段：推荐使用 `tinyint(1)`，不推荐 `chat(1)` 或 `bit(1)`。为什么首选 `tinyint(1)` ？ + 存储极小：只占用 1 字节。通常用 0 代表 女，1 代表男，2 代表未知 + 扩展性好：如果未来需要增加 “保密”、“其他” 等选项，直接插入新数字即可 索引建立策略：手机号字段必须建立索引，推荐使用唯一索引，因为手机号通常是用户的唯一标识，并且查询频率极高 + 使用唯一索引不仅能加速查询，还能从数据库层面防止同一个手机号注册两次 索引建立策略：性别字段千万不要单独建索引。 推荐方式：不建索引，或仅在联合索引中作为附属，核心原因（考点）： 区分度太低：索引的作用是 “快速过滤”，只有男女两个值，数据分布通常是 1:1，查所有男性需要扫描全表 50% 的数据 优化器会放弃索引：MySQL 优化器非常聪明，当发现某个条件需要筛选出一半以上的数据，它会认为走索引还要回表，太麻烦，不如直接全表扫描快。因此，建立了这个索引大概率也是闲置的，反而会存储空间并降低写入速度 #### 5. 哪些列上适合添加索引 ---- 在 MySQL 中，为合适的列添加索引是提升查询性能的关键，但索引并非越多越好，因为它会占用额外的磁盘空间，并降低增删改的速度。因此，选择索引列的核心思想是在查询效率和写入性能之间找到最佳平衡点。 通常，在以下类型的列上创建索引能带来显著的性能提升： + 高频查询条件列 + 在 where 子句中频繁出现的列。索引可以帮助数据库快速定位到符合条件的行，避免全表扫描 + 示例：`SELECT * FROM users WHERE email = 'test@example.com'`，建议在 `email` 列上创建索引 + 表连接（join）的关联列 + 用于连接多个表的列。为这些列创建索引可以极大地加速连接操作 + 示例：`SELECT * FROM orders JOIN users ON orders.user_id = users.id` + 建议：在 `orders.user_id` 和 `users.id`（通常是主键，已有索引）上创建索引 + 排序和分组列 + 在 `order by` 和 `group by` 子句中使用的列。索引本身是有序的，可以避免数据库进行额外的文件排序 + 示例：`SELECT * FROM products ORDER BY price DESC`，建议：在 `price` 上创建索引 + 高区分度的列 + 列中不同值的数量占总行数的比例很高，越接近唯一性越好。索引的过滤效果非常好 + 示例：身份证号、手机号、用户名等 + 建议：这些列是创建索引的理想选择。可以通过 `COUNT(DISTINCT column) / COUNT(*)` 估算一列区分度 在以下类型的列上创建索引，往往弊大于利（不适合添加索引的列）： + 区分度极低的列 + 列中不同值的数量极少，这种情况下，数据库优化器通常会认为全表扫描比使用索引更有效 + 示例：性别、逻辑删除标识、状态标识（如只有启用/禁用，区分度仅0.5） + 频繁更新的列 + 每次更新这些列的值，数据库都需要同步更新对应的索引，这会带来巨大的写入开销，严重拖慢性能 + 示例：订单状态、商品库存等频繁变化的字段 + 很少被查询的列 + 如果一个列几乎不会出现在查询条件、连接、排序或分组中，那么为它创建索引就是一种资源浪费 + 数据量很小的表 + 对于行数很少的表，全表扫描的速度非常快，创建索引带来的性能提升微乎其微，反而增加了维护成本 + 大字段（text） + 过长的字段不适合作为普通索引，因为索引会占用大量空间。如果必须索引，可以考虑使用前缀索引，也就是只对字段的前 N 个字符创建索引 + 示例：`CREATE INDEX idx_desc ON products (description(50));` 除了单列索引，掌握以下策略能让你更好地优化查询： + 联合索引：将区分度高和查询最频繁的列放在联合索引的最左侧 + 覆盖索引：查询所需的所有列都包含在同一个索引中时，数据库可以直接从索引中获取数据，无需回表 #### 6. 为什么根据主键 id 查询速度更快 --- 简单来说，主键查询快，是因为在 InnoDB 引擎中，主键索引就是数据本身，它不需要回头去找数据（核心原因：聚簇索引） 在 MySQL 的 InnoDB 引擎中，表的数据文件本身就是按照 B+ 树组织的，这棵树就是聚簇索引 + 叶子节点存数据：主键索引（聚簇索引）的叶子节点直接存储了完整的行数据（所有字段） + 一步到位：当执行 `where id = 100` 时，只需要在 B+ 树上找到对应的叶子节点，就拿到数据了，不需要额外操作 为了凸显主键的快，通常需要对比普通索引（二级索引）： + 普通索引的叶子节点存主键：普通索引的叶子节点不存数据，只存索引列的值和主键 ID + 回表：通过普通索引查询，数据库先在普通索引树找到主键ID，然后再拿着这个 ID 去主键索引树里查找完整的行数据 + 结论：普通索引查询通常需要 2 次 B+ 树搜索，而主键查询只需要 1 次 #### 7. 为什么 MySQL 索引结构采用 B+ 树 --- 面试考点：MySQL 选择 B+ 树索引，而不是 Hash 索引的原因是什么 ？根本原因在于： + Hash 索引虽然单点查询极快，但是功能太单一（偏科），无法满足数据库的通用业务需求（如范围查询、排序） + 而 B+ 树在磁盘 I/O 效率和功能全面性之间取得了最佳平衡 虽然 Hash 索引在等值查询时，时间复杂度是 O(1)，速度极快，但是它在关系型数据库中有着无法接受的短板 + 不支持范围查询： + Hash 索引经过哈希算法计算后，数据在物理上是无序且散乱分布的 + 场景：当执行 `where id > 100` 或 `between` 查询时，Hash 索引无法利用索引的顺序性，只能全表扫描 + 对比：B+ 树的叶子节点通过双向链表连接，且数据有序，范围查询只需找到起点，顺着链表遍历即可，效率极高 + 不支持排序 + 同样因为 Hash 数据的无序性，数据库无法利用 Hash 索引来优化排序操作 + 如果使用 Hash 索引，执行 `order by` 时数据库必须进行额外的文件排序（Filesort），性能开销巨大 + 不支持模糊查询 + Hash 数据只能进行精确的等值匹配 MySQL 的数据主要存储在磁盘上，磁盘的随机读写（I/O）速度远慢于内存，B+ 树的设计完全是为了减少磁盘 I/O 次数