【MySQL篇】DEPENDENT SUBQUERY(依賴性子查詢)優化：從百秒到秒級響應的四種優化辦法

💫《博主介紹》：?又是一天沒白過，我是奈斯，從事IT領域?

💫《擅長領域》：??擅長阿里云AnalyticDB for MySQL(分布式數據倉庫)、Oracle、MySQL、Linux、prometheus監控；并對SQLserver、NoSQL(MongoDB)有了解??

💖💖💖大佬們都喜歡靜靜的看文章，并且也會默默的點贊收藏加關注💖💖💖

? ? 數據庫中的慢查詢，真的是讓人抓狂！對于慢查詢不僅會讓數據庫服務器的資源使用率瞬間飆升，甚至一個慢查詢就能把?CPU、內存、IO?等資源幾乎全部占滿?，并且還會導致后續的查詢全部變慢，從而導致系統響應速度直接癱了📉，所以優化慢SQL是一件非常非常重要的事情。

? ? 優化慢 SQL 不僅需要對 SQL 語法了如指掌 🧠，還得熟悉MySQL中各種執行計劃中的訪問路徑，比如全表掃描、索引掃描、臨時表等。最近博主正好遇到一個經典的慢 SQL 問題，就是標題提到的?DEPENDENT SUBQUERY(依賴子查詢) 。這個案例非常典型，優化過程也很有意思，所以整理出來分享給大家。

? ? ? ? ? ??

生產 DEPENDENT SUBQUERY 案例分享：

優化DEPENDENT SUBQUERY方法一：改寫成CTE（Common Table Expression，公用表表達式）（需要注意：CTE是MySQL 8.0.1版本中引入的，所以8.0以下的版本沒有辦法改寫成CTE寫法）

優化DEPENDENT SUBQUERY方法二：將 IN 子查詢重寫為?JOIN

優化DEPENDENT SUBQUERY方式三：使用 EXISTS 替代 IN 子查詢

優化DEPENDENT SUBQUERY方法四：創建索引。無論使用上述哪種方法，確保相關列上有索引可以顯著提升性能。

? ? ? ? ? ? ? ? ? ?

? ? 在開始之前先聊一聊 DEPENDENT SUBQUERY(依賴性子查詢)?。在MySQL8.0官網文檔中描述DEPENDENT SUBQUERY是執行計劃中的select_type列的輸出。

? ? 在MySQL中，?DEPENDENT SUBQUERY 是一種子查詢類型，它的執行依賴于外部查詢的每一行數據。這意味著，對于外部查詢的每一行，子查詢都會重新執行一次。這種類型的子查詢通常會導致性能問題，尤其是在數據量較大的情況下。

? ? ? ? ? ?

DEPENDENT SUBQUERY 的工作原理：

依賴關系：?DEPENDENT SUBQUERY 的子查詢會引用外部查詢中的列，因此它的執行結果依賴于外部查詢的當前行。
執行次數：對于外部查詢的每一行，子查詢都會重新執行一次。如果外部查詢有?N 行，子查詢就會執行?N 次。
性能影響：由于子查詢需要重復執行，這種類型的子查詢通常會導致性能問題，尤其是在外部查詢返回大量數據時。

? ? 以下是一個簡單的圖示，幫助理解?DEPENDENT SUBQUERY的執行流程：

? ?

DEPENDENT SUBQUERY 案例：
SELECT *
FROM employees e
WHERE EXISTS (SELECT 1FROM departments dWHERE d.manager_id = e.employee_id
);
? ? ? ? ? ? ? ?

在這個查詢中：

外部查詢從?employees 表中檢索數據。
子查詢檢查 departments 表中是否存在?manager_id?等于當前?employee_id 的記錄。
對于 employees 表中的每一行，子查詢都會執行一次。

? ? 如果執行計劃中顯示子查詢的?select_type 為?DEPENDENT SUBQUERY ，這意味著子查詢會為?employees 表中的每一行執行一次.這種案例很容易理解，因為在子查詢中引用了外部查詢中的表，所以子查詢會為外部表中的每一行執行一次。

