索引失效大全：记住这一条主线，推导出所有失效场景

前言

“我明明建了索引，EXPLAIN 一看却是全表扫描，索引怎么就失效了？”

这大概是 MySQL 里最让人头疼的问题之一。上网一搜，清一色是"索引失效的 15 种场景"——函数、隐式转换、最左前缀、!=、LIKE '%x'……列一大串，背完就忘，遇到新写法又懵了。

这篇文章想换个讲法：这些场景根本不用背。 所有索引失效，追到底只有两条主线。把这两条主线理解透，你不但能解释已知的十几种场景，还能自己推导出没见过的新情况。

先引一条《阿里巴巴 Java 开发手册》里的**【推荐】**规约作为锚点：

【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
反例：explain 结果，type=index，索引物理文件全扫描，速度非常慢。

索引失效，说白了就是 type 掉到了 range 以下（index 或 ALL）。为什么会掉？就看这两条主线。

环境说明：本文基于 MySQL 8.0，存储引擎 InnoDB。继续复用前几篇的 orders 表（100 万行）。表上有联合索引 idx_user_status(user_id, status)、普通索引 idx_created_at(created_at)、唯一索引 uk_order_no(order_no)。

一、先给地图：失效的两条主线

别急着看场景，先记住这张地图。所有索引失效，非此即彼：

第一类：破坏了索引的有序性 —— “不能用”

索引（B+ 树）的全部本事，来自它是有序的。一旦你的写法让 MySQL 没法利用这个有序性去"定位一个点"或"扫一段连续区间"，索引在结构上就用不了了。函数、隐式转换、最左前缀、LIKE '%x' 都属于这一类。

第二类：结果集占比太大 —— “不划算”

索引能用，但优化器算了一笔账：走索引要一条条回表取数据，如果命中的行占了全表一大半，那还不如直接全表顺序扫。于是它主动放弃索引。!=、OR、IS NOT NULL 都属于这一类。

两类的本质区别：

• 第一类是 “不能用”——索引结构上就没法走，和数据多少无关。
• 第二类是 “不选用”——索引能走，只是优化器嫌不划算，和结果集占比强相关。

后面每讲一个场景，我都会标它属于哪一类。带着这张地图往下看。

二、快速回顾：索引凭什么快

要理解"失效"，得先知道索引"生效"时靠的是什么。一句话：靠有序。

InnoDB 的索引是 B+ 树，叶子节点上的值从小到大排好序，并用链表连起来。正因为有序，它才能干两件快事：

• 定位一个点（= 88888）：像查字典，二分直达。
• 扫一段连续区间（> 88888、BETWEEN）：定位到起点，顺着有序链表往后连续读。

另外补充一个后面要用的概念：二级索引的叶子只存索引列 + 主键值，要取其他列得拿主键回表去聚簇索引查（详见上一篇《为什么不要用 SELECT *》）。命中越多行，回表越多次——这正是第二类失效的成本来源。

记住这两点：有序性是第一类的命根，回表成本是第二类的账本。

三、第一类·破坏有序性："不能用"的失效

这一类的共同特征：你的写法让索引列的"原始有序值"派不上用场，B+ 树的有序性被架空。

3.1 索引列上用函数或运算

created_at 上有索引，但这样写会失效：

EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2024-06-01';

+----+--------+------+---------------+------+---------+------+---------+-------------+
| id | table  | type | possible_keys | key  | key_len | rows    | filtered | Extra       |
+----+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | orders | ALL  | NULL          | NULL | NULL    | 1000000 |   100.00 | Using where |
+----+--------+------+---------------+------+---------+------+---------+-------------+

key = NULL、type = ALL，全表扫描。列上做运算也一样，比如 WHERE amount + 10 > 100。

为什么失效（原理）： 索引树里存的是 created_at 的原始值（如 2024-06-01 13:24:07），它们是按原始值排好序的。而 DATE(created_at) 是算出来的新值，这些新值并不存在于索引树里，树也不是按它们排序的。MySQL 要拿到 DATE() 的结果，只能把每一行都取出来现算一遍——这就绕开了索引，退化成全表扫。

怎么救：

• 改写成范围查询，让索引列保持"裸露"：

  WHERE created_at >= '2024-06-01 00:00:00' AND created_at < '2024-06-02 00:00:00'
  

• MySQL 8.0 支持函数索引，可以直接给 DATE(created_at) 建索引：ALTER TABLE orders ADD INDEX idx_date ((DATE(created_at)));

3.2 隐式类型转换

order_no 是 VARCHAR，用数字去查：

EXPLAIN SELECT * FROM orders WHERE order_no = 123456;   -- 数字，没加引号
-- key = NULL, type = ALL，失效

为什么失效： 字符串列和数字比较时，MySQL 会把每一行的 order_no 都转成数字再比。这个"转换"等价于在列上套了个 CAST() 函数——于是就退化成了 3.1 的情况，本质是同一类。加上引号 = '123456' 即可恢复走索引。

类似地，两张表 JOIN 时如果关联列的字符集或排序规则不一致，也会触发隐式转换导致索引失效，排查时容易被忽略。

3.3 违反最左前缀

