后端面试:索引失效的六个真实场景与判断方法
- 轮次
- 面试复盘
- 时间
- 2026-11
- 来源
- 牛客网
《面试题目》
- 索引在哪些情况下会失效?
- 为什么
WHERE order_no = 1001而order_no是 varchar 时会走全表扫描?反过来 int 列传字符串为什么没事? - 在索引列上套函数或做运算会怎样?怎么改写?
- 前导模糊匹配为什么用不上 B+ 树索引?靠覆盖索引能不能救回来?
- 联合索引的最左前缀是怎么断的?范围条件后面的列还能用吗?
OR两侧有一侧没有索引会怎样?怎么处理?- 有索引但优化器不用,是什么原因?怎么验证?
- 排查索引问题时应该看执行计划的哪几列?
《参考解析》
隐式类型转换是最隐蔽的一类。 order_no 是 varchar(32) 而参数传了数字时,MySQL 会把字符串列逐行转成数字再比较,等价于 CAST(order_no AS SIGNED) = 1001,列上套了函数,B+ 树的有序性用不上,索引自然失效。反方向(int 列传字符串)是安全的,因为常量会被转成数字,列本身保持「干净」。规则只有一条:参数类型必须与列类型对齐,尤其是长得像数字的编号列。这类问题的特征是 possible_keys 有值而 key 为 NULL。
列上做运算或套函数同理。 WHERE DATE(create_time) = '2026-03-11' 会让索引失效,改写成左闭右开的时间范围(>= 当天 00:00:00 AND < 次日 00:00:00)即可走 range;WHERE id + 1 = 100 改写成 WHERE id = 99。要区分的是函数作用在常量上(如 create_time >= NOW() - INTERVAL 7 DAY)——列是干净的,索引照样可用。判断口诀是「把计算挪到等号右边」。
前导模糊匹配无法用 B+ 树定位。 索引按前缀有序,LIKE 'abc%' 能定位到区间,LIKE '%abc' 与 LIKE '%abc%' 不能。覆盖索引可以让执行计划从 ALL 变成 index,但那是全索引扫描而非范围扫描,本质仍是扫一遍整棵索引树,数据量大时依然慢;真正的解法是全文索引或外部搜索引擎。
最左前缀断裂有两种形态。 建了 (a, b, c) 后:b = 2 AND c = 3 一列都用不上;a = 1 AND c = 3 只能用到 a,c 不参与定位(可能被索引下推在引擎层过滤,Extra 显示 Using index condition,但这不算用上索引定位);a > 1 AND b = 2 中 a 是范围条件,b 在索引里已无序,只能退化过滤。设计索引时把等值列放前面、范围列放最后;业务允许时把范围拆成 IN 列表可以让后续列重新参与排序与定位。
OR 要求两侧都能走索引才划算。 WHERE user_id = 1001 OR remark = 'urgent' 中 remark 无索引,优化器通常直接全表扫描;要么给 remark 建索引(低区分度时通常不值得),要么拆成两条查询用 UNION ALL 合并,再在应用层去重。
「优化器主动放弃」不是失效,是成本选择。 低区分度字段(状态只有两三个取值、某个值占九成数据)单独建索引时,走索引要回表大量行,优化器估算后认为顺序扫描更便宜,EXPLAIN 的 key 为 NULL 而 possible_keys 有值。FORCE INDEX 可以验证猜想,但线上写死索引在数据分布变化后会变成负担,只应作为临时验证手段。
排查方法固定为四步。 先看 EXPLAIN 的 type(ALL / index / range / ref / const)、key、rows、filtered,判断是「用不上」还是「不想用」;再用 SHOW WARNINGS 看优化器改写后的 SQL;需要成本细节时用 EXPLAIN FORMAT=JSON 看 cost_info;仍不清楚时打开 optimizer_trace 看优化器为何排除某个索引。回答面试题时先区分这两类原因,比背「!=、IS NOT NULL、NOT IN 都会失效」这类过度简化的结论更可靠——这些条件是否走索引取决于区分度与数据分布,说死反而露怯。