【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 优化案例 ，比如索引失效、全表扫描、临时表滥用等。如果你也遇到过类似的“坑”，欢迎在评论区留言讨论 💬，一起交流学习，共同进步！