MySQL 子查詢邏輯優化全攻略：合併、展開、聚合消除、半連接轉換

MySQL 子查詢邏輯優化全攻略：合併、展開、聚合消除、半連接轉換

子查詢是 SQL 編寫直觀但效能隱患最大的結構之一。MySQL 優化器對子查詢有多層次優化策略，理解其原理能指導寫出高效 SQL。

1. 子查詢分類體系

2. 核心優化技術

① 子查詢合併

語義等價的多個子查詢合併為一個，減少掃描/連接次數。

-- 原始：兩個 EXISTS
SELECT * FROM t1 WHERE a1 < 10 AND (
  EXISTS (SELECT a2 FROM t2 WHERE t2.a2 < 5 AND t2.b2 = 1) OR
  EXISTS (SELECT a2 FROM t2 WHERE t2.a2 < 5 AND t2.b2 = 2)
);
-- 優化：合併條件
SELECT * FROM t1 WHERE a1 < 10 AND (
  EXISTS (SELECT a2 FROM t2 WHERE t2.a2 < 5 AND (t2.b2 = 1 OR t2.b2 = 2))
);

② 子查詢展開 / 反嵌套 — 最關鍵優化

將子查詢「拉平」為外層查詢的連接 (JOIN)，讓優化器統一考慮連接順序、存取路徑。

-- 原始：FROM 子查詢
SELECT * FROM t1, (SELECT * FROM t2 WHERE a2 > 10) v_t2
WHERE t1.a1 < 10 AND v_t2.a2 < 20;
-- 展開後：直接連接
SELECT * FROM t1, t2 WHERE t1.a1 < 10 AND t2.a2 < 20 AND t2.a2 > 10;

展開必要條件：

• 子查詢為 SPJ 形式 (無聚合、DISTINCT、LIMIT、ORDER BY)。
• 外層查詢結果無重複 (含主鍵或 DISTINCT)，或內層結果無重複。
• 展開後不產生多餘元組 (需補 DISTINCT 或利用半連接)。

③ 聚合子查詢消除

標量聚合子查詢 (如 SELECT AVG(...)) 可下推或轉為連接。

-- 原始
SELECT * FROM t1 WHERE t1.a1 > (SELECT AVG(t2.a2) FROM t2);
-- 優化：物化聚合結果再連接 (MySQL 5.7+ 衍生表合併)
SELECT t1.* FROM t1
JOIN (SELECT AVG(a2) AS avg_a2 FROM t2) dt ON t1.a1 > dt.avg_a2;

④ 半連接 策略

針對 IN / EXISTS 子查詢，MySQL 採用半連接 避免重複行：

• DuplicateWeedout：臨時表去重
• Materialize：子查詢物化到臨時表 + 索引查找
• FirstMatch：外層匹配第一行即停止內層搜尋
• LooseScan：利用索引跳躍掃描

可透過 optimizer_switch 控制：

SET optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on';

3. 實戰改寫模式

4. 驗證優化效果

EXPLAIN SELECT ...;
-- 關注：
-- select_type: DEPENDENT SUBQUERY → SIMPLE / PRIMARY (展開成功)
-- type: ALL → ref/eq_ref (索引生效)
-- Extra: Using temporary; Using filesort → 消失

5. 版本演進提醒

• MySQL 5.6：引入 optimizer_trace、半連接 策略。
• MySQL 5.7：衍生表合併、條件下推 (Condition Pushdown)。
• MySQL 8.0：CTE (WITH) 原生支援、側寫、直方圖統計、隱式索引，子查詢優化大幅增強。

建議：MySQL 8.0+ 直接使用 WITH 重寫複雜子查詢，可讀性與效能雙贏。