面灵AI→

「你背的很多条其实都是错的」:索引失效的七个场景与优化器成本

时间
2026-11
来源
牛客网

《面试题目》

  1. SELECT id, order_no, status FROM orders WHERE DATE(created_at) = '2024-03-01' 这条语句有什么问题?
  2. 那你具体说说什么情况下索引会失效?
  3. 隐式类型转换到底转了什么?为什么转换之后就用不上索引?
  4. 索引列上做函数运算为什么会失效?怎么改写?
  5. 联合索引 (a, b, c) 里,WHERE b = ? AND c = ? 和 WHERE a = ? AND c = ? 分别能用上哪些列?
  6. LIKE '%关键词' 为什么用不上索引?业务上真要前后模糊怎么办?
  7. WHERE a = 1 OR b = 2 一定会全表扫吗?
  8. 范围查询后面的列为什么用不上?索引下推能补救吗?
  9. 两张表 join,单独查都快、一 join 就慢,可能是什么原因?
  10. !=、NOT IN、IS NOT NULL 是不是必然导致索引失效?

《参考解析》

先纠正一个认知:「索引失效」和「优化器判断走索引不划算」是两件事。前者是写法让 B+ 树的有序性用不上,后者是代价问题——回表太多、区分度太低、统计信息过期,优化器主动选择全表扫。面试官想听的往往是后者,因为这说明你真的看过执行计划。

场景一:隐式类型转换。字符串列与数字比较时,MySQL 会把列上的每一行转成数字再比,等价于在索引列上套了 CAST,B+ 树按字符串排序的有序性就用不上了。方向性是关键:varchar 列传数字会失效,int 列传字符串不失效,因为转换发生在常量侧。这个坑在代码里看不出来,只能靠 EXPLAIN 的 type 从 ref 掉成 ALL、key 变成 NULL 发现,也可以用 SHOW WARNINGS 看优化器重写后的语句。

场景二:函数与运算包裹索引列。DATE(created_at)、amount + 100 > 500、id + 1 = 100 都是同一类问题:索引按原始值排序,你在列上算出新值,排序关系就断了。改写方式是把运算挪到常量侧,日期查询用左闭右开区间。

场景三:最左前缀被破坏。联合索引按定义顺序逐列排序,缺少最左列的等值条件就无法定位。WHERE b = ? AND c = ? 完全用不上;WHERE a = ? AND c = ? 只用到 a;条件书写顺序颠倒不影响,优化器会重排。

场景四:前导模糊。LIKE '%x' 与 LIKE '%x%' 无法定位起点,LIKE 'x%' 可以。业务上确实需要前后模糊时,正确做法是换搜索引擎或维护一张检索表,而不是在 MySQL 上硬扛。

场景五:OR 混用。OR 要求两边都能走索引才可能用 index merge,只要有一边是普通列,大概率退化成全表扫。解决办法是两边都建索引,或拆成两条查询在业务层合并。

场景六:范围查询之后的列。(a, b) 索引上 WHERE a > 10 AND b = 1,a 走范围扫描后,b 在区间内不再全局有序,只能靠索引下推做过滤,不能缩小扫描区间。

场景七:join 的字符集不一致。两表关联字段一个 utf8 一个 utf8mb4,join 时需要转换,被驱动表的索引失效。这类问题最隐蔽,因为单表查询都正常,只有 join 才暴露。

优化器主动放弃:!=、NOT IN、IS NOT NULL 不是必然失效,取决于过滤后剩余的数据量与选择性。status 只有几个取值、又要取其中一大半时,走索引再回表比顺序扫更贵,优化器自然放弃。判断方法是 EXPLAIN 看 type、key、rows、filtered,必要时 EXPLAIN FORMAT=JSON 看 cost_info,统计信息不准时先 ANALYZE TABLE。

排查习惯:先 EXPLAIN 再怀疑写法,别急着加索引。很多慢查询的问题不在索引数量,而在索引列有没有被「包住」、参数类型对不对、统计信息是否过期。