联合索引 idx_user_status(user_id, status)，跳过第一列直接查第二列：

EXPLAIN SELECT * FROM orders WHERE status = 2;
-- key = NULL, type = ALL，失效

但只要带上最左列，就能用：

EXPLAIN SELECT * FROM orders WHERE user_id = 88888;            -- 用上索引
EXPLAIN SELECT * FROM orders WHERE user_id = 88888 AND status = 2;  -- 两列都用上

为什么失效： 联合索引是先按第一列 user_id 排序，user_id 相同的行之间再按 status 排序。所以整棵树对 user_id 是全局有序的，但对 status 只是"局部有序"。你不给 user_id 条件，直接找 status = 2，这些行零散分布在整棵树里，没有连续性可言，索引自然用不上。

这就像字典按"拼音首字母 + 第二个字母"排序：你可以查"所有 a 开头的"，但没法直接查"第二个字母是 b 的"——它们散落在 ab、bb、cb… 各处。

3.4 范围查询中断后续列

这是最左前缀的"变体"，更隐蔽。联合索引 (user_id, status)：

EXPLAIN SELECT * FROM orders WHERE user_id > 88888 AND status = 2;

| type  | key             | key_len | Extra       |
| range | idx_user_status | 8       | Using where |

key_len = 8（只有 user_id 的 8 字节），说明只有 user_id 用上了索引，status 没有。

为什么失效： user_id > 88888 是范围查询，命中的是一大段 user_id 各不相同的行。在这段范围里，status 是乱序的（只有 user_id 相同时 status 才有序）。所以 status = 2 没法借助索引的有序性，只能作为 Using where 在结果里逐行过滤。

这就是"范围列右边的列，索引失效"的真相，和最左前缀是同一个道理：范围一旦介入，它右边的列就丧失了有序性。

3.5 LIKE 以通配符开头

EXPLAIN SELECT * FROM orders WHERE order_no LIKE '%888';   -- 失效, ALL
EXPLAIN SELECT * FROM orders WHERE order_no LIKE 'NO88%';  -- 生效, range

为什么： B+ 树是按字符串的前缀排序的（先比第一个字符，再比第二个……）。'NO88%' 给了明确的前缀 NO88，能定位到"以 NO88 开头"的那一段连续区间。而 '%888' 没有前缀锚点——你不知道开头是什么，所有值都可能匹配，只能挨个全表比对。

同样是字典的类比：你能快速翻到"以 abc 开头"的词，但要找"包含 abc"的词，只能一页页翻。

四、第二类·占比太大："不划算"的失效

这一类的关键词是结果集占比。索引能用，但命中的行太多，优化器算下来觉得走索引+回表不如全表扫，于是主动放弃。

4.1 != / NOT IN / <>

EXPLAIN SELECT * FROM orders WHERE user_id != 88888;
-- key = NULL, type = ALL

!= 要的是"除了 88888 以外的所有值"，几乎是整张表。走索引反而要扫几乎全部索引项还得逐行回表，不如全表顺序扫。

这一场景的完整推导（为什么是"两段+挖空"、什么时候 != 又能走索引）见上一篇《为什么 != 和 NOT IN 会让索引失效》，这里只归类：它属于占比太大这一类。

4.2 OR 连接非索引列

EXPLAIN SELECT * FROM orders WHERE user_id = 88888 OR amount = 500;
-- key = NULL, type = ALL

user_id 有索引，但 amount 没有。OR 要求两个条件任一满足即可，只要有一边（amount）没法用索引，就必须全表扫才能不漏行——于是整条 SQL 退化成全表扫。

例外： 如果 OR 两边都有各自的索引，优化器可能用 index_merge（索引合并），分别走两个索引再取并集。所以 OR 不必然失效，关键看两边是不是都有索引。

4.3 IS NOT NULL 与低选择性条件

EXPLAIN SELECT * FROM orders WHERE created_at IS NOT NULL;

如果绝大多数行的 created_at 都非 NULL，那这个条件几乎命中全表，优化器同样倾向全表扫。反过来，如果只有极少数行非 NULL，它又会走索引。

这恰好说明第二类的本质：失效与否不取决于用了什么运算符，而取决于最终命中的行占全表多大比例。

4.4 收口：第二类是"成本选择"，不是"不能用"

第二类和第一类有个根本区别：第一类是结构上没法走，第二类是能走但优化器不选。

判断依据就是 EXPLAIN 里的 rows 和 filtered：优化器估算命中行数占比，占比小就走索引，占比大就全表扫。所以：

• 同一个 !=，在"排除的值占 1%"的表上可能走索引，在"排除的值占 99%"的表上就失效。
• 这也是为什么 possible_keys 有索引、key 却是 NULL——索引可用，但优化器没选它。

记住：第二类失效永远和数据分布绑定，脱离占比谈"某写法是否失效"都是耍流氓。

五、还有一类"伪失效"：其实走了，是你没看懂

有些情况你以为索引失效了，其实它好好地走着，只是表象骗了你。

