MySQL 子查詢類型詳解與優化策略：IN、EXISTS、ALL/ANY/SOME 子查詢執行計劃深度分析

前言

子查詢是 SQL 編寫的強大工具，但也是性能殺手重災區。MySQL 優化器對不同類型子查詢的處理策略差異巨大：有的轉為半連接、有的物化、有的保留依賴子查詢。本文通過 EXPLAIN EXTENDED 實測，系統分析 IN、EXISTS、NOT IN、ALL/ANY/SOME 等類型的優化行為。

一、子查詢基本分類

1. WHERE 型：內層結果作外層比較條件。WHERE col IN (SELECT ...)、WHERE col = (SELECT ...)。
2. FROM 型：內層結果作派生表供外層查詢。SELECT * FROM (SELECT ...) AS t。
3. EXISTS 型：外層行傳入內層判斷是否存在。WHERE EXISTS (SELECT 1 FROM ... WHERE outer.col = inner.col)。

二、優化器處理策略實測 (基於 MySQL 5.7+)

測試表：t1(id1, a1, b1), t2(id2, a2, b2)，a2 有唯一索引。

1. EXISTS / NOT EXISTS：不優化，保留 Dependent Subquery

EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a1 = t2.a2 AND t2.a2 > 10);

• 執行計劃：select_type=DEPENDENT SUBQUERY，外層 ALL，內層 ALL。
• 優化後 SQL：仍保留 EXISTS (SELECT ...) 結構。
• 結論：MySQL **不支援** EXISTS/NOT EXISTS 子查詢的半連接或物化優化。外層每行都需執行內層查詢。

2. IN (非相關子查詢)：半連接優化

EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2 > 10);

• 執行計劃：select_type=MATERIALIZED (內層物化) + SIMPLE (外層) + Using join buffer (Block Nested Loop)。
• 優化後 SQL：SELECT ... FROM t1 SEMI JOIN (t2) WHERE (t1.a1 = t2.a2 AND t2.a2 > 10)。
• 結論：支援半連接優化。內層物化為臨時表 (含自動鍵)，外層塊巢狀循環半連接。子查詢被「上拉」消除。

3. IN (相關子查詢)：同樣半連接優化

EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t1.a1 = 10);

• 內層引用外層列 t1.a1，但優化器識別出可轉為常數條件，同樣執行半連接，select_type 變為 SIMPLE，無 DEPENDENT SUBQUERY。

4. NOT IN (非相關子查詢)：物化優化，但不消除子查詢

EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 NOT IN (SELECT a2 FROM t2 WHERE t2.a2 > 10);

• 執行計劃：外層 PRIMARY，內層 SUBQUERY，Extra: Using where。
• 優化後 SQL：包含 <in_optimizer>(... <materialize>(SELECT ...) ... <primary_index_lookup> ...)。
• 結論：採用物化策略 (建立臨時表 + 索引查找)，但**不支援半連接消除**，子查詢節點仍存在。

5. > ALL / < ALL / = ALL：最值優化

-- > ALL 等價於 > (SELECT MAX(...))
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 > ALL (SELECT a2 FROM t2 WHERE t2.a2 > 10);
-- < ALL 等價於 < (SELECT MIN(...))
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 < ALL (SELECT a2 FROM t2 WHERE t2.a2 = 10);
-- = ALL 複雜，轉為 EXISTS 策略
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 = ALL (SELECT a2 FROM t2 WHERE t2.a2 = 10);

• > ALL / < ALL：優化為 <= <max>(subquery) / >= <min>(subquery)。若子查詢列有唯一索引，**只需一次索引查找取極值**，極高效。select_type=SUBQUERY。
• = ALL：優化為 NOT EXISTS 策略，select_type=DEPENDENT SUBQUERY。

6. > SOME / > ANY / = SOME / = ANY：最值/半連接優化

• > SOME / > ANY：等價 > (SELECT MIN(...))，最值優化，select_type=SUBQUERY。
• = SOME / = ANY：等價 IN，**半連接優化**，物化 + Block Nested Loop，select_type=SIMPLE + MATERIALIZED。
• < SOME / < ANY：等價 < (SELECT MAX(...))，最值優化。

三、優化策略總結表

四、實戰優化建議

1. 優先改寫為 JOIN：IN/EXISTS 往往可轉為 INNER JOIN / LEFT JOIN ... IS NULL，讓優化器有更多連接順序選擇。
2. 避免 NOT IN + 含 NULL 子查詢：NOT IN (NULL, ...) 永遠為 FALSE/UNKNOWN，導致語義錯誤。用 NOT EXISTS 或 LEFT JOIN ... IS NULL。
3. 為子查詢關聯列建立索引：尤其對 ALL/ANY/SOME 最值優化、半連接物化構建索引至關重要。
4. 檢查 EXPLAIN：關注 select_type 是否為 DEPENDENT SUBQUERY (未優化)、MATERIALIZED (物化)、SIMPLE (半連接上拉)。
5. 衍生表合併：MySQL 5.6+ 支援 derived_merge，FROM (SELECT ...) 可合併到外層，避免物化臨時表。用 optimizer_switch='derived_merge=on'。

五、總結

MySQL 優化器對子查詢的處理「參差不齊」：IN/ANY/SOME 半連接優化最成熟，EXISTS 系列仍保留依賴執行，NOT IN 僅物化不消除，ALL/ANY/SOME 比較操作符可觸發最值優化。理解這些規則，配合索引設計與改寫技巧，是寫出高性能 SQL 的關鍵。