当前位置：首页 > news >正文

MySQL 子查询优化：从慢查询到飞起的实战之路

news 2026/5/24 8:51:07

开场白

说起 MySQL 子查询优化，这事儿我还真踩过大坑。有一次上线一个报表功能，SQL 里套了两层子查询，测试环境跑得挺快，上了生产直接把数据库干到 CPU 100%，整个系统卡了十分钟。后来 DBA 找过来，一看执行计划，子查询走了全表扫描，关联了几百万行数据。那次事故之后，我就下决心把子查询优化彻底搞明白。

子查询本身不是什么坏东西，但用不好就是性能杀手。今天这篇文章，我把常见的子查询优化手段梳理一遍，结合实际场景讲清楚什么时候该用子查询，什么时候该换写法。

子查询的分类

先明确一下概念，MySQL 里的子查询大致分这几种：

标量子查询：返回一行一列的结果，可以用在 SELECT、WHERE 等位置。

SELECT*FROMordersWHEREamount>(SELECTAVG(amount)FROMorders);

列子查询：返回一列多行，通常配合 IN、ANY、ALL 使用。

SELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREstatus=1);

行子查询：返回一行多列。

SELECT*FROMusersWHERE(age,city)=(SELECTage,cityFROMusersWHEREid=100);

表子查询：用在 FROM 子句中，也叫派生表。

SELECTu.name,o.totalFROMusers uJOIN(SELECTuser_id,SUM(amount)AStotalFROMordersGROUPBYuser_id)oONu.id=o.user_id;

相关子查询：子查询引用了外层查询的列，每行都要执行一次。

SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_id=u.idANDo.status=1);

最坑的就是相关子查询，外层有多少行，子查询就执行多少次，数据量一大直接完蛋。

子查询的常见性能问题

1. IN 子查询慢

我之前碰到过这样一个查询：

SELECT*FROMproductsWHEREcategory_idIN(SELECTidFROMcategoriesWHERElevel=3);```categories 表里level=3的记录有好几万条，products 表有五百万行。这个查询跑了40多秒才出结果。 用EXPLAIN看执行计划：

±—±------------±----------±-----------±-----±--------------±-----±--------±-----±--------±---------±------------+
| id | select_type | table | type | key | rows | Extra |
±—±------------±----------±-----------±-----±--------------±-----±--------±-----±--------±---------±------------+
| 1 | SIMPLE | products | ALL | NULL | 5000000 | Using where |
| 1 | SIMPLE | categories| eq_ref | PRIMARY | 1 | Using where |
±—±------------±----------±-----------±-----±--------±-----±--------±-----±--------±---------±------------+

MySQL 优化器把 IN 子查询改写成了半连接（Semi-Join），但 products 表没有合适的索引，走了全表扫描。 ### 2. NOT IN 子查询更慢 NOT IN 是重灾区： ```sql SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

这个查询要找出从未下过单的用户。如果 orders 表的 user_id 列有 NULL 值，整个结果集直接变成空的——这是 NOT IN 的经典坑，NULL 值会导致比较结果为 UNKNOWN，整行被过滤掉。

3. FROM 子句中的子查询（派生表）

SELECT*FROM(SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_id)tWHEREt.cnt>10;```MySQL 需要先执行子查询，把结果存到临时表里，再从临时表里过滤。这个临时表没有索引，数据量大的时候很慢。 ## 优化手段 ### 1. IN 子查询改写为 JOIN ```sql-- 原始写法SELECTp.*FROMproducts pWHEREp.category_idIN(SELECTidFROMcategoriesWHERElevel=3);-- 优化写法SELECTp.*FROMproducts pINNERJOINcategories cONp.category_id=c.idANDc.level=3;

改写成 JOIN 之后，MySQL 优化器可以选择驱动表，利用索引做嵌套循环，效率大幅提升。实测上面这个例子从 40 秒降到了 0.3 秒。

2. NOT IN 改写为 LEFT JOIN + IS NULL

-- 原始写法（注意 NULL 问题）SELECTu.*FROMusers uWHEREu.idNOTIN(SELECTuser_idFROMorders);-- 优化写法SELECTu.*FROMusers uLEFTJOINorders oONu.id=o.user_idWHEREo.user_idISNULL;

这样写既避开了 NULL 值的坑，性能也好得多。LEFT JOIN 能利用索引，而 NOT IN 子查询往往退化为全表扫描。

3. EXISTS 替代 IN

当子查询结果集较大、外层表较小时，EXISTS 通常比 IN 更快：

