为什么 `!=` 和 `NOT IN` 会让索引失效：从 B+ 树的有序性说起

最新推荐文章于 2026-07-30 23:53:41 发布

原创最新推荐文章于 2026-07-30 23:53:41 发布 · 392 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL

前言

“这条 SQL 明明在索引列上查，怎么还是全表扫描？”

如果你把 WHERE status = 1 改成 WHERE status != 1，很可能就会遇到这个现象：同一个列、同一个索引，等值查询走得好好的，一换成 !=（或 NOT IN、<>）索引就"失效"了。

《阿里巴巴 Java 开发手册》里有一条相关的**【推荐】**规约：

【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
说明：
1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2）ref 指的是使用普通的索引（normal index）。
3）range 对索引进行范围检索。
反例：explain 结果，type=index，索引物理文件全扫描，速度非常慢。

!= 和 NOT IN 之所以危险，正是因为它们很容易让查询掉到 range 级别以下，退化成全表扫描。这篇文章从 B+ 树的结构讲清楚：为什么"不等于"这类否定条件，天生就和索引不对付。

环境说明：本文基于 MySQL 8.0，存储引擎 InnoDB。继续复用前几篇的 orders 表（100 万行）。

一、先看现象：一个 `!=` 让索引失效

orders 表上有联合索引 idx_user_status(user_id, status)。先看等值查询：

EXPLAIN SELECT * FROM orders WHERE user_id = 88888;

+----+--------+------+-----------------+---------+------+-------+
| id | table  | type | key             | key_len | rows | Extra |
+----+--------+------+-----------------+---------+------+-------+
|  1 | orders | ref  | idx_user_status | 8       |   10 | NULL  |
+----+--------+------+-----------------+---------+------+-------+

type = ref，走索引，只扫 10 行。很理想。

现在把 = 换成 !=：

EXPLAIN SELECT * FROM orders WHERE user_id != 88888;

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

type = ALL、key = NULL——索引没用上，直接全表扫 100 万行。possible_keys 里明明有 idx_user_status（说明这个索引"可用"），但优化器最终选择了不用它。

NOT IN 也是一样：

EXPLAIN SELECT * FROM orders WHERE user_id NOT IN (88888, 99999);
-- 同样 type = ALL，全表扫描

为什么会这样？答案在索引的底层结构里。

二、底层：索引为什么"怕"否定条件

2.1 B+ 树的本质是"有序"

InnoDB 的索引是 B+ 树，它最核心的特性是：叶子节点上的数据，是按索引列的值从小到大排好序的。

正因为有序，索引才能高效地做两件事：

等值查找（= 88888）：像查字典一样，直接二分定位到那个值，O(log n)。
范围查找（> 88888、BETWEEN、< 88888）：定位到范围的起点，然后顺着有序的叶子链表往后连续读，读到终点为止。

关键词是"连续"。索引能加速，靠的就是把要找的数据圈定在一段连续的区间里，一次定位、顺序扫描。

在这里插入图片描述

2.2 `!=` 圈出来的不是一段区间，而是"两段 + 挖空"

现在看 user_id != 88888 要的是什么：除了 88888 以外的所有值。

在有序的 B+ 树上，这意味着要的是 88888 左边的一整段（< 88888）加上右边的一整段（> 88888），中间挖掉一个点。

这就麻烦了：

它不是一段连续区间，而是两段，中间还断开
更要命的是，这两段加起来，几乎是整张表——排除掉一个值，剩下的还是绝大多数数据

优化器一算：走索引的话，要扫描几乎全部索引项，还得每条回表取完整数据（因为是 SELECT *）；这么大的量，回表的代价比直接全表顺序扫还高。于是它干脆放弃索引，选择全表扫描。

这就是 != / NOT IN / <> "让索引失效"的真相：不是不能用，而是它们圈定的数据范围太大、太碎，优化器算下来用索引反而更慢，主动放弃了。

2.3 对比：`=` 和 `>` 为什么就没事

= 88888：圈定的是一个点，命中极少，走索引稳赚。
> 88888：圈定的是一段连续区间，如果这段不算太大，走索引扫这一段仍然比全表扫划算。
!= 88888：圈定的是几乎全表，走索引毫无优势，反而多了回表开销。

看出规律了吗？索引怕的不是"否定"本身，而是"要的数据范围太大"。 != 恰好几乎总是圈中"绝大部分数据"，所以几乎总是失效。

