后端实习二面:一条慢查询引出的索引失效三类场景
- 轮次
- 二面
- 时间
- 2026-11
- 来源
- 牛客网
《面试题目》
- 这条 SQL 你加了索引,为什么还是全表扫描?
- 索引在哪些情况下会失效?
- 索引列上套函数或做运算为什么会失效?怎么改写?
- 隐式类型转换是怎么让索引失效的?为什么本地测试没暴露?
- 联合索引
(status, create_time, user_id)下,WHERE status = 1 AND create_time > ? AND user_id = ?能用上几列? LIKE '%abc'为什么不走索引?OR连接了没有索引的列会怎样?怎么改写?- 明明建了索引,优化器却不用,是什么原因?
- 排查这类问题应该看执行计划的哪些字段?
《参考解析》
第一类:索引列被「加工」过。 B+ 树里存的是索引列的原始值,一旦在列上套函数或做运算,有序性就用不上了。WHERE DATE(create_time) = '2024-05-01' 改写成 create_time >= '2024-05-01' AND create_time < '2024-05-02';WHERE id + 1 = 100 改写成 WHERE id = 99。更隐蔽的是隐式类型转换:user_no 是 varchar(32) 却写成 WHERE user_no = 1001,MySQL 会把字符串列转成数字再比较,等价于在列上套了 CAST,索引直接失效。这类问题在本地往往测不出来——测试数据恰好都是纯数字,看着完全正常,直到有带字母前缀的新数据进来才暴露。判断特征是 possible_keys 有值而 key 为 NULL。
第二类:联合索引的最左前缀被范围查询截断。 建了 (status, create_time, user_id) 后:WHERE status = 1 用到 status;WHERE create_time > ? 不走索引(最左列缺失);WHERE status = 1 AND user_id = ? 只能用到 status;WHERE status = 1 AND create_time > ? AND user_id = ? 用到 status 与 create_time,user_id 用不上——因为范围条件之后的列在 B+ 树里已经无序,无法继续定位。这里容易犯的错误是「只要字段出现在 WHERE 里就算用上索引」。验证方法是看 EXPLAIN 的 key_len:它比按全列估算的长度短一截,就说明只用到了前几列。
索引条件下推(ICP)容易被误当成「用上了索引」。 MySQL 5.6 之后,上面第四条里的 user_id 虽然不能用于定位,但可以在存储引擎层先过滤、减少回表次数,Extra 显示 Using index condition。严格说这不算用上索引定位,回答时最好把两者分开讲,否则会被追问到答不上来。
第三类:前置通配符的 LIKE,以及 OR 里的「猪队友」。 LIKE 'abc%' 能走索引,因为前缀有序;LIKE '%abc' 与 LIKE '%abc%' 不行。OR 连接的字段只要有一个没有索引,整个条件就可能退化成全表扫描,例如 WHERE status = 1 OR remark = 'x',优化器大概率放弃 status 上的索引。实在要写这种 SQL,可以拆成两条查询在应用层合并,或给 remark 也建索引让优化器走 index merge。
第四类不是失效,是优化器「不想用」。 某个状态字段只有两三个取值,单独建索引后 EXPLAIN 仍然是全表扫描——不是索引坏了,而是区分度太低,走索引要回表大量行,代价比顺序扫描整张表更高,优化器算完成本主动放弃。低区分度的列不该单独建索引,放进联合索引做前缀才有意义;线上突发时可以用 FORCE INDEX 验证猜想,但不要长期写在代码里,数据分布一变原来的索引又会变成负担。
回答的组织方式比清单更重要。 先讲本质——索引失效要么是「B+ 树的有序性用不上」(硬性失效),要么是「代价太高,优化器主动放弃」(成本性失效);再分三类展开;最后说明验证手段:EXPLAIN 看 type、key、key_len、rows,必要时用 optimizer_trace 看优化器为什么排除某个索引。作者复盘时说得很直白:面试官在意的不是能背几条结论,而是会不会用执行计划证明自己的判断。