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

开场白说起 MySQL 子查询优化这事儿我还真踩过大坑。有一次上线一个报表功能SQL 里套了两层子查询测试环境跑得挺快上了生产直接把数据库干到 CPU 100%整个系统卡了十分钟。后来 DBA 找过来一看执行计划子查询走了全表扫描关联了几百万行数据。那次事故之后我就下决心把子查询优化彻底搞明白。子查询本身不是什么坏东西但用不好就是性能杀手。今天这篇文章我把常见的子查询优化手段梳理一遍结合实际场景讲清楚什么时候该用子查询什么时候该换写法。子查询的分类先明确一下概念MySQL 里的子查询大致分这几种标量子查询返回一行一列的结果可以用在 SELECT、WHERE 等位置。SELECT*FROMordersWHEREamount(SELECTAVG(amount)FROMorders);列子查询返回一列多行通常配合 IN、ANY、ALL 使用。SELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREstatus1);行子查询返回一行多列。SELECT*FROMusersWHERE(age,city)(SELECTage,cityFROMusersWHEREid100);表子查询用在 FROM 子句中也叫派生表。SELECTu.name,o.totalFROMusers uJOIN(SELECTuser_id,SUM(amount)AStotalFROMordersGROUPBYuser_id)oONu.ido.user_id;相关子查询子查询引用了外层查询的列每行都要执行一次。SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_idu.idANDo.status1);最坑的就是相关子查询外层有多少行子查询就执行多少次数据量一大直接完蛋。子查询的常见性能问题1. IN 子查询慢我之前碰到过这样一个查询SELECT*FROMproductsWHEREcategory_idIN(SELECTidFROMcategoriesWHERElevel3);categories 表里level3的记录有好几万条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.cnt10;MySQL 需要先执行子查询把结果存到临时表里再从临时表里过滤。这个临时表没有索引数据量大的时候很慢。 ## 优化手段 ### 1. IN 子查询改写为 JOIN sql-- 原始写法SELECTp.*FROMproducts pWHEREp.category_idIN(SELECTidFROMcategoriesWHERElevel3);-- 优化写法SELECTp.*FROMproducts pINNERJOINcategories cONp.category_idc.idANDc.level3;改写成 JOIN 之后MySQL 优化器可以选择驱动表利用索引做嵌套循环效率大幅提升。实测上面这个例子从 40 秒降到了 0.3 秒。2. NOT IN 改写为 LEFT JOIN IS NULL-- 原始写法注意 NULL 问题SELECTu.*FROMusers uWHEREu.idNOTIN(SELECTuser_idFROMorders);-- 优化写法SELECTu.*FROMusers uLEFTJOINorders oONu.ido.user_idWHEREo.user_idISNULL;这样写既避开了 NULL 值的坑性能也好得多。LEFT JOIN 能利用索引而 NOT IN 子查询往往退化为全表扫描。3. EXISTS 替代 IN当子查询结果集较大、外层表较小时EXISTS 通常比 IN 更快-- IN 写法SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREvip1);-- EXISTS 写法SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.ido.user_idANDu.vip1);IN 是先执行子查询拿到结果集再和外表做匹配EXISTS 是对外表的每一行去子查询里找是否满足条件找到一条就停。所以外表小、内表大时用 EXISTS外表大、内表小时用 IN。 但说实话MySQL 5.6 优化器已经能自动把 IN 子查询改写为半连接了大多数情况下性能差距不大。不过理解原理还是很重要的至少出了问题知道往哪个方向排查。 ### 4. 派生表优化加条件下推 sql-- 原始写法先全量聚合再过滤SELECT*FROM(SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_id)tWHEREt.cnt10;-- 优化写法用 HAVING 提前过滤SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_idHAVINGcnt10;如果条件能下推到子查询内部就别在外面过滤。让数据库少干点活性能自然就上来了。MySQL 8.0 引入了派生表条件下推Derived Condition Pushdown优化器会自动把外层的 WHERE 条件推入派生表。但这是 8.0.22 才有的特性低版本还是得手动优化。5. 用临时表替代复杂子查询有时候子查询嵌套太多层优化器也搞不定不如手动拆成临时表-- 先把子查询结果存到临时表CREATETEMPORARYTABLEtmp_vip_ordersASSELECTuser_id,SUM(amount)AStotalFROMordersWHEREstatus1ANDcreate_time2024-01-01GROUPBYuser_id;-- 再关联查询SELECTu.name,t.totalFROMusers uINNERJOINtmp_vip_orders tONu.idt.user_idWHEREu.vip1;DROPTEMPORARYTABLEtmp_vip_orders;临时表可以加索引查询效率可控。别嫌弃这种方法土有时候就是最管用的。MySQL 优化器对子查询的自动优化MySQL 5.6 之后优化器对子查询的处理有了很大改进主要策略包括半连接优化Semi-Join把 IN/EXISTS 子查询改写为半连接避免全表扫描。支持的策略有 Duplicate Weedout、FirstMatch、LooseScan、Materialization。子查询物化Materialization把子查询结果缓存到内部临时表中并自动创建索引避免重复执行。Exists 策略对相关子查询不再逐行执行而是转为半连接处理。看看优化器选了什么策略EXPLAINFORMATJSONSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREvip1);在输出的 JSON 里找optimized_away_subqueries或chosen字段能看到优化器的决策过程。但别完全依赖优化器复杂场景下它也会选错策略。我自己就遇到过优化器选了 FirstMatch 但实际 Materialization 更快的情况最后还是手动改写 SQL 解决的。实战案例多层子查询优化来一个真实案例。需求是统计每个部门中薪资高于该部门平均薪资的员工数量。原始写法SELECTd.dept_name,COUNT(*)AShigh_salary_countFROMemployees eJOINdepartments dONe.dept_idd.idWHEREe.salary(SELECTAVG(salary)FROMemployeesWHEREdept_ide.dept_id)GROUPBYd.dept_name;这个相关子查询对每个员工都要算一次部门平均薪资employees 表 50 万行直接跑了 2 分钟。 优化方案一先算出各部门平均薪资再 JOIN sqlSELECTd.dept_name,COUNT(*)AShigh_salary_countFROMemployees eJOINdepartments dONe.dept_idd.idJOIN(SELECTdept_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdept_id)dept_avgONe.dept_iddept_avg.dept_idWHEREe.salarydept_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_idd.id)tWHEREsalaryavg_salaryGROUPBYdept_name;窗口函数只扫一遍表执行时间0.5秒是最优解。## 小结几个关键点1.IN子查询在 MySQL5.6会被自动优化为半连接但不是所有场景都能优化复杂情况还是手动改写JOIN更靠谱2.2.NOTIN有NULL值的坑建议统一改写为LEFTJOINISNULL3.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章 查询性能优化