先说一个总结论MySQL里子查询不是“不能用”而是在很多真实场景下会被优化器“带偏”导致明明简单的需求跑出几秒甚至几十秒的耗时。我自己这几年做后端开发接手过的慢SQL优化没有一百也有八十几乎一半的案例都能追溯到某条看似人畜无害的子查询。这篇内容不讲空洞的理论就拿实际踩过的坑、优化过的SQL、翻过的执行计划把“MySQL不使用子查询的原因”这件事彻底聊透。想直接拿优化经验的看第3章和第4章想彻底搞清楚背后原理的建议从第1章顺着读。1. 子查询为什么成了“慢SQL”重灾区1.1 先搞清楚子查询的三种形态在讨论原因之前得先把子查询按出现的位置分成三类因为它们在MySQL里的“待遇”完全不同。FROM子句中的子查询官方叫派生表Derived Table比如SELECT * FROM (SELECT ...) t。这类子查询相当于先查出一个临时结果集再包一层查询。WHERE子句中的子查询比如WHERE id IN (SELECT ...)、WHERE EXISTS (SELECT ...)、WHERE id (SELECT ...)这是最常见的写法也是出问题最多的地方。SELECT字段列表中的标量子查询比如SELECT name, (SELECT title FROM article WHERE id a.aid) AS title FROM author a。这种写法特别隐蔽因为它“看起来”只是多取了一个字段实际上每一行都要执行一次子查询。这三种形态里最容易踩坑的是WHERE里的IN和SELECT里的标量子查询其次才是FROM里的派生表。我早期做项目时也特别喜欢嵌套IN毕竟它符合人的第一直觉先在子表里查出符合条件的ID集合再拿这个集合去过滤主表。但在MySQL的底层这个“先用后查”的顺序往往和你想象的完全不一样。1.2 一个线上案例从11秒优化到0.8秒举个我印象很深的例子。订单系统里有个后台列表要查“最近7天内下过含某类商品的订单”那时候刚接手发现页面每次打开都要十秒以上。简化后的SQL大概长这样SELECT * FROM orders WHERE order_id IN ( SELECT order_id FROM order_items WHERE product_id IN ( SELECT product_id FROM products WHERE category_id 1024 ) ) AND created_at 2024-06-01 00:00:00;orders表一千万行左右order_items五千万行products一百万行三层嵌套。当时EXPLAIN一眼看过去执行计划用的是DEPENDENT SUBQUERY也就是说对于orders表里筛选出来的每一行MySQL都跑到内层子查询里重新执行一遍那两条SQL。换算一下外层只要剩下一万行内层就会被执行一万次每次都要碰order_items和products两张千万级表不慢才怪。后面改写成了两次JOIN配合上合适的索引整个查询耗时从11秒直接降到0.8秒。这个反差之大让我后来对“IN子查询”这四个字都有了生理性警觉。1.3 优化器在子查询上做了什么努力其实MySQL并不是对子查询完全不管。从5.6、5.7开始优化器引入了不少手段去改写子查询比如把IN子查询改写成半连接semi-join比如把某些派生表做合并derived_merge再比如把子查询结果物化成临时表来减少重复执行。但这些优化都有一个共同的前提优化器得“看得懂”你的子查询并且能找到一个划算的执行策略。一旦子查询里出现聚合函数、DISTINCT、GROUP BY、LIMIT、UNION、窗口函数等复杂结构或者子查询向外层引用了列相关子查询优化器就很可能放弃改写退回到最原始的逐行执行方案。换句话说不是MySQL不愿意优化子查询而是它的优化能力远远跟不上你写SQL的自由度。这也是为什么有经验的老开发会反复强调写子查询之前先想想能不能用JOIN或者提前查询来代替。这不是什么高端技巧本质上就是在和优化器的弱点做对冲。2. 三个性能黑洞逐行执行、临时表和索引失效2.1 相关子查询的逐行执行陷阱“相关子查询”是指内层子查询引用了外层查询的字段。比如SELECT a.*, (SELECT b.name FROM users b WHERE b.id a.user_id) AS user_name FROM orders a WHERE a.created_at 2024-01-01;这条SQL在逻辑上没问题但它有一个致命的性能特征外层每返回一行MySQL都要执行一次内层的SELECT b.name FROM users WHERE b.id ...。这个行为在慢日志里特别容易看到App端只是回了20条数据后端却可能要执行几千上百次内部查询。很多人会直觉地觉得“内层有索引查一次很快啊几毫秒而已。”但你把几毫秒乘以几万行再叠加网络、锁等待、日志记录等开销数字就非常可观了。而且如果内层的b.id a.user_id这个等值条件上的索引被函数包裹、或者字段类型不一致连那“几毫秒”都保不住直接变成全表扫描循环。我处理过最夸张的一个案例是某个报表接口里一个标量子查询被嵌在3层循环里每天凌晨跑批时要跑两个多小时后来改成预处理再连表直接压缩到10分钟以内。遇到相关子查询第一反应不是去分析内层快不快而是思考“能不能把内外两层数据一次性捞出来再关联”。2.2 物化和临时表怎么把一个简单查询拖垮MySQL对某些子查询会采用“物化”策略——把子查询先执行一遍结果存进临时表再让外层去访问这个临时表。这看起来是个好事情比逐行执行强多了但物化也有自己的代价。临时表的存储位置是有讲究的。如果数据量小MySQL会优先在内存里建MEMORY临时表一旦数据量超过tmp_table_size默认16MB关于这个参数不同版本有差异或者max_heap_table_size的阈值临时表就会被转成磁盘上的InnoDB临时表。磁盘临时表的读写性能比起内存来慢一个数量级而且在并发的线上环境里每个连接各建一张临时表内存磁盘两头折腾数据库整体负载很快就会上去。所以看到EXPLAIN结果里出现Using temporary就要提高警惕看看到底是“正常排序附带的小临时表”还是“把几千行几十列的数据整个物化出来还绕过了索引”。2.3 派生表与索引被遗忘的角落FROM子句里的子查询派生表有个很坑爹的点派生表生成的结果在很长一段历史版本里是没有索引可用的。即使你在内层SQL里给某个字段建了索引一旦把结果作为派生表供外层查询访问外层的ON、WHERE、GROUP BY等条件都无法直接利用内层字段上的索引只能基于派生表的全表扫描去做过滤或连接。举个例子SELECT t.* FROM ( SELECT id, user_id, amount FROM orders WHERE status 1 ORDER BY amount DESC LIMIT 1000 ) t JOIN users u ON u.id t.user_id;如果MySQL选择把内层结果物化成临时表那么t.user_id这个临时表字段上是没有索引的JOIN的时候无论users表有多小MySQL都需要对派生表做一遍全扫描或者哈希处理。新版本MySQL的派生表合并优化能够在一定程度上避免这种情况但是当派生表内部带了 GROUP BY、聚合、窗口函数等限制时合并依然无法生效。这也是个很常见的优化点能用原始表直接JOIN的就不要先查一个临时结果集再去JOIN。2.4 半连接优化也不是万能的半连接是MySQL针对IN子查询引入的优化策略它能让内层子查询和外层表以“类似JOIN但不产生重复行”的方式去执行。乍一听很完美但真实环境里它有几种执行策略——materialization物化、firstmatch先匹配、loosescan松散扫描、duplicateweedout去重淘汰——而优化器是怎么选的是一个黑色的决策树。更麻烦的是半连接在遇到NOT IN的时候通常不会生效。NOT IN跟NOT EXISTS的语义有差别NOT IN在子查询结果里即使碰上一个NULL值整个查询结果都会变成空集合。MySQL为了避免语义出错往往不会对NOT IN做太激进的优化。这条规则我见过太多人踩坑后面第4章会专门用一个案例讲。3. 替代方案解析怎么把子查询改成高效率查询3.1 IN子查询改为JOIN先解决去重问题最常见的改写方式是把WHERE id IN (SELECT ...)改成 JOIN-- 改写前 SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM vip_users WHERE level 3); -- 改写后 SELECT DISTINCT o.* FROM orders o INNER JOIN vip_users v ON v.user_id o.user_id WHERE v.level 3;这里必须加DISTINCT或者先对vip_users子表去重因为IN的语义本身自带“结果集去重”而JOIN如果一边是多行一边是单行会产生笛卡尔式重复。我实际操作时会优先选择先对右边做一次去重派生表或者提前在子查询里把vip_users整理成唯一集合这样加不加DISTINCT都逻辑清晰不会误伤结果。不过JOIN改写也不是银弹。如果两个表的数据量差异悬殊优化器选择的驱动表和连接顺序就变得异常关键得用EXPLAIN验证执行计划是否真的走对了索引而不是改完就以为万事大吉。3.2 关联查询里EXISTS的取舍很多人有一个根深蒂固的误解“EXISTS比IN快”这话在Oracle时代可能有点道理在MySQL里必须打问号。EXISTS如果写成了相关子查询照样有逐行执行的风险而IN如果被优化器正确转换成了半连接性能不见得比 EXISTS 差。我自己的选择标准是这样的如果子查询的结果集很小且这段SQL是给运营后台用的低并发场景用IN或EXISTS都可以接受。如果子查询的结果集很大或者外层表很大尽量改写成JOIN让优化器有全貌来做连接顺序决策。如果确实需要保留EXISTS要确保子查询里引用的外层字段所对应的内层表索引非常明确比如存在性判断WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id o.id)这种单点索引查询在逻辑上非常干净保留它是合理的。判断标准其实就一条子查询和内层表能不能紧密配合使用索引。能保留不能改写。3.3 派生表重写为临时表或视图遇到FROM子句里那种复杂的派生表建议重写为“先建临时表再加索引”的思路虽然代码里多了几条语句但性能会非常稳定。-- 第一步查出中间结果 CREATE TEMPORARY TABLE tmp_order_summary AS SELECT order_id, SUM(amount) AS total_amount FROM order_items WHERE status 1 GROUP BY order_id; -- 第二步给临时表加索引 ALTER TABLE tmp_order_summary ADD INDEX idx_order_id(order_id); -- 第三步正式业务查询 SELECT s.order_id, s.total_amount, u.user_name FROM tmp_order_summary s LEFT JOIN users u ON u.id s.order_id;这和直接写派生表的最终目的是一样的但因为临时表里我们可以显式加索引就绕开了“派生表字段无索引”的限制性能稳定得多。当然要注意临时表会话一结束就没了适合在存储过程、脚本或者后台任务里使用。如果是在线上SQL里高频调用就得考虑改成物化视图或定期汇总表而不是每次都实时算。3.4 标量子查询可以用“内存关联”代替SELECT列表里的标量子查询最彻底的优化方案是在应用层做“内存关联”。比如前面那个查用户名的例子如果订单列表有20条只需要先查出20条订单再把这20条订单里的 user_id 拼成IN (1,2,3,...)去users表里一次性查出来然后在内存里组装成Map最后在循环里填上名字。这个方案的优点是彻底消灭了每条结果各执行一次子查询的问题在Java、Go、PHP这类后端语言里实现起来非常顺手。很多人不知道的一点是很多时候SQL查询的价值在于数据过滤而不在于最后那10行数据的格式化。把“补字段”的动作从数据库挪到应用内存里数据库的压力可以小很多。4. 四个实战复盘从EXPLAIN到改写全程记录4.1 订单查询两层嵌套IN子查询的改写过程回到开头那个三层嵌套的案例我当时完整走了一遍排查流程先拿到慢SQL粘进EXPLAIN里看到内层出现了DEPENDENT SUBQUERY且整条语句预估扫描行数超过五千万。这种执行计划没有继续分析和调优的必要直接改写。我的改法是分步把内层结果查出来再逐级JOIN-- 第一步锁定商品类目对应的商品 -- 因为category_id上有索引这一步只扫匹配到的商品行 SELECT product_id FROM products WHERE category_id 1024; -- 第二步用商品ID去关联订单明细 -- order_items表(product_id, order_id)建成联合索引后这一步走索引覆盖 SELECT DISTINCT order_id FROM order_items WHERE product_id IN (刚才的结果集);实际SQL里我用JOIN把它拼成一条并给order_items建了(product_id, order_id)的联合索引products表建了(category_id, product_id)的联合索引。改写前后执行时间从11秒降到0.8秒扫描行数从“预估全表几千万”降到“实际扫描两万左右”。整个过程最核心的点不是SQL语法本身而是让每一步都踩上索引而不是让优化器去猜一个大集合。4.2 统计报表NOT IN引发的空结果与性能黑洞有一个统计需求是“找出近30天没有下过单的VIP客户”大家都喜欢写NOT INSELECT customer_id, customer_name FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM orders WHERE order_time 2024-05-01 );这个SQL我接手时已经线上跑了很久负责维护的同事抱怨说“结果偶尔对偶尔不对慢的时候要半分钟”。我一查执行计划果然Cost很大大概率是全表循环再一查数据orders表里 order_time 字段核心索引没问题但 customer_id 列里有NULL。NOT IN的经典陷阱在这里炸开了只要子查询返回的结果里包含任何一个NULL值整条NOT IN语句的返回值就是空集合不是“过滤掉NULL”而是“全部为空”。这就是为什么结果“偶尔不对”。再加上NULL存在时MySQL很难以高效的半连接方式去处理NOT IN只能退回到逐行相关子查询性能自然也好不了。优化方案有两种二选一就好-- 方案A将NOT IN改为NOT EXISTS SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.order_time 2024-05-01 );-- 方案B改写成LEFT JOIN 空值过滤 SELECT c.customer_id, c.customer_name FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id AND o.order_time 2024-05-01 WHERE o.customer_id IS NULL;改写成LEFT JOIN后执行计划就变成了一项普通的连接过滤配合orders表(customer_id, order_time)的联合索引扫描量骤降。这条SQL让我深刻记住了NOT IN不是简单的“反向IN”它自带NULL语义地雷。4.3 分页查询标量子查询越翻越慢的根因分页接口翻页越深越慢这个现象大家应该都有体感。理论上LIMIT 10000, 20也就是多扫描了一万行而已但如果你在SELECT字段里挂了标量子查询那情况就完全不一样了SELECT o.*, (SELECT u.user_name FROM users u WHERE u.id o.user_id) AS user_name, (SELECT p.title FROM products p WHERE p.id o.product_id) AS product_title FROM orders o ORDER BY o.created_at DESC LIMIT 10000, 20;这条SQL的扫描范围不是一万行而是每一行扫描时都要去users和products表做一次索引点查扫到10020行等于users和products各执行了一万多次点查找再加上排序和回表慢是必然的。我的处理办法很简单先把分页条件查出来全部放在内存里再补信息SQL层面用JOIN一次性关联应用层拼字段。SELECT o.*, u.user_name, p.title FROM orders o LEFT JOIN users u ON u.id o.user_id LEFT JOIN products p ON p.id o.product_id ORDER BY o.created_at DESC LIMIT 10000, 20;注意点这里要确保o表的created_at有索引且LEFT JOIN连接的字段类型一致否则不但没优化反而可能更差。这个案例再次证明标量子查询一旦配合大数据量的外层扫描就是慢SQL最经典的孵化器。4.4 一个被优化器“救回来”的子查询不能全盘否定子查询。我在压测里见过一次很有意思的场景某个IN查询子查询结果集非常小几十条外层表很大千万级EXPLAIN显示MySQL选择了materialization把几十条子查询结果物化成一个带索引的临时表然后外层用索引去关联。改写前后性能几乎没差别甚至子查询写法在逻辑可读性上反而更好。这种场景通常具备几个特点子查询里没有复杂的聚合或相关引用、子查询返回的行数很少、外层条件能够借用物化临时表的索引。遇到这类SQL我也会保留子查询的写法同时也在注释里说明“优化器已物化勿随意改动改动后需重新EXPLAIN验证”。所以我始终强调“不使用子查询”不等于“禁止子查询”而是说你得有判断力分得清子查询会被优化还是会被带偏。5. 什么时候可以放心用子查询判断标准与排查技巧5.1 检查执行计划抓住四个关键信号无论什么经验总结落到实操都绕不开EXPLAIN。我建议在把任何一条新SQL上线前都执行一遍EXPLAIN重点关注四个信号信号含义风险等级DEPENDENT SUBQUERY子查询逐行执行外层每行都重查一次高必须改写Using temporary使用了临时表大概率有形如物化的操作中需结合数据量判断key列为NULL某个表查询没走索引高优先解决rows预估过大优化器预计扫描行数远大于实际筛选结果中结合实际情况只要看到DEPENDENT SUBQUERY不用犹豫直接改。我的经验是它几乎不会出现在一条高效SQL的执行计划里。另外MySQL 5.7及以上版本里EXPLAIN还支持 EXPLAIN ANALYZE能直接输出每一步的“实际执行时间和实际行数”用它来验证改写效果比我上面说的预估rows更可靠。5.2 允许保留子查询的三种典型场景讲了这么多“不要用”也得讲清楚哪些场景保留是合理的免得大家矫枉过正把所有子查询当成洪水猛兽。存在性判断WHERE EXISTS (SELECT 1 FROM ... WHERE ...)这种写法只要子查询里能用到索引并且返回列不重要用1代替*性能通常和JOIN相当保留反而可读性更好。小型关联结果集子查询返回的数据只有几十几百行且外层通过索引就能快速关联时优化器物化子查询通常很高效保留没毛病。聚合辅助限制比如“查询每个分类里订单数量最多的商品”这类需求如果用JOIN改写往往需要配合窗口函数或临时表写起来更绕但用子查询配合分组取第一条的逻辑反而更容易理解和维护。5.3 一些排查慢SQL的实操建议最后分享几条我用得最多的实操习惯慢日志打开是第一步。slow_query_log和long_query_time的设置能帮你快速定位到所有需要关心的SQL而不是靠业务方反馈才知道系统卡了。写SQL时先看表结构和索引。常见的慢查询很多时候在开发阶段就能避免掉了——只要你知道目标表有哪些索引根本不会有“在无索引字段上做子查询过滤”这种写法。大表子查询之前先跑一步看结果体积。比如把IN (SELECT ...)里的子查询单独拿出来跑一遍看看它返回多少行再判断是否值得改写。这一点成本最低效果却极好。我在实际业务里还有个习惯凡是涉及JOIN、IN、EXISTS、子查询的敏感SQL都要在评审时附上EXPLAIN结果。这比在代码里写多少注释都管用因为执行计划会直接告诉你这条路通不通。写到最后的一点体会这么多年下来我对“MySQL不使用子查询”这件事的态度可以浓缩成两句话第一子查询最大的问题不是语法本身而是它经常让优化器做出糟糕的决策尤其体现在相关子查询、NOT IN、复杂派生表这几个场景里第二不要期待“记住一句规则就能避免所有坑”真正靠谱的做法是每条SQL上线前跑一遍EXPLAIN用执行计划来判断子查询到底有没有被优化器接管。我个人现在写SQL的习惯是默认优先用JOIN表达多表关联只有极少数结果集很小、逻辑很清晰、且EXPLAIN验证过的场景下才保留子查询。这个习惯帮我挡掉了大量线上慢SQL也让接手我代码的同事少骂几句脏话。把这套判断方法带回去下次遇到慢SQL不妨先看一眼是不是子查询在作祟。