MySQL 子查詢優化實戰：從 DEPENDENT SUBQUERY 到衍生表關聯

線上故障現場

• 長時間執行 SQL 佔用 1179 秒，阻塞其他更新
• KILL 後業務恢復，需根治

問題 SQL 分析

SELECT tradedto0_.* 
FROM a1 tradedto0_ 
WHERE tradedto0_.tradestatus='1' 
  AND tradedto0_.tradeoid IN (
    SELECT orderdto1_.tradeoid 
    FROM a2 orderdto1_ 
    WHERE orderdto1_.proname LIKE '%??%' 
       OR orderdto1_.procode LIKE '%??%'
  )
  AND tradedto0_.undefine4='1' 
  AND tradedto0_.invoicetype='1' 
  AND tradedto0_.tradestep='0' 
  AND tradedto0_.orderCompany LIKE '0002%'
ORDER BY tradedto0_.tradesign ASC, tradedto0_.makertime DESC LIMIT 15;

執行計劃診斷

+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
| 1  | PRIMARY            | tradedto0_ | ALL  | NULL          | NULL | NULL    | NULL | 27454 | Using where; Using filesort |
| 2  | DEPENDENT SUBQUERY | orderdto1_ | ALL  | NULL          | NULL | NULL    | NULL | 40998 | Using where              |
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+

雙表全掃描，預估關聯 27,454 × 40,998 ≈ 11 億次，filesort 加劇災難。

索引優化嘗試（失敗）

1. 建立覆蓋索引 (proname, procode, tradeoid) 失敗：proname VARCHAR(1000) 超過 1000 位元組限制
2. 縮減 proname VARCHAR(156) 後建立索引，子查詢走 Using index，但雙表掃描行數未降（8962 × 41005）
3. 實測子查詢結果為空集，應反向驅動

終極改寫：衍生表關聯

SELECT tradedto0_.* 
FROM a1 tradedto0_,
     (SELECT orderdto1_.tradeoid 
      FROM a2 orderdto1_ 
      WHERE orderdto1_.proname LIKE '%??%' 
         OR orderdto1_.procode LIKE '%??%') t2
WHERE tradedto0_.tradestatus='1' 
  AND tradedto0_.tradeoid = t2.tradeoid
  AND tradedto0_.undefine4='1' 
  AND tradedto0_.invoicetype='1' 
  AND tradedto0_.tradestep='0' 
  AND tradedto0_.orderCompany LIKE '0002%'
ORDER BY tradedto0_.tradesign ASC, tradedto0_.makertime DESC LIMIT 15;

改寫後執行計劃與效果

+----+-------------+------------+-------+---------------+----------+---------+------+------+--------------------------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra                          |
+----+-------------+------------+-------+---------------+----------+---------+------+------+--------------------------------+
| 1  | PRIMARY     |  | ALL   | NULL          | NULL     | NULL    | NULL | 0    | Impossible WHERE noticed...    |
| 2  | DERIVED     | orderdto1_ | index | NULL          | ind_a2   | 777     | NULL | 41005| Using where; Using index       |
+----+-------------+------------+-------+---------------+----------+---------+------+------+--------------------------------+

• 優化器識別衍生表結果為空（Impossible WHERE），直接返回空集
• 執行時間：0.03 秒（毫秒級）

關鍵經驗總結

1. DEPENDENT SUBQUERY 是效能殺手，優先改寫為 DERIVED（衍生表）或 JOIN
2. 子查詢結果極小時，衍生表優勢最大
3. 覆蓋索引配合 LIKE '%...%' 仍需全索引掃描，無法根治
4. MySQL 8.0+ 半連接優化可自動處理部分場景，但複雜 OR 條件仍建議手工改寫