三、这是"优化器的选择"，不是"语法禁止"

有一点要澄清：!= 让索引失效，是优化器基于成本的主动选择，不是 MySQL 语法上"禁止 != 用索引"。

证据是：如果否定条件排除掉的是大部分数据（即最终只剩一小部分），优化器又会愿意走索引了。

举个例子，假设某个 status 值占了全表 99% 的数据，那 status != 那个值 只剩 1%，这时候走索引扫这 1% 就划算了，优化器可能就会用索引。

也就是说，最终结果集占全表的比例，才是优化器决策的关键：

结果集占比小 → 走索引划算 → 用索引
结果集占比大（!= 通常如此）→ 走索引还不如全表扫 → 放弃索引

所以严格讲，不是"!= 一定不走索引"，而是"!= 通常命中太多行，导致优化器算下来不划算"。理解这一层，比死记"!= 让索引失效"更有用。

四、那该怎么办

4.1 能改成范围/等值就改

如果 != 在业务上可以等价改写成一段明确的范围，就改。比如"状态不是已完成（3）"，如果状态只有 0/1/2/3，可以写成 IN：

-- 不推荐
WHERE status != 3

-- 如果能明确列举，改成 IN（正向枚举）
WHERE status IN (0, 1, 2)

IN 是正向的、离散的等值集合，每个值都能走索引定位，比 != 友好得多。

4.2 接受它，但别让它扫大表

有些 != 无法避免。那就要保证它不是在大表上裸跑——通过其他更有选择性的条件先把范围缩小。比如：

-- user_id 先用索引把范围缩到几十行，再在这几十行里过滤 status != 3
WHERE user_id = 88888 AND status != 3

这条 SQL 里，user_id = 88888 先走索引定位到约 10 行，status != 3 只是在这极小的结果集里做过滤，完全没问题。让高选择性的等值条件走索引，把 != 降级为"过滤"而非"检索"。

4.3 用 EXPLAIN 确认，别猜

最实在的办法：写完 SQL 用 EXPLAIN 看一眼 type。对照手册那条规约：

type = ALL → 全表扫描，最差，要优化
type = index → 全索引扫描，也慢
type = range → 及格线，范围扫描
type = ref → 良好，普通索引等值
type = const → 最优，主键/唯一索引

只要没掉到 range 以下，就基本达标。

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

Q：所有 != 都一定不走索引吗？

不是。这是优化器基于"结果集占比"的成本选择。当 != 排除后剩下的数据很少时，优化器仍可能走索引。只是大多数场景下 != 命中绝大部分行，所以"通常"失效。别绝对化。

Q：NOT IN 和 != 是一回事吗？

原理一样，都是否定条件，圈定的都是"排除某些值后的剩余大部分数据"，所以都容易失效。另外 NOT IN 遇到子查询、遇到 NULL 时还有额外的坑（NULL 会导致整个结果异常），能用 NOT EXISTS 或正向 IN 时优先考虑。

Q：IS NOT NULL 也会失效吗？

同理，取决于非 NULL 的行占多大比例。如果绝大多数行都非 NULL，IS NOT NULL 命中几乎全表，也会倾向全表扫描。

Q：为什么 possible_keys 有索引，key 却是 NULL？

这正是"索引可用但优化器不用"的典型信号。possible_keys 表示这个索引理论上能用于这个查询，key = NULL 表示优化器算完成本后决定不用它——通常就是因为走索引的代价（大量回表）比全表扫还高。

总结

!= / NOT IN / <> 让索引失效，根子在 B+ 树的有序结构：

索引靠有序加速，擅长圈定一段连续区间（等值是一个点，范围是一段）。
!= 圈定的是"排除一个值后的几乎全表"——不是连续区间，且数据量巨大。
优化器算下来，走索引（大量回表）还不如直接全表顺序扫，于是主动放弃索引，type 掉到 ALL。
本质是结果集占比决定的成本选择，不是语法禁止。占比小时 != 也能走索引。

应对：能正向枚举就用 IN；避免不了就用高选择性的等值条件先缩小范围，让 != 只做过滤；最后用 EXPLAIN 确认 type 不低于 range。

一句话记忆： 索引怕的不是"否定"，是"范围太大"。!= 几乎总是命中绝大部分数据，所以几乎总是失效——把它降级成过滤条件，别让它当检索条件。

标签

#数据库