問題背景：一次看似簡單的數據清理

在日常功能開發過程在中，我們經常需要根據某些條件清理特定數據。某天，我需要在 tbl_doa_activityspecial 表中刪除與另一組條件匹配的記錄。直覺上，我寫下了這樣的 SQL：

DELETE FROM tbl_doa_activityspecial 
WHERE ActSetId IN (SELECT DISTINCT ActSetId FROM ...);

這個查詢邏輯清晰，但執行時卻發現性能極差。使用 EXPLAIN 分析執行計劃後，發現了一個令人困惑的現象：MySQL 優化器沒有使用 tbl_doa_activityspecial_ActSetId_IDX 這個明顯應該使用的索引，而是顯示 possible_keys: null。

探尋根源：MySQL 優化器的"保守策略"

經過深入分析，我發現這個問題背後有幾個關鍵原因：

1. 子查詢物化導致的性能陷阱

當 MySQL 遇到 IN (子查詢) 結構時，它可能會選擇將子查詢的結果物化（Materialize）到一個臨時表中，然後再執行主查詢。這個過程包括：

• 執行子查詢並將結果寫入臨時表
• 可能對臨時表進行去重（如使用 DISTINCT 時）
• 最後執行基於臨時表的查詢

物化過程破壞了索引使用的連續性，優化器難以將外部查詢的條件與子查詢的結果高效關聯。

2. DELETE 操作的特殊性

與 SELECT 查詢不同，DELETE 操作有以下特點：

• 風險更高：數據刪除是不可逆操作
• 鎖定要求：需要獲取行鎖，可能影響併發性能
• 日誌記錄：需要生成回滾日誌用於事務處理

因此，MySQL 優化器在處理 DELETE 語句時會更加"保守"，傾向於選擇更可靠而非最高效的執行計劃。

3. 統計信息的影響

如果表的統計信息不是最新的，優化器可能錯誤地估計使用索引與全表掃描的成本，從而做出非最優決策。

解決方案：JOIN 重寫的力量

將查詢重寫為 JOIN 形式後，問題迎刃而解：

DELETE t 
FROM tbl_doa_activityspecial t
JOIN (SELECT DISTINCT ActSetId FROM ...) s ON t.ActSetId = s.ActSetId;

使用 EXPLAIN 分析新查詢，確認已經正確使用了 tbl_doa_activityspecial_ActSetId_IDX 索引。

為什麼 JOIN 更有效？

1. 明確的連接關係：優化器能夠清晰識別兩個數據集之間的關聯條件
2. 避免不必要的物化：減少了創建臨時表的開銷
3. 更好的成本估算：優化器可以更準確地評估不同執行計劃的成本
4. 直接的索引利用：連接條件直接指向索引字段，使索引使用更加直接

深度解析：MySQL 優化器的工作機制

查詢重寫優化

MySQL 優化器會對查詢進行重寫，但不同的原始寫法會導致不同的重寫結果：

• IN 子查詢可能被重寫為 EXISTS 或物化形式
• JOIN 語法則提供了更直接的連接語義

成本估算差異

優化器基於成本估算選擇執行計劃，主要考慮：

• IO 成本：讀取數據的開銷
• CPU 成本：處理數據的開銷
• 內存使用：臨時表、排序等的內存需求

對於 IN 子查詢，優化器可能高估使用索引的成本或低估全表掃描的成本。

其他解決方案對比

方案一：使用 EXISTS 子查詢

DELETE FROM tbl_doa_activityspecial t
WHERE EXISTS (
    SELECT 1 FROM ... s 
    WHERE s.ActSetId = t.ActSetId
);

方案二：強制使用索引

DELETE FROM tbl_doa_activityspecial FORCE INDEX (tbl_doa_activityspecial_ActSetId_IDX)
WHERE ActSetId IN (SELECT ActSetId FROM ...);

DELETE t
FROM tbl_doa_activityspecial t
INNER JOIN (
    SELECT DISTINCT ActSetId FROM ...
) s USING (ActSetId);

-- 先檢查會影響到多少行
SELECT COUNT(*) FROM tbl_doa_activityspecial 
WHERE ActSetId IN (SELECT ActSetId FROM ...);

-- 每次刪除1000條記錄，避免長事務
DELETE FROM tbl_doa_activityspecial 
WHERE ActSetId IN (...)
LIMIT 1000;