? ? ? ? ? ?

? ? ? ?

關于上述SQL DEPENDENT SUBQUERY 的執行流程：

步驟一：外部查詢開始執行。從?employees 表中讀取第一行數據。
步驟二：子查詢執行。根據當前行的?employee_id?，在 departments 表中查找是否存在匹配的?manager_id?。
步驟三：返回結果。如果子查詢返回結果，則外部查詢的當前行被保留；否則，丟棄。
步驟四：重復執行。外部查詢繼續讀取下一行，重復上述步驟，直到所有行都被處理。

? ? ? ? ? ? ? ? ? ? ? ? ?

生產 DEPENDENT SUBQUERY 案例分享：

? ? SQL中部分表名和字段有點敏感，所以博主用通用方式表達。這個SQL不長，并且邏輯也不復雜，硬是執行了幾百秒都沒有返回結果😰。liu_mysqloltp_ywcs_org表只有9萬行數據，liu_mysqloltp_ywcs_cmn更是只有4092行數據。
SELECTa.Region_Name,t.* 
FROMliu_mysqloltp_ywcs_org t,liu_mysqloltp_ywcs_cmn a 
WHEREt.REGION_ID = a.id AND t.Hospital_Name IN ( SELECT tt.Hospital_Name FROM liu_mysqloltp_ywcs_org tt GROUP BY tt.Hospital_Name HAVING count(*) > 1 );
? ? ??

? ? 通過explain查看一下這個SQL的執行計劃，在這個查詢中：

外部查詢：從?liu_mysqloltp_ywcs_org?表和?liu_mysqloltp_ywcs_cmn?表中選擇數據。
子查詢：查找?liu_mysqloltp_ywcs_org?表中?Hospital_Name?出現次數大于 1 的記錄。
問題：子查詢是?DEPENDENT SUBQUERY?，會為外部查詢的每一行執行一次，導致性能低下。

? ? 主要性能瓶頸是?DEPENDENT SUBQUERY?和全表掃描，可以看出有以下兩個優化點：

PRIMARY?表?a?的掃描：從?ALL?變為?ref?或?range，減少掃描行數。

DEPENDENT SUBQUERY：被消除或優化為?JOIN 等，減少子查詢的執行次數。

? ? ? ? ? ? ? ? ? ??

優化DEPENDENT SUBQUERY方法一：改寫成CTE（Common Table Expression，公用表表達式）（需要注意：CTE是MySQL 8.0.1版本中引入的，所以8.0以下的版本沒有辦法改寫成CTE寫法）

? ? with as 是 SQL 中用于定義?CTE（Common Table Expression，公用表表達式） 的語法。它允許你在一個查詢中定義一個臨時的命名結果集，這個結果集可以在同一個查詢中多次引用。WITH AS 的主要作用是提高查詢的可讀性和可維護性，尤其是在處理復雜查詢時。

? ? ??

作用：把重復用到的sql語句放在with as里面，取一個別名，后面的查詢就可以用它。對大批量的sql語句起到一個優化的作用，而且清楚明了

優點：可讀性增強，比如對特定with子查詢取個有意義的名字。With子查詢只執行一次，將結果存儲在用戶臨時表空間中，多次引用，增強性能

? ? ? ?
WITH DuplicateHospitals AS (SELECTtt.Hospital_NameFROMliu_mysqloltp_ywcs_org ttGROUP BYtt.Hospital_NameHAVINGCOUNT(*) > 1
)
SELECTa.Region_Name,t.*
FROMliu_mysqloltp_ywcs_org t
JOINliu_mysqloltp_ywcs_cmn a ON t.REGION_ID = a.id
JOIN
DuplicateHospitals dh ON t.Hospital_Name = dh.Hospital_Name;
優化點如下：

