百日打怪升级 · 第25天 · 索引失效的典型场景
前两天有个哥们儿私信我，语气挺急的：
"老司机救命——我给查询条件字段建了索引，explain 也显示 possible_keys，但 type 是 ALL，这什么情况？"
我让他把 SQL 发过来一看，大概长这样：
SELECT * FROM user WHERE DATE(create_time) = '2026-05-20';
create_time 字段上有索引，但查的时候包了一层 DATE() 函数。
索引不是没用，是他写了个让 MySQL 没法用索引的写法。
这种事我见得太多次了。面试里头"索引失效的场景"也是高频考点——背结论谁都会，但落到自己代码里该踩的坑一个没少。今天干脆把最常见的 6 种翻车现场摆出来，每个都带复现 SQL，你对着跑一遍，下次就不慌了。
翻车①：在索引列上用了函数
-- 失效 ❌
SELECT * FROM user WHERE DATE(create_time) = '2026-05-20';
-- 有效 ✅
SELECT * FROM user WHERE create_time >= '2026-05-20 00:00:00'
AND create_time < '2026-05-21 00:00:00';
为什么？ B+ 树里存的是原始值，不是 DATE() 之后的结果。你在条件里对列做计算，MySQL 只能把每个值算出来再比——全表扫描。
MySQL 8.0.13 之后支持函数索引，可以建 CAST(create_time AS DATE) 的虚拟列索引来绕过。但说实话，改查询写法比建函数索引更优雅——不挑版本、不占额外空间。
同样的坑还包括：YEAR(create_time)、MONTH(create_time)、LENGTH(name)、ABS(amount)……只要列出现在函数参数位置，索引就废。
翻车②：隐式类型转换
-- 假设 phone 字段是 VARCHAR 类型，建有索引
-- 失效 ❌
SELECT * FROM user WHERE phone = 13800138000;
-- 有效 ✅
SELECT * FROM user WHERE phone = '13800138000';
为什么？ MySQL 遇到字符串列跟数字比，会把字符串转成数字。一旦转换发生在索引列上——跟翻车①一样，索引失效。
最蛋疼的是：MySQL 不报错。查询能正常返回结果，你根本意识不到它走了全表扫描。
用 EXPLAIN 看一眼 Extra 列，如果出现 Using where; Using index 可能还在用索引，如果直接 type = ALL，恭喜踩坑。
判断技巧：
SELECT '13800138000' = 13800138000返回 1 说明字符串被转成了数字，索引大概率会失效。
还有一个贼隐蔽的兄弟：JOIN 时字符集不一致。
-- 表 A（utf8mb4） JOIN 表 B（latin1）
-- 关联字段都有索引，但 JOIN 就是慢
SELECT * FROM a INNER JOIN b ON a.user_id = b.user_id;
为什么？ 两张表的字符集不同，MySQL 会自动把低优先级（latin1）转成高优先级（utf8mb4）。如果转换发生在被驱动表的索引列上——索引失效，全表扫描。
最坑的是：EXPLAIN 看不出来。rows 显示正常，Extra 也没警告。得用 SHOW WARNINGS 才能看到隐式转换信息。
判断方法：
SHOW CREATE TABLE a; -- 看 CHARSET 和 COLLATE
SHOW CREATE TABLE b; -- 看 CHARSET 和 COLLATE，不一样就要小心
判断技巧：
EXPLAIN看不出来这个坑，得SHOW WARNINGS才能看到"Converting character set"的隐式转换信息。
解决方案：
- 统一字符集——从根上解决，强烈推荐。
- 无法马上改表，临时硬扛时，把函数写在非索引的那一侧：
-- 假设 a 表 utf8mb4（驱动表，a.user_id 不一定是索引）
-- b 表 latin1（被驱动表，b.user_id 有索引）
-- 正确写法：函数写在非索引列上，索引列保持干净 ✅
SELECT * FROM a INNER JOIN b ON CONVERT(a.user_id USING latin1) = b.user_id;
-- 错误写法：函数写在索引列上 = 索引报废 ❌
SELECT * FROM a INNER JOIN b ON a.user_id = CONVERT(b.user_id USING utf8mb4);
CONVERT(a.user_id USING latin1) 把 a 的值转成 latin1 再去匹配 b，b.user_id 的索引不受影响。但注意：latin1 不支持 emoji 等 4 字节字符，转换可能截断数据，只应作为临时方案。
这种坑通常在老系统迁移时出现——旧表 latin1，新表 utf8mb4，JOIN 一把梭，全员从头扫。
翻车③：OR 条件
-- 假设 id 有索引，name 也有索引
-- 可能失效 ❌
SELECT * FROM user WHERE id = 1 OR name = '张三';
-- 有效 ✅
SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE name = '张三';
为什么？ OR 的语义是"任意一个条件匹配就行"，MySQL 必须把两个条件的结果合并。问题是：OR 两边的字段如果只有部分有索引，或者优化器觉得分别走索引再合并比全表扫描还慢，它就干脆不走索引了。
MySQL 5.0+ 引入了 Index Merge 优化，在某些情况下 OR 条件也能走索引（type = index_merge）。但这事儿很玄学——取决于数据分布、成本估算。
实操判断：
EXPLAIN看到type=index_merge且rows很小 → MySQL 已经自动优化好了，不用改。看到type=ALL→ 立刻上UNION，别犹豫。
翻车④：LIKE 通配符在开头
-- 失效 ❌
SELECT * FROM user WHERE name LIKE '%张三%';
-- 有效 ✅
SELECT * FROM user WHERE name LIKE '张三%';
为什么？ B+ 树的索引是有序的。'张三%' 能定位到"张三"开头的范围，但 '%张三%' 不知道从哪开始找——因为前缀是模糊的。
但 MySQL 5.6+ 的 ICP（Index Condition Pushdown，索引条件下推） 能稍微缓解一下：存储引擎层用索引先过滤掉一部分明显不匹配 %张三% 的行，减少回表行数。Extra 列会显示 Using index condition。
注意：Using index condition 不是覆盖索引。 Using index 才是覆盖索引（所有数据直接从索引拿，不用回表）。你的查询是 SELECT *，不可能覆盖索引所有列。ICP 只是减速带，不是刹车——type 依然是 ALL，该慢还是慢。
实际业务中
'%关键词%'是搜索刚需。别指望 MySQL 硬扛——上 Elasticsearch 或者至少用FULLTEXT索引的MATCH ... AGAINST。
翻车⑤：联合索引没用最左前缀
-- 联合索引：(city, age, name)
-- 有效 ✅
SELECT * FROM user WHERE city = '北京' AND age = 25;
-- 有效 ✅
SELECT * FROM user WHERE city = '北京' AND name = '张三'; -- city 走了索引，name 走不了
-- 失效 ❌
SELECT * FROM user WHERE age = 25 AND name = '张三'; -- 跳过了 city
为什么？ 联合索引就像一本电话黄页——先按城市排，城市里再按年龄排，年龄里再按姓名排。你直接跳过城市查年龄和姓名，相当于让人在整本黄页里翻——翻不了。
最左前缀原则：查询条件必须从联合索引的最左列开始，不能跳过中间的列。
还有一种隐蔽的翻车：写成 WHERE age = 25 AND city = '北京'——你以为顺序反了会失效？不会。 MySQL 优化器会帮你重排 WHERE 条件的顺序，只要条件里包含了最左列就行。
翻车⑥：NOT IN / != / <>
-- 失效 ❌
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE status <> 1;
SELECT * FROM user WHERE status NOT IN (1, 2);
-- 某些情况下有效（看数据分布）✅
SELECT * FROM user WHERE status IN (2, 3, 4);
为什么？ 索引的目的是快速定位少量数据。NOT IN 和 != 的语义是"排除少数，取剩下的大部分"，对优化器来说，全表扫描比走索引再合并效率更高——因为回表的随机 IO 成本可能比顺序扫描还高。
如果 status = 1 只占数据的 5%，改成 SELECT * FROM user WHERE status IN (2,3,4,5,...) 且选择性够高，索引是能用的。但实际中不建议依赖这个——数据分布一变，执行计划就变了。
🛠️ 排查工具箱：怎么证明索引失效了？
看完上面 6 种场景，回到你自己的 SQL 怎么查？三个 EXPLAIN 排查动作，即插即用：
1. EXPLAIN 看 type 列：ALL → 基本就是全表扫描
2. EXPLAIN 看 key 列：NULL → 索引完全没被用上
3. EXPLAIN FORMAT=JSON 看 used_key_parts 数组：为空 → 索引有名字但没真正用
留个作业：找一条你觉得"应该走索引但好像没走"的业务 SQL，跑一遍上面三步。评论区告诉我你发现了什么。
面试一句话总结
面试官问"索引失效的场景有哪些"，不需要背 6 条。记住这一句话就够了：
索引失效的本质就一个原因——MySQL 无法从 B+ 树的左侧开始，精确地定位到你要的那一小段数据。
逐一对照：
- 函数/类型转换 → 列的值被改了，B+ 树里找不到改完的值
- OR → 要合并多个范围，不如全扫
%like→ 不知道前缀是什么，无法定位起点- 跳过最左列 → 不知道从哪一段开始翻
- NOT IN → 取大部分数据，不如全扫
把"能不能从 B+ 树左侧开始定位"这个逻辑想通了，索引失效根本不用背。
下期预告：覆盖索引与最左前缀原则 —— 同样是联合索引，为什么你的查询 extra 显示 Using index，我的显示 Using where？
