MySQL 子查詢改寫優化：從相關子查詢到衍生表關聯

效能災難現場

SELECT i_id, SUM(i_sell) AS i_sell 
FROM table_data 
WHERE i_id IN (
  SELECT i_id FROM table_data 
  WHERE Gmt_create >= '2011-10-07 00:00:00'
) 
GROUP BY i_id;

表 70 萬行，子查詢結果含大量重複 i_id。MySQL 將子查詢轉為 EXISTS 相關子查詢，導致外層全表掃描 70 萬行 × 內層關聯，執行數小時未完成。

MySQL 子查詢優化器缺陷

• 預期：先執行子查詢 → 產生小結果集 → 驅動外層查詢
• 實際：外層表優先全掃描，每行代入子查詢驗證（DEPENDENT SUBQUERY）
• 無法利用子查詢結果集的小特性

改寫策略：衍生表 + 去重 + 關聯

SELECT t2.i_id, SUM(t2.i_sell) AS sold 
FROM (
  SELECT DISTINCT i_id 
  FROM table_data 
  WHERE gmt_create >= '2011-10-07 00:00:00'
) t1
JOIN table_data t2 ON t1.i_id = t2.i_id
GROUP BY t2.i_id;

優化效果

• 子查詢 DISTINCT 大幅縮減驅動行數
• JOIN 替代 IN，優化器可選擇最佳連接順序
• 執行時間：數小時 → 100ms 內

通用改寫模板

進階提示

• MySQL 8.0+ 引入 SEMI JOIN 優化，部分場景自動優化
• 若子查詢結果極小（<100 行），可考慮 IN (值列表) 硬編碼
• 永遠用 EXPLAIN 驗證 select_type 是否為 DERIVED 而非 DEPENDENT SUBQUERY