MySQL 中 NOT IN、LEFT JOIN 与 NOT EXISTS 性能对比及优化指南

核心结论与性能排名

在处理包含数百万行数据的表时，不同写法的性能差异显著。根据实测数据，在具备合适索引的前提下：

• 最优解：NOT IN 与经过优化的单表自连接 LEFT JOIN，执行时间通常在 0.07s - 0.13s 之间。
• 次优/可变解：NOT EXISTS，表现接近 NOT IN，约 0.08s。
• 危险写法：LEFT JOIN 若将两个大结果集提前独立聚合（Derived Table），极易触发大规模笛卡尔积，耗时可能飙升至 21s 以上。

场景复现与测试数据

测试基础为一个名为 UserMsg 的大表，需要查询满足以下条件的 RUID：

1. 在当前时间段内有效：CreateTime > '2009-8-14 15:30:00' AND CreateTime <= '2009-8-17 16:00:00'
2. 不在过去的时间段内出现：不存在 SubjectID=12 且 CreateTime < '2009-8-14 15:30:00' 的记录。

第1步：使用 NOT IN 进行反连接

这是最直观的反向筛选逻辑。先筛选出当前时段数据，再排除掉过去时段已存在的 ID。

SELECT add_tb.RUID 
FROM (SELECT DISTINCT RUID 
      FROM UserMsg 
      WHERE SubjectID = 12 
        AND CreateTime > '2009-8-14 15:30:00' 
        AND CreateTime <= '2009-8-17 16:00:00') add_tb 
WHERE add_tb.RUID NOT IN (
    SELECT DISTINCT RUID 
    FROM UserMsg 
    WHERE SubjectID = 12 
      AND CreateTime < '2009-8-14 15:30:00'
);

执行结果：返回 444 行记录，耗时 0.07秒。

EXPLAIN 分析：

分析：子查询部分利用了索引范围扫描，效率极高。主查询虽然使用了衍生表，但由于过滤条件严格，参与计算的数据量极小。

第2步：错误的 LEFT JOIN 写法（双重派生表）

很多开发者习惯先将左右两表的限定条件分别封装为子查询（派生表），再进行 Join。这种写法在数据量小时无妨，但在数据量大时会引发严重问题。

SELECT a.ruid, b.ruid 
FROM(
    SELECT DISTINCT RUID FROM UserMsg 
    WHERE SubjectID = 12 AND CreateTime >= '2009-8-14 15:30:00' AND CreateTime<='2009-8-17 16:00:00'
) a 
LEFT JOIN (
    SELECT DISTINCT RUID FROM UserMsg 
    WHERE SubjectID = 12 AND CreateTime< '2009-8-14 15:30:00'
) b ON a.ruid = b.ruid 
WHERE b.ruid IS NULL;

执行结果：返回 444 行记录，耗时 0.39秒（若去除 SubjectID 限制，耗时飙升至 21.31秒）。

EXPLAIN 关键指标：

致命缺陷：该写法对 UserMsg 表进行了两次独立的聚合操作生成临时表。当去掉 SubjectID 限制后，derived3 表的大小急剧膨胀，导致 Join 过程产生巨大的内存开销和 CPU 负载。这就是为什么在生产环境删除数千万数据时，这种写法会导致 SQL Server/MySQL 崩溃的原因。

第3步：优化的单表自连接 LEFT JOIN

通过只进行一次全表扫描，并在 Join 阶段应用右侧表的过滤条件，可以大幅减少中间结果集的体积。

SELECT DISTINCT a.RUID 
FROM UserMsg a 
LEFT JOIN UserMsg b 
ON a.ruid = b.ruid 
AND b.subjectID = 12 
AND b.createTime < '2009-8-14 15:30:00'
WHERE a.subjectID = 12 
AND a.createTime >= '2009-8-14 15:30:00' 
AND a.createTime <= '2009-8-17 16:00:00' 
AND b.ruid IS NULL;

执行结果：耗时 0.07秒。

EXPLAIN 分析：

优势：左侧表 a 作为驱动表，利用 CreateTime 范围扫描获取少量候选数据；右侧表 b 利用主键/索引直接定位匹配项。这种写法避免了创建大型临时表，是 LEFT JOIN 的最优形态。

第4步：使用 NOT EXISTS

逻辑上等价于 NOT IN，但通常具有更好的语义表达性。

SELECT DISTINCT a.ruid 
FROM UserMsg a 
WHERE a.subjectID = 12 
  AND a.createTime >= '2009-8-14 15:30:00' 
  AND a.createTime <= '2009-8-17 16:00:00' 
  AND NOT EXISTS (
      SELECT 1 
      FROM UserMsg 
      WHERE subjectID = 12 
        AND createTime < '2009-8-14 15:30:00' 
        AND ruid = a.ruid
  );

执行结果：耗时 0.08秒。

分析：执行计划显示这是一个相关子查询（Dependent Subquery），查询规划器能够很好地利用索引进行查找。对于百万级以下的数据量，其性能与 NOT IN 几乎无异。

底层原理：为何有些 LEFT JOIN 会慢?

数据库查询优化器的工作原理是将 SQL 解析为抽象语法树（AST），然后尝试不同的 Join 顺序和执行策略，计算估算成本（Cost）。成本的简易估算公式往往与访问行数（Rows）的乘积有关。

1. 资源消耗差异：第一种写法（优化的 JOIN）和第二种写法（双重派生表）的根本区别在于中间结果集的大小。第二种写法强制 MySQL 先构建两个完整的 Distinct 集合，如果集合很大，必须占用大量 Buffer Pool 甚至交换到磁盘。
2. 索引利用情况：在双重派生表的写法中，优化器可能无法穿透子查询看到外层的关联条件，导致 Join 时的索引失效或全表扫描。
3. 笛卡尔积风险：原则是让笛卡尔积发生在尽可能小的集合之间。双重派生表违背了这一原则，导致了指数级的计算复杂度增长。

总结与建议

在进行数据清除或复杂关联查询时，请务必注意：

• 慎用嵌套子查询式的 LEFT JOIN：不要将两侧的筛选条件都放在独立的子查询中，除非你能确定子查询返回的结果集极小。
• 首选方案：NOT EXISTS 或 单表自连接的 LEFT JOIN 通常比 NOT IN 更安全，因为 NOT IN 在子查询中包含 NULL 值时会导致结果为空集（需确保字段非空）。
• 善用 EXPLAIN：检查 rows 字段和 Extra 列。如果出现 Using temporary 且行数极大，应优先考虑改写为不产生大临时表的索引关联方式。

以上就是 MySQL 中 NOT IN、LEFT JOIN 与 NOT EXISTS 效率问题的详细内容，更多关于 MySQL 性能调优的资料请关注本站其它相关文章！