MySQL 子查詢類型全解析與優化器行為深度剖析

三大子查詢類型語法示例

1. WHERE 型（標量/列子查詢）

-- 查詢最新商品
SELECT goods_id, goods_name FROM goods 
WHERE goods_id = (SELECT MAX(goods_id) FROM goods);

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

2. FROM 型（派生表）

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

3. EXISTS 型（相關子查詢）

-- 有商品的分類
SELECT cat_id, cat_name FROM category 
WHERE EXISTS (SELECT 1 FROM goods WHERE goods.cat_id = category.cat_id);

MySQL 8.0 優化器對各類子查詢的處理策略

基於 EXPLAIN EXTENDED 實測數據（表 t1、t2 均有索引）：

關鍵優化機制詳解

半連接

將子查詢「上拉」到外層查詢，轉換為類似 JOIN 的執行計劃，避免重複掃描。適用於 IN、=ANY、=SOME 非相關子查詢。EXPLAIN 中出現 semi join 標識。

物化

將子查詢結果集寫入臨時表（內存/磁盤），建立哈希索引，外層查詢通過索引查找。適用於 IN、NOT IN 非相關子查詢。EXPLAIN 中 select_type=MATERIALIZED。

MAX/MIN 優化

針對 >ALL、<ALL、>SOME、<SOME、>ANY、<ANY，若子查詢列有唯一索引，優化器直接走索引求極值，子查詢僅執行一次。

生產環境改寫最佳實踐

1. EXISTS/NOT EXISTS → LEFT JOIN / NOT IN：
   SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id=t2.id)
   → SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NOT NULL
2. IN 子查詢 → JOIN：確保被驅動表有索引，小表驅動大表
3. FROM 派生表 → CTE (WITH)：MySQL 8.0 支持 CTE，可避免物化臨時表開銷
4. 避免相關子查詢：優化器無法消除，必須逐行執行，性能隨外表行數線性增長

調試技巧

-- 查看優化器重寫後的 SQL
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

-- 強制指定優化策略
SELECT /*+ MAX_EXECUTION_TIME(1000) */ ...;
SELECT /*+ NO_SEMIJOIN(t2) */ ...;