-- IN 写法SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREvip=1);-- EXISTS 写法SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.id=o.user_idANDu.vip=1);```IN 是先执行子查询拿到结果集，再和外表做匹配；EXISTS 是对外表的每一行，去子查询里找是否满足条件，找到一条就停。所以外表小、内表大时用 EXISTS，外表大、内表小时用 IN。 但说实话，MySQL 5.6+ 优化器已经能自动把 IN 子查询改写为半连接了，大多数情况下性能差距不大。不过理解原理还是很重要的，至少出了问题知道往哪个方向排查。 ### 4. 派生表优化：加条件下推 ```sql-- 原始写法：先全量聚合，再过滤SELECT*FROM(SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_id)tWHEREt.cnt>10;-- 优化写法：用 HAVING 提前过滤SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_idHAVINGcnt>10;

如果条件能下推到子查询内部，就别在外面过滤。让数据库少干点活，性能自然就上来了。

MySQL 8.0 引入了派生表条件下推（Derived Condition Pushdown），优化器会自动把外层的 WHERE 条件推入派生表。但这是 8.0.22+ 才有的特性，低版本还是得手动优化。

5. 用临时表替代复杂子查询

有时候子查询嵌套太多层，优化器也搞不定，不如手动拆成临时表：

-- 先把子查询结果存到临时表CREATETEMPORARYTABLEtmp_vip_ordersASSELECTuser_id,SUM(amount)AStotalFROMordersWHEREstatus=1ANDcreate_time>='2024-01-01'GROUPBYuser_id;-- 再关联查询SELECTu.name,t.totalFROMusers uINNERJOINtmp_vip_orders tONu.id=t.user_idWHEREu.vip=1;DROPTEMPORARYTABLEtmp_vip_orders;

临时表可以加索引，查询效率可控。别嫌弃这种方法土，有时候就是最管用的。

MySQL 优化器对子查询的自动优化

MySQL 5.6 之后，优化器对子查询的处理有了很大改进，主要策略包括：

半连接优化（Semi-Join）：把 IN/EXISTS 子查询改写为半连接，避免全表扫描。支持的策略有 Duplicate Weedout、FirstMatch、LooseScan、Materialization。

子查询物化（Materialization）：把子查询结果缓存到内部临时表中，并自动创建索引，避免重复执行。

Exists 策略：对相关子查询，不再逐行执行，而是转为半连接处理。

看看优化器选了什么策略：

EXPLAINFORMAT=JSONSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREvip=1);

在输出的 JSON 里找optimized_away_subqueries或chosen字段，能看到优化器的决策过程。

但别完全依赖优化器，复杂场景下它也会选错策略。我自己就遇到过优化器选了 FirstMatch 但实际 Materialization 更快的情况，最后还是手动改写 SQL 解决的。

实战案例：多层子查询优化

来一个真实案例。需求是统计每个部门中，薪资高于该部门平均薪资的员工数量。

原始写法：

SELECTd.dept_name,COUNT(*)AShigh_salary_countFROMemployees eJOINdepartments dONe.dept_id=d.idWHEREe.salary>(SELECTAVG(salary)FROMemployeesWHEREdept_id=e.dept_id)GROUPBYd.dept_name;```这个相关子查询对每个员工都要算一次部门平均薪资，employees 表 50 万行，直接跑了 2 分钟。 优化方案一：先算出各部门平均薪资，再 JOIN： ```sqlSELECTd.dept_name,COUNT(*)AShigh_salary_countFROMemployees eJOINdepartments dONe.dept_id=d.idJOIN(SELECTdept_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdept_id)dept_avgONe.dept_id=dept_avg.dept_idWHEREe.salary>dept_avg.avg_salaryGROUPBYd.dept_name;```执行时间降到 0.8 秒。 优化方案二：用窗口函数（MySQL 8.0+）： ```sqlSELECTdept_name,COUNT(*)AShigh_salary_countFROM(SELECTe.salary,d.dept_name,AVG(e.salary)OVER(PARTITIONBYe.dept_id)ASavg_salaryFROMemployees eJOINdepartments dONe.dept_id=d.id)tWHEREsalary>avg_salaryGROUPBYdept_name;```窗口函数只扫一遍表，执行时间0.5秒，是最优解。## 小结几个关键点：1.IN子查询在 MySQL5.6+会被自动优化为半连接，但不是所有场景都能优化，复杂情况还是手动改写JOIN更靠谱2.2.NOTIN有NULL值的坑，建议统一改写为LEFTJOIN+ISNULL3.3.EXISTS和IN的选择看数据量分布：外表小内表大用EXISTS，外表大内表小用IN4.4.派生表尽量条件下推，减少中间结果集5.5.复杂子查询可以拆成临时表，虽然土但管用6.6.MySQL8.0的窗口函数是处理分组聚合类子查询的利器 核心思路就一条：让数据库少干活。能用索引就用索引，能少扫描就少扫描，能提前过滤就提前过滤。子查询优化说到底就是把这些原则落到实处。## 相关阅读-[MySQL 官方文档-Subquery Optimization](https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html)--[MySQL 半连接优化策略详解](https://dev.mysql.com/doc/refman/8.0/en/semijoins.html)--《高性能MySQL》第6章 查询性能优化