MySQL 優化器對子查詢（特別是 IN/EXISTS 子查詢）的處理歷來較弱，易將可獨立執行的子查詢錯誤轉為 DEPENDENT SUBQUERY（相關子查詢），導致外層全表掃描、內層重複執行，性能災難級下跌。本文通過實戰案例剖析優化器誤判機制，提供「衍生表物化」、「Join 重寫」、「直方圖統計」三大破解方案。
-- category (幾千行) → subcategory (幾萬行) → item (百萬行)
CREATE TABLE subcategory (id INT PK, category INT, INDEX(category));
CREATE TABLE item (id INT PK AUTO_INCREMENT, subcategory INT, INDEX(subcategory));
-- 目標：找出 category=14 中 item 數 > 2000 的所有 subcategory 全字段SELECT c.*
FROM subcategory c
JOIN item i ON i.subcategory = c.id
WHERE c.category = 14
GROUP BY c.id
HAVING COUNT(*) > 2000;EXPLAIN 顯示：type=ref 走索引，rows=100，極快。
SELECT * FROM subcategory
WHERE id IN (
SELECT c.id
FROM subcategory c
JOIN item i ON i.subcategory = c.id
WHERE c.category = 14
GROUP BY c.id
HAVING COUNT(*) > 2000
);id=1: PRIMARY, table=subcategory, type=ALL, rows=300783, Extra=Using where
→ 外層全表掃描 30 萬行！
id=2: DEPENDENT SUBQUERY, table=c, type=ref, key=category, rows=100
→ 內層變為「相關子查詢」，外層每行都執行一次！
id=2: DEPENDENT SUBQUERY, table=i, type=ref, key=subcategory, rows=28核心原因：優化器將 IN (subquery) 重寫為 EXISTS (correlated subquery)，並在 HAVING 中注入外層引用 <cache>(outer.id) = <ref_null_helper>(c.id)，導致從「內→外」變為「外→內」嵌套循環。
SELECT * FROM subcategory
WHERE id IN (
SELECT id FROM (
SELECT c.id
FROM subcategory c
JOIN item i ON i.subcategory = c.id
WHERE c.category = 14
GROUP BY c.id
HAVING COUNT(*) > 2000
) AS derived_tbl -- 關鍵：包一層衍生表，強制優化器先物化
);原理：MySQL 將最內層衍生表視為獨立查詢，先執行並將結果寫入內存臨時表（衍生表），外層 IN 僅掃描這張小表。EXPLAIN 顯示 select_type=DERIVED、type=ALL（臨時表全掃描，但行數極少）。
SELECT c.*
FROM subcategory c
JOIN (
SELECT c2.id
FROM subcategory c2
JOIN item i ON i.subcategory = c2.id
WHERE c2.category = 14
GROUP BY c2.id
HAVING COUNT(*) > 2000
) AS filtered ON filtered.id = c.id;或直接用最初的 Join + GROUP BY，無需子查詢。Join 讓優化器自由選擇驅動表、訪問路徑，避免子查詢優化器的固有缺陷。
ANALYZE TABLE subcategory UPDATE HISTOGRAM ON category WITH 100 BUCKETS;讓優化器精準知道 category=14 的選擇率，減少誤判概率。但不保證 100% 修復，建議配合寫法優化。
| 模式 | 風險等級 | 建議 |
|---|---|---|
WHERE col IN (SELECT ...) | ⭐⭐⭐⭐⭐ | 改 Join 或衍生表 |
WHERE col NOT IN (SELECT ...) | ⭐⭐⭐⭐⭐ | 改 LEFT JOIN ... WHERE IS NULL 或 NOT EXISTS（注意 NULL 語義） |
SELECT (SELECT ...) FROM ... (標量子查詢) | ⭐⭐⭐ | 若返回多行報錯；可用 Join 聚合替代 |
WHERE EXISTS (SELECT 1 ...) | ⭐⭐ | 相關子查詢通常優化較好，但大表驅動小表仍需警惕 |
FROM (SELECT ...) AS t (衍生表) | ⭐ | MySQL 8.0+ 衍生表合併優化良好，可放心使用 |
8.0 引入 derived_merge 優化（默認開啟），將衍生表「合併」到外層查詢，避免物化臨時表開銷。若導致計劃劣化，可關閉：SET optimizer_switch='derived_merge=off'; 或查詢級 /*+ NO_DERIVED_MERGE() */ Hint。
MySQL 子查詢優化核心矛盾：優化器傾向將 IN/EXISTS 轉為相關子查詢（DEPENDENT SUBQUERY），導致外層全掃描。破解鐵律：
1. 首選 Join 重寫（語義等價、優化器自由度最高）。
2. 次選衍生表物化（IN (SELECT id FROM (subquery) AS t)），強制先執行內層。
3. 常態化 EXPLAIN 檢查：見 DEPENDENT SUBQUERY + 外層 type=ALL 立即重構。
4. 大表嚴禁 WHERE ... IN (subquery)，寫入代碼評審清單。
