索引失效与联合索引的底层原理
在实际工作中，你是如何确保查询语句能够命中索引的？通过 EXPLAIN 查看执行计划时，如果使用了联合索引，如何判断是只用到了部分字段还是全部字段？请解释最左前缀原则，并从 B+Tree 数据结构的角度分析，为什么不满足最左匹配或存在范围查询时，索引会失效？
考察点
- 执行计划分析能力：掌握
EXPLAIN命令，特别是key_len字段的含义，这是判断联合索引使用了多少个列的关键。 - B+Tree 结构理解：深刻理解 B+Tree 索引“有序性”的本质。联合索引是全局无序、局部有序（先按第一列排，第一列相同时按第二列排……）。
- 最左前缀原则底层逻辑：能解释为什么跳过第一列或中间出现范围查询会导致后续列无法利用索引（因为后续列在逻辑上不再有序）。
- 优化器原理：理解 MySQL 优化器是基于“成本（Cost）”的，而非仅仅基于规则。即便符合索引规则，如果回表成本过高，优化器仍可能选择全表扫描。
解释
1. 如何判断联合索引是否完全被使用？ 在 EXPLAIN 的结果中，除了看 key 字段确认是否走了索引，最关键的是看 key_len 字段。
- 计算逻辑：
key_len表示索引使用的字节数。例如，联合索引(a, b, c)，字段类型均为int（4字节）且不为 NULL。- 如果
key_len= 4，说明只用到了a。 - 如果
key_len= 8，说明用到了a, b。 - 如果
key_len= 12，说明a, b, c全用到了。
- 如果
- 辅助判断：结合
rows（扫描行数）字段。如果rows很大接近全表，说明索引区分度不够或只命中前缀，过滤效果不佳。
2. 为什么会失效？（基于 B+Tree 的有序性分析） 索引查询的本质是利用数据的有序性来快速缩小检索范围（二分查找）。
联合索引的排序规则： 对于索引
(a, b, c)，B+Tree 是先按a排序；在a相等的情况下，按b排序；在b相等的情况下，按c排序。关键点：如果
a不确定，b就是无序的；如果b不确定，c就是无序的。失效场景解析：
- 不满足最左匹配（跳过前导列）：
- 比如查询
WHERE b = 1。因为 B+Tree 是先按a排序的，跨过a直接看b，b的值在全局是散乱无序的，无法进行二分查找，只能全表扫描。
- 比如查询
- 范围查询导致后续失效：
- 比如
WHERE a = 1 AND b > 10 AND c = 5。 a=1确定了，b是有序的，所以b>10可以走索引。- 但在
b > 10的范围内，b的值是变化的（11, 12, 13...），在不同的b值下，c的排序是割裂的（即c只是在b确定的某个具体值下才有序）。因此，在范围b>10这一段里，c是无序的，索引扫描在b处就截断了。
- 比如
- 类型转换：
- 比如字符串字段存了数字，查询时不加引号
WHERE str_col = 123。数据库会进行隐式类型转换，转换后的值排序规则与原值不同（例如字符串 "10" 可能会排在 "2" 前面，但数字 10 在 2 后面），破坏了有序性，导致全表扫描。
- 比如字符串字段存了数字，查询时不加引号
- 不满足最左匹配（跳过前导列）：
3. 优化器的最终裁决 即使 SQL 写的符合上述规则，MySQL 优化器依然可能放弃索引。
- 它会评估成本：如果二级索引查出来的数据多，且需要频繁回表（Random IO），成本可能高于直接扫聚簇索引（Sequential IO）。
- 这时可以通过
force index强制尝试，或者优化 SQL 减少回表（如使用覆盖索引）。
相关扩展知识
- key_len 详细计算：面试时需了解常见类型的长度。
int: 4字节。bigint: 8字节。varchar(n)(utf8mb4): n * 4 + 2字节(存长度)。NULL: 如果字段允许为 NULL，通常需 +1 字节。
- 索引跳跃扫描 (Index Skip Scan)：MySQL 8.0 的新特性。在特定条件下（前导列基数很小），即使不满足最左前缀，优化器也会尝试把查询拆分成多个 range 查询来利用联合索引，但这属于特例。
- Filesort：如果索引失效，Order By 往往也会无法利用索引排序，导致出现
Using filesort，性能大打折扣。
扩展问题
- 如果建立了 (a, b) 索引，查询
WHERE b = 1 AND a = 1会走索引吗？为什么？- 思路：会走。MySQL 优化器会自动调整 WHERE 条件的顺序来匹配索引。
- 范围查询
>和IN有什么区别？IN会导致索引截断吗？- 思路：
>是范围查询，会导致后续字段失效。IN在 MySQL 5.7+ 经常被优化为等值查询的组合（Range checked for each record），在某些情况下后续字段仍可能用到索引（取决于数据量和优化器版本）。
- 思路：
- 什么是索引下推（ICP）？它在这个场景下有什么作用？
- 思路：即使联合索引中途截断（如 b 做了范围查询），MySQL 5.6+ 也可以利用后续字段（c）在存储引擎层先做过滤，减少回表次数。