NOT IN 慢的关键在于NULL值导致语义失效和索引无法下推：右表含NULL时整个条件返回UNKNOWN，优化器被迫全表扫描；应改用LEFT JOIN + IS NULL或NOT EXISTS，并确保连接字段非空、类型字符集一致、有有效索引。

NOT IN 为什么慢？关键在 NULL 和执行计划

MySQL 中 NOT IN 子查询一旦遇到右表结果含 NULL，整个表达式返回 UNKNOWN，导致 WHERE 条件永远不成立，最终查不到任何数据——这不是 bug，是 SQL 标准行为。更糟的是，优化器往往因此放弃使用索引，强制全表扫描。

你看到的 Handler_read_rnd_next 高达几百万次、rows 显示为全表行数、key_len 为 NULL，基本就是这个原因。

• NOT IN (SELECT id FROM t2) 只要 t2.id 里有一个 NULL，结果就为空
• 即使加了索引，MySQL 也不敢用，因为语义上无法保证安全下推
• EXPLAIN 里 type 是 ALL、Extra 出现 Using where; Using join buffer 就是危险信号

LEFT JOIN + IS NULL 的正确写法

改写核心是：用 LEFT JOIN 保留左表所有行，再用 WHERE 右表关联字段 IS NULL 筛出“没匹配上”的记录。但必须确保连接字段本身非空，否则 IS NULL 判断会漏数据或逻辑错位。

• 连接条件必须是确定的等值，如 ON a.stu_id = b.stu_id，不能带函数或表达式
• 右表用于连接的字段（如 b.stu_id）最好有 NOT NULL 约束；若不能改表结构，至少在子查询中过滤掉 NULL：(SELECT stu_id FROM t_student WHERE stu_id IS NOT NULL)
• 左表连接字段也建议非空，否则 a.stu_id IS NULL 的行也会被保留进结果，可能不符合业务预期
• 示例原语句：SELECT * FROM t_class WHERE stu_id NOT IN (SELECT stu_id FROM t_student);
• 安全改写：SELECT a.* FROM t_class a LEFT JOIN t_student b ON a.stu_id = b.stu_id WHERE b.stu_id IS NULL;

为什么有时候 LEFT JOIN 也不走索引？

不是写了 LEFT JOIN 就自动快——如果连接字段没索引、类型不一致、或者用了隐式转换，照样全表扫描。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

• 检查 EXPLAIN 输出中的 key 和 key_len：若为 NULL，说明没走索引
• a.stu_id 和 b.stu_id 必须是相同类型（比如都是 BIGINT UNSIGNED），字符集也要一致
• 常见陷阱：左表字段是 VARCHAR，右表对应字段是 CHAR，或一个带 utf8mb4_0900_as_cs，另一个是 utf8mb4_general_ci
• 如果右表是子查询，别名后的字段无法直连索引，应尽量把子查询提前物化或改用临时表

比 LEFT JOIN 更稳的选择：NOT EXISTS

当右表数据量大、且连接字段存在大量重复或 NULL 时，NOT EXISTS 往往比 LEFT JOIN 更可控——它天然规避 NULL 语义问题，且支持半连接优化（semijoin），执行计划更干净。

• 等价改写：SELECT * FROM t_class a WHERE NOT EXISTS (SELECT 1 FROM t_student b WHERE b.stu_id = a.stu_id);
• 注意子查询里必须关联外层，即 WHERE b.stu_id = a.stu_id，否则变成恒真/恒假
• NOT EXISTS 不要求右表字段非空，也不怕右表结果含 NULL
• 配合 b.stu_id 上的索引，EXPLAIN 通常显示 type=ref、rows 极小

真正容易被忽略的点在于：你改写了 SQL，但没验证右表数据是否含 NULL；或者加了索引，却没确认两边字段类型和字符集是否完全一致。性能问题从来不是单点问题，而是执行路径、数据分布、索引定义三者咬合的结果。