MySQL 子查詢改寫優化：從數小時降至 100ms 的實戰技巧

MySQL 子查詢改寫優化：從數小時降至 100ms 的實戰技巧

Oracle 等資料庫對子查詢優化成熟，但 MySQL 處理子查詢機制不同，易導致災難性效能。

問題 SQL

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;

業務邏輯：先找 10/07 新售出的書，再查這些書全年的銷量。

MySQL 子查詢執行機制缺陷

• MySQL 會將子查詢改寫為「外層掃描 + 內層關聯」
• 外表 70 萬行，每行都傳入子查詢關聯 → **近 70 萬次關聯**
• 子查詢結果有大量重複，未去重 → 執行數小時未完成

改寫策略：子查詢轉 JOIN + 去重

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;

優化關鍵點

1. IN 子查詢 → 衍生表 JOIN：讓優化器先執行子查詢產生臨時結果集
2. 加上 DISTINCT：將 70 萬次關聯降為唯一值數量（極少）
3. 小表驅動大表：t1 只有少量 distinct i_id，反向驅動 t2 索引查找

結果

**執行時間從數小時 → < 100ms**，效能提升 **數萬倍**。

延伸：MySQL 子查詢優化原則

💡 核心心法：MySQL 子查詢優化器較弱，**主動改寫為 JOIN** 往往能獲得數量級效能提升。