MySQL 子查詢三大類型全解析：WHERE 型、FROM 型、EXISTS 型寫法與優化指南

子查詢是 SQL 表達複雜邏輯的核心工具。本文按結果集角色劃分為三類，給出典型場景、寫法範例、優化建議與常見陷阱。

一、WHERE 型子查詢（標量/列/行子查詢）

特徵：內層結果作為外層 WHERE 條件的比較值。

1. 標量子查詢（單值）

-- 查詢最新商品（無 ORDER BY）
SELECT goods_id, goods_name 
FROM goods 
WHERE goods_id = (SELECT MAX(goods_id) FROM goods);

2. 列子查詢（IN / NOT IN / ANY / ALL）

-- 每個分類最新商品
SELECT cat_id, goods_id, goods_name
FROM goods
WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id);

3. 優化要點

• IN 子查詢 MySQL 5.6+ 優化為 SEMI JOIN，效率接近 JOIN。
• NOT IN 遇 NULL 導致空結果集陷阱 → 改用 NOT EXISTS 或 LEFT JOIN ... IS NULL。
• 相關子查詢（引用外層表）若無索引支援，退化為 Nested Loop 全表掃描，建議改寫為 JOIN 或 APPLY（MariaDB/8.0 LATERAL）。

二、FROM 型子查詢（衍生表/物化視圖）

特徵：內層結果作為外層 FROM 的數據源，必須起別名。

典型場景：多步聚合/過濾

-- 找出掛科 ≥2 門學生的平均分
SELECT name, AVG(score) AS avg_score
FROM stu
WHERE name IN (
  SELECT name FROM (
    SELECT name, COUNT(*) AS fail_cnt
    FROM stu
    WHERE score < 60
    GROUP BY name
    HAVING fail_cnt >= 2
  ) AS t
)
GROUP BY name;

優化策略

1. 物化優化：MySQL 5.6+ 自動將衍生表物化到內存臨時表（含索引），外層查詢走 ref 訪問。
2. 合併優化：滿足條件時優化器將衍生表合併到外層查詢（避免物化開銷），需無聚合/無 LIMIT/無 DISTINCT 等。
3. 手動改寫：用 CTE (WITH) 替代嵌套 FROM 子查詢，可讀性強且 8.0+ 支援物化/合併選擇。

三、EXISTS 型子查詢（相關存在性檢查）

特徵：外層行驅動內層執行，內層只判斷是否存在（返回 TRUE/FALSE），不返回具體數據。

典型場景：找出有商品的分類

SELECT cat_id, cat_name
FROM category
WHERE EXISTS (
  SELECT 1 FROM goods WHERE goods.cat_id = category.cat_id
);

EXISTS vs IN 效能對比

EXISTS 內部可用 LIMIT 1

WHERE EXISTS (SELECT 1 FROM goods WHERE cat_id = c.cat_id LIMIT 1)

明確語義：只要存在一行即可，優化器可提前終止掃描。

四、綜合優化清單

1. 優先考慮 JOIN 替代子查詢（可讀性允許時），利用優化器 Join Reorder。
2. 相關子查詢務必給驅動列建立索引。
3. NOT IN 禁用，全面改用 NOT EXISTS / LEFT JOIN ... IS NULL。
4. 衍生表加 /*+ MAX_EXECUTION_TIME(1000) */ 防止物化超時（8.0+）。
5. 使用 EXPLAIN 觀察 select_type（DEPENDENT SUBQUERY/DERIVED/MATERIALIZED）與 type 確認訪問策略。