MySQL NOT IN / LEFT JOIN / NOT EXISTS 效能深度對比與優化實戰

問題背景

查詢「集合 A 中不在集合 B 的數據」有三種經典寫法：

-- 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);

實戰壓測：千萬級數據清理場景

需刪除 2000 萬行數據，初用 NOT IN 耗時 1h32min，日志膨脹 21GB。改用 LEFT JOIN 後 40 分鐘未刪 5 萬行，甚至導致 SQL Server 崩潰。單表測試：NOT IN 4 秒，LEFT JOIN 18 秒，NOT EXISTS 接近 NOT IN。

深度分析：為何 LEFT JOIN 成「大忌」

假設 Buffer Pool 足夠大，LEFT JOIN 相比 NOT IN/EXISTS 存在三大劣勢：

1. 中間結果集巨大： LEFT JOIN 產生的中間結果集不小於表 A，消耗更多內存/CPU
2. 額外過濾開銷： 需對中間結果再做 IS NULL 篩選，而 NOT IN/EXISTS 在 JOIN 同時完成篩選
3. 資源爭奪導致換頁： 高併發下中間結果集超出內存，觸發磁盤 I/O，性能雪崩

MySQL 實測對比（附 EXPLAIN 分析）

場景：查詢某時間窗口內的新用戶（不在舊窗口）

寫法一：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');

EXPLAIN 亮點： 子查詢使用 index_subquery，外層結果集小（452 行），臨時表開銷可控。

寫法二：LEFT JOIN 笛卡爾積版（耗時 0.39s）

SELECT a.ruid, b.ruid
FROM (SELECT DISTINCT RUID ... ) a
LEFT JOIN (SELECT DISTINCT RUID ... ) b ON a.ruid = b.ruid
WHERE b.ruid IS NULL;

EXPLAIN 痛點： 兩個衍生表全表掃描（ALL），產生笛卡爾積（452 × 1112），無法使用索引，Using temporary、Not exists 開銷巨大。

寫法三：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;

EXPLAIN 亮點： 兩表均走 range/ref 索引，Not exists 優化生效，無臨時表，性能與 NOT IN 持平。

寫法四：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 WHERE SubjectID=12 AND CreateTime<'2009-8-14 15:30:00' AND ruid=a.ruid);

EXPLAIN： 與優化版 LEFT JOIN 幾乎一致，DEPENDENT SUBQUERY 走索引，性能略遜於寫法三。

大數據量驗證（移除 SubjectID 過濾）

優化器行為深度解析

MySQL 優化器將 SQL 轉為分析樹，枚舉執行計劃，估算開銷（以 rows 乘積近似笛卡爾積大小）選擇最低成本。

• 核心原則： 讓笛卡爾積發生在最小的集合之間
• 子查詢陷阱： 將條件放在子查詢內部（衍生表）可能導致優化器無法下推索引條件，產生巨大中間集
• 寫法三/四優勢： 條件直接寫在 JOIN/WHERE 中，優化器能利用索引直接關聯，避免物化大衍生表

最佳實踐建議

1. 首選 NOT EXISTS 或 NOT IN（確保子查詢非 NULL） — 語義清晰、優化器成熟
2. 若用 LEFT JOIN，務必將過濾條件寫在 ON 子句，而非衍生表內部 — 使優化器能下推索引
3. 避免「雙衍生表 LEFT JOIN」模式 — 極易產生不可控的笛卡爾積
4. 大表關聯前，先用 EXPLAIN 確認 type 為 ref/eq_ref/range，無 Using temporary、ALL
5. 適當增加覆蓋索引，減少回表

結語

「寫法決定性能」在海量數據下尤為明顯。MySQL 優化器對子查詢處理仍有提升空間，**將過濾條件盡量外置、讓索引直接參與 JOIN**，是穩健高效的通用原則。