SQL索引失效六大场景详解与排查优化指南

-- 表 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，不一致就要小心了

解决方案有两个：

1. 统一字符集：这是根治之法，强烈推荐。
2. 临时方案：如果无法立即修改表结构，可以尝试将转换函数写在非索引列的一侧。例如，假设a表是utf8mb4，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字节字符，转换可能导致数据截断，这只应作为临时过渡方案。

翻车③：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）。但这个优化很“玄学”，严重依赖于具体的数据分布和优化器的成本估算，并不稳定可靠。

翻车④：LIKE 通配符在开头

-- 失效 ❌
SELECT * FROM user WHERE name LIKE '%张三%';
-- 有效 ✅
SELECT * FROM user WHERE name LIKE '张三%';

这背后的原理与B+树索引的有序性有关。'张三%'这种前缀匹配的查询，可以快速定位到索引中“张”字开头的位置，然后向后扫描。而'%张三%'这种前后都模糊的查询，由于无法确定前缀，优化器不知道从索引的哪个位置开始查找，只能退而求其次选择全表扫描。

MySQL 5.6引入的ICP（Index Condition Pushdown，索引条件下推）优化能稍微缓解这个问题：存储引擎层可以先用索引过滤掉一部分明显不匹配%张三%的行，减少需要回表检查的行数。此时EXPLAIN的Extra列会显示Using index condition。

但需要警惕：Using index condition不等于覆盖索引。Using index才代表覆盖索引，即所需数据可以直接从索引中获取，无需回表。对于SELECT *这类查询，不可能覆盖所有列。ICP只是一个“减速带”，并非“刹车”——查询的type很可能依然是ALL，该慢的还是慢。

翻车⑤：联合索引没用最左前缀

-- 联合索引：(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条件的顺序，只要你的查询条件里包含了最左列city，索引就依然有效。

翻车⑥：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的记录占了95%，那么查询status != 1（实际只取5%的数据）是有可能走索引的。但更稳妥的做法是，如果业务允许，尽量将否定查询改写为肯定的范围查询，例如用status IN (2,3,4,...)来代替status NOT IN (1)。不过要注意，这种方法高度依赖于数据分布，一旦数据比例发生变化，执行计划就可能改变，因此并不推荐作为常规方案。

?️ 排查工具箱：怎么证明索引失效了？

了解了上面六种场景，回到实际问题：怎么快速判断自己的SQL有没有踩坑？记住下面三个EXPLAIN的排查动作，即插即用：

1. EXPLAIN 看 type 列：ALL → 基本就是全表扫描
2. EXPLAIN 看 key 列：NULL → 索引完全没被用上
3. EXPLAIN FORMAT=JSON 看 used_key_parts 数组：为空 → 索引有名字但没真正用

面试一句话总结

面试时被问到“索引失效的场景有哪些”，其实不需要死记硬背上面六条。抓住一个核心逻辑就够了：索引失效，本质上是查询条件破坏了B+树索引的“最左匹配”和“有序定位”能力。

拿这个逻辑去套：

• 函数/类型转换：列的值被改了，B+树里找不到改完后的值。
• OR条件：需要合并多个不连续的范围，优化器觉得不如全扫。
• %like：不知道前缀是什么，无法在索引中定位起点。
• 跳过最左列：不知道从索引的哪一段开始翻。
• NOT IN：要取大部分数据，顺序扫描可能比回表更划算。

想通了“能不能从B+树左侧开始快速定位”这个根本，索引失效的问题就不再需要死记硬背，而是可以推理出来了。