MySQL 子查詢限制與優化策略完整指南：行比較、相關子查詢、物化、半連接與改寫為 JOIN

已知 Bug：NULL 與 ALL/ANY/SOME 比較

NULL = (SELECT ...) 子查詢回空集 → 非標準結果 (非 TRUE/FALSE)。需額外處理 IS NULL 邏輯。

行比較操作支援度

原因：IN 內部改寫為 = + AND 序列，ALL/ANY 無對應改寫邏輯。

行建構器優化差異

-- 可優化 (索引合併/範圍掃描)
col1 = val1 AND col2 = val2 AND ...

-- 難優化 (全表掃描風險)
(col1, col2) = (val1, val2)

建議：明確展開為 AND 串聯，獲得穩定執行計畫。

IN 子查詢效能陷阱

典型慢場景：子查詢回少量行、外部查詢大量行需比較。
優化器未有效將外部大表驅動轉為內部小表驅動。

FROM 子句子查詢 (衍生表) 限制

• 非相關：優先物化 (建立臨時表)，無法使用索引。
• 相關：不支援，報錯 Invalid reference to FROM-clause subquery。

同表更新/查詢限制

-- 禁止
DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ...);
UPDATE t SET ... WHERE col = (SELECT ... FROM t ...);
INSERT INTO t SELECT ... FROM t ...;

-- 允許：衍生表物化隔離
UPDATE t SET col = (SELECT * FROM (SELECT ... FROM t ...) AS dt);

子查詢改寫為 JOIN 優化策略

優化器未來改進方向 (歷史視角)

• 連接順序重排：允許優化器決定先讀外部表還是內部表。
• 相關子查詢結果快取：外部行值未變時重用上次結果。
• 衍生表合併演算法：類似 View MERGE，下推條件、使用索引。
• IN/ANY/ALL 記憶體雜湊表：小結果集建雜湊，大結果集建臨時表索引。

實戰優化清單

1. EXPLAIN FORMAT=JSON 觀察 subquery 節點 materialized / dependent。
2. 小表驅動大表：確保 EXISTS 子查詢在大表上、IN 子查詢回小結果集。
3. 衍生表加索引：CREATE INDEX idx ON derived_table (col) (5.6+ 支援衍生表索引)。
4. 避免 NOT IN 含 NULL，改用 NOT EXISTS。
5. 複雜報表查詢考慮 WITH RECURSIVE (CTE, 8.0+) 替代多層巢狀子查詢。

結語

MySQL 子查詢優化歷經「物化 → 半連接 → 衍生表合併 → CTE」演進。現代版本 (8.0+) 優先使用 CTE + 明確 JOIN，讓優化器獲得完整統計資訊，避免巢狀子查詢的黑盒行為。核心原則：小結果集驅動大表、顯式 JOIN 替代隱式 IN、善用索引下推。