面灵AI→

索引失效别背清单:隐式转换、联合索引与优化器的成本判断

时间
2026-11
来源
牛客网

《面试题目》

  1. 聊聊索引失效的场景。
  2. 这些场景你实际遇到过哪个?怎么确认的?
  3. 联合索引 (a, b, c),WHERE b = 2 AND c = 3 走不走?WHERE a = 1 AND c = 3 呢?
  4. 范围查询后面的列还能用上索引吗?
  5. 索引列上套函数、做运算、前导模糊,本质是什么?
  6. OR 一定会导致索引失效吗?
  7. status 这种区分度低的列建了索引,为什么还是全表扫?
  8. 重新回答这道题,你会怎么组织?

《参考解析》

隐式转换的实证:订单表里 order_no 是 varchar(32),代码里用数字接参,生成 WHERE order_no = 1001。EXPLAIN 显示 type=ALL、key=NULL,扫了整张表;改成 order_no = '1001' 之后 type=ref、key=idx_order_no,rows 从五位数降到 1。原因是对字符串列与数字比较时,MySQL 会把列转成数字,等于在索引列上套了函数。这个坑在测试环境数据量小时完全无感,只有上线后表变大才暴露。

联合索引要区分程度:WHERE b = 2 AND c = 3 跳过最左列,整个联合索引用不上;WHERE a = 1 AND c = 3 只用到 a,c 用不上——不是「索引废了」,而是「用了一部分」。范围查询之后的列同理:WHERE a > 1 AND b = 2 里 a 做范围扫描,b 通常不能继续用于精确定位,但可能参与索引下推过滤。回答时不要一刀切说「失效」,说清用到哪一列、key_len 是多少,更专业。

「索引列被包住」是一类问题:DATE(created_at) = '2024-01-01'、id + 1 = 100、LIKE '%1001'、LEFT(order_no, 6) = 'abc123' 本质相同——查询条件不是直接比较列值,而是比较列值经过变换后的结果,B+ 树的有序性用不上。改写方向是把变换挪到常量侧:日期用左闭右开区间,id + 1 = 100 改成 id = 99,前缀匹配用 LIKE '1001%'。

OR 与优化器成本:OR 两边都有索引时可能走 index merge,只有一边有索引时容易全表扫。更值得注意的是「索引有效但优化器不用」:status 只有几个取值、查询又要取其中一大半,走二级索引再回表比顺序扫更贵,优化器主动放弃。帖主遇到过一次统计信息长期未更新导致的错误选择,ANALYZE TABLE 之后才恢复。

验证方法固定成三步:先 EXPLAIN 看 type、key、rows、filtered、Extra,再判断是写法问题还是成本问题,最后才决定改 SQL 还是改索引。Extra 里的 Using index 表示覆盖索引、Using index condition 表示索引下推、Using filesort 表示排序没走索引,这三个标记比 key 是否有值更有信息量。

回答模板:规则层面用不上(隐式转换、函数运算、前导模糊、不满足最左前缀);优化器主动放弃(区分度低、回表太多、统计信息不准);确认方式统一走 EXPLAIN。把「失效」拆成这两类,比背十条清单更接近真实排查。