MySQL 優化系列：DELETE 子查詢改寫為 JOIN 大幅提升效能

MySQL 優化系列：DELETE 子查詢改寫為 JOIN 大幅提升效能

1. 問題場景

某業務 SQL 使用子查詢執行 DELETE，耗時 31.74 秒；改為 SELECT 後僅需 0 秒。經分析，子查詢導致優化器選擇全表掃描 (type=ALL)，掃描 571 萬行；而 SELECT 版本則走主鍵等值查詢 (type=eq_ref)，僅掃描 1 行並避免回表。

原始低效 SQL

DELETE FROM trade_info
WHERE id IN (
    SELECT id FROM (
        SELECT a.id
        FROM trade_info a
        JOIN order_info b ON a.order_id = b.id
        JOIN user c ON b.buyer = c.id
        WHERE c.itv_account = '90000248'
    ) temp
);

2. 優化思路：子查詢改寫為多表 JOIN DELETE

MySQL 支援多表 DELETE 語法：DELETE t1 FROM t1 JOIN t2 ON ... WHERE ...。將子查詢轉換為衍生表並 JOIN 回主表：

優化後 SQL

DELETE trade_info
FROM trade_info
JOIN (
    SELECT a.id
    FROM trade_info a
    JOIN order_info b ON a.order_id = b.id
    JOIN user c ON b.buyer = c.id
    WHERE c.itv_account = '90000248'
) t2 ON trade_info.id = t2.id;

3. 效能對比

• 優化前：31.74 秒 (全表掃描 571 萬行)
• 優化後：0.01 秒 (索引查詢，極少量掃描)

4. 經驗總結

• MySQL 5.6+ 雖優化子查詢，但特定場景仍不如 JOIN 效能穩定
• 發現子查詢效能差時，優先嘗試改寫為 JOIN 形式
• 需警惕優化器在特定情況下的不足，主動繞過潛在陷阱

此案例證明：將相關子查詢轉換為多表 JOIN DELETE，能讓優化器選擇更優執行計畫，實現數量級效能提升。