測試場景

表 a(id) 2.5 萬行，表 b(aid) 2.477 萬行 (少 230 行)。查詢 a 中不在 b 的記錄。

三種寫法與實測 (無索引)

-- 1. LEFT JOIN ... IS NULL
SELECT a.id FROM a LEFT JOIN b ON a.id = b.aid WHERE b.aid IS NULL;
-- 39 分 0.48 秒

-- 2. NOT IN 子查詢
SELECT a.id FROM a WHERE a.id NOT IN (SELECT b.aid FROM b);
-- 38 分 7.48 秒

-- 3. NOT EXISTS 子查詢
SELECT a.id FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.aid = a.id);
-- 37 分 52.44 秒

結論：無索引時三者皆極慢 (全表掃描 + 嵌套循環)，差異微乎其微。

加上索引後

CREATE INDEX idx_b_aid ON b(aid);

三種寫法**均在 0.52 秒內完成**，效能差異可忽略。

深度原理分析

寫法	執行計劃特徵	NULL 值陷阱	適用場景
LEFT JOIN ... IS NULL	驅動表 a，被驅動表 b 走 ref/index	無	需顯示 b 表欄位、或多表關聯
NOT IN	子查詢物化/半連接	子查詢結果含 NULL → 整體無結果	簡單單表、確定無 NULL
NOT EXISTS	相關子查詢、半連接優化	無 (NULL 不影響邏輯)	複雜條件、關聯多表、最通用

NOT IN 致命陷阱演示

-- b.aid 含 NULL
INSERT INTO b(aid) VALUES (NULL);

-- NOT IN 返回空結果集 (因 NULL 比較結果為 UNKNOWN)
SELECT * FROM a WHERE a.id NOT IN (SELECT aid FROM b);
-- 0 rows

-- NOT EXISTS 正常
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.aid = a.id);
-- 正確返回 230 行

MySQL 8.0+ 優化器增強

• NOT IN 子查詢自動轉換為 NOT EXISTS (Anti-Join)
• LEFT JOIN ... IS NULL 同樣優化為 Anti-Join
• 優化器提示：/*+ MAX_EXECUTION_TIME(1000) */、/*+ BKA(b) */

最佳實踐建議

1. 首選 NOT EXISTS：語義清晰、無 NULL 陷阱、優化器支援最好
2. 必須為關聯欄位建立索引：INDEX (aid) 是效能分水嶺
3. 避免 NOT IN 含 NULL 子查詢：若不可避免，加 WHERE aid IS NOT NULL
4. 大表考慮 NOT EXISTS + LIMIT 或分批處理
5. EXPLAIN 驗證：確認 type=ref/eq_ref、Extra=Using where; Not exists

擴展：Anti-Join 物理算子

MySQL 8.0.17+ 引入 Hash Join，Anti-Join 可使用 Hash Join，大表關聯效能再提升：

SET optimizer_switch='hash_join=on';
EXPLAIN SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.aid = a.id);
-- type=hash_join (或類似)