「你背的很多条其实都是错的」:索引失效的七个场景与优化器成本
- 时间
- 2026-11
- 来源
- 牛客网
《面试题目》
SELECT id, order_no, status FROM orders WHERE DATE(created_at) = '2024-03-01'这条语句有什么问题?- 那你具体说说什么情况下索引会失效?
- 隐式类型转换到底转了什么?为什么转换之后就用不上索引?
- 索引列上做函数运算为什么会失效?怎么改写?
- 联合索引 (a, b, c) 里,
WHERE b = ? AND c = ?和WHERE a = ? AND c = ?分别能用上哪些列? LIKE '%关键词'为什么用不上索引?业务上真要前后模糊怎么办?WHERE a = 1 OR b = 2一定会全表扫吗?- 范围查询后面的列为什么用不上?索引下推能补救吗?
- 两张表 join,单独查都快、一 join 就慢,可能是什么原因?
!=、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 再怀疑写法,别急着加索引。很多慢查询的问题不在索引数量,而在索引列有没有被「包住」、参数类型对不对、统计信息是否过期。