MySQL UPDATE 子查詢性能陷阱：IN 子查詢被優化器轉為 DEPENDENT SUBQUERY 導致全表掃描

MySQL UPDATE 子查詢性能陷阱：IN 子查詢被優化器轉為 DEPENDENT SUBQUERY

同樣的 IN (subquery) 寫法，SELECT 走索引毫秒級，UPDATE 卻退化為 DEPENDENT SUBQUERY 全表掃描秒級。本文還原現場、分析原因、給出改寫方案與驗證結果。

一、現場還原

表結構與數據量

• mer_stage：21.6 萬行，主鍵 STAGE_ID，索引 mer_stage_s_e_ms (STATUS, EDITABLE, MER_STATUS)
• proc：6,450 行，主鍵 proc_id，索引 proc_mer_id_index (mer_id)

問題 SQL

UPDATE mer_stage
SET editable = 1
WHERE stage_id IN (
    SELECT associated_id FROM proc
    WHERE proc_id IN (6446, 6447, 6450)
);

執行計劃（EXPLAIN）

+----+--------------------+-----------+-------+-------------------+---------+---------+------+--------+--------------------------------------------------+
| id | select_type        | table     | type  | possible_keys     | key     | key_len | ref  | rows   | Extra                                            |
+----+--------------------+-----------+-------+-------------------+---------+---------+------+--------+--------------------------------------------------+
|  1 | PRIMARY            | mer_stage | ALL   | PRIMARY           | NULL    | NULL    | NULL | 190102 | Using where                                      |
|  2 | DEPENDENT SUBQUERY | proc      | range | PRIMARY,proc_mer_ | PRIMARY | 4       | NULL |      3 | Using where                                      |
+----+--------------------+-----------+-------+-------------------+---------+---------+------+--------+--------------------------------------------------+

關鍵點：select_type = DEPENDENT SUBQUERY，外表 mer_stage 走 ALL 全表掃描 19 萬行，內表 proc 每行都執行一次 range 查詢，總耗時 2.1 秒。

二、對比驗證：SELECT 同寫法卻走優化路徑

SELECT * FROM mer_stage
WHERE stage_id IN (
    SELECT associated_id FROM proc
    WHERE proc_id IN (6446, 6447, 6450)
);

執行計劃顯示 select_type = SIMPLE，類型 ref，耗時 0.053 秒。優化器對 SELECT 進行了子查詢展開/半連接優化，但對 UPDATE 未啟用。

三、根因分析

MySQL 優化器對 UPDATE/DELETE 的 IN/EXISTS 子查詢優化較保守（歷史版本 Bug #33292），將非相關子查詢錯誤標記為 DEPENDENT SUBQUERY，導致外表無法利用索引，退化為全表掃描逐行評估子查詢。

四、解決方案：改寫為多表 UPDATE（JOIN）

UPDATE mer_stage m
JOIN proc p ON m.stage_id = p.associated_id
SET m.editable = 1
WHERE p.proc_id IN (6446, 6447, 6450);

優化後執行計劃

+----+-------------+-------+-------+-------------------+---------+---------+--------------------------+------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref                      | rows | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | p     | range | PRIMARY,proc_mer_ | PRIMARY | 4       | NULL                     |    3 | Using where |
|  1 | SIMPLE      | m     | eq_ref| PRIMARY           | PRIMARY | 4       | db.p.associated_id       |    1 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+--------------------------+------+-------------+

驅動表 proc 走 range 3 行，被驅動表 mer_stage 走 eq_ref 主鍵查找，耗時 0.047 秒，提升 45 倍。

五、最佳實踐清單

1. UPDATE/DELETE 含子查詢優先改寫為 JOIN，語義等價且穩定走索引。
2. EXPLAIN 關注 select_type：出現 DEPENDENT SUBQUERY 且外表 type=ALL 即為紅旗。
3. 子查詢結果集較小時，可考慮應用層先查出 ID 列表，拼接 IN (id1,id2...) 直接走 range。
4. MySQL 8.0.16+ 引入 WITH (CTE) 可輔助優化器展開，但 JOIN 仍是最穩妥寫法。

六、總結

「同樣的子查詢，SELECT 快 UPDATE 慢」是 MySQL 優化器歷史遺留行為。生產環境所有帶子查詢的 DML 務必 EXPLAIN 驗證，見 DEPENDENT SUBQUERY 必改 JOIN。