面试题学习笔记 | 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
字段的常见值,按性能从优到劣排序:
system:表只有一行(类似系统表),最优
const:表最多有一行匹配(通常是主键或唯一索引),优化器会将其视为常量
eq_ref:对于每个前一张表的行组合,最多匹配一行。通常用于主键或唯一索引扫描
ref:对于每个前一张表的行组合,匹配多行。通常用于非主键或非唯一索引扫描
range:只检索给定范围的行,使用索引选择行。通常用于范围查询(如
BETWEEN
、>
、<
等)index:全索引扫描,遍历索引树,通常比全表扫描更快
all:全表扫描,最差
索引失效的原因
索引失效通常发生在查询条件修改了索引的顺序或结构,导致索引无法发挥作用。常见的索引失效场景包括:
使用联合索引时未符合最左前缀原则
索引字段使用了函数
索引字段进行运算
使用不适当的 LIKE 查询
表中两个不同字段进行比较
ORDER BY
后使用的不是主键或覆盖索引
索引建立的注意事项
不要盲目建立索引:索引占用存储空间且需要维护,过多的索引会消耗大量资源。应根据实际查询需求合理建立索引
避免为重复值较多的字段建立索引:例如,性别等字段,在大多数查询场景中建立索引不会显著提升效率。部分场景下,如定时任务查询失败记录时,建立索引可以加速查询
长字段不建议建立索引:长字段会占用大量内存,不利于索引性能
考虑字段的修改频率:如果数据更新频繁(写多读少),不建议建立索引。否则,频繁的索引更新会影响性能
经常作为检索条件的字段可考虑建立索引:尤其是经常组合在一起的字段,可以考虑建立联合索引
对经常参与
ORDER BY
、GROUP BY
、DISTINCT
操作的字段建立索引:通过索引可以加快这些操作的排序、分组和去重效率