1. type = index 不是"用好了索引"，但也不是"没走索引"。 它是全索引扫描——把整棵索引树从头扫到尾。比全表扫（ALL）稍好（因为索引树比整表小、且可能覆盖索引免回表），但依然要警惕。别一看到 index 就以为万事大吉，也别误以为它等于失效。

2. 数据量太小，优化器故意不走索引。 测试库里表只有几十行，EXPLAIN 经常显示 ALL。因为数据太少，走索引的开销（定位+回表）比直接全表扫还大，优化器很理性地选了全表扫。这不是失效，换到百万行的生产库上它就走索引了。 别用小表的 EXPLAIN 结果下结论。

3. 统计信息过期，优化器"看走眼"。 优化器靠索引统计信息（cardinality）估算成本，如果数据变动大又没更新统计，它可能估错、选错索引。执行 ANALYZE TABLE orders; 重新统计后往往就正常了。

破除这三种误判，能省掉大量"我加了索引怎么没走"的无效焦虑。

六、实战：一套排查索引失效的固定流程

遇到疑似失效，别猜，按这三步走：

Step 1 — EXPLAIN 看四个字段

• key：是不是 NULL？为 NULL 才是真没走索引。
• type：到没到 range？ALL/index 要警惕。
• key_len：联合索引到底用了几列（对照 3.4）。
• Extra：Using where（回表过滤）、Using index（覆盖索引）等信号。

Step 2 — 判断属于哪一类

• possible_keys 为 NULL → 大概率第一类（结构上就没索引可用，如函数、最左前缀）。
• possible_keys 有值但 key 为 NULL → 第二类（索引可用但优化器嫌不划算）。
• 表很小 / 刚导完数据 → 怀疑伪失效，先 ANALYZE TABLE。

Step 3 — 对症下药

• 第一类：去掉列上的函数/转换、补齐最左列、调整联合索引顺序、LIKE 别用前导 %。
• 第二类：改写条件降低占比（!= 换正向 IN）、用高选择性条件先缩小范围、必要时 FORCE INDEX。

综合案例： 一条"查某用户最近未完成订单"的 SQL：

EXPLAIN SELECT * FROM orders
WHERE DATE(created_at) = '2024-06-01'      -- ① 函数，第一类失效
  AND user_id != 88888                      -- ② !=，第二类
ORDER BY status;

排查：key = NULL。定位到 ① 的 DATE() 是主凶（第一类，结构失效），改成范围查询后 created_at 的索引恢复；② 的 != 若占比大则本就该全表，考虑业务上能否改成正向条件。改写后：

SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02'
  AND user_id != 88888
ORDER BY status;
-- 现在能走 idx_created_at 的 range，把范围先缩小，!= 只在小结果集里过滤

七、常见误区与面试高频问答

Q：索引失效是不是就是"没建对索引"？

多数情况不是。索引建得没问题，是用的方式让它失效了（函数、最左前缀、!=…）。先怀疑 SQL 写法，再怀疑索引设计。

Q：!= 一定会导致索引失效吗？

不一定。它属于"占比太大"这一类，取决于结果集占比。占比小时照样走索引。详见《为什么 != 和 NOT IN 会让索引失效》。

Q：怎么强制走索引？有风险吗？

SELECT * FROM orders FORCE INDEX(idx_created_at) WHERE ... 可以强制。但要慎用——优化器不走某索引，通常是它算过成本觉得不划算，强制可能更慢。FORCE INDEX 是"确信优化器选错了"时的最后手段，不是常规操作。

Q：为什么 EXPLAIN 显示走了索引，SQL 还是慢？

走了索引不等于快。可能是命中行数太多导致回表次数巨大，或 range 的范围过大。看 rows 估算和 Extra，必要时用 EXPLAIN ANALYZE 看真实耗时。

Q：函数索引、前缀索引能解决哪些失效？

• 函数索引（8.0+）：解决 3.1 的函数失效，直接给 DATE(created_at) 这类表达式建索引。
• 前缀索引：对长字符串列取前 N 个字符建索引，省空间，但注意它无法用于覆盖索引和排序。

总结

索引失效不用背清单，就记这张地图：

• 第一类·破坏有序性（不能用）：函数/运算、隐式转换、违反最左前缀、范围中断后续列、LIKE '%x'。共同点是让索引的有序性失效，索引结构上没法走。
• 第二类·占比太大（不划算）：!=/NOT IN、OR 连非索引列、IS NOT NULL。共同点是命中行占比太大，优化器主动放弃，和数据分布绑定。
• 伪失效（其实走了）：type=index 全索引扫描、小表全表扫、统计信息过期。别误判。

排查三步走：EXPLAIN 看 key/type/key_len/Extra → 判断属于哪一类 → 对症下药。

一句话记忆：

• 列上别动手脚（函数、运算、类型不匹配）→ 保住有序性
• 联合索引从最左用起，范围列放最后 → 别断了有序性
• LIKE 别用前导 % → 留住前缀锚点
• !=/OR/IS NOT NULL 看占比 → 占比大就是不划算
• 拿不准就 EXPLAIN，别猜

理解了这两条主线，下一篇我们讲更进一步的事：既然知道了怎么会失效，那联合索引到底该怎么设计才最高效——从"避坑"走向"主动建好索引"。