MySQL NOT IN / LEFT JOIN / NOT EXISTS 效率深度對比：百萬級數據實測與優化原理

MySQL NOT IN / LEFT JOIN / NOT EXISTS 效率深度對比：百萬級數據實測與優化原理

1. 三種反向查詢寫法

-- 寫法 1：NOT IN 子查詢
SELECT COUNT(*) FROM A WHERE A.a NOT IN (SELECT a FROM B);

-- 寫法 2：LEFT JOIN + IS NULL
SELECT COUNT(*) FROM A LEFT JOIN B ON A.a = B.a WHERE B.a IS NULL;

-- 寫法 3：NOT EXISTS 相關子查詢
SELECT COUNT(*) FROM A WHERE NOT EXISTS (SELECT a FROM B WHERE A.a = B.a);

2. 實測數據對比（千萬級表，刪除 2000 萬行）

3. 單表千萬行查詢微基準

4. 核心原理分析：為什麼 LEFT JOIN 最慢？

1. 中間結果集大：LEFT JOIN 產生的中間表不小於驅動表 A
2. 額外過濾開銷：需對中間結果再做 IS NULL 篩選
3. 資源消耗高：佔用更多內存/CPU，Buffer Pool 不足時落盤，引發 I/O 風暴

結論：大數據量場景嚴禁使用 LEFT JOIN + IS NULL 做反向查詢。

5. 實戰 EXPLAIN 深度解析（UserMsg 表）

場景：查詢某時間段後的 RUID，排除之前出現過的

-- 寫法 A：NOT IN（最快 0.07s）
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'
);

-- 寫法 B：LEFT JOIN 子查詢（慢 0.39s）
-- 產生兩個派生表，再做笛卡爾積，無法用索引

-- 寫法 C：LEFT JOIN 直接關聯（快 0.07s）
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;

-- 寫法 D：NOT EXISTS（0.08s）
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 b
    WHERE b.SubjectID=12 AND b.CreateTime<'2009-8-14 15:30:00' AND b.RUID=a.RUID
  );

6. 優化器行為差異關鍵點

• 子查詢中 WHERE 位置決定索引使用：條件寫在 JOIN ON 子句 vs 子查詢 WHERE，優化器選擇不同訪問路徑
• 派生表：DERIVED 類型表示物化臨時表，若兩張派生表 JOIN 會導致笛卡爾積災難
• MySQL 優化器局限：對複雜子查詢/派生表優化不如 PostgreSQL 成熟，需人工重寫

7. 最佳實踐建議

1. 首選 NOT EXISTS：語義清晰、性能穩健、NULL 安全（NOT IN 遇 NULL 會返回空集）
2. 次選 NOT IN：確保子查詢結果無 NULL 時可用
3. LEFT JOIN 僅用於簡單關聯：條件下推到 ON 子句，避免派生表
4. 大批量刪除：分批 DELETE ... LIMIT 5000 + 事務提交，控制日志膨脹
5. 加索引：關聯列、篩選列必須有索引