后端二面复盘:联合索引失效的四种情形与 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)
);
where user_id = 100 and status > 0 and created_at > '2024-03-01',索引用到哪一列?where remark like '%退款%'能不能走索引?where user_id = '100'(列是 BIGINT、参数是字符串)会不会失效?反过来,列是字符串、参数是数字呢?- 「最左前缀」到底怎么理解?
where status = 1 and user_id = 100能走idx_user_status_time吗? where user_id = 100 and created_at > '2024-03-01'为什么只用到一列?- 联合索引的列顺序应该按什么排?
where user_id = 100 or status = 1会怎样?- 线上写
where DATE(created_at) = '2024-03-01'为什么慢?怎么改? - 排查一条慢查询,你会按什么顺序看?
《参考解析》
范围条件之后的列断在定位上,不是断在过滤上: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 而非锁等待、连接池耗尽导致的慢。