← 课程目录 🚀 从零到一
📐 学习维度 阶段3 · 第9章 MySQL 🚀 从零到一 📚 课程目录 🏠 首页 🛤️ 学习路径

9.3 索引失效十大场景:明明建了索引却不走

审校 2026-09-25 · 素材来源:L16-MySQL索引深度(底稿改写)、个人笔记
📑 本篇章节导航 (共10节,点击直达)
十大场景速查表用一个例子看懂「隐式转换」OR、负向查询这些「半失效」一次真实排查:函数包列拖垮订单查询函数与运算为什么失效LIKE 左模糊与最左前缀是同一个道理怎么快速定位失效踩坑清单面试怎么问一句话总结

「这字段我建索引了,怎么 EXPLAIN 还是全表扫?」这是 SQL 优化的头号日常问题。索引失效场景能列十个,但底层原因其实只有一句话:索引的有序性被破坏了(B+ 树按原始值排序,你在查询里把它加工了,树就帮不上忙)。先把十大场景背熟,再理解共同根源。

背场景不如背分组。十个场景按根因分三类:列被加工(函数、运算、隐式转换、左模糊)——索引键不再是原值;索引用不上(最左前缀缺失、OR 连带、负向、NULL 判断)——锚点或命中率出了问题;优化器的选择(表太小、排序无索引)——它算完账主动放弃。面试按组展开,条理立刻出来。分组还有日常用途:代码评审看到 WHERE 条件先归类再判风险,比逐条背清单快得多;不少团队干脆把清单做成 SQL 静态扫描规则,提交时自动卡点,比人肉记忆靠谱。

十大场景速查表

#场景反例正确写法
1隐式类型转换WHERE phone = 13800001111(列是字符串)WHERE phone = '13800001111'
2函数计算WHERE YEAR(create_time) = 2026WHERE create_time >= '2026-01-01'
3LIKE 左模糊WHERE name LIKE '%张'LIKE '张%' 或交给搜索引擎
4OR 连了无索引列WHERE a=1 OR b=2(b 无索引)给 b 也加索引
5违背最左前缀联合索引(a,b) 只查 WHERE b=2条件带上 a
6负向查询WHERE status != 1WHERE status IN (2,3)
7IS NOT NULLWHERE col IS NOT NULL给默认值代替 NULL
8列参与运算WHERE id + 1 = 10WHERE 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、负向查询这些「半失效」

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 左模糊与最左前缀是同一个道理

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 是双刃剑,数据分布变了它不会自己调整,定位成「临时止血」,根治靠统计信息。

踩坑清单

面试怎么问

一句话总结

索引失效万变不离其宗:查询破坏了 B+ 树的有序性;排查先 EXPLAIN,改法永远是让列保持原样、把加工留给常量。