「这字段我建索引了,怎么 EXPLAIN 还是全表扫?」这是 SQL 优化的头号日常问题。索引失效场景能列十个,但底层原因其实只有一句话:索引的有序性被破坏了(B+ 树按原始值排序,你在查询里把它加工了,树就帮不上忙)。先把十大场景背熟,再理解共同根源。
背场景不如背分组。十个场景按根因分三类:列被加工(函数、运算、隐式转换、左模糊)——索引键不再是原值;索引用不上(最左前缀缺失、OR 连带、负向、NULL 判断)——锚点或命中率出了问题;优化器的选择(表太小、排序无索引)——它算完账主动放弃。面试按组展开,条理立刻出来。分组还有日常用途:代码评审看到 WHERE 条件先归类再判风险,比逐条背清单快得多;不少团队干脆把清单做成 SQL 静态扫描规则,提交时自动卡点,比人肉记忆靠谱。
| # | 场景 | 反例 | 正确写法 |
|---|---|---|---|
| 1 | 隐式类型转换 | WHERE phone = 13800001111(列是字符串) | WHERE phone = '13800001111' |
| 2 | 函数计算 | WHERE YEAR(create_time) = 2026 | WHERE create_time >= '2026-01-01' |
| 3 | LIKE 左模糊 | WHERE name LIKE '%张' | LIKE '张%' 或交给搜索引擎 |
| 4 | OR 连了无索引列 | WHERE a=1 OR b=2(b 无索引) | 给 b 也加索引 |
| 5 | 违背最左前缀 | 联合索引(a,b) 只查 WHERE b=2 | 条件带上 a |
| 6 | 负向查询 | WHERE status != 1 | WHERE status IN (2,3) |
| 7 | IS NOT NULL | WHERE col IS NOT NULL | 给默认值代替 NULL |
| 8 | 列参与运算 | WHERE id + 1 = 10 | WHERE id = 9 |
| 9 | 优化器放弃 | 表太小,走索引反而慢 | 数据量大自然恢复 |
| 10 | 排序无索引 | ORDER BY 非索引列 → filesort | 排序列加进索引 |
CREATE TABLE t (id INT PRIMARY KEY, phone VARCHAR(20), INDEX idx_phone(phone));
EXPLAIN SELECT * FROM t WHERE phone = 13800001111;
-- type=ALL:全表扫描!
EXPLAIN SELECT * FROM t WHERE phone = '13800001111';
-- type=ref:正常走 idx_phone
原因:phone 是字符串,拿数字比,MySQL 会把每一行的 phone 转成数字再比。等价于给列套了函数 CAST(phone AS 数值),索引的排序作废。反过来,数字列拿字符串查不会失效(常量被转换),方向要记清。
OR 的坑是条件触发:WHERE a=1 OR b=2,若两列都有索引,优化器可以做索引合并(分别走两棵索引树再合并结果集);但只要一侧没索引,「或」要求两侧结果的并集,走索引那侧也得逐行验证,优化器干脆全表扫。规矩:OR 两侧的列都要有索引,否则改写成 UNION ALL。
负向查询(!=、NOT IN)的失效是概率问题:status != 1 若留下九成数据,走索引反而更慢,优化器算完账自动放弃。改写思路是「负向改正向」——「不是 1」等价于「在 (2,3,4) 里」,IN 写法给了优化器走索引的机会。
IS NOT NULL 同理:大多为 NULL 的列上这个条件命中率极低,索引没意义。设计上的解法更彻底:给列默认值(空串、0),从根上消灭 NULL 判断。
某接口偶发超时,EXPLAIN 显示 type=ALL、rows 接近全表:
SELECT * FROM orders WHERE DATE(create_time) = '2026-09-24' AND channel = 'app';
-- DATE() 包住列 → create_time 索引报废
-- channel 有索引但区分度低,优化器也看不上
改写成范围查询后,type 回到 ref,rows 从百万级降到千级。套路三连:函数包列先拆函数;低区分度列并进联合索引;改完必复测。这是 9.6 排查流水线里「改写 SQL」环节的标准动作。
B+ 树按 create_time 原值排序。YEAR(create_time) 的结果在树里不是有序的,只能逐行算。同理 id + 1 = 10、SUBSTR(name,1,2) 都是对列加工。改法永远是同一个思路:加工常量,别加工列。
-- ❌ 列上套函数
SELECT * FROM orders WHERE YEAR(pay_time) = 2026;
-- ✅ 改成范围,索引的有序性直接可用
SELECT * FROM orders
WHERE pay_time >= '2026-01-01' AND pay_time < '2027-01-01';
LIKE '张%' 等价于范围查询 [张, 张+1),树还有序可用;'%张' 开头不定,任何值都可能命中,等于没条件。这与联合索引的最左前缀是同一个原理:锚点没了,有序性就使不上劲。
顺带两个易漏点。其一,'%张%' 双侧模糊同样失效,但业务偏偏常要搜名字中间的字——这类需求的正解是搜索引擎(分词加倒排索引,等学到 Elasticsearch 那篇再展开),而不是为难 MySQL,知道边界在哪比硬优化更专业。其二,ORDER BY LENGTH(name) 这种排序键套表达式同样触发 filesort,道理和 WHERE 里函数包列一模一样:参与排序的不是原始列。
怀疑索引失效,别猜,直接看执行计划(EXPLAIN 输出的查询路线图):
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- 关键三列:
-- type:ALL 全表扫描要警惕;ref/range 是走了索引
-- key:实际用的索引,NULL 就是没用上
-- rows:预估扫描行数,越少越好
Extra 列同样信息量大:Using index 是覆盖索引(好),Using filesort 是额外排序(差),Using temporary 是临时表(差)。系统排查方法在 9.6 展开。
排查顺序建议固定成四步:看 type 确认走没走索引 → 看 key 是不是预期的那棵树 → 看 rows 和真实行数是不是一个量级 → 最后看 Extra 有没有 filesort 和 temporary。四步下来,失效清单里对应哪一项基本浮出水面,把这套动作练成肌肉记忆,慢 SQL 到手几分钟就能定位病因。
还要知道优化器不是神:统计信息过期(rows 估错)、数据分布倾斜(某状态占九成),都可能让它选错索引——症状是新加了索引 EXPLAIN 却不认,rows 高得离谱。两招:ANALYZE TABLE 重算统计信息;确需干预时 FORCE INDEX 显式指路。但 FORCE 是双刃剑,数据分布变了它不会自己调整,定位成「临时止血」,根治靠统计信息。
FORCE INDEX 指路!= 干掉整棵树:负向条件命中率低时,优化器直接放弃索引索引失效万变不离其宗:查询破坏了 B+ 树的有序性;排查先 EXPLAIN,改法永远是让列保持原样、把加工留给常量。