文章

面试题学习笔记 | MySQL 索引使用

索引覆盖与回表查询

在 MySQL 的 InnoDB 存储引擎中,索引覆盖与回表查询涉及到聚簇索引与非聚簇索引的区别。理解这些概念可以帮助我们优化数据库查询,提升性能

聚簇索引

聚簇索引通常是主键索引。如果没有主键,MySQL 会自动选择一个非空的唯一索引来创建聚簇索引。如果没有符合条件的唯一索引,MySQL 会使用表的隐藏主键 db_row_id 来创建聚簇索引

聚簇索引使用的是 B+ 树结构。每个非叶子节点存储索引值和页指针,而叶子节点存储完整的数据行。因此,聚簇索引的数据存储顺序就是表的物理存储顺序

非聚簇索引

非聚簇索引(也称二级索引)与聚簇索引的区别在于,其叶子节点存储的仅是索引字段的值和对应的主键 ID。通过主键 ID,MySQL 可以从聚簇索引中获取完整的行数据

索引覆盖与回表查询

在查询时,假如通过非聚簇索引查询到的结果能够满足所有查询字段的需求,就可以直接返回结果,这种情况称为索引覆盖。而如果查询字段不足以满足条件,系统需要通过非聚簇索引返回的主键 ID 到聚簇索引中查找剩余字段,这种情况叫做回表查询

索引覆盖的意义

索引覆盖相比回表查询具有显著的性能优势。回表查询需要额外的查询步骤,增加了查询次数。由于主键 ID 在聚簇索引中的分布通常不是连续的,回表查询会导致更多的随机 I/O 操作。与顺序 I/O 相比,随机 I/O 的查询速度较慢,频繁的回表查询会显著降低查询效率

因此,在学习 MySQL 时,我们常被建议避免使用 SELECT *,以避免因缺少索引覆盖而触发回表查询,进而提升查询性能

索引是否一定生效?如何排查索引效果?

首先,我们需要明确“索引生效”是什么意思

在 MySQL 5.5 版本之后,InnoDB 存储引擎成为默认引擎。InnoDB 中的数据是基于聚簇索引存储的,聚簇索引的叶子节点存储了完整的数据行。如果没有其他索引,查询就只能通过聚簇索引

当有了二级索引时,二级索引的叶子节点存储了索引字段和对应的主键,因此查询可以通过二级索引直接查找数据

然而,为什么我们有时会说索引“未生效”而出现全表扫描呢?这通常是因为 MySQL 的 B+ 树中,叶子节点是通过链表连接的。全表扫描发生时,MySQL 会从叶子节点的最左边开始依次扫描所有记录。这种方式就是全表扫描。而如果查询通过 B+ 树的搜索特性直接找到目标叶子节点,我们才称之为“使用索引”

使用 EXPLAIN 排查索引效果

要判断查询是否使用了索引,可以使用 EXPLAIN 查看查询的执行计划。通过 EXPLAIN 输出的 type 字段,可以确定查询的访问类型。如果 type 显示为 index,表示使用了全索引扫描(即在二级索引树中进行了全扫描);如果显示为 all,则表示走了全表扫描,聚簇索引中进行了全扫描。这两种情况都应尽量避免

EXPLAIN 详解

EXPLAIN 是 MySQL 中查看查询执行计划的工具,可以帮助我们评估查询效率。主要输出以下几个字段:

  • type(访问类型)

  • rows(扫描的行数)

  • key(实际使用的索引)

type 字段的值越优,查询性能越好。以下是 type 字段的常见值,按性能从优到劣排序:

  1. system:表只有一行(类似系统表),最优

  2. const:表最多有一行匹配(通常是主键或唯一索引),优化器会将其视为常量

  3. eq_ref:对于每个前一张表的行组合,最多匹配一行。通常用于主键或唯一索引扫描

  4. ref:对于每个前一张表的行组合,匹配多行。通常用于非主键或非唯一索引扫描

  5. range:只检索给定范围的行,使用索引选择行。通常用于范围查询(如 BETWEEN>< 等)

  6. index:全索引扫描,遍历索引树,通常比全表扫描更快

  7. all:全表扫描,最差

索引失效的原因

索引失效通常发生在查询条件修改了索引的顺序或结构,导致索引无法发挥作用。常见的索引失效场景包括:

  1. 使用联合索引时未符合最左前缀原则

  2. 索引字段使用了函数

  3. 索引字段进行运算

  4. 使用不适当的 LIKE 查询

  5. 表中两个不同字段进行比较

  6. ORDER BY 后使用的不是主键或覆盖索引

索引建立的注意事项

  1. 不要盲目建立索引:索引占用存储空间且需要维护,过多的索引会消耗大量资源。应根据实际查询需求合理建立索引

  2. 避免为重复值较多的字段建立索引:例如,性别等字段,在大多数查询场景中建立索引不会显著提升效率。部分场景下,如定时任务查询失败记录时,建立索引可以加速查询

  3. 长字段不建议建立索引:长字段会占用大量内存,不利于索引性能

  4. 考虑字段的修改频率:如果数据更新频繁(写多读少),不建议建立索引。否则,频繁的索引更新会影响性能

  5. 经常作为检索条件的字段可考虑建立索引:尤其是经常组合在一起的字段,可以考虑建立联合索引

  6. 对经常参与 ORDER BYGROUP BYDISTINCT 操作的字段建立索引:通过索引可以加快这些操作的排序、分组和去重效率

License:  CC BY 4.0