一、核心结论（背下来就能用）
- 单列索引 + 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 user WHERE name = '张三' AND age != 18;
结果：
索引只用到 name（等值匹配），age 用不上！
执行过程：
- 先通过
name = '张三'在索引idx_name_age中快速定位到所有相关记录。 - 然后在得到的结果集里，逐行判断
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 条铁律
- 单列索引 +
NOT IN/!=/IS NOT NULL→ 索引必失效。 - 联合索引 + 前面等值，后面否定 → 只命中前面等值部分。
- 否定条件天生不适合索引（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 u LEFT JOIN order o ON u.id = o.user_id WHERE o.id IS NULL; -- 或 SELECT * FROM user u WHERE 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 句就够了）
- OR 只要连接一个无索引字段 → 整条 SQL 索引直接失效，全表扫描！
- OR 想要生效 → 两边字段必须都有独立索引。
你以后写 SQL 记住这个口诀：
AND 可以混，OR 必须全！
- AND：一个有索引就行
- OR：必须全部有索引，否则完蛋
六、一句话终极总结
只要 SQL 中出现 NOT IN、!=、IS NOT NULL 这类否定条件，就默认索引不会生效，除非数据分布极端特殊（否定后数据量极少）。在设计和优化时，应尽量避免直接使用这类条件。
