MySQL 5.6+ 在满足条件时将 NOT IN/NOT EXISTS 自动优化为 Anti-Join，支持过滤下推、规避 NULL 陷阱并优先选用哈希连接；而手写 LEFT JOIN 需严格满足索引、驱动表和语义约束才可达同等性能。
子查询转反连接（Anti-Join）时优化器能下推过滤条件
MySQL 5.6+ 对 NOT IN 和 NOT EXISTS 子查询，在满足“无相关列、可物化、无 NULL 风险”等条件下，会自动重写为 Anti-Join（反连接），而不是按字面意思先执行子查询再逐行比对。这种改写让优化器有机会把右表的过滤条件（如 status = 'active'）直接下推到连接阶段，从而提前裁剪数据流。
而手写的 LEFT JOIN ... WHERE right_col IS NULL，如果过滤条件误写在 WHERE 中（比如 WHERE r.status = 'active' AND r.id IS NULL），会导致逻辑错误；若写在 ON 中，又受限于连接算法选择——优化器未必总选哈希连接，尤其当右表结果集预估不准时，可能退化为嵌套循环。
-
NOT IN (SELECT id FROM t2 WHERE type = 'user')→ 可能触发物化 + 索引查找，右表只扫描匹配type的行 -
LEFT JOIN t2 ON t1.id = t2.t1_id AND t2.type = 'user' WHERE t2.t1_id IS NULL→ 若t2缺少(type, t1_id)复合索引，ON中的type条件无法高效过滤，仍要扫大量无效行
反连接天然规避 NULL 语义陷阱
NOT IN 子查询在右表含 NULL 时整个结果为空，这是 SQL 标准行为，但容易被忽略；而 LEFT JOIN ... IS NULL 写法本身不依赖右表是否含 NULL，逻辑更清晰。但问题在于：很多开发者把 NOT IN 当作“安全替代”，却没意识到优化器是否真把它转成了 Anti-Join。
关键看 EXPLAIN 输出的 select_type 字段：
- 显示
DEPENDENT SUBQUERY→ 还是老式 N+1 扫描，性能差 - 显示
FirstMatch或LooseScan→ 已启用半连接/反连接优化，效率接近 JOIN - 显示
MATERIALIZED→ 子查询被物化为临时表并建索引，后续连接快
左外连接需显式控制驱动表与索引覆盖
手写 LEFT JOIN 要达到反连接的性能，必须同时满足三个硬性条件：
- 右表连接字段和过滤字段必须有合适的复合索引（如
(t1_id, status)），否则ON中的条件无法跳过无关行 - 优化器要选对驱动表：小结果集做驱动表才能触发哈希连接；若误判大小，可能用嵌套循环遍历大表
- 不能在
WHERE中加右表非 NULL 条件，否则语义退化为INNER JOIN，彻底失去反连接意图
相比之下，优化器对反连接的路径选择更激进——它知道这是“找不存在”，会优先尝试哈希或排序合并，并主动丢弃右表中所有不匹配的中间结果，内存和 I/O 开销更可控。
真实场景中反连接更快的根本原因
不是子查询“天生快”，而是现代优化器对特定模式（NOT IN/NOT EXISTS）做了专用路径优化，而手写 LEFT JOIN 是通用语法，缺乏语义提示。当业务逻辑明确是“排除存在记录”，直接用 NOT EXISTS 并确保右表有索引，往往比手动模拟反连接更稳。
最容易被忽略的一点：反连接优化依赖统计信息准确。如果 ANALYZE TABLE 长期未运行，优化器可能低估右表结果集大小，放弃物化或哈希连接，回落到低效路径——此时无论子查询还是 LEFT JOIN 都慢，但前者至少还有 fallback 机制，后者只能硬扛。
