MySQL 子查詢優化實戰：從 DEPENDENT SUBQUERY 到衍生表，耗時從分鐘級降至毫秒級

MySQL 子查詢優化實戰：從 DEPENDENT SUBQUERY 到衍生表，耗時從分鐘級降至毫秒級

MySQL 子查詢優化器歷來薄弱，IN (subquery) 常被轉為 DEPENDENT SUBQUERY 導致外表全掃描 × 內表全掃描的笛卡爾級災難。本文實戰案例：通過覆蓋索引嘗試失效後，將子查詢改寫為衍生表，配合索引優化，將執行時間從分鐘級降至 30 毫秒。

事故現場

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;

長時間執行，阻塞其他更新，Kill 後業務恢復。

執行計劃分析

EXPLAIN ...\G
id=1  select_type=PRIMARY      table=tradedto0_  type=ALL  rows=27454  Extra: Using where; Using filesort
id=2  select_type=DEPENDENT SUBQUERY  table=orderdto1_  type=ALL  rows=40998  Extra: Using where

外表全掃描 2.7 萬行，內表全掃描 4 萬行，**預估掃描 10 億行**，且子查詢為 DEPENDENT SUBQUERY 无法下推。

嘗試一：添加覆蓋索引

-- proname VARCHAR(1000) 過長，修改為 156
ALTER TABLE a2 MODIFY COLUMN PRONAME VARCHAR(156);
ALTER TABLE a2 ADD INDEX ind_a2 (proname, procode, tradeoid);

執行計劃變為：

id=1  type=ref  key=ind_tradestatus  rows=8962
id=2  type=index  key=ind_a2  rows=41005  Extra: Using where; Using index

掃描行數仍近 9 萬 × 4 萬，**性能未本質改善**。

關鍵發現：子查詢結果集為空

SELECT tradeoid FROM a2 WHERE proname LIKE '%??%' OR procode LIKE '%??%';
Empty set (0.05 sec)

子查詢無結果 → 應以子查詢為驅動表（先執行子查詢，結果為空則直接返回）。

優化方案：改寫為衍生表

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;

優化後執行計劃

EXPLAIN ...\G
id=1  select_type=PRIMARY  table=  type=ALL  rows=0  Extra: Impossible WHERE noticed after reading const tables
id=2  select_type=DERIVED  table=orderdto1_  type=index  key=ind_a2  rows=41005  Extra: Using where; Using index

衍生表 t2 先執行，結果為空 → 外層 Impossible WHERE 直接返回，**執行時間 0.03 秒**。

核心心法

1. MySQL 子查詢天然弱：IN/EXISTS 子查詢常轉為 DEPENDENT SUBQUERY，外表驅動內表，性能不可控。
2. 優先改寫為 JOIN 或衍生表：JOIN (SELECT ...) t 使子查詢獨立執行（DERIVED），可利用索引、結果集小則極快。
3. 覆蓋索引是前提：子查詢/衍生表必須走覆蓋索引（Using index），避免回表。
4. 結果集特性決定策略：子查詢結果極小/為空 → 衍生表優先；結果集大且需去重 → EXISTS 或 JOIN 配合 DISTINCT。

版本演進提示

• MySQL 5.6+ 引入 subquery_materialization、semijoin 等優化，部分場景自動轉衍生表。
• MySQL 8.0 CTE (WITH) 寫法更優雅，優化器對 CTE 物化支持更好。
• 但**改寫為顯式衍生表/JOIN 仍是最穩妥的跨版本方案**。