联合索引(a,b,c)使用(b,c)查询的索引失效分析

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

这是一个非常经典的MySQL索引优化问题，涉及到联合索引（Composite Index）的最左匹配原则（Leftmost Prefix Rule）。

直接给出结论：使用 (b,c) 查询 通常会导致索引失效（即走不到索引），除非 MySQL 优化器判定使用索引覆盖（Covering Index） 代价更低。

下面我们结合 (a,b,c) 的联合索引结构，深入分析底层原理和具体情况。

1. 核心原理：联合索引的存储结构

联合索引 (a,b,c) 在 B+ 树索引文件中的存储顺序是严格按照字段顺序排列的：

1. 先按 a 排序。
2. 在 a 相同的情况下，按 b 排序。
3. 在 a 和 b 都相同的情况下，按 c 排序。

类比生活场景：这就好比一本电话簿，索引规则是“按【省份(a)】、【城市(b)】、【姓名(c)】”排序。

• 你可以直接查“所有深圳市的人”（因为深圳在广东下面，且广东是第一索引）。
• 但你如果直接查“所有城市名和姓名”，电话簿是乱序的，无法直接定位。

2. 为什么 (b,c) 会导致索引失效？

当查询条件是 WHERE b = ? AND c = ? 时，MySQL 无法使用索引 (a,b,c) 来快速定位数据，原因如下：

1. 缺少左前缀 a：索引树的最底层节点是由 a 区分的。没有 a 的条件，数据库不知道该从哪个根节点开始查找。
2. 数据无序：在整个索引树中，b 和 c 的组合并不是连续存储的。数据库必须进行全表扫描（Full Table Scan） 才能过滤出符合 b 和 c 的记录。

例外情况：索引覆盖（Covering Index）

如果你的查询语句是 SELECT b, c FROM ... WHERE b = ? AND c = ?，并且不包含 a 的查询，情况会有所不同。

• 因为索引 (a,b,c) 已经包含了 b 和 c 的所有数据（叶子节点包含所有字段）。
• MySQL 的优化器可能会选择跳过主键索引，直接遍历联合索引的叶子节点来获取数据。
• 此时，虽然没有使用索引进行查找（Search），但使用了索引进行读取（Lookup），这种情况下称为“索引覆盖”，性能通常优于全表扫描。但在常规分析中，我们仍认为它未走索引查找。

3. 索引匹配规则速查表

针对联合索引 (a, b, c)，以下是不同查询条件的索引命中情况：

4. 最佳实践建议

为了让索引 (a,b,c) 发挥最大作用，请遵循以下原则：

1. 遵循最左匹配：查询条件中，必须包含联合索引的第一列，才能触发索引匹配。
2. 区分度高的字段放前面：将过滤性最强（基数最高）的字段放在最左边。例如，如果 a 的唯一值很少，而 b 很多，应该建立 (b,a,c)。
3. 避免隐式转换：确保查询字段类型与索引字段一致，避免 MySQL 进行隐式转换导致索引失效。
4. 覆盖索引设计：如果业务上经常只查询 b 和 c，可以直接建立单列索引 (b,c) 或单独的索引，这样更轻量。

5. 验证方法：EXPLAIN

要确定你的 SQL 是否真的命中了索引，请使用 EXPLAIN 命令：

• type 列：如果显示 ref 或 range，说明索引有效。如果显示 ALL，说明是全表扫描（索引失效）。
• key 列：显示 MySQL 实际选择使用的索引。
• Extra 列：如果出现 Using index，说明使用了索引覆盖；如果出现 Using where，通常表示需要回表查询或索引部分失效。

总结：在 (a,b,c) 索引下，(b,c) 由于缺失第一列 a，无法利用索引进行快速查找，建议调整查询逻辑或重建索引。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花