MySQL 嵌套查詢優化陷阱：從「依賴子查詢」到「衍生表/Join」的性能躍遷

慘痛教訓：優化器將獨立子查詢誤判為相關子查詢

場景：三層分級 category → subcategory → item，需查詢某 category 下 item 數 > 2000 的所有 subcategory 完整信息。

高效 Join 寫法（10 秒內）

SELECT c.id
FROM subcategory AS c
INNER JOIN item AS i ON i.subcategory = c.id
WHERE c.category = 14
GROUP BY c.id
HAVING COUNT(*) > 2000;

嵌套 IN 寫法（執行不完）

SELECT * FROM subcategory
WHERE id IN (
    SELECT c.id
    FROM subcategory AS c
    INNER JOIN item AS i ON i.subcategory = c.id
    WHERE c.category = 14
    GROUP BY c.id
    HAVING COUNT(*) > 2000
);

EXPLAIN 揭秘：外層全表掃描 + 依賴子查詢

id=1, select_type=PRIMARY, table=subcategory, type=ALL, rows=300783, Extra=Using where
id=2, select_type=DEPENDENT SUBQUERY, table=c, type=ref, rows=100, Extra=Using where; Using index; Using temporary; Using filesort
id=2, select_type=DEPENDENT SUBQUERY, table=i, type=ref, rows=28, Extra=Using index

災難核心：MySQL 將內層轉為 DEPENDENT SUBQUERY，外層掃描 30 萬行，每行執行一次內層查詢 → 嵌套循環 30 萬次。

優化器重寫後的邏輯等價形式

SELECT * FROM subcategory AS s
WHERE (
    s.id,
    (
        SELECT c.id
        FROM subcategory AS c
        JOIN item AS i ON i.subcategory = c.id
        WHERE c.category = 14
        GROUP BY c.id
        HAVING COUNT(0) > 2000
           AND (s.id) = (c.id)
    )
);

破局方案一：衍生表強制物化（MySQL 5.6+）

SELECT * FROM subcategory
WHERE id IN (
    SELECT id FROM (
        SELECT c.id
        FROM subcategory AS c
        INNER JOIN item AS i ON i.subcategory = c.id
        WHERE c.category = 14
        GROUP BY c.id
        HAVING COUNT(*) > 2000
    ) AS x
);

關鍵：最內層包裹 SELECT id FROM ( ... ) AS x，優化器識別為獨立衍生表，先物化到臨時表（僅 10 行），外層再關聯，秒級完成。

破局方案二：標準 Join 改寫（推薦，通用性最強）

SELECT s.*
FROM subcategory AS s
INNER JOIN (
    SELECT c.id
    FROM subcategory AS c
    INNER JOIN item AS i ON i.subcategory = c.id
    WHERE c.category = 14
    GROUP BY c.id
    HAVING COUNT(*) > 2000
) AS t ON s.id = t.id;

優勢：語義清晰、穩定走 Join 邏輯、不依賴優化器版本行為、易維護。

避坑指南

• 原則：有疑問，EXPLAIN 看 select_type 是否出現 DEPENDENT SUBQUERY。
• 大表禁用：WHERE ... IN (SELECT ...)、WHERE ... NOT IN (SELECT ...)。
• 衍生表限制：MySQL 5.7+ 衍生表默認合併（derived_merge=on），若需強制物化可用 /*+ MAX_EXECUTION_TIME(1) */ 或關閉 optimizer_switch='derived_merge=off'。
• 同表更新限制：UPDATE t SET ... WHERE id IN (SELECT id FROM t ...) 報錯，需用多表 Join 或衍生表繞過。

結語

MySQL 歷史版本對獨立子查詢優化不足是已知限制。開發者應養成「優先 Join、謹慎 IN/EXISTS、必跑 EXPLAIN」習慣，將性能風險消滅在代碼評審階段。