在MySQL圈子待久了你肯定听过一句经验之谈能不用子查询就别用改写成JOIN往往会快一个量级。我第一次听到这句话其实是不太服气的——子查询写起来语义直观怎么到了MySQL这里就成了“性能毒药”后来在真正处理线上慢查询时才意识到这句话背后藏着一整套执行计划层面的逻辑不能简单当成教条背下来。这篇文章想把“MySQL不使用子查询的原因”这件事彻底讲透。我会从优化器执行机制入手结合几类最容易翻车的子查询场景给出可以直接照抄的改写方案最后再聊聊怎么用EXPLAIN和慢日志快速判断一条查询该不该改。适合刚接触SQL调优的开发同学也适合那些在老旧项目里被子查询坑过、想搞清楚“为什么慢”的DBA同行。1. 子查询变慢的三层根源执行计划、物化与优化器判断1.1 相关子查询不是“先跑一次再合并”而是逐行执行很多人对子查询的误解是从“子查询是独立执行的”这个直觉开始的。对于不相关子查询这个直觉有部分道理但对于相关子查询比如 SELECT 列表里带一个依赖外层字段的聚合情况就完全变了。看一个很典型的例子SELECT u.id, u.name, (SELECT MAX(o.amount) FROM orders o WHERE o.user_id u.id) AS max_amount FROM users u;这条SQL在EXPLAIN里的表现是DEPENDENT SUBQUERY。它的执行逻辑是先从users表取出一行拿着这行的id去orders里执行一次MAX查询然后再取一行users再执行一次……外层有多少用户orders就被“反复查询”多少次。我习惯用收银台类比来解释这个过程正常做促销活动应该提前把价目表读一遍放到手边结账时直接查相关子查询更像每扫一件商品就跑到库房里翻一次价目表翻完再回来扫下一件。库房数据量一大这个“反复跑腿”的成本就完全失控了。一旦出现这种写法users表有100万行orders表有5000万行即便orders的user_id上有索引实际执行次数也是100万次索引探测。若索引不存在就是100万次全表扫描慢到怀疑人生才是正常的。1.2 非相关子查询也未必省心物化临时表可能没有索引有人会说那把子查询从相关改成不相关的IN查询是不是就安全了MySQL 5.6之后对不相关的IN子查询确实引入了物化优化理论上会先执行子查询把结果集放到临时表里再与外表进行关联。但“物化”这件事本身就有隐藏成本。举个例子SELECT p.id, p.name FROM products p WHERE p.category_id IN ( SELECT id FROM categories WHERE parent_id 5 AND status 1 );优化器可能选择把categories的查询结果物化成一张临时表。如果这个子查询只返回几十行临时表在内存里性能完全可以接受。但如果子查询返回几十万行临时表放不下MySQL就会把它落到磁盘上走的是临时表的全表扫描。更麻烦的是物化临时表上的索引不是自动帮你建好的。某些版本和场景下优化器生成的物化临时表没有可用索引外表与临时表关联时只能逐行探测成本比直接改写成JOIN高出很多。EXPLAIN里如果看到MATERIALIZED且关联时是ALL或者Using where基本就是在告诉你物化查询没吃到索引红利。1.3 优化器没有上帝视角统计信息误差放大了子查询问题即使优化器有物化和半连接这些优化手段它也依赖成本估算来决定执行策略。MySQL的优化器是典型的CBO基于表行数、索引区分度、数据分布等统计信息做判断。问题在于很多生产环境里的表从没跑过ANALYZE TABLE统计信息严重失真。子查询内部的过滤条件一旦复杂优化器对子查询结果行数的估算可能偏差几十倍。按错误估算选出的执行计划自然容易翻车。我遇到过一条IN子查询优化器以为子查询只返回几百行实际跑出来是十几万行结果原本打算物化后走临时表索引关联变成了一次十几万行的临时表全表扫描加外表全表扫描。这种问题SQL写得再“标准”也没用关键要看执行计划。2. 最容易翻车的三类子查询从真实SQL看现场2.1 SELECT列表里的相关子查询百万行级别的灾难刚才的MAX案例属于最典型的“每行执行一次子查询”。这类写法在业务代码里出现频率高得惊人尤其是报表系统里“给每一行补充额外信息”的场景。我处理过一条类似的查询原意是给每个用户附带最近一笔订单金额。用户表只有两万行订单表三十万行按理说索引建好不会太慢。但实际请求耗时超过了十五秒高峰时段直接把应用服务器的连接池打满。EXPLAIN一查DEPENDENT SUBQUERY亮在那里type是ALLExtra里还有Using where。原因很简单用户表虽然只有两万行但这条查询被报表前端请求了几十次并发而且订单表里user_id没有合适的索引每次子查询都扫全表。两万乘以三十万亿任何机器都顶不住。这类查询最直观的解决方案不是去优化子查询内部而是彻底换掉这种“逐行探测”的写法具体改写方案后面会专门讲。2.2 IN关键字接大结果集明明有索引却全表扫另一种高发问题是业务上需要“查在某个范围内”的数据直接用了IN加子查询。表面看起来没问题子查询独立执行一次结果集交给外层做索引查找可执行计划往往不按你想的走。假设products表有category_id索引categories表的过滤条件返回五千个id。直观的想法是先算出五千个id然后走products的category_id索引五万次索引探测很快。但优化器可能会反过来把products表全表扫一遍每行去物化临时表里查是否存在。为什么因为统计信息告诉它products表只有两万行而物化临时表没有索引走全表扫“更划算”。问题在哪物化临时表没有索引的话外层每来一行就要扫描一次临时表成本是两万乘以五千的复杂度。而如果改写成了JOIN优化器反而更倾向于用小结果集作为驱动表并能在关联列上尝试建立临时索引或走索引。这类坑通常不会出现在数据量小的开发环境一旦到了生产环境数据量上来执行计划的差异会被放大得非常明显。2.3 NOT IN 与 NULL一个常被忽略的语义陷阱很多人不知道IN子查询在处理NULL值时存在一个语义层面的“隐形杀器”。看这两条SQLSELECT * FROM t1 WHERE id IN (SELECT id FROM t2); SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id t1.id);如果t2的id列包含NULL而且t1中某行id在t2里找不到匹配第一条IN查询的结果会让人困惑预期返回该行实际上却没有。原因在于SQL的三值逻辑——NULL既不是TRUE也不是FALSE当表达式结果为UNKNOWN时WHERE条件不成立行被过滤掉。而NOT IN更危险SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);只要t2的id列里存在任何一个NULL这条查询可能一行都不返回。原因同样是三值逻辑匹配到NULL时结果变成UNKNOWN全部不满足条件。这跟“不使用子查询”有什么关系关系很大。很多老项目把NOT IN当成正常业务逻辑在跑线上数据一旦出现脏数据或者NULL结果集就会悄悄失真。改写成NOT EXISTS或者LEFT JOIN后这些问题会被显式暴露出来至少你能意识到必须处理NULL值而不是被SQL默默吞掉一堆行。2.4 UPDATE/DELETE中的同表子查询报错与全表锁并存子查询的坑不仅在SELECT里UPDATE和DELETE里也很常见。MySQL有一个老限制不能直接在子查询中引用正在被修改的目标表。UPDATE users SET score (SELECT MAX(score) FROM users WHERE ...) WHERE id 1;这条在MySQL里可能会直接报错You cant specify target table users for update in FROM clause。常规做法是给子查询包一层临时派生表绕过去UPDATE users SET score (SELECT max_score FROM (SELECT MAX(score) AS max_score FROM users) tmp) WHERE id 1;包一层确实不报错了但本质上还是一个相关子查询大表场景下性能依旧糟糕。更合理的方案是用JOIN UPDATEUPDATE users u JOIN (SELECT MAX(score) AS max_score FROM users) tmp ON u.id 1 SET u.score tmp.max_score;这类问题提醒我们把子查询用在写操作上不仅要考虑语义正确性还得考虑MySQL的语法限制和执行计划稍不留神就是一个线上事故。3. 改写方案的落地细节从烂SQL到正常秒回3.1 IN改JOIN的隐藏成本去重比你想的麻烦看到子查询慢第一反应是全部改成JOIN。这个思路方向对但如果不注意行数放大会引出更隐蔽的bug。拿最早的“VIP用户ID”例子来说SELECT u.* FROM users u WHERE u.id IN (SELECT user_id FROM vip_log WHERE expire_time NOW());如果vip_log表里一个用户有多条会员记录直接改成JOIN会变成SELECT u.* FROM users u JOIN vip_log v ON u.id v.user_id WHERE v.expire_time NOW();看起来差不多实际结果是每个用户可能返回多行——只要有几条会员记录就返回几行。页面列表会平白多出一堆重复用户报表统计也会被带偏。老老实实加DISTINCT数据量大时DISTINCT往往会引入额外的排序或者临时表性能不见得比子查询好多少。需要“存在性判断”的时候正确的做法是用EXISTS改写SELECT u.* FROM users u WHERE EXISTS ( SELECT 1 FROM vip_log v WHERE v.user_id u.id AND v.expire_time NOW() );EXISTS的语义天然不会产生重复优化器也会把它当成半连接处理通常能取得和JOIN相似的性能又不用操心去重问题。这一点是很多改写出错的重灾区。3.2 标量子查询改LEFT JOIN派生表一次聚合顶一万次查询SELECT列表里的相关子查询改写思路是“把逐行探测变成一次聚合并提前物化”。回到最开始的例子SELECT u.id, u.name, (SELECT SUM(o.amount) FROM orders o WHERE o.user_id u.id) AS total_amount FROM users u;改写为SELECT u.id, u.name, s.total_amount FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) s ON u.id s.user_id;这个改写的核心逻辑是先对orders表做一次完整分组聚合得到每个用户的总额放到派生表s中然后再和users做一次LEFT JOIN。orders表只被扫描一次而不是对每个用户扫描一次。执行计划会从DEPENDENT SUBQUERY变成PRIMARY加DERIVED。聚合结果如果比较大同样会涉及临时表物化但物化一次和物化几百万次是完全不同的概念。需要提醒的是改写后要确认聚合结果集的数量级。如果orders表本身有上亿行分组聚合本身也是一个成本很高的操作这时候要考虑是不是能从业务层面做预聚合比如引入汇总表而不是靠一句SQL硬扛。3.3 EXISTS不产生重复行另一种“半连接”思路EXISTS改写适合“存在性判断”它的价值和JOIN不同JOIN可能会放大行数EXISTS只会返回外层表中满足条件的行语义更接近原来的IN子查询。EXISTS子查询里的SELECT列表写什么其实无所谓优化器根本不在乎写SELECT 1只是习惯。重要的是相关条件必须正确连到外层表。SELECT p.* FROM products p WHERE EXISTS ( SELECT 1 FROM categories c WHERE c.id p.category_id AND c.parent_id 5 AND c.status 1 );在MySQL优化器眼里这条EXISTS子查询和其他半连接写法一样会被转换成semi-join执行性能通常不差于等价JOIN。它的好处是把“是否存在”的业务语义显式写了出来后续维护的人一看就懂不会因为JOIN引发重复数据问题。3.4 分组取最新一条用窗口函数替代复杂子查询“分组取每组最新一条”是另一个子查询重灾区。传统写法往往是SELECT o.* FROM orders o JOIN ( SELECT user_id, MAX(created_at) AS max_time FROM orders GROUP BY user_id ) t ON o.user_id t.user_id AND o.created_at t.max_time;这条SQL有两个隐患一是如果同一个用户在同一个时间点下了多笔订单JOIN结果会重复二是关联条件无法唯一锁定一条记录。要彻底解决只能再加一个主键ID字段做二次比较SQL会变得又长又绕。MySQL 8.0之后这类问题的最佳答案是窗口函数WITH ranked AS ( SELECT o.*, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC, id DESC ) AS rn FROM orders o ) SELECT * FROM ranked WHERE rn 1;逻辑清晰能稳定处理时间戳重复的边界情况性能上也比老式相关子查询好得多。如果你的业务还在5.7甚至更早的版本那确实只能靠JOIN加ID比较来模拟窗口函数但这也正是“能不用子查询就不用”这句老话依然有价值的现实背景。4. 子查询不是原罪这几个场合保留反而更舒服4.1 结果集很小几十行的子查询没必要强行JOIN不是所有子查询都会慢。当子查询返回的数据量很小比如只有几十行、甚至几个常量时将其物化的成本极低相应的执行计划往往也不差。例如SELECT * FROM t_order WHERE status IN ( SELECT status_code FROM system_status WHERE enabled 1 );system_status表可能总共不到二十行。这种场景下子查询写法简单直接优化器也能轻松选择物化或半连接硬改成JOIN既不会带来可感知的性能提升还降低了可读性。判断标准只有一个EXPLAIN后看行数和耗时不要凭“用了子查询”就一刀切。4.2 统计型查询一次物化本来就在所难免有些查询的核心本来就是“先算出子集再做聚合”。比如计算某分类商品销售额占全部销售额的比例SELECT category_id, SUM(amount) AS category_sales, (SELECT SUM(amount) FROM orders) AS total_sales FROM orders WHERE category_id 5;这种场景子查询本身就是业务语义的一部分一次物化或常量扫描不可避免。改写成JOIN不但不能让执行计划更好反而会把两条业务逻辑纠缠到一起增加维护成本。我在实际项目中见过有人为了实现“占比”强行用JOIN结果不得不给聚合结果表加多个别名SQL膨胀成两百行性能却没有任何提升。这是典型的过度优化。4.3 版本决定论5.7/8.0的优化器早已脱胎换骨“不要用子查询”这句老话真正对应的MySQL版本是5.5及更早时代。那个时期的优化器对子查询几乎是“逐行执行”一条路走到黑性能当然难看。到了5.6优化器加入了半连接和物化5.7开始派生表的合并、子查询条件下推、相关子查询优化都有明显进步8.0更是引入了窗口函数和更完善的执行计划分析很多老旧经验已经不再适用。换句话说如果有人还在用一个5.6时代的经验去限制8.0生产库的写法很可能会错过一些更简洁、更优雅的表达。正确态度不是“完全不用子查询”而是“每次写完后用EXPLAIN验证一下执行计划”用数据和计划说话而不是用记忆里的教条说话。5. EXPLAIN与慢日志排查一眼判断该不该改5.1 EXPLAIN中必须看懂的四个信号判断一条SQL要不要从子查询改写不需要猜EXPLAIN能给出明确答案。重点看这几个字段字段关键表现含义select_typeDEPENDENT SUBQUERY相关子查询每行外层数据都会执行一次select_typeMATERIALIZED子查询被物化为临时表需检查物化表索引typeALL或index没有走有效索引可能在全表扫描ExtraUsing temporary; Using filesort存在临时表或排序数据量大时风险高只要EXPLAIN结果中出现DEPENDENT SUBQUERY且外层表数据量达到百万级别基本可以直接判定需要改写。出现MATERIALIZED则需要进一步分析关联阶段是否走了索引如果物化表关联时出现ALL也要警惕。另外5.7版本之后多了filtered字段表示存储引擎层扫描后有多少比例的行真正满足条件。filtered小说明索引区分度不够或者查询条件存在大量无效扫描。5.2 慢日志定位和8.0的EXPLAIN ANALYZE找问题SQL的第一工具是慢查询日志。如果还没开启可以先设置SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time设成1秒意思就是超过1秒的查询都会被记录下来。注意这个参数需要按需调整线上环境如果设置成0.1秒可能会日志暴涨设置成5秒又可能漏掉一些低频率但极慢的分析型查询。定位到候选SQL后8.0.18及以上版本有一个很好用的工具EXPLAIN ANALYZE。它不是像EXPLAIN那样给估算值而是真正把SQL跑一遍返回每个执行节点的实际耗时、实际返回行数、实际扫描行数。EXPLAIN ANALYZE SELECT u.id, u.name, (SELECT MAX(o.amount) FROM orders o WHERE o.user_id u.id) AS max_amount FROM users u;输出里会直接显示子查询节点被调用了多少次、每次平均耗时多少。看到百万级别的调用次数对“为什么慢”的理解会比任何理论都直观。5.3 optimizer_switch能做什么以及别乱动它MySQL提供的optimizer_switch参数可以控制某些优化策略的开关比如半连接、物化、派生表合并等。调试时可以用它验证“是不是某个优化策略导致执行计划走偏”。-- 查看当前开关状态 SELECT optimizer_switch\G -- 会话级别临时关闭物化 SET SESSION optimizer_switch materializationoff;这类操作通常只建议在会话级别做用来对比不同计划下的耗时不适合直接全局改。全局改参数容易引发连锁反应关掉materialization会迫使优化器走FirstMatch或DuplicateWeedout某些子查询可能变快另一些可能反而更慢。定位问题后更合理的处理方式是优化索引或改写SQL而不是靠调整优化器开关来迁就一条语句。5.4 改写后的压测与行数回归验证子查询改写成JOIN或EXISTS之后必须做两项验证。第一项是执行计划对比核心指标是rows估算总量也就是把EXPLAIN输出里各节点rows乘以调用次数再求和改后的估算总量要比改前低一个数量级以上才算成功。第二项是结果集数量对比。JOIN改写最怕行数放大我建议改写后分别执行一次原来的子查询版本和改写后的JOIN版本对比返回行数是否一致。这一步虽然繁琐但能避免“性能好了数据错了”这种最尴尬的情况。压测时不要只看一两次耗时至少跑三轮取中位数。MySQL的查询受缓冲池命中率影响很大第一次跑可能冷缓存第二次可能热缓存取中位数更有参考价值。如果条件允许最好在接近生产数据的量级下压测开发环境几万行数据和生产几千万行数据执行计划可能会完全不同。我个人在实际操作中的体会是与其背“不用子查询”的结论不如把它当成一个排查信号。看到SELECT列表带子查询看到EXPLAIN里的DEPENDENT SUBQUERY看到NOT IN配合可能出现NULL数据的列停下脚步跑一次EXPLAIN多花两分钟验证执行计划能省下后续调优的几小时也能避免那些改了之后数据对不齐的尴尬事故。真正的高手不是从不写子查询而是知道每一种写法在什么版本、什么数据量下会变成什么样。