面试题学习笔记 | 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操作的字段建立索引：通过索引可以加快这些操作的排序、分组和去重效率