面灵AI→

后端二面复盘:联合索引失效的四种情形与 EXPLAIN 判断顺序

轮次
二面
时间
2026-11
来源
牛客网

《面试题目》

给定建表语句与联合索引:

CREATE TABLE t_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  remark VARCHAR(200),
  KEY idx_user_status_time (user_id, status, created_at)
);
  1. where user_id = 100 and status > 0 and created_at > '2024-03-01',索引用到哪一列?
  2. where remark like '%退款%' 能不能走索引?
  3. where user_id = '100'(列是 BIGINT、参数是字符串)会不会失效?反过来,列是字符串、参数是数字呢?
  4. 「最左前缀」到底怎么理解?where status = 1 and user_id = 100 能走 idx_user_status_time 吗?
  5. where user_id = 100 and created_at > '2024-03-01' 为什么只用到一列?
  6. 联合索引的列顺序应该按什么排?
  7. where user_id = 100 or status = 1 会怎样?
  8. 线上写 where DATE(created_at) = '2024-03-01' 为什么慢?怎么改?
  9. 排查一条慢查询,你会按什么顺序看?

《参考解析》

范围条件之后的列断在定位上,不是断在过滤上:user_id = 100 是等值,可以在索引里定位到连续区间;status > 0 是范围,索引仍然能在该区间内按 status 顺序扫描;但 created_at 已经无法利用索引做进一步定位,只能靠扫描到的行回表后过滤。EXPLAIN 的 key_len 会如实反映用了哪几列(BIGINT 8 字节、TINYINT 1 字节、DATETIME 5 字节,允许 NULL 各加 1 字节)。MySQL 5.6 之后的索引条件下推(Using index condition)会把 created_at 的判断带到存储引擎层过滤,减少回表,但它不改变「用不上索引定位」这个事实——很多人在这一步把 ICP 误当成索引起作用。

隐式转换的方向决定生死:比较时 MySQL 会把两边转成同一类型。列是 BIGINT、参数是字符串 '100',转换发生在参数上,列本身仍是索引里的原值,索引可用;列是 VARCHAR、参数是数字,MySQL 会把列逐行转成 DOUBLE 再比,等价于对列做了函数运算,索引失效,且语义上 '100abc' 也会等于 100。判断口诀只有一句:转换落在参数上没事,落在列上就废。

最左前缀是「连续匹配」而不是「必须写出第一列」:优化器会重排等值条件的顺序,所以 status = 1 and user_id = 100 与反过来写等价,都能用到前两列。真正卡住的是跳过中间列——user_id = 100 and created_at > ... 缺了 status,只能用到 user_id。列顺序的经验法则是「等值列在前、范围列在后、排序列紧随」,因为范围列一旦出现,它后面的列就无法再参与定位。帖主把 status 挪到末尾后 key_len 变化,正是这个原因。

函数、表达式与前导模糊都会让索引白建:DATE(created_at) = '2024-03-01'、id + 1 = 100、substr(remark,1,2) = '退款' 都属于把列包进表达式;改写成半开区间 created_at >= '2024-03-01 00:00:00' and created_at < '2024-03-02 00:00:00' 即可走索引,MySQL 8.0 也可以建函数索引。like '%退款%' 前导通配无法利用 B+ 树的有序性,只能全扫;能改成 like '退款%' 就走索引,全文检索需求应交给全文索引或搜索引擎。or 只要有一侧无索引,整个条件就退化为全表扫描,可以拆成两条走索引的查询再 union all。

排查顺序比背条目有用:先 EXPLAIN 看 type(ALL 即全表)、key(实际用了哪个索引)、key_len(用到几列)、rows 与 filtered(预估扫描量与过滤率),不靠猜;再检查条件里有没有对列做运算或隐式转换;再看联合索引是否在中间列或范围列处断开;最后评估回表量,回表次数多且查询列少时考虑覆盖索引。线上还要结合慢日志与 performance_schema 确认是这条 SQL 而非锁等待、连接池耗尽导致的慢。