面灵AI→

索引失效复盘三天:隐式转换、key_len 与优化器的成本账

轮次
面试复盘
时间
2026-11
来源
牛客网

《面试题目》

  1. MySQL 里哪些情况索引会失效?
  2. 你线上遇到过哪一种?是怎么定位的?
  3. EXPLAIN 的哪一行、看什么字段判断?
  4. 手机号字段是 varchar,Java 实体却用 Long,会发生什么?
  5. 联合索引 (a, b, c) 下,WHERE a = ? AND c = ? 算不算用上了索引?
  6. 为什么 WHERE a > ? AND b = ? 里 b 用不上索引?
  7. 区分度极低的字段建了索引却不走,是索引坏了吗?
  8. 确认原因的手段有哪些?

《参考解析》

隐式转换是安静的全表扫描。 用户表 phone 建的是 varchar(20),Java 实体为省事写成 Long,Mapper 里就是 WHERE phone = #{phone}。数据量小的时候全表扫也就几十毫秒,涨到几百万行后接口直接超时。EXPLAIN 显示 type = ALL、key = NULL、rows 两百多万,作者一度以为索引坏了或统计信息不准,ANALYZE TABLE 也没用。真实原因是等值比较时一边是字符串、一边是数字,MySQL 把字符串列转成数字再比,等于在列上套了函数。改法是把参数按 String 传或 SQL 里显式加引号;教训是隐式转换不报错,尤其当实体类字段类型与建表语句对不上时,这类问题会成批出现。

「用上了」与「用好了」差在 key_len。 联合索引 (a, b, c) 下 WHERE a = ? AND c = ? 的 EXPLAIN 里 key 有值,看起来索引生效了,但 key_len 只覆盖 a 一列的长度,c 完全没参与定位,只是被索引下推在引擎层过滤。如果 a 的区分度很低(比如是个状态字段),这个索引基本等于没用。看执行计划时先看 key 再看 key_len,才能知道联合索引用到了第几列。

范围条件让后续列失去有序性。 WHERE a > ? AND b = ? 中 a 是范围,b 在索引里对于不同的 a 值不再全局有序,无法继续用于定位。作者说这个结论他背过,但真正理解是在画了一遍 B+ 树索引结构之后——索引是先按 a 排序、a 相同再按 b 排序,一旦 a 是区间,b 的顺序在每个 a 值内部才有意义。

最该警惕的是优化器主动不走索引。 作者给一个区分度极低的状态字段建了索引,九成的行都是同一个值,查询 WHERE status = 1 时 EXPLAIN 显示不走索引。不是失效,而是走索引要扫索引再回表,回表的行数比全表扫还多,优化器算完成本主动放弃。所以「索引失效」这个词本身不够准确:多数时候不是索引失效,而是优化器基于成本估算选择了另一条路。区分度太低、回表代价太高、统计信息过期,都会导致这个结果;这也解释了为什么有时 FORCE INDEX 反而更慢——你否定了优化器的判断,但它可能是对的。

确认原因的三级手段。 先用 EXPLAIN 看 type、key、key_len、rows、filtered;拿不准时用 EXPLAIN FORMAT=JSON 看 cost_info 里两套成本的对比;仍看不出就打开 optimizer_trace,能看到优化器为什么排除了某个索引。

复盘后改掉的四个习惯。 看到慢查询先跑 EXPLAIN,不靠读 SQL 猜原因;看完 key 一定再看 key_len,确认联合索引用到第几列;参数类型与字段类型不匹配的问题统一在 DAO 层收敛,不靠人记;低区分度字段单独建索引之前,先算一下过滤后的行数占比。再被问到索引失效,作者说他会先讲手机号那个例子,再补一句:多数情况不是失效,是优化器算过账,觉得不值。