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 机制，后者只能硬扛。