問題現場

• 問題 SQL 執行 12 次，耗時 286-480 秒
• 外層表 t1 90 萬行，每次全掃描
• 優化器將外層表壓入子查詢 (Correlated Subquery → Dependent Subquery)

優化器為什麼這樣做？

MySQL 優化器認為：「將外層條件下推到子查詢，可用索引精準查找，比全表掃描外層再逐行執行子查詢更高效。」

但現實是：外層表巨大 (90 萬)，子查詢無法有效利用索引，導致 90 萬 × 子查詢成本 的災難性複雜度。

優化前 SQL (簡化)

SELECT * FROM t1
WHERE t1.col1 IN (
  SELECT t2.col2 FROM t2 WHERE t2.col3 = t1.col4
);

優化策略：改寫為 JOIN / 衍生表 / CTE

方案一：INNER JOIN (最通用)

SELECT t1.* 
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col4
WHERE t2.col2 IS NOT NULL; -- 視語意調整

方案二：衍生表 + JOIN (物化子查詢)

SELECT t1.*
FROM t1
JOIN (
  SELECT DISTINCT col3, col2 FROM t2 WHERE col2 IS NOT NULL
) dt ON dt.col3 = t1.col4;

方案三：CTE 物化 (MySQL 8.0+)

WITH dt AS (
  SELECT DISTINCT col3, col2 FROM t2 WHERE col2 IS NOT NULL
)
SELECT t1.* FROM t1 JOIN dt ON dt.col3 = t1.col4;

方案四：EXISTS (語意等價時)

SELECT * FROM t1
WHERE EXISTS (
  SELECT 1 FROM t2 WHERE t2.col3 = t1.col4 AND t2.col2 IS NOT NULL
);

優化後效果

• 執行時間：從 286-480 秒降至 < 1 秒
• 掃描行數：從 90 萬全掃描降至索引查找 ~100 行
• 無鎖爭用、無堵塞

核心原則

MySQL 5.6-8.0 對關聯子查詢優化有限。能用 JOIN/CTE 改寫就改寫，將「逐行執行」變為「集合運算」。8.0 引入 Materialized CTE 和 Semi-join 優化，但複雜關聯子查詢仍建議手工改寫。

檢測腳本：找出潛在問題 SQL

-- 慢查詢日誌中尋找 Dependent Subquery
SELECT * FROM mysql.slow_log 
WHERE sql_text LIKE '%DEPENDENT SUBQUERY%' 
   OR sql_text LIKE '%correlated%';