上个月帮一个业务团队排查慢查询一条统计SQL跑了快三个小时还没出结果。拿过执行计划一看三层相关子查询嵌套在外面二十万行结果集里每一行都要触发一次内部全表扫描累计的行访问量直接飙到几个数量级的浪费。拆成JOIN后同样统计120毫秒跑完。这种“MySQL子查询慢”的案例几乎每个用MySQL的团队都遇到过。这篇就把子查询慢的根本原因、优化器背后的处理机制、以及我沉淀下来的一套改写与排查方法完整讲清楚。适合被慢SQL困扰的开发同学也适合正准备做MySQL SQL规范的技术负责人参考。1. 子查询的执行模型三种形态慢的根源完全不同很多同学把“子查询慢”当成一个统一问题其实MySQL里子查询至少分成三类执行方式完全不同慢也不慢在同一个地方。不把这层分清楚后面所有优化都是瞎猜。1.1 非相关子查询先算后查可以当作常量非相关子查询就是不引用外层查询任何列的子查询。比如SELECT * FROM t_order WHERE user_id (SELECT id FROM t_user WHERE user_name demo_user);这种子查询和外部查询之间没有数据依赖MySQL会先独立执行子查询拿到一个确定的值再把这个值当作常量代入外层查询整个过程子查询只执行一次。这种形态通常不是性能瓶颈。你只要保证子查询内部的WHERE条件能走索引它就相当于一次快速点查外层再根据拿到的常量值继续走索引。我见过很多人一听到子查询就眉头一皱其实非相关子查询是很安全的不需要刻意改写。1.2 相关子查询外层每一行内层重跑一遍相关子查询才是“慢SQL重灾区”。它引用了外层查询的列比如SELECT * FROM t_order o WHERE o.user_id ( SELECT id FROM t_user u WHERE u.id o.user_id );MySQL在无法自动改写的场景下会采用一种非常直白的执行方式外层查询每取出一行就把当前行的列值代入子查询执行一次然后判断条件接着取下一行再执行一次……外层返回两万行子查询就要执行两万次。把账算细一点你就有体感了。假设外层返回2万行内层每次走索引扫描500行那就是2万乘以500一千万次行访问如果内层字段没有索引变成全表扫描50万行那就是2万乘以50万十亿次行访问。这个规模放在任何一个数据库里都扛不住而且不是靠调整参数能解决的。我经常打一个比方相关子查询就像快递员每派一单都要回到仓库把所有面单重新翻一遍查询地址对不对。而合理做法是出门前就把当天所有快件按片区排好序一次派送完。SQL里对应的就是JOIN或集团操作的思维。1.3 派生表物化临时表和没有索引的隐患FROM子句后面的子查询叫派生表比如SELECT * FROM t_user u JOIN ( SELECT user_id, MAX(create_time) AS max_time FROM t_order GROUP BY user_id ) t ON u.user_id t.user_id;MySQL拿到派生表后通常先执行子查询把结果集落到一张内部临时表里这个动作叫物化。物化本身不一定慢真正麻烦的是这张临时表在很多情况下是没有索引的。外层每条记录去和临时表做关联时只能在临时表里做全表扫描。MySQL 5.6开始支持物化5.7和8.0在某些条件下能自动为物化表生成索引或者直接把派生表合并进主查询。但限制也很明显如果派生表里有GROUP BY聚合、DISTINCT、LIMIT这类操作优化器往往不能合并还是得老老实实物化。所以当你发现执行计划里出现DERIVED加上Using temporary就要警惕大结果集的笛卡尔式扫描。2. 从优化器机制看MySQL处理子查询的内部逻辑理解为什么子查询慢光看表面还不够。翻到MySQL优化器这一层能看到很多反直觉的决策也有不少它其实已经帮你优化了的场景。2.1 嵌套循环模型与N1次查询MySQL的JOIN模型本质上是嵌套循环和子查询的执行是同一个底层机制。相关子查询被优化器处理时往往不能变成普通JOIN而是退化为“每行触发一次子查询”这种逐行触发的执行方式和集合式思维完全相反。SQL本身是描述“要什么结果”而不是“怎么去取”。当优化器能力不足把声明式查询退化成程序式循环时性能就任由数据量摆布。这也是为什么我一直强调写SQL时要想到它最终可能怎么被执行而不是只看逻辑对不对。执行计划里如果看到DEPENDENT SUBQUERY基本可以断定这个子查询是相关依赖的。碰到这种标识建议认真检查外层数据量。mysql EXPLAIN SELECT * FROM t_order o WHERE o.user_id (SELECT id FROM t_user u WHERE u.id o.user_id); --------------------------------------------------------- | id | select_type | table | type | Extra | --------------------------------------------------------- | 1 | PRIMARY | o | ALL | Using where | | 2 | DEPENDENT SUBQUERY | u | eq_ref| Using index | ---------------------------------------------------------注意select_type那列DEPENDENT SUBQUERY就是相关子查询的代名词。它不一定会慢到离谱但凡是出现这个标识都要下意识问一句外层结果集多大2.2 半连接优化5.6之后MySQL已经在上手救人很多人不知道MySQL 5.6之后引入了semi-join半连接优化。对于IN和EXISTS这类判断“存在性”的子查询MySQL在满足条件时能把子查询改写为半连接执行。半连接的执行策略有好几种我挑常见的说Duplicate Weedout先做连接再通过临时表对驱动表主键去重First Match当某一行能找到第一个满足条件的匹配就返回不再继续往后扫描Loose Scan利用索引的跳跃扫描来减少重复探测Materialization把子查询结果物化并可能为物化表生成索引。这套优化可以从optimizer_switch看到SELECT optimizer_switch\G里面有semijoinon、materializationon这些开关。也就是说5.6之后的MySQL遇到像WHERE user_id IN (SELECT user_id FROM t_order)这种非相关子查询很多情况下早就自动帮你改成JOIN了并不存在“子查询一定慢”的铁律。真正容易踩坑的是两种一种是5.5及更老版本没有半连接优化同一句话在旧库上就是灾难另一种是相关子查询因为依赖外层列难以直接转成半连接还是会走下层的逐行执行。2.3 为什么JOIN通常更受优化器待见JOIN之所以稳是因为优化器把所有参与连接的表放在一个统一计划空间里考虑可以选择驱动表、连接顺序、索引访问方式还可以提前做条件过滤。而子查询在优化器眼里更像一个相对独立的执行单元如果无法改写优化器能动的空间就很小。同样一个需求-- 子查询版本 SELECT * FROM t_user WHERE user_id IN (SELECT user_id FROM t_order WHERE order_status 1); -- JOIN版本 SELECT DISTINCT u.* FROM t_user u JOIN t_order o ON u.user_id o.user_id WHERE o.order_status 1;在5.6之前第一个版本很可能子查询先执行一遍结果集再传给外层做逐行匹配第二个版本优化器可以自己决定先从哪个表开始扫描数据量小的表做驱动表另一张表走索引。随着数据量增长这种计划选择空间带来的收益差距会越来越大。需要注意JOIN改写会带来重复行上面例子我用DISTINCT去重。实际生产里如果只需要判断存在性我更推荐用EXISTS它保持了“存在即返回”的语义又不会产生重复行后面马上讲到。3. 实战改写三个典型场景把子查询换成JOIN/EXISTS理论讲多了容易飘直接上场景。这些场景都是我在实际项目里高频遇到的每条都给出了改写前后对比和注意事项。3.1 场景一IN子查询查“有订单的用户”先看一段很常见的SQLSELECT * FROM t_user WHERE user_id IN ( SELECT user_id FROM t_order WHERE order_status 1 );在MySQL 5.6以上这条一般会被优化成半连接执行计划里能看到Start temporary和End temporary。但如果你管理的库比较老或者子查询里还套了其他复杂逻辑优化器没法改写那么可以主动改写成JOINSELECT u.* FROM t_user u JOIN ( SELECT DISTINCT user_id FROM t_order WHERE order_status 1 ) o ON u.user_id o.user_id;这里我把子查询放到FROM里面先对t_order去重再JOIN避免t_user被重复返回。不过还有个更轻量的写法SELECT * FROM t_user u WHERE EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.user_id AND o.order_status 1 );EXISTS的判断逻辑是外层每一行去内层找找到一个匹配立刻返回。这个写法看起来很像相关子查询但因为EXISTS特殊语义配合半连接优化MySQL在5.6以后通常能处理得不错。实际执行前看一眼计划确认没有出现DEPENDENT SUBQUERY即可。3.2 场景二每个分类下最新一条商品记录这个需求特别容易写出慢SQL。常见悲剧写法是这样SELECT * FROM t_product p WHERE p.id ( SELECT p1.id FROM t_product p1 WHERE p1.category_id p.category_id ORDER BY p1.create_time DESC LIMIT 1 );外层有多少条商品相关子查询就跑多少遍而且每遍都要做一次排序取最大值。数据量上万后就很难看了。我的建议是先按分类聚合出最大时间再JOIN回原表SELECT p.* FROM t_product p JOIN ( SELECT category_id, MAX(create_time) AS max_time FROM t_product GROUP BY category_id ) t ON p.category_id t.category_id AND p.create_time t.max_time;这个方案第一步只做一次GROUP BY第二步走category_id create_time的联合索引整体效率高很多。前提是给(category_id, create_time)建立联合索引。如果线上是MySQL 8.0还有更优雅的窗口函数方案SELECT * FROM ( SELECT p.*, ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY create_time DESC ) AS rn FROM t_product p ) t WHERE rn 1;窗口函数的优点是一步到位适合追求可读性的场景。但它同样可能在排序阶段产生临时表和文件排序所以写完还是要用EXPLAIN确认数据量级可接受。不要因为8.0提供了新语法就无脑替换执行计划永远是你最终的裁判。3.3 场景三NOT IN遇到NULL的巨坑说到NOT IN一定要单独提醒一次这个坑我见过太多人踩。SELECT * FROM t_user WHERE user_id NOT IN ( SELECT user_id FROM t_order );如果t_order.user_id列里存在任何一个NULL这条SQL返回的结果集是空的。原因是SQL三值逻辑NULL表示未知NOT IN (NULL)的结果不是true也不是false而是NULL所以整行被过滤掉。就算你的业务上觉得user_id不该有NULL只要表结构没加NOT NULL约束这个风险就一直存在。正确做法是改用NOT EXISTSSELECT * FROM t_user u WHERE NOT EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.user_id );或者使用LEFT JOIN IS NULLSELECT u.* FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id WHERE o.user_id IS NULL;这两种写法都不会掉进NULL陷阱。实际选择上如果t_order很大且user_id重复较多我更倾向NOT EXISTS因为LEFT JOIN会产生大量中间膨胀行然后再过滤掉内存和临时表压力都更大。4. 排查实录用EXPLAIN定位子查询是否被优化改写之前一定先诊断。没有EXPLAIN的SQL优化都是碰运气。4.1 select_type里的几个危险信号执行计划里select_type是最该先看的一列。我平时主要盯这几个值select_type含义风险程度PRIMARY外层主查询正常SUBQUERY独立子查询会先执行一般DEPENDENT SUBQUERY相关子查询外层每行触发高DERIVED派生表可能物化中高MATERIALIZED子查询被物化中特别是DEPENDENT SUBQUERY看到它就像看到警报。另外一个需要盯的是Extra列出现Using temporary、Using filesort往往意味着临时表和排序这类操作会显著放大内存和CPU开销。8.0.18以后还可以直接使用EXPLAIN ANALYZE SELECT * FROM t_user WHERE user_id IN (SELECT user_id FROM t_order);这条语句会返回每个步骤实际执行的行数和耗时比看估算值靠谱得多。我在分析疑难慢SQL时基本都会先用它拿真实数据。4.2 不同版本的执行计划差异MySQL版本之间差异大到让人怀疑是两个产品。我整理了一张简表MySQL版本子查询相关优化能力5.5及以前无半连接优化相关子查询普遍逐行执行派生表每次物化且无索引5.6引入semi-join和materializationIN/EXISTS非相关子查询显著改善5.7优化器进一步提升派生表可合并或条件下推临时表性能增强8.0对EXISTS/NOT EXISTS支持更激进的优化提供窗口函数和EXPLAIN ANALYZE我遇到过一个项目从5.6升到8.0原本几条写得很糟糕的相关子查询SQL升级后执行计划自动从DEPENDENT SUBQUERY变成了半连接性能反而翻了几倍。反过来如果你线上是5.5不要指望优化器能救你老老实实改写SQL才是唯一出路。这也是为什么我劝大家项目升级MySQL大版本后一定要把核心SQL的执行计划整体看一遍旧经验在新版本里不一定成立。4.3 实操排查清单结合我的现场经验排查子查询慢SQL时可以按这个顺序走第一步确认表索引。子查询内部的WHERE字段、外层关联字段都应有合适索引优先联合索引覆盖查询条件。第二步看执行计划。重点找DEPENDENT SUBQUERY、DERIVED、Start temporary等关键词。第三步估算外层结果集。如果外层只有几十行相关子查询慢不到哪去不用强行改写如果外层有十万行以上就算内层走索引也要慎重。第四步分析是否可改写。判断子查询和外部是“存在性”关系还是“取多行结果”关系。存在性优先EXISTS取结果集优先JOIN。第五步用小数据量验证改写结果确保返回行数一致再用真实数据量做性能对比。这个清单不是固定流程但覆盖了我日常遇到的大部分场景。改完SQL后一定要回归业务因为JOIN和子查询在语义上并不完全等价尤其是重复行和NULL值。5. 什么时候子查询依然很好用说了这么多子查询的不好也不能一棒子打死。任何技术都有它合适的土壤子查询在特定场景下反而更简洁、更高效。5.1 标量子查询取单个聚合值就像前面说的非相关标量子查询SELECT (SELECT MAX(price) FROM t_product) AS max_price, (SELECT MIN(price) FROM t_product) AS min_price;子查询只执行一次独立拿值性能开销很小SQL又很直观。强行去改JOIN反而显得绕。5.2 数据量小或结果集本身可预测当你明确知道外层数据量只有几百甚至几十行时相关子查询即使逐行执行内层又有索引总成本也就是几百次索引点查毫秒级完成。这种场景没必要为了“消除相关子查询”而引入复杂的JOIN重写可读性损失大于性能收益。5.3 优化器已自动转化不必强行改写8.0的优化器在不少场景已经能把子查询改写为连接或半连接。如果EXPLAIN显示select_type不是DEPENDENT SUBQUERY而是已经走了半连接那么子查询在逻辑上和JOIN几乎没有执行差异。强行改成JOIN反而可能因为写错去重条件引入重复行得不偿失。5.4 EXISTS/NOT EXISTS本身就是正确表达我前面反复用EXISTS做改写目标但它本质上也是一种子查询。重点不是“去掉子查询”而是“去掉不恰当的子查询用法”。当需要表达“存在”或“不存在”时EXISTS和NOT EXISTS是最贴合语义的写法。优化器对它们的支持也越来越成熟不要一听子查询就全部改成JOIN。最后说点个人的操作习惯踩过太多子查询的坑之后我现在写MySQL查询基本会按这个顺序思考先问这个查询要的是“存在性判断”还是“结果集关联”存在性优先用EXISTS或NOT EXISTS结果集关联优先用JOIN并注意去重需要取每个分组内某条记录时先考虑GROUP BY加关联8.0再考虑窗口函数。SQL写完后我一定会用EXPLAIN确认没有DEPENDENT SUBQUERY和Using filesort这两个危险信号然后再放上线。子查询不是洪水猛兽真正的问题是很多开发者不看执行计划只凭感觉写SQL。只要养成先看数据量级、再看执行计划的习惯MySQL子查询这关你很快就能迈过去。 
长河君 · 2026/10/22 22:05:01 · 阅读 1.2k MySQL子查询慢的根因分析：从执行机制到JOIN改写实战
上个月帮一个业务团队排查慢查询一条统计SQL跑了快三个小时还没出结果。拿过执行计划一看三层相关子查询嵌套在外面二十万行结果集里每一行都要触发一次内部全表扫描累计的行访问量直接飙到几个数量级的浪费。拆成JOIN后同样统计120毫秒跑完。这种“MySQL子查询慢”的案例几乎每个用MySQL的团队都遇到过。这篇就把子查询慢的根本原因、优化器背后的处理机制、以及我沉淀下来的一套改写与排查方法完整讲清楚。适合被慢SQL困扰的开发同学也适合正准备做MySQL SQL规范的技术负责人参考。1. 子查询的执行模型三种形态慢的根源完全不同很多同学把“子查询慢”当成一个统一问题其实MySQL里子查询至少分成三类执行方式完全不同慢也不慢在同一个地方。不把这层分清楚后面所有优化都是瞎猜。1.1 非相关子查询先算后查可以当作常量非相关子查询就是不引用外层查询任何列的子查询。比如SELECT * FROM t_order WHERE user_id (SELECT id FROM t_user WHERE user_name demo_user);这种子查询和外部查询之间没有数据依赖MySQL会先独立执行子查询拿到一个确定的值再把这个值当作常量代入外层查询整个过程子查询只执行一次。这种形态通常不是性能瓶颈。你只要保证子查询内部的WHERE条件能走索引它就相当于一次快速点查外层再根据拿到的常量值继续走索引。我见过很多人一听到子查询就眉头一皱其实非相关子查询是很安全的不需要刻意改写。1.2 相关子查询外层每一行内层重跑一遍相关子查询才是“慢SQL重灾区”。它引用了外层查询的列比如SELECT * FROM t_order o WHERE o.user_id ( SELECT id FROM t_user u WHERE u.id o.user_id );MySQL在无法自动改写的场景下会采用一种非常直白的执行方式外层查询每取出一行就把当前行的列值代入子查询执行一次然后判断条件接着取下一行再执行一次……外层返回两万行子查询就要执行两万次。把账算细一点你就有体感了。假设外层返回2万行内层每次走索引扫描500行那就是2万乘以500一千万次行访问如果内层字段没有索引变成全表扫描50万行那就是2万乘以50万十亿次行访问。这个规模放在任何一个数据库里都扛不住而且不是靠调整参数能解决的。我经常打一个比方相关子查询就像快递员每派一单都要回到仓库把所有面单重新翻一遍查询地址对不对。而合理做法是出门前就把当天所有快件按片区排好序一次派送完。SQL里对应的就是JOIN或集团操作的思维。1.3 派生表物化临时表和没有索引的隐患FROM子句后面的子查询叫派生表比如SELECT * FROM t_user u JOIN ( SELECT user_id, MAX(create_time) AS max_time FROM t_order GROUP BY user_id ) t ON u.user_id t.user_id;MySQL拿到派生表后通常先执行子查询把结果集落到一张内部临时表里这个动作叫物化。物化本身不一定慢真正麻烦的是这张临时表在很多情况下是没有索引的。外层每条记录去和临时表做关联时只能在临时表里做全表扫描。MySQL 5.6开始支持物化5.7和8.0在某些条件下能自动为物化表生成索引或者直接把派生表合并进主查询。但限制也很明显如果派生表里有GROUP BY聚合、DISTINCT、LIMIT这类操作优化器往往不能合并还是得老老实实物化。所以当你发现执行计划里出现DERIVED加上Using temporary就要警惕大结果集的笛卡尔式扫描。2. 从优化器机制看MySQL处理子查询的内部逻辑理解为什么子查询慢光看表面还不够。翻到MySQL优化器这一层能看到很多反直觉的决策也有不少它其实已经帮你优化了的场景。2.1 嵌套循环模型与N1次查询MySQL的JOIN模型本质上是嵌套循环和子查询的执行是同一个底层机制。相关子查询被优化器处理时往往不能变成普通JOIN而是退化为“每行触发一次子查询”这种逐行触发的执行方式和集合式思维完全相反。SQL本身是描述“要什么结果”而不是“怎么去取”。当优化器能力不足把声明式查询退化成程序式循环时性能就任由数据量摆布。这也是为什么我一直强调写SQL时要想到它最终可能怎么被执行而不是只看逻辑对不对。执行计划里如果看到DEPENDENT SUBQUERY基本可以断定这个子查询是相关依赖的。碰到这种标识建议认真检查外层数据量。mysql EXPLAIN SELECT * FROM t_order o WHERE o.user_id (SELECT id FROM t_user u WHERE u.id o.user_id); --------------------------------------------------------- | id | select_type | table | type | Extra | --------------------------------------------------------- | 1 | PRIMARY | o | ALL | Using where | | 2 | DEPENDENT SUBQUERY | u | eq_ref| Using index | ---------------------------------------------------------注意select_type那列DEPENDENT SUBQUERY就是相关子查询的代名词。它不一定会慢到离谱但凡是出现这个标识都要下意识问一句外层结果集多大2.2 半连接优化5.6之后MySQL已经在上手救人很多人不知道MySQL 5.6之后引入了semi-join半连接优化。对于IN和EXISTS这类判断“存在性”的子查询MySQL在满足条件时能把子查询改写为半连接执行。半连接的执行策略有好几种我挑常见的说Duplicate Weedout先做连接再通过临时表对驱动表主键去重First Match当某一行能找到第一个满足条件的匹配就返回不再继续往后扫描Loose Scan利用索引的跳跃扫描来减少重复探测Materialization把子查询结果物化并可能为物化表生成索引。这套优化可以从optimizer_switch看到SELECT optimizer_switch\G里面有semijoinon、materializationon这些开关。也就是说5.6之后的MySQL遇到像WHERE user_id IN (SELECT user_id FROM t_order)这种非相关子查询很多情况下早就自动帮你改成JOIN了并不存在“子查询一定慢”的铁律。真正容易踩坑的是两种一种是5.5及更老版本没有半连接优化同一句话在旧库上就是灾难另一种是相关子查询因为依赖外层列难以直接转成半连接还是会走下层的逐行执行。2.3 为什么JOIN通常更受优化器待见JOIN之所以稳是因为优化器把所有参与连接的表放在一个统一计划空间里考虑可以选择驱动表、连接顺序、索引访问方式还可以提前做条件过滤。而子查询在优化器眼里更像一个相对独立的执行单元如果无法改写优化器能动的空间就很小。同样一个需求-- 子查询版本 SELECT * FROM t_user WHERE user_id IN (SELECT user_id FROM t_order WHERE order_status 1); -- JOIN版本 SELECT DISTINCT u.* FROM t_user u JOIN t_order o ON u.user_id o.user_id WHERE o.order_status 1;在5.6之前第一个版本很可能子查询先执行一遍结果集再传给外层做逐行匹配第二个版本优化器可以自己决定先从哪个表开始扫描数据量小的表做驱动表另一张表走索引。随着数据量增长这种计划选择空间带来的收益差距会越来越大。需要注意JOIN改写会带来重复行上面例子我用DISTINCT去重。实际生产里如果只需要判断存在性我更推荐用EXISTS它保持了“存在即返回”的语义又不会产生重复行后面马上讲到。3. 实战改写三个典型场景把子查询换成JOIN/EXISTS理论讲多了容易飘直接上场景。这些场景都是我在实际项目里高频遇到的每条都给出了改写前后对比和注意事项。3.1 场景一IN子查询查“有订单的用户”先看一段很常见的SQLSELECT * FROM t_user WHERE user_id IN ( SELECT user_id FROM t_order WHERE order_status 1 );在MySQL 5.6以上这条一般会被优化成半连接执行计划里能看到Start temporary和End temporary。但如果你管理的库比较老或者子查询里还套了其他复杂逻辑优化器没法改写那么可以主动改写成JOINSELECT u.* FROM t_user u JOIN ( SELECT DISTINCT user_id FROM t_order WHERE order_status 1 ) o ON u.user_id o.user_id;这里我把子查询放到FROM里面先对t_order去重再JOIN避免t_user被重复返回。不过还有个更轻量的写法SELECT * FROM t_user u WHERE EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.user_id AND o.order_status 1 );EXISTS的判断逻辑是外层每一行去内层找找到一个匹配立刻返回。这个写法看起来很像相关子查询但因为EXISTS特殊语义配合半连接优化MySQL在5.6以后通常能处理得不错。实际执行前看一眼计划确认没有出现DEPENDENT SUBQUERY即可。3.2 场景二每个分类下最新一条商品记录这个需求特别容易写出慢SQL。常见悲剧写法是这样SELECT * FROM t_product p WHERE p.id ( SELECT p1.id FROM t_product p1 WHERE p1.category_id p.category_id ORDER BY p1.create_time DESC LIMIT 1 );外层有多少条商品相关子查询就跑多少遍而且每遍都要做一次排序取最大值。数据量上万后就很难看了。我的建议是先按分类聚合出最大时间再JOIN回原表SELECT p.* FROM t_product p JOIN ( SELECT category_id, MAX(create_time) AS max_time FROM t_product GROUP BY category_id ) t ON p.category_id t.category_id AND p.create_time t.max_time;这个方案第一步只做一次GROUP BY第二步走category_id create_time的联合索引整体效率高很多。前提是给(category_id, create_time)建立联合索引。如果线上是MySQL 8.0还有更优雅的窗口函数方案SELECT * FROM ( SELECT p.*, ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY create_time DESC ) AS rn FROM t_product p ) t WHERE rn 1;窗口函数的优点是一步到位适合追求可读性的场景。但它同样可能在排序阶段产生临时表和文件排序所以写完还是要用EXPLAIN确认数据量级可接受。不要因为8.0提供了新语法就无脑替换执行计划永远是你最终的裁判。3.3 场景三NOT IN遇到NULL的巨坑说到NOT IN一定要单独提醒一次这个坑我见过太多人踩。SELECT * FROM t_user WHERE user_id NOT IN ( SELECT user_id FROM t_order );如果t_order.user_id列里存在任何一个NULL这条SQL返回的结果集是空的。原因是SQL三值逻辑NULL表示未知NOT IN (NULL)的结果不是true也不是false而是NULL所以整行被过滤掉。就算你的业务上觉得user_id不该有NULL只要表结构没加NOT NULL约束这个风险就一直存在。正确做法是改用NOT EXISTSSELECT * FROM t_user u WHERE NOT EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.user_id );或者使用LEFT JOIN IS NULLSELECT u.* FROM t_user u LEFT JOIN t_order o ON u.user_id o.user_id WHERE o.user_id IS NULL;这两种写法都不会掉进NULL陷阱。实际选择上如果t_order很大且user_id重复较多我更倾向NOT EXISTS因为LEFT JOIN会产生大量中间膨胀行然后再过滤掉内存和临时表压力都更大。4. 排查实录用EXPLAIN定位子查询是否被优化改写之前一定先诊断。没有EXPLAIN的SQL优化都是碰运气。4.1 select_type里的几个危险信号执行计划里select_type是最该先看的一列。我平时主要盯这几个值select_type含义风险程度PRIMARY外层主查询正常SUBQUERY独立子查询会先执行一般DEPENDENT SUBQUERY相关子查询外层每行触发高DERIVED派生表可能物化中高MATERIALIZED子查询被物化中特别是DEPENDENT SUBQUERY看到它就像看到警报。另外一个需要盯的是Extra列出现Using temporary、Using filesort往往意味着临时表和排序这类操作会显著放大内存和CPU开销。8.0.18以后还可以直接使用EXPLAIN ANALYZE SELECT * FROM t_user WHERE user_id IN (SELECT user_id FROM t_order);这条语句会返回每个步骤实际执行的行数和耗时比看估算值靠谱得多。我在分析疑难慢SQL时基本都会先用它拿真实数据。4.2 不同版本的执行计划差异MySQL版本之间差异大到让人怀疑是两个产品。我整理了一张简表MySQL版本子查询相关优化能力5.5及以前无半连接优化相关子查询普遍逐行执行派生表每次物化且无索引5.6引入semi-join和materializationIN/EXISTS非相关子查询显著改善5.7优化器进一步提升派生表可合并或条件下推临时表性能增强8.0对EXISTS/NOT EXISTS支持更激进的优化提供窗口函数和EXPLAIN ANALYZE我遇到过一个项目从5.6升到8.0原本几条写得很糟糕的相关子查询SQL升级后执行计划自动从DEPENDENT SUBQUERY变成了半连接性能反而翻了几倍。反过来如果你线上是5.5不要指望优化器能救你老老实实改写SQL才是唯一出路。这也是为什么我劝大家项目升级MySQL大版本后一定要把核心SQL的执行计划整体看一遍旧经验在新版本里不一定成立。4.3 实操排查清单结合我的现场经验排查子查询慢SQL时可以按这个顺序走第一步确认表索引。子查询内部的WHERE字段、外层关联字段都应有合适索引优先联合索引覆盖查询条件。第二步看执行计划。重点找DEPENDENT SUBQUERY、DERIVED、Start temporary等关键词。第三步估算外层结果集。如果外层只有几十行相关子查询慢不到哪去不用强行改写如果外层有十万行以上就算内层走索引也要慎重。第四步分析是否可改写。判断子查询和外部是“存在性”关系还是“取多行结果”关系。存在性优先EXISTS取结果集优先JOIN。第五步用小数据量验证改写结果确保返回行数一致再用真实数据量做性能对比。这个清单不是固定流程但覆盖了我日常遇到的大部分场景。改完SQL后一定要回归业务因为JOIN和子查询在语义上并不完全等价尤其是重复行和NULL值。5. 什么时候子查询依然很好用说了这么多子查询的不好也不能一棒子打死。任何技术都有它合适的土壤子查询在特定场景下反而更简洁、更高效。5.1 标量子查询取单个聚合值就像前面说的非相关标量子查询SELECT (SELECT MAX(price) FROM t_product) AS max_price, (SELECT MIN(price) FROM t_product) AS min_price;子查询只执行一次独立拿值性能开销很小SQL又很直观。强行去改JOIN反而显得绕。5.2 数据量小或结果集本身可预测当你明确知道外层数据量只有几百甚至几十行时相关子查询即使逐行执行内层又有索引总成本也就是几百次索引点查毫秒级完成。这种场景没必要为了“消除相关子查询”而引入复杂的JOIN重写可读性损失大于性能收益。5.3 优化器已自动转化不必强行改写8.0的优化器在不少场景已经能把子查询改写为连接或半连接。如果EXPLAIN显示select_type不是DEPENDENT SUBQUERY而是已经走了半连接那么子查询在逻辑上和JOIN几乎没有执行差异。强行改成JOIN反而可能因为写错去重条件引入重复行得不偿失。5.4 EXISTS/NOT EXISTS本身就是正确表达我前面反复用EXISTS做改写目标但它本质上也是一种子查询。重点不是“去掉子查询”而是“去掉不恰当的子查询用法”。当需要表达“存在”或“不存在”时EXISTS和NOT EXISTS是最贴合语义的写法。优化器对它们的支持也越来越成熟不要一听子查询就全部改成JOIN。最后说点个人的操作习惯踩过太多子查询的坑之后我现在写MySQL查询基本会按这个顺序思考先问这个查询要的是“存在性判断”还是“结果集关联”存在性优先用EXISTS或NOT EXISTS结果集关联优先用JOIN并注意去重需要取每个分组内某条记录时先考虑GROUP BY加关联8.0再考虑窗口函数。SQL写完后我一定会用EXPLAIN确认没有DEPENDENT SUBQUERY和Using filesort这两个危险信号然后再放上线。子查询不是洪水猛兽真正的问题是很多开发者不看执行计划只凭感觉写SQL。只要养成先看数据量级、再看执行计划的习惯MySQL子查询这关你很快就能迈过去。