MySQL IN vs EXISTS 子查詢深度對比：執行機制、優化策略與版本差異

核心執行機制差異

EXISTS：外層驅動，逐行檢查

EXPLAIN SELECT * FROM vendor 
WHERE EXISTS (SELECT * FROM area WHERE area_code = vendor_prov_code) 
LIMIT 10;

執行流程：

1. 從外層表 vendor 讀取一行
2. 將該行的 vendor_prov_code 代入子查詢
3. 執行子查詢，返回 TRUE/FALSE
4. TRUE 保留該行，FALSE 捨棄
5. 循環直到外層表掃描完畢

關鍵特點：子查詢不返回實際數據，僅返回布爾值；可利用外層表索引。

IN：子查詢先行，轉換為多次等值查詢

EXPLAIN SELECT * FROM vendor 
WHERE vendor_prov_code IN (SELECT area_code FROM area) 
LIMIT 10;

執行流程（傳統優化器）：

1. 先執行子查詢 SELECT area_code FROM area 獲取結果集
2. 將結果集轉換為多條獨立查詢：

SELECT * FROM vendor WHERE vendor_prov_code = 結果1;
SELECT * FROM vendor WHERE vendor_prov_code = 結果2;
...
SELECT * FROM vendor WHERE vendor_prov_code = 結果N;

MySQL 版本優化演進

效能決策關鍵因素

外表小、內表大 → EXISTS 勝出

-- 外層 100 行，內層 100 萬行
SELECT * FROM small_table s
WHERE EXISTS (SELECT 1 FROM large_table l WHERE l.fk = s.id);

EXISTS 只需 100 次索引查找，IN 需先掃描 100 萬行構建集合。

外表大、內表小 → IN 可能更優

-- 外層 100 萬行，內層 10 行
SELECT * FROM large_table l
WHERE l.category IN (SELECT id FROM small_category);

MySQL 5.6+ 會將小結果集物化到臨時表，再做高效 JOIN。

NULL 值陷阱

-- IN 含 NULL 時邏輯異常
SELECT * FROM t1 WHERE col IN (1, 2, NULL);  -- 永遠不會匹配 NULL

-- EXISTS 不受影響
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col);

現代最佳實踐（MySQL 8.0+）

統一使用 JOIN 替代（可讀性與效能雙贏）

-- 替代 IN
SELECT v.* FROM vendor v
JOIN area a ON v.vendor_prov_code = a.area_code;

-- 替代 EXISTS（語意更清晰）
SELECT v.* FROM vendor v
WHERE EXISTS (SELECT 1 FROM area a WHERE a.area_code = v.vendor_prov_code);

使用 EXPLAIN ANALYZE 驗證（8.0.18+）

EXPLAIN ANALYZE SELECT * FROM vendor 
WHERE vendor_prov_code IN (SELECT area_code FROM area);

關注 actual time、rows、loops 等實際執行指標，而非估算值。

總結建議

1. MySQL 8.0+：兩者效能差異極小，優先考慮語義清晰度
2. 舊版本/大數據量：小表驅動大表用 EXISTS，大表篩選小集合用 IN
3. 含 NULL 風險：優先用 EXISTS 或顯式 IS NOT NULL
4. 複雜業務邏輯：重寫為標準 JOIN，利用優化器完整能力
5. 務必實測：同樣 SQL 在不同數據分布、索引狀態下表現可能截然不同