MySQL 優化實戰：將 DELETE 子查詢重寫為多表 JOIN 大幅提升效能

問題背景

開發人員常遇到一個令人困惑的現象：同一條 SQL，將 DELETE 改為 SELECT 後，執行時間從 31.74 秒驟降至近 0 秒。原始語句使用子查詢刪除 trade_info 表中符合條件的記錄：

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

執行計劃顯示 trade_info 走全表掃描（type=ALL），掃描 571 萬行；而改為 SELECT 後則走主鍵等值查詢（type=eq_ref），僅掃描 1 行，且避免了回表。兩者代價差異巨大。

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

MySQL 優化器在處理 DELETE ... WHERE id IN (subquery) 時，無法有效下推條件，導致外層表全掃描。解決方案是利用 MySQL 支援的多表 DELETE 語法，將子查詢轉為內連接：

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;

優化後僅需 0.01 秒，掃描行數大幅下降，效能提升超過 3000 倍。

最佳實踐建議

• 遇到子查詢效能不佳時，優先嘗試改寫為 JOIN 形式。
• MySQL 5.6+ 雖優化了子查詢，但在複雜關聯刪除/更新場景下仍可能失效，需具備「繞過優化器缺陷」的意識。
• 針對大表刪除，建議先用 SELECT 驗證條件與執行計劃，再套用多表 DELETE 語法。