DuplicateHospitals 是一個 CTE，用于查找 liu_mysqloltp_ywcs_org 表中 Hospital_Name 出現次數大于 1 的記錄。
這個 CTE 只執行一次，結果會被緩存供后續查詢使用。避免了原始查詢中 IN? 子查詢的重復執行。
主查詢從 liu_mysqloltp_ywcs_org 表和 liu_mysqloltp_ywcs_cmn 表中選擇數據。
通過 JOIN 將 DuplicateHospitals ?CTE 與 liu_mysqloltp_ywcs_org 表關聯，過濾出 Hospital_Name 出現次數大于 1 的記錄。

? ? ? ? ? ? ? ??

優化DEPENDENT SUBQUERY方法二：將 IN 子查詢重寫為?JOIN

? ? 可以將?IN?子查詢重寫為?JOIN?，避免?DEPENDENT SUBQUERY?。?IN?子查詢可能會導致性能問題，尤其是在子查詢返回的結果集較大時。可以嘗試將子查詢改寫為?JOIN?，這樣可以減少查詢的復雜度。
SELECTa.Region_Name,t.*
FROMliu_mysqloltp_ywcs_org t
JOIN liu_mysqloltp_ywcs_cmn a ON t.REGION_ID = a.id
JOIN (SELECT tt.Hospital_NameFROM liu_mysqloltp_ywcs_org ttGROUP BY tt.Hospital_NameHAVING COUNT(*) > 1
) sub ON t.Hospital_Name = sub.Hospital_Name;
優化點如下：

子查詢被提取為一個派生表（sub），只執行一次。
外部查詢通過 JOIN 與派生表關聯，避免了 DEPENDENT SUBQUERY 。

? ? ? ? ? ? ? ? ? ??

優化DEPENDENT SUBQUERY方式三：使用 EXISTS 替代 IN 子查詢

? ? EXISTS?通常比?IN?更高效，因為它可以在找到第一個匹配項后立即停止搜索。
SELECTa.Region_Name,t.* 
FROMliu_mysqloltp_ywcs_org t
JOINliu_mysqloltp_ywcs_cmn a 
ONt.REGION_ID = a.id 
WHEREEXISTS (SELECT 1 FROM liu_mysqloltp_ywcs_org tt WHERE tt.Hospital_Name = t.Hospital_Name GROUP BY tt.Hospital_Name HAVING COUNT(*) > 1); 
優化點如下：

EXISTS 子查詢在找到第一個匹配項后就會停止搜索，避免了不必要的掃描。
對于依賴子查詢，EXISTS 通常比 IN 更高效，因為它不需要緩存結果集。
EXISTS 可以更好地利用索引，尤其是在子查詢中使用了索引列時。

? ? ? ? ? ? ? ? ? ? ? ??

優化DEPENDENT SUBQUERY方法四：創建索引。無論使用上述哪種方法，確保相關列上有索引可以顯著提升性能。

在 liu_mysqloltp_ywcs_org 表的 REGION_ID 列上創建索引：
CREATE INDEX idx_region_id ON liu_mysqloltp_ywcs_org(REGION_ID);
在 liu_mysqloltp_ywcs_org 表的 Hospital_Name 列上創建索引：

??
CREATE INDEX idx_Hospital_Name ON liu_mysqloltp_ywcs_org(Hospital_Name);
在 liu_mysqloltp_ywcs_cmn 表的 id 列上創建索引：
CREATE INDEX idx_id ON liu_mysqloltp_ywcs_cmn(id);

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

? ? 關于?DEPENDENT SUBQUERY?的優化分享就到這里。?博主覺得這個案例真的是非常經典，堪稱慢 SQL 優化的“教科書級”范例 📚。通過這次優化，不僅解決了性能瓶頸，還加深了對 MySQL 執行計劃的理解。

? ? 以后，博主還會繼續分享更多有趣的慢 SQL 優化案例，比如索引失效、全表掃描、臨時表濫用等。如果你也遇到過類似的“坑”，歡迎在評論區留言討論 💬，一起交流學習，共同進步！