MySQL索引失效场景分析之NOTIN != ISNOTNULL何时生效与失效

一、核心结论（背下来就能用）

• 单列索引 + NOT IN / != / IS NOT NULL → 99% 索引失效
  • MySQL 优化器会直接判定：全表扫描更快。
• 联合索引（最左前缀）
  • 前面是等值匹配（=），后面跟 NOT IN / != / IS NOT NULL → 前面能命中索引，后面失效。
  • 也就是说：索引只能用到前面的等值字段，后面的范围 / 否定条件用不上索引。
• 唯一索引 + 否定条件
  • 依然无法高效使用索引。

二、分场景详细讲（一看就懂）

场景 1：单列索引（最常见）

表结构：

CREATE TABLE user (    id INT PRIMARY KEY,    age INT,    name VARCHAR(50),    INDEX idx_age(age));

这些 SQL 索引一定失效：

SELECT * FROM user WHERE age != 18;SELECT * FROM user WHERE age NOT IN (18, 19, 20);SELECT * FROM user WHERE age IS NOT NULL;

原因：

MySQL 优化器认为：否定条件意味着要查询表中的大部分数据（高选择性差），直接进行全表扫描比走索引回表再过滤更快。

场景 2：联合索引（最容易踩坑）

表结构：

CREATE TABLE user (    id INT PRIMARY KEY,    name VARCHAR(50),    age INT,    INDEX idx_name_age(name, age) -- 联合索引);

SQL：

SELECT * FROM userWHERE name = '张三'  AND age != 18;

结果：

索引只用到 name（等值匹配），age 用不上！

执行过程：

1. 先通过 name = '张三' 在索引 idx_name_age 中快速定位到所有相关记录。
2. 然后在得到的结果集里，逐行判断 age != 18（无法再利用索引进行高效过滤）。

场景 3：什么时候 NOT IN / != 能用到索引？（极少）

只有一种情况：否定条件过滤后，剩下的数据极少极少。

示例：

假设 status 字段只有 0 和 1，且表中 99% 的数据是 status=1。

SELECT * FROM order WHERE status != 1;

这种情况下，MySQL 可能会走索引（因为需要扫描的数据量很小）。但这种数据分布在业务中几乎遇不到！

三、最关键：IS NOT NULL 什么时候失效？

• 只要字段允许 NULL，IS NOT NULL 一定无法有效使用索引。

• WHERE email IS NOT NULL

• MySQL 认为：几乎所有行都满足 IS NOT NULL → 全表扫描。
• 唯一例外： 字段绝大部分都是 NULL，你查 IS NOT NULL 才会走索引。（现实中极少）

四、总结：你只要记住这 3 条铁律

1. 单列索引 + NOT IN / != / IS NOT NULL → 索引必失效。
2. 联合索引 + 前面等值，后面否定 → 只命中前面等值部分。
3. 否定条件天生不适合索引（MySQL 优化器不喜欢）。

五、实战优化方案（遇到 NOT IN 怎么改？）

1. 尽量改成范围查询> < >= <=

-- 原语句（索引失效）SELECT * FROM user WHERE age NOT IN (18);-- 优化后（可能走 range 索引扫描）SELECT * FROM user WHERE age > 18 OR age < 18;-- 注意：OR 条件可能导致索引失效，需结合实际情况看执行计划

2. 尽量使用覆盖索引

-- 原语句（全表扫描）SELECT * FROM user WHERE age != 18;-- 优化后（可能走 index 索引扫描，比全表快）SELECT age FROM user WHERE age != 18;-- 因为 age 字段就在索引 idx_age 中，无需回表

3. 业务上避免使用 NOT IN

用 LEFT JOIN / NOT EXISTS 代替：

-- 查找没有订单的用户SELECT u.* FROM user uLEFT JOIN order o ON u.id = o.user_idWHERE o.id IS NULL;-- 或SELECT * FROM user uWHERE NOT EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

用状态字段代替（0/1）： 设计表时，对于是否、开关等字段，尽量使用 TINYINT 表示状态，查询时使用 status = 0 而非 status != 1。

六、OR 连接无索引字段：索引失效的隐形杀手

核心定义（一句话说清）

OR 连接无索引字段 = 一边有索引，一边没索引 → 整条 SQL 索引直接失效！

MySQL 的规则很简单：只要 OR 两边任意一个字段没有索引，整个 WHERE 条件就无法使用索引，直接全表扫描。

举个超级简单的例子（一看就明白）

假设你有一张用户表：

CREATE TABLE user (    id INT PRIMARY KEY,    name VARCHAR(50),    phone VARCHAR(20));

你只给 name 建了索引，phone 没建索引：

CREATE INDEX idx_name ON user(name);  -- name 有索引-- phone 无索引

现在你写了这条 SQL：

SELECT * FROM user WHERE name = '张三'    OR phone = '13800138000';

结果：索引完全失效！

明明 name 有索引，但因为 OR 后面的 phone 没有索引，MySQL 只能：

全表扫描每一行，判断 name 或 phone 是否满足条件

为什么 OR 连无索引字段会失效？（超简单原理）

• AND 的逻辑：先查有索引的字段 → 缩小范围 → 再查无索引字段（没问题）
• OR 的逻辑：两个条件任意一个满足就算 → 你必须把整张表都扫一遍，才能确保不漏数据 → 所以索引直接废掉

3 种必失效场景（你写 SQL 时对照看）

场景 1：一边有索引，一边无索引（最常见）

WHERE 有索引字段 = '值' OR 无索引字段 = '值'-- 结果：索引失效

场景 2：两边都无索引

WHERE 无索引字段1 = '值' OR 无索引字段2 = '值'-- 结果：本来就没索引，全表扫描

场景 3：联合索引不满足 OR 条件

CREATE INDEX idx_a_b ON table(a, b);  -- 联合索引WHERE a = 1 OR c = 2-- 结果：c 无索引 → 失效

什么时候 OR 不会失效？（唯一正确用法）

OR 两边的字段 都 有 独 立 索 引！

例子：

CREATE INDEX idx_name ON user(name);CREATE INDEX idx_phone ON user(phone);  -- 两个字段都有独立索引SELECT * FROM user WHERE name = '张三'    OR phone = '13800138000';

✅ 这种情况 MySQL 会使用：index_merge（索引合并）

两边分别走索引，再合并结果，不会失效。

终极总结（背会这 2 句就够了）

1. OR 只要连接一个无索引字段 → 整条 SQL 索引直接失效，全表扫描！
2. OR 想要生效 → 两边字段必须都有独立索引。

你以后写 SQL 记住这个口诀：

AND 可以混，OR 必须全！

• AND：一个有索引就行
• OR：必须全部有索引，否则完蛋

六、一句话终极总结

只要 SQL 中出现 NOT IN、!=、IS NOT NULL 这类否定条件，就默认索引不会生效，除非数据分布极端特殊（否定后数据量极少）。在设计和优化时，应尽量避免直接使用这类条件。