拓冰网站定制 
1. 为什么不建议用子查询先看一个真实案例先说一个我记忆深刻的线上事故。有个订单查询接口业务逻辑很简单查最近30天内有下单记录的用户资料。当时新人同事用了最直观的写法SELECT * FROM user WHERE user_id IN ( SELECT user_id FROM order WHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY) );单看这条SQL逻辑完全没问题教科书标准答案。但上线后数据库CPU直接飙到100%连接数打满整个服务差点被拖垮。为什么因为这条子查询在MySQL里执行时会对order表全表扫描当时order表已经有2000万行然后对每一条order记录去匹配user表。这个执行计划完全无法利用索引走了全表扫描嵌套循环的路线性能差得离谱。这就是本文想说的核心问题MySQL优化器对子查询的处理机制和我们直觉里的“先查子表、再走索引”完全不是一回事。很多场景下更合理的做法是改写成JOIN或EXISTS让执行计划真正走上索引。写这篇文章不只是为了列几条“不要用子查询”的规矩而是想把这背后的执行原理、优化器行为、改写技巧都捋清楚让各位在面试或实际开发中能真正判断出什么时候该用子查询什么时候必须绕开它。2. 子查询慢在哪儿MySQL执行计划的真相2.1 优化器对子查询的“默认处理方式”MySQL处理子查询的核心机制早期版本5.5及之前基本是先执行子查询得到完整结果集把这个结果集当临时表再和外部查询做连接。这个过程会产生两个问题临时表没有索引子查询结果如果数据量大物化成临时表后外部查询每次关联都需要全表扫描这张临时表。执行顺序的误区很多开发者以为“先算内层、再算外层”是顺序执行但MySQL的优化器不总是这么做它会根据自己的规则成本估算来决定执行路径有时候甚至会把子查询重写成其他形式。到了5.6、5.7版本MySQL引入了半连接semi-join优化和物化materialization优化部分IN子查询能被自动改写成最优形式。但注意——这仅限于IN子查询且是非相关子查询的情况。对于相关子查询、EXISTS子查询、FROM子查询优化器能做的优化手段就非常有限了。2.2 执行计划手术最毁索引的三种操作实际开发中我只关心执行计划里这几种“危险信号”危险信号含义后果SIMPLE DEPENDENT SUBQUERY相关子查询内层依赖外层值外层每扫描一行内层就执行一次等于O(N×M)复杂度Using temporary产生临时表存储中间结果数据量大时磁盘IO爆炸内存不够还走磁盘temptableUsing filesort结果集中需要排序但没法用索引子查询结果集过大时几乎必现尤其ORDER BY在子查询外层时最典型的坑是WHERE IN 大表。我见过有人对百万级用户表做WHERE user_id IN (SELECT user_id FROM order WHERE ...)这个执行计划在5.7里可能被优化成semi-join但如果子查询结果集超过optimizer_switch里materialization的阈值或者统计信息不准优化器就会选错路径照样慢成狗。2.3 为什么大家的直觉会错写子查询的人一般都这么想先算出子集比如先筛选出满足条件的user_id列表然后再拿这个小的结果集去关联大表这不就快了吗这个直觉等价于“先缩小范围、再做匹配”逻辑上没毛病。但问题是MySQL执行器不是程序员它得按执行计划走。执行计划里如果出现DEPENDENT SUBQUERY就意味着外层表的每一行都要触发一次内层查询——这就是典型的“由外到内”的驱动顺序和直觉里“由内到外”的先后顺序完全颠倒了。用生活化的类比来解释你去超市买一批特定品牌的商品正常思路是拿着采购单结果集去货架大表扫货。但子查询的执行方式可能是站在货架前每看到一个商品就去翻一遍采购单确认是不是目标。商品10万件、采购单1万条就是10万次×1万条的对比这不慢才怪。3. 五类子查询场景逐个拆解原因与改写方案3.1 WHERE IN 子查询能改JOIN就改JOIN这是最高频的场景也是最容易踩坑的。先说结论非相关IN子查询在MySQL 5.6很可能被自动优化成semi-join但结果不保证改写JOIN更稳妥。-- 原写法IN子查询 SELECT * FROM a WHERE id IN (SELECT a_id FROM b WHERE b.status 1); -- 改写法JOIN SELECT DISTINCT a.* FROM a JOIN b ON a.id b.a_id WHERE b.status 1;注意看改写后加了DISTINCT。因为JOIN如果遇到一对多关系会产生重复行IN天然去重而JOIN不去重。这是最容易漏掉的差异点漏了就会导致数据翻倍线上直接出bug。我之前遇到过一例订单表和订单明细表关联查询有有效明细的订单改JOIN时忘了加DISTINCT结果订单数据翻了好几倍运营那边导出的报表全是错的排查了半天才定位到是数据重复问题。教训就是改JOIN必须同步验证结果集行数。不过也要说个公道话如果IN子查询的结果集很小且稳定比如状态枚举的bool过滤、字典表过滤IN子查询的性能并不可怕5.7后的优化器大概率会处理得很好。真正要担心的是结果集不可预估、可能爆炸的那种IN子查询。3.2 EXISTS 相关子查询别滥用但也别妖魔化EXISTS其实是一种相关子查询它的执行方式完全不一样。它关心的是“有没有”只要内层查到一行就立即返回不需要收集整个结果集。-- 原写法EXISTS相关子查询 SELECT * FROM user u WHERE EXISTS ( SELECT 1 FROM order o WHERE o.user_id u.user_id AND o.status PAID );这个语义是外层每扫描一个用户就去order表里查有没有对应的支付订单。如果order表在user_id上有索引单次查询走索引非常快但如果order表没有索引那就是灾难。这里我必须纠正一个广泛流传的误区“EXISTS肯定比IN快”——不一定。要分情况order表大、user表小EXISTS的驱动顺序合适先扫外层小表每次内层走索引快。user表也很大内层每次也得做全表判断EXISTS一样扛不住。所以关键不是“用EXISTS不用IN”而是确认驱动表是谁、内层能不能走索引。EXISTS适合“外部大表内层小索引”的场景适合业务语义上“存在性判断”的场景。如果子查询需要返回多列数据EXISTS就完全无能为力了那应该是JOIN的战场。3.3 FROM 子查询派生表临时表没索引可能卡死查询FROM子查询也就是派生表Derived Table它是真正意义上“完全物化”的子查询MySQL必须先执行它把结果物化成临时表再作为外层查询的数据源。SELECT t.user_id, COUNT(*) FROM ( SELECT user_id, amount FROM order WHERE create_time 2024-01-01 ) t GROUP BY t.user_id;这段SQL的问题在于子查询不管筛出多少数据都会先全部物化成临时表。如果筛出500万行临时表就有500万行外层聚合就对这个500万行的临时表做全表扫描。MySQL 5.7后有一个derived_merge优化会尝试把符合条件的派生表合并到外层查询中就等于直接对order表做聚合不再物化。这个优化触发条件比较多稍微复杂一点就失效。替代方案很简单直接在外层做聚合去掉一层嵌套。SELECT user_id, COUNT(*) FROM order WHERE create_time 2024-01-01 GROUP BY user_id;一条SQL能搞定的事没必要套一层壳。切记FROM子查询最大的坑是你以为写得很清晰实际上让优化器多干活。判断方法很简单看EXPLAIN里有没有“Derived”关键字有就说明派生表物化了八成有优化空间。3.4 SELECT 子查询标量子查询可能让每行都执行一次标量子查询长这样SELECT name, (SELECT COUNT(*) FROM order o WHERE o.user_id u.user_id) AS order_cnt FROM user u;这种写法非常直观还能顺便给结果集加个计算列。但执行计划里它是DEPENDENT SUBQUERY——外层user表有多少行内层就执行多少次。如果外层100万用户那这个查询等于额外执行了100万次COUNT(*)即便order表有索引整体开销也很大。改法之一是先用GROUP BY把聚合结果算好再JOIN回来SELECT u.name, t.order_cnt FROM user u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM order GROUP BY user_id ) t ON u.user_id t.user_id;这个改写方案本质上是把百万次小查询换成“一次聚合一次连接”。数据量大后效果立竿见影。我做过一次对比测试100万用户、500万订单原写法跑了17秒改写后只需要1.2秒差距极其夸张。3.5 关联更新删除子查询在UPDATE/DELETE里问题更大UPDATE和DELETE语句里使用子查询时MySQL的约束非常严格——不能直接修改子查询涉及到的同一张表会报You cant specify target table for update in FROM clause。这是很多人第一次遇到就发懵的报错。-- 会报错的写法 UPDATE order SET status CANCELLED WHERE order_id IN ( SELECT order_id FROM order WHERE create_time 2020-01-01 );这种需求更新表中部分行的正确姿势是包一层派生表把MySQL的检查绕过去UPDATE order SET status CANCELLED WHERE order_id IN ( SELECT t.order_id FROM ( SELECT order_id FROM order WHERE create_time 2020-01-01 LIMIT 100 ) t );注意子查询里的LIMIT 100——批量更新千万不能一把梭哈建议分批更新每批几百几千条避免长事务和锁竞争。另外UPDATE 子查询的组合在写之前务必先查一遍子查询的结果集验证要影响的行数、要更新的范围这能帮你躲过一堆“手抖全表更新”的惨剧。在MySQL 8.0里其实可以直接用UPDATE ... JOIN语法UPDATE order o JOIN ( SELECT order_id FROM order WHERE create_time 2020-01-01 LIMIT 100 ) t ON o.order_id t.order_id SET o.status CANCELLED;这个可读性更好也完全不碰子查询的限制。4. 什么时候子查询反而是对的别一刀切把子查询批得体无完肤之后我得拉回立场子查询不是毒药有些场景下它就是最合理的选择。4.1 IN 小结果集简单直接没毛病如果子查询的返回结果集量级很小几十几百条比如查“所有已下线分类下的商品”而分类表本身没几行那IN子查询根本不需要改。此时改写JOIN还可能引入去重问题纯属给自己找不痛快。判断标准不是“有没有子查询”而是“执行计划里子查询的扫描行数”。EXPLAIN能看到rows列如果内层结果集小执行计划会很干净那就放心用。4.2 逻辑表达上的高可读性有些业务的“存在性判断”EXISTS写法确实比JOIN加DISTINCT更直观-- 查从未下单的用户 SELECT * FROM user u WHERE NOT EXISTS ( SELECT 1 FROM order o WHERE o.user_id u.user_id );这个表达里“不存在”的语义非常清晰。如果强行用LEFT JOIN IS NULL来写SELECT u.* FROM user u LEFT JOIN order o ON u.user_id o.user_id WHERE o.user_id IS NULL;结果集是对的但语义上绕了个弯。尤其维护旧代码的人看到LEFT JOIN IS NULL第一反应还会想“为什么要这么写”不如NOT EXISTS直接表意。可读性在长期维护里也是性能的一部分。4.3 MySQL 8.0的优化器进步必须承认MySQL 8.0的优化器比5.7强了一大截。它引入了Hash Join用于等值连接优化器在合适的成本评估下会自动选用、对半连接和物化策略的调整也更智能。在8.0里跑一些旧版很慢的子查询自动生成出来的执行计划可能出乎意料地好。但这不意味着可以无脑写子查询。8.0里的Hash Join虽然快可它本身也意味着“一张表完整倒进内存做哈希”——数据量超大时一样吃内存。此外还有个隐藏差异要记住8.0的窗口函数非常强大很多以前必须用相关子查询算的排名、累计值、分组TopN现在用窗口函数干净利落地搞定性能和可读性双双碾压-- 查每个用户最近一单 SELECT * FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM order ) t WHERE rn 1;这是子查询窗口函数合理配合的典型例子外层套的薄壳只是为了引用窗口函数的结果列内层才是真正的核心计算。5. 验证方式EXPLAIN 和实际压测5.1 看EXPLAIN的关键字段不管是子查询还是JOIN改写落到执行计划上都逃不过这几点type列ALL全表扫是最差信号range、ref、const都是好信号说明走索引了。key列是否真的用到了索引没用到就是NULL。Extra列出现Using temporary、Using filesort要警惕说明中间结果集被物化、需要排序。select_type出现DEPENDENT SUBQUERY或UNCACHEABLE SUBQUERY说明是相关子查询外部表每行都要执行一次内层。我一般在MySQL里加一句EXPLAIN FORMATTREE需要8.0.16它能直接显示优化器的完整执行树包括哪个表是驱动表、哪个是内层、是否做了物化比传统EXPLAIN好理解得多EXPLAIN FORMATTREE SELECT * FROM user WHERE user_id IN (SELECT user_id FROM order WHERE status 1);如果能看懂TREE输出对执行顺序的判断会比看表格精确很多。5.2 实测对比同样的结果不同的执行计划我用一套测试数据user表10万行、order表200万行做过一轮对比SQL写法执行时间执行计划关键点WHERE IN 子查询4.8sselect_type: SIMPLE/SUBQUERY物化临时表全表扫EXISTS 相关子查询8.2sDEPENDENT SUBQUERY外层10万行每行执行内层JOIN DISTINCT0.9s使用order表索引ref连接走semi-join改写先GROUP BY再JOIN0.7s无临时表无文件排序这个对比数据说明改写不是玄学是实打实的执行计划变化。JOIN让MySQL能以order表为主驱动表直接按user_id走索引分组的路径比“先物化再关联”省去了一整个中间结果的生成和读取过程。5.3 别忘统计信息与索引基础说一万句优化技巧根基始终是统计信息准确、索引设计合理。MySQL优化器依赖于SHOW INDEX、information_schema.statistics里的统计信息来评估成本。如果统计信息过期、采样偏差大优化器就可能“自信地”选择一条烂路径。实际开发中遇到子查询突然变慢的先看三件事ANALYZE TABLE更新统计信息很多时候慢查询可能只是统计数据过期。确认关联字段的类型和字符集一致varchar和int比较、或者两个字符集不同的列做关联索引会直接失效。确认子查询/关联列上真的有索引SHOW INDEX FROM看一下别想当然。6. 我踩过的坑都在这里了经验这东西说多了都是泪。捡几个关于子查询的典型事故大家引以为戒。坑一一个IN子查询直接把生产库拖垮。20万用户表 500万订单表IN子查询的执行计划里出现了DEPENDENT SUBQUERY实际执行的扫描行数达到了恐怖的几十亿次关联。当时的解法就是改写JOIN 加索引几分钟内CPU就从100%降到了20%不到。这个事故让我意识到上线前必须检查所有涉及大表的SQL执行计划没有例外。坑二GROUP BY忘记考虑非聚合列。把子查询改成JOIN后发现结果集有重复行补DISTINCT后又发现性能下降。折腾了很久才明白不同业务场景对数据粒度要求不同改SQL前必须想清楚“我要的是订单粒度还是用户粒度”否则改出来的结果对不上业务预期改了等于白改。坑三不要一次性改太多SQL。曾经有一次优化几十个慢查询一次性改完直接上线结果有个SELECT子查询改成了JOIN后因为一对多关系数据膨胀报表模块的汇总全错了线上告警响了一个通宵。从那以后我学乖了每个改写都要前后结果比对、逐批次灰度上线先验证再全量。坑四别忽视参数配置。MySQL 5.7之后有个optimizer_switch里面有semi_join、materialization、derived_merge这些开关有时候生产环境和测试环境性能差异巨大八成是optimizer_switch配置不同导致的。排查劣质执行计划时记得确认两个环境的开关一致否则你本地怎么测都好上了生产就是另一副面孔。7. 面试阶段的参考答案“MySQL为什么不建议使用子查询”这个面试题基本是考察优化器理解深度的。我会按这个层次回答第一层回答执行计划。MySQL对部分子查询场景的优化能力有限IN子查询早期版本可能被物化成临时表没有索引可用相关子查询需要外层每行执行一次内层查询天然是O(N×M)的复杂度。第二层回答优化器策略。5.6、5.7引入半连接优化和物化策略但触发条件受结果集大小、统计信息影响不保证每次都走上好路径。FROM子查询产生的派生表默认物化没索引外层查询会全表扫临时表。第三层回答替代方案。根据业务语义改写JOIN、EXISTS或窗口函数让连接条件落在索引上让执行计划更“顺”但改写必须同步考虑去重、结果集行数、驱动表选择不能无脑替换。这三层说完面试官基本能确认你不仅会写SQL还真的看过执行计划。我个人在实际操作中的体会是子查询不是不能用而是必须在了解执行计划的“底细”后再用。能走JOIN的时候优先JOIN能走GROUP BY算聚合就先聚合能用窗口函数的就用窗口函数这些小习惯攒下来线上的慢查询数量会肉眼可见地减少。最后再分享一个小技巧每一条要上线的新SQL都养成先跑一次EXPLAIN的习惯这个习惯救过我无数次也希望能帮到你。 MySQL子查询性能陷阱：从执行计划到JOIN改写实战
1. 为什么不建议用子查询先看一个真实案例先说一个我记忆深刻的线上事故。有个订单查询接口业务逻辑很简单查最近30天内有下单记录的用户资料。当时新人同事用了最直观的写法SELECT * FROM user WHERE user_id IN ( SELECT user_id FROM order WHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY) );单看这条SQL逻辑完全没问题教科书标准答案。但上线后数据库CPU直接飙到100%连接数打满整个服务差点被拖垮。为什么因为这条子查询在MySQL里执行时会对order表全表扫描当时order表已经有2000万行然后对每一条order记录去匹配user表。这个执行计划完全无法利用索引走了全表扫描嵌套循环的路线性能差得离谱。这就是本文想说的核心问题MySQL优化器对子查询的处理机制和我们直觉里的“先查子表、再走索引”完全不是一回事。很多场景下更合理的做法是改写成JOIN或EXISTS让执行计划真正走上索引。写这篇文章不只是为了列几条“不要用子查询”的规矩而是想把这背后的执行原理、优化器行为、改写技巧都捋清楚让各位在面试或实际开发中能真正判断出什么时候该用子查询什么时候必须绕开它。2. 子查询慢在哪儿MySQL执行计划的真相2.1 优化器对子查询的“默认处理方式”MySQL处理子查询的核心机制早期版本5.5及之前基本是先执行子查询得到完整结果集把这个结果集当临时表再和外部查询做连接。这个过程会产生两个问题临时表没有索引子查询结果如果数据量大物化成临时表后外部查询每次关联都需要全表扫描这张临时表。执行顺序的误区很多开发者以为“先算内层、再算外层”是顺序执行但MySQL的优化器不总是这么做它会根据自己的规则成本估算来决定执行路径有时候甚至会把子查询重写成其他形式。到了5.6、5.7版本MySQL引入了半连接semi-join优化和物化materialization优化部分IN子查询能被自动改写成最优形式。但注意——这仅限于IN子查询且是非相关子查询的情况。对于相关子查询、EXISTS子查询、FROM子查询优化器能做的优化手段就非常有限了。2.2 执行计划手术最毁索引的三种操作实际开发中我只关心执行计划里这几种“危险信号”危险信号含义后果SIMPLE DEPENDENT SUBQUERY相关子查询内层依赖外层值外层每扫描一行内层就执行一次等于O(N×M)复杂度Using temporary产生临时表存储中间结果数据量大时磁盘IO爆炸内存不够还走磁盘temptableUsing filesort结果集中需要排序但没法用索引子查询结果集过大时几乎必现尤其ORDER BY在子查询外层时最典型的坑是WHERE IN 大表。我见过有人对百万级用户表做WHERE user_id IN (SELECT user_id FROM order WHERE ...)这个执行计划在5.7里可能被优化成semi-join但如果子查询结果集超过optimizer_switch里materialization的阈值或者统计信息不准优化器就会选错路径照样慢成狗。2.3 为什么大家的直觉会错写子查询的人一般都这么想先算出子集比如先筛选出满足条件的user_id列表然后再拿这个小的结果集去关联大表这不就快了吗这个直觉等价于“先缩小范围、再做匹配”逻辑上没毛病。但问题是MySQL执行器不是程序员它得按执行计划走。执行计划里如果出现DEPENDENT SUBQUERY就意味着外层表的每一行都要触发一次内层查询——这就是典型的“由外到内”的驱动顺序和直觉里“由内到外”的先后顺序完全颠倒了。用生活化的类比来解释你去超市买一批特定品牌的商品正常思路是拿着采购单结果集去货架大表扫货。但子查询的执行方式可能是站在货架前每看到一个商品就去翻一遍采购单确认是不是目标。商品10万件、采购单1万条就是10万次×1万条的对比这不慢才怪。3. 五类子查询场景逐个拆解原因与改写方案3.1 WHERE IN 子查询能改JOIN就改JOIN这是最高频的场景也是最容易踩坑的。先说结论非相关IN子查询在MySQL 5.6很可能被自动优化成semi-join但结果不保证改写JOIN更稳妥。-- 原写法IN子查询 SELECT * FROM a WHERE id IN (SELECT a_id FROM b WHERE b.status 1); -- 改写法JOIN SELECT DISTINCT a.* FROM a JOIN b ON a.id b.a_id WHERE b.status 1;注意看改写后加了DISTINCT。因为JOIN如果遇到一对多关系会产生重复行IN天然去重而JOIN不去重。这是最容易漏掉的差异点漏了就会导致数据翻倍线上直接出bug。我之前遇到过一例订单表和订单明细表关联查询有有效明细的订单改JOIN时忘了加DISTINCT结果订单数据翻了好几倍运营那边导出的报表全是错的排查了半天才定位到是数据重复问题。教训就是改JOIN必须同步验证结果集行数。不过也要说个公道话如果IN子查询的结果集很小且稳定比如状态枚举的bool过滤、字典表过滤IN子查询的性能并不可怕5.7后的优化器大概率会处理得很好。真正要担心的是结果集不可预估、可能爆炸的那种IN子查询。3.2 EXISTS 相关子查询别滥用但也别妖魔化EXISTS其实是一种相关子查询它的执行方式完全不一样。它关心的是“有没有”只要内层查到一行就立即返回不需要收集整个结果集。-- 原写法EXISTS相关子查询 SELECT * FROM user u WHERE EXISTS ( SELECT 1 FROM order o WHERE o.user_id u.user_id AND o.status PAID );这个语义是外层每扫描一个用户就去order表里查有没有对应的支付订单。如果order表在user_id上有索引单次查询走索引非常快但如果order表没有索引那就是灾难。这里我必须纠正一个广泛流传的误区“EXISTS肯定比IN快”——不一定。要分情况order表大、user表小EXISTS的驱动顺序合适先扫外层小表每次内层走索引快。user表也很大内层每次也得做全表判断EXISTS一样扛不住。所以关键不是“用EXISTS不用IN”而是确认驱动表是谁、内层能不能走索引。EXISTS适合“外部大表内层小索引”的场景适合业务语义上“存在性判断”的场景。如果子查询需要返回多列数据EXISTS就完全无能为力了那应该是JOIN的战场。3.3 FROM 子查询派生表临时表没索引可能卡死查询FROM子查询也就是派生表Derived Table它是真正意义上“完全物化”的子查询MySQL必须先执行它把结果物化成临时表再作为外层查询的数据源。SELECT t.user_id, COUNT(*) FROM ( SELECT user_id, amount FROM order WHERE create_time 2024-01-01 ) t GROUP BY t.user_id;这段SQL的问题在于子查询不管筛出多少数据都会先全部物化成临时表。如果筛出500万行临时表就有500万行外层聚合就对这个500万行的临时表做全表扫描。MySQL 5.7后有一个derived_merge优化会尝试把符合条件的派生表合并到外层查询中就等于直接对order表做聚合不再物化。这个优化触发条件比较多稍微复杂一点就失效。替代方案很简单直接在外层做聚合去掉一层嵌套。SELECT user_id, COUNT(*) FROM order WHERE create_time 2024-01-01 GROUP BY user_id;一条SQL能搞定的事没必要套一层壳。切记FROM子查询最大的坑是你以为写得很清晰实际上让优化器多干活。判断方法很简单看EXPLAIN里有没有“Derived”关键字有就说明派生表物化了八成有优化空间。3.4 SELECT 子查询标量子查询可能让每行都执行一次标量子查询长这样SELECT name, (SELECT COUNT(*) FROM order o WHERE o.user_id u.user_id) AS order_cnt FROM user u;这种写法非常直观还能顺便给结果集加个计算列。但执行计划里它是DEPENDENT SUBQUERY——外层user表有多少行内层就执行多少次。如果外层100万用户那这个查询等于额外执行了100万次COUNT(*)即便order表有索引整体开销也很大。改法之一是先用GROUP BY把聚合结果算好再JOIN回来SELECT u.name, t.order_cnt FROM user u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM order GROUP BY user_id ) t ON u.user_id t.user_id;这个改写方案本质上是把百万次小查询换成“一次聚合一次连接”。数据量大后效果立竿见影。我做过一次对比测试100万用户、500万订单原写法跑了17秒改写后只需要1.2秒差距极其夸张。3.5 关联更新删除子查询在UPDATE/DELETE里问题更大UPDATE和DELETE语句里使用子查询时MySQL的约束非常严格——不能直接修改子查询涉及到的同一张表会报You cant specify target table for update in FROM clause。这是很多人第一次遇到就发懵的报错。-- 会报错的写法 UPDATE order SET status CANCELLED WHERE order_id IN ( SELECT order_id FROM order WHERE create_time 2020-01-01 );这种需求更新表中部分行的正确姿势是包一层派生表把MySQL的检查绕过去UPDATE order SET status CANCELLED WHERE order_id IN ( SELECT t.order_id FROM ( SELECT order_id FROM order WHERE create_time 2020-01-01 LIMIT 100 ) t );注意子查询里的LIMIT 100——批量更新千万不能一把梭哈建议分批更新每批几百几千条避免长事务和锁竞争。另外UPDATE 子查询的组合在写之前务必先查一遍子查询的结果集验证要影响的行数、要更新的范围这能帮你躲过一堆“手抖全表更新”的惨剧。在MySQL 8.0里其实可以直接用UPDATE ... JOIN语法UPDATE order o JOIN ( SELECT order_id FROM order WHERE create_time 2020-01-01 LIMIT 100 ) t ON o.order_id t.order_id SET o.status CANCELLED;这个可读性更好也完全不碰子查询的限制。4. 什么时候子查询反而是对的别一刀切把子查询批得体无完肤之后我得拉回立场子查询不是毒药有些场景下它就是最合理的选择。4.1 IN 小结果集简单直接没毛病如果子查询的返回结果集量级很小几十几百条比如查“所有已下线分类下的商品”而分类表本身没几行那IN子查询根本不需要改。此时改写JOIN还可能引入去重问题纯属给自己找不痛快。判断标准不是“有没有子查询”而是“执行计划里子查询的扫描行数”。EXPLAIN能看到rows列如果内层结果集小执行计划会很干净那就放心用。4.2 逻辑表达上的高可读性有些业务的“存在性判断”EXISTS写法确实比JOIN加DISTINCT更直观-- 查从未下单的用户 SELECT * FROM user u WHERE NOT EXISTS ( SELECT 1 FROM order o WHERE o.user_id u.user_id );这个表达里“不存在”的语义非常清晰。如果强行用LEFT JOIN IS NULL来写SELECT u.* FROM user u LEFT JOIN order o ON u.user_id o.user_id WHERE o.user_id IS NULL;结果集是对的但语义上绕了个弯。尤其维护旧代码的人看到LEFT JOIN IS NULL第一反应还会想“为什么要这么写”不如NOT EXISTS直接表意。可读性在长期维护里也是性能的一部分。4.3 MySQL 8.0的优化器进步必须承认MySQL 8.0的优化器比5.7强了一大截。它引入了Hash Join用于等值连接优化器在合适的成本评估下会自动选用、对半连接和物化策略的调整也更智能。在8.0里跑一些旧版很慢的子查询自动生成出来的执行计划可能出乎意料地好。但这不意味着可以无脑写子查询。8.0里的Hash Join虽然快可它本身也意味着“一张表完整倒进内存做哈希”——数据量超大时一样吃内存。此外还有个隐藏差异要记住8.0的窗口函数非常强大很多以前必须用相关子查询算的排名、累计值、分组TopN现在用窗口函数干净利落地搞定性能和可读性双双碾压-- 查每个用户最近一单 SELECT * FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM order ) t WHERE rn 1;这是子查询窗口函数合理配合的典型例子外层套的薄壳只是为了引用窗口函数的结果列内层才是真正的核心计算。5. 验证方式EXPLAIN 和实际压测5.1 看EXPLAIN的关键字段不管是子查询还是JOIN改写落到执行计划上都逃不过这几点type列ALL全表扫是最差信号range、ref、const都是好信号说明走索引了。key列是否真的用到了索引没用到就是NULL。Extra列出现Using temporary、Using filesort要警惕说明中间结果集被物化、需要排序。select_type出现DEPENDENT SUBQUERY或UNCACHEABLE SUBQUERY说明是相关子查询外部表每行都要执行一次内层。我一般在MySQL里加一句EXPLAIN FORMATTREE需要8.0.16它能直接显示优化器的完整执行树包括哪个表是驱动表、哪个是内层、是否做了物化比传统EXPLAIN好理解得多EXPLAIN FORMATTREE SELECT * FROM user WHERE user_id IN (SELECT user_id FROM order WHERE status 1);如果能看懂TREE输出对执行顺序的判断会比看表格精确很多。5.2 实测对比同样的结果不同的执行计划我用一套测试数据user表10万行、order表200万行做过一轮对比SQL写法执行时间执行计划关键点WHERE IN 子查询4.8sselect_type: SIMPLE/SUBQUERY物化临时表全表扫EXISTS 相关子查询8.2sDEPENDENT SUBQUERY外层10万行每行执行内层JOIN DISTINCT0.9s使用order表索引ref连接走semi-join改写先GROUP BY再JOIN0.7s无临时表无文件排序这个对比数据说明改写不是玄学是实打实的执行计划变化。JOIN让MySQL能以order表为主驱动表直接按user_id走索引分组的路径比“先物化再关联”省去了一整个中间结果的生成和读取过程。5.3 别忘统计信息与索引基础说一万句优化技巧根基始终是统计信息准确、索引设计合理。MySQL优化器依赖于SHOW INDEX、information_schema.statistics里的统计信息来评估成本。如果统计信息过期、采样偏差大优化器就可能“自信地”选择一条烂路径。实际开发中遇到子查询突然变慢的先看三件事ANALYZE TABLE更新统计信息很多时候慢查询可能只是统计数据过期。确认关联字段的类型和字符集一致varchar和int比较、或者两个字符集不同的列做关联索引会直接失效。确认子查询/关联列上真的有索引SHOW INDEX FROM看一下别想当然。6. 我踩过的坑都在这里了经验这东西说多了都是泪。捡几个关于子查询的典型事故大家引以为戒。坑一一个IN子查询直接把生产库拖垮。20万用户表 500万订单表IN子查询的执行计划里出现了DEPENDENT SUBQUERY实际执行的扫描行数达到了恐怖的几十亿次关联。当时的解法就是改写JOIN 加索引几分钟内CPU就从100%降到了20%不到。这个事故让我意识到上线前必须检查所有涉及大表的SQL执行计划没有例外。坑二GROUP BY忘记考虑非聚合列。把子查询改成JOIN后发现结果集有重复行补DISTINCT后又发现性能下降。折腾了很久才明白不同业务场景对数据粒度要求不同改SQL前必须想清楚“我要的是订单粒度还是用户粒度”否则改出来的结果对不上业务预期改了等于白改。坑三不要一次性改太多SQL。曾经有一次优化几十个慢查询一次性改完直接上线结果有个SELECT子查询改成了JOIN后因为一对多关系数据膨胀报表模块的汇总全错了线上告警响了一个通宵。从那以后我学乖了每个改写都要前后结果比对、逐批次灰度上线先验证再全量。坑四别忽视参数配置。MySQL 5.7之后有个optimizer_switch里面有semi_join、materialization、derived_merge这些开关有时候生产环境和测试环境性能差异巨大八成是optimizer_switch配置不同导致的。排查劣质执行计划时记得确认两个环境的开关一致否则你本地怎么测都好上了生产就是另一副面孔。7. 面试阶段的参考答案“MySQL为什么不建议使用子查询”这个面试题基本是考察优化器理解深度的。我会按这个层次回答第一层回答执行计划。MySQL对部分子查询场景的优化能力有限IN子查询早期版本可能被物化成临时表没有索引可用相关子查询需要外层每行执行一次内层查询天然是O(N×M)的复杂度。第二层回答优化器策略。5.6、5.7引入半连接优化和物化策略但触发条件受结果集大小、统计信息影响不保证每次都走上好路径。FROM子查询产生的派生表默认物化没索引外层查询会全表扫临时表。第三层回答替代方案。根据业务语义改写JOIN、EXISTS或窗口函数让连接条件落在索引上让执行计划更“顺”但改写必须同步考虑去重、结果集行数、驱动表选择不能无脑替换。这三层说完面试官基本能确认你不仅会写SQL还真的看过执行计划。我个人在实际操作中的体会是子查询不是不能用而是必须在了解执行计划的“底细”后再用。能走JOIN的时候优先JOIN能走GROUP BY算聚合就先聚合能用窗口函数的就用窗口函数这些小习惯攒下来线上的慢查询数量会肉眼可见地减少。最后再分享一个小技巧每一条要上线的新SQL都养成先跑一次EXPLAIN的习惯这个习惯救过我无数次也希望能帮到你。