MySQL的子查询到底能不能用这个问题我被问过不下一百次。每次我都先反问他一句你口中的不能用是听别人说的还是自己用EXPLAIN验证过大多数人会愣住然后含糊地说网上都这么说子查询性能差。其实这话不算错但严重失准。今天我想用一次线上事故和一组实测数据把MySQL不使用子查询的原因这件事彻底讲透也聊聊哪些子查询该保留、哪些必须改。这篇文章适合还在背结论的开发同学也适合遇到慢SQL不知道从哪下手的运维和DBA。你不需要成为优化器源码专家只要掌握几个判断维度就能在性能和可读性之间找到比较舒服的平衡点。1. 从一次高峰期慢查询说起子查询的问题是怎么暴露的1.1 事故现场一条IN子查询打满数据库CPU先交代一下背景。那是一套电商系统订单表和用户表分开存储。业务要做的是最近30天内有成功支付订单的用户列表开发同学很自然地在接口里写了一条IN子查询SELECT u.id, u.nickname, u.mobile FROM users u WHERE u.id IN ( SELECT o.user_id FROM orders o WHERE o.status paid AND o.pay_time NOW() - INTERVAL 30 DAY );从语法角度看这句SQL没有任何毛病语义也一目了然甚至比JOIN更好懂。但上线之后这个接口在峰值时段频繁超时。我接手排查时数据库的CPU已经长时间徘徊在85%以上慢查询日志里这条SQL动不动跑出两秒多。第一反应就是先EXPLAIN。MySQL执行计划出来之后问题其实已经比较清楚了users表走了全量扫描rows列写着五十万orders表倒是走了索引但Extra列里出现了Materialize、Start temporary、End temporary等字样说明优化器把orders子查询的结果整个物化成了一张临时表。也就是说这条SQL根本不是先找出符合条件的订单再回users匹配而是先把一堆订单数据完整落进内存/临时表再去和50万行users做外层扫描。几十万行订单一旦全部物化内存、排序、扫描全部压上来不快才奇怪。这个场景我后来复盘了很多次。它最大的迷惑性在于子查询本身在优化器看来没什么语法错误数据分布也没问题但它选择了一条看起来合理、实际上非常重的执行路径。你如果不去看EXPLAIN完全是为什么慢都不知道。1.2 是子查询的锅还是写法的锅故障解决之后团队里自然有人提出以后禁止用子查询一律改JOIN。我没有直接同意。因为这次事故的真正问题不是子查询这三个字而是执行计划选择了物化路径。说白了同样的语义如果优化器在某条统计信息下恰好改成了半连接semi-join那SQL可能跑得跟JOIN一样快。禁止一个语法不如学会看懂执行计划。这也是为什么我后来带团队时第一课永远是EXPLAIN而不是SQL规范。你可以有倾向性地推荐JOIN但不能把子查询定义为禁用因为在MySQL里子查询被嫌弃是有历史原因的早年优化器对子查询的处理非常简陋几乎没有任何转换慢是真慢现在优化器先进了却仍然有不少场景会打开物化或逐行执行这两条开销很大的路径。要把原因讲清楚我们得先看优化器到底在哪些情况下会走极端。2. 子查询慢的根源逐行执行、物化临时表与NOT IN的NULL陷阱2.1 关联子查询的逐行地狱先说最经典的一种坑关联子查询。它的特征就是内层SQL引用了外层表的某个字段内外层产生依赖。比如很多人推崇的EXISTS写法SELECT u.* FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );看到EXISTS很多开发第一反应是EXISTS比IN快。我年轻时也这么以为直到用EXPLAIN看了执行计划才发现只要优化器没有做进一步转换这类关联子查询就会按嵌套循环的方式执行外层users有多少行内层orders查询就跑多少次。users有50万行那orders的索引查找就要执行50万次哪怕是逻辑很简单的主键索引查找乘以这个倍数也扛不住。这里有个很直观的代价模型总成本约等于外层行数乘以内层单次查询成本。外层行数N是50万时内层单次哪怕只要0.1毫秒合计也要50秒要是内层查询本身需要排序或回表单次成本轻松上毫秒那条SQL基本就别想用了。所以遇到关联子查询第一件事就是看外层表有多大、内层有没有可用的索引。只有外层小、内层有索引这个写法才安全。2.2 非关联子查询的物化陷阱和内层独立执行的关联子查询不同非关联子查询的内层SQL不依赖外层字段理论上优化器有更多发挥空间。MySQL从5.6开始引入物化和半连接优化5.7继续增强8.0也一直在演进。但问题在于物化不等于免费。什么是物化简单说优化器把子查询的结果先算出来放进一张临时表后续再拿着这张表和外层表匹配。临时表可能在内存也可能因为太大落到磁盘。建临时表、写入数据、建立索引或排序每一步都有成本。如果子查询的结果集有几十万行而外层经过过滤后其实只需要匹配几百行那优化器就做了一个又重又亏的中间步骤。更要命的是半连接转换不是所有子查询都能触发。MySQL官方文档里明确列出了一些情况子查询只要包含GROUP BY、聚合函数、LIMIT、ORDER BY等就无法走半连接优化器只能选择物化或逐行执行。很多开发只知道IN子查询会被优化成JOIN但实际写出来的子查询往往因为带了聚合或排序根本没触发半连接只有物化甚至是更差的执行路径在等着。2.3 NOT IN与NULL最容易被忽略的语义雷前面两条讲的是性能这一条直接关系到结果正确性。SQL里有三值逻辑除了TRUE和FALSE还有UNKNOWN。NULL参与比较就会产生UNKNOWN。而NOT IN遇到子查询结果中出现NULL时整个条件会变成UNKNOWN最终结果集就是空的。举一个很简单的例子SELECT id FROM users WHERE id NOT IN ( SELECT user_id FROM orders );只要orders表里任意一行的user_id是NULL这条SQL就什么都查不出来。无论业务上这些用户是否下过订单结果都是空。更麻烦的是优化器不会因为你心里想的是反连接就自动处理NULL它必须严格按三值逻辑计算执行计划也因此会变得保守。所以我在代码评审里一直强调如果要用A表有、B表没有这种反连接语义优先写NOT EXISTS或者LEFT JOIN再加IS NULL过滤。这样语义明确执行路径也可控比起NOT IN那种表面简单、暗藏杀机的写法安全得多。这个坑我见过不止一个团队在数据校验场景里踩进去而且因为返回为空看起来太正常往往要等业务反馈数据对不上才发现。3. 实测对比IN、EXISTS与JOIN在200万行订单表上的差距3.1 测试环境、表结构与造数原理讲再多不如跑一组数据直观。我专门搭了一个测试环境MySQL 8.0.328核16G的普通云主机模拟业务场景造了两张表。users表50万行orders表200万行orders的user_id字段建了普通索引status也建了索引。表结构大致如下CREATE TABLE users ( id INT PRIMARY KEY, nickname VARCHAR(50) ) ENGINEInnoDB; CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(10), amount DECIMAL(12,2), pay_time DATETIME, KEY idx_user_id (user_id), KEY idx_status_amount (status, amount) ) ENGINEInnoDB;数据用存储过程循环插入金额、状态、时间都做了随机分布。测试目标是找出有金额大于100元且状态为paid订单的用户。我用三种等价SQL分别跑每一条都先执行几次做热身后再计时取均值。3.2 三条等价SQL的耗时对比第一种IN子查询SELECT u.id, u.nickname FROM users u WHERE u.id IN ( SELECT o.user_id FROM orders o WHERE o.status paid AND o.amount 100 );第二种EXISTS关联子查询SELECT u.id, u.nickname FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status paid AND o.amount 100 );第三种JOIN后去重SELECT DISTINCT u.id, u.nickname FROM users u JOIN orders o ON o.user_id u.id WHERE o.status paid AND o.amount 100;让我直接说结果。我这个环境下的平均耗时大致是IN子查询约1.9秒EXISTS关联子查询约3.2秒JOINDISTINCT约0.8秒。执行计划核心信息我也整理了一下写法平均耗时执行计划关键点IN子查询约1.9秒物化临时表外层全表扫描EXISTS关联子查询约3.2秒外层全表扫描内层索引查找DEPENDENT SUBQUERYJOIN DISTINCT约0.8秒过滤后订单集做驱动users主键回表这个数字肯定随数据分布波动但差距方向是稳定的。三个版本里最慢的居然是被很多人视为更高效的EXISTS这不是说EXISTS写法不好而是在这个数据模型下优化器没能在它身上选到一条更聪明的路径只能老老实实地逐行执行。反而是IN子查询虽然走了物化但物化之后的匹配方式比逐行EXISTS还快些。JOIN能赢靠的是优化器对两表连接成本评估的老练。3.3 为什么JOIN赢在给了优化器更多选择JOIN版本之所以快不是因为JOIN语法比子查询魔法而是因为它给了优化器更完整的统计和路径选择空间。优化器在处理JOIN时可以基于orders表过滤后的结果集大小决定谁是驱动表可以按需选择嵌套循环、哈希连接MySQL 8.0后也有hash join还可以借助主键回表高效拿users字段。而子查询尤其是物化路径下优化器要面对一张临时生成的结果集统计信息往往不完整连接顺序选择就容易保守。我也要说明一点如果IN子查询恰好被优化器转换成了半连接那它的执行计划会和JOIN非常接近性能自然也不差。所以别把IN一定慢当成真理。我平时判断一条子查询到底该不该改从来不是看它写了IN还是EXISTS而是看EXPLAIN最终怎么落地。落地成半连接那就不折腾落地成物化或DEPENDENT SUBQUERY且行数巨大那就立刻考虑改写。4. 不是所有子查询都要改哪些场景可以放心保留4.1 子查询结果集很小或外层结果集很小时一个技术结论如果只用一句不要用来概括一定会在某些场景下误导人。子查询之所以被嫌弃核心是执行路径重。但如果结果集本身很小重也就不存在了。第一种常见场景子查询结果集小。比如状态表、分类表、白名单表可能就几十行几百行物化成本可以忽略。这种IN子查询直观好读强行改JOIN反而显得绕。SELECT * FROM products WHERE category_id IN ( SELECT id FROM categories WHERE status 1 );第二种场景外层结果集很小。我前面说过关联子查询最怕外层表大反过来如果外层经过WHERE过滤后只剩几十行内层又有索引命中那逐行执行也不再是问题EXISTS甚至可以用得很安逸。问题从来不是用了某个语法而是语法匹配的数据规模不匹配。4.2 LIMIT取每组TOP N相关子查询反而更直观还有一个场景是JOIN改写很容易把人绕晕的每组取TOP N。比如要查每个用户最近一笔订单用GROUP BY加MAX(pay_time)再回表当然可以但SQL很长索引也不好设计。用相关子查询加LIMIT 1反而直观SELECT o.* FROM orders o WHERE o.id ( SELECT o2.id FROM orders o2 WHERE o2.user_id o.user_id ORDER BY o2.pay_time DESC, o2.id DESC LIMIT 1 );前提是orders表上有针对(user_id, pay_time, id)的联合索引否则内层每一次都要全表排序等于把子查询的缺点全部引爆。索引正确时这个写法比很多聚合改写都干净。当然MySQL 8.0有窗口函数ROW_NUMBER()可以用但窗口函数要构建分区窗口内存开销大压测不过的时候回退到相关子查询也是常见操作。4.3 MySQL 8.0优化器进步后还需要禁止子查询吗过去那句MySQL子查询慢如牛的经验绝大多数诞生在5.1、5.5年代。那时优化器对子查询的处理确实简陋很多子查询只能逐行跑甚至有些物化和派生表优化还没有。今天用5.7或8.0的团队如果你还沿用逢子查询必改JOIN的规矩可能会改掉不少本来就很快的语句。MySQL 8.0在派生表合并、半连接、哈希连接上都做了不少改进尤其是连续多个小版本里都有人专门针对IN/EXISTS相关子查询调优。所以我的观点是版本升级后规则不是可以乱用子查询而是必须在EXPLAIN的引导下使用。你完全可以立一条规矩——所有带子查询的SQL上线前必须贴EXPLAIN看到materialize或dependent subquery就重点评估。这个规矩比不允许子查询科学得多也灵活得多。5. 慢SQL排查如何判断这条子查询是否需要改写5.1 看EXPLAIN的四个关键线索排查子查询相关慢SQL我会先抓四个字段type列外层表出现ALL全表扫描。如果外层是大表基本就是性能隐患。rows列估算行数是不是和外层表一个量级。如果EXPLAIN里rows显示的是几十万潜意识就可以确认优化器没有缩小数据范围。Extra列出现DEPENDENT SUBQUERY、SUBQUERY、Materialize、Start temporary、End temporary。这些关键词分别对应逐行执行和物化两条重路径。key列内层子查询如果没有走任何索引key为NULL那内层查询可能每次都在扫全表这条SQL基本无解。其中Extra列最容易被忽略。很多人EXPLAIN只看type还算不算好不看Extra结果漏掉了物化和临时表这两个真正的性能杀手。5.2 我习惯的改写SOP先验证结果集再改语法我自己处理这类慢SQL有一套固定顺序简化下来是这样先EXPLAIN原SQL记录执行路径和时间。记录当前SQL返回的结果集行数作为改写的基准。根据业务语义选择改写方案关联子查询优先想EXISTS或JOIN反连接用NOT EXISTS或LEFT JOIN IS NULL普通IN看能不能拆成JOIN。改写后先对比结果集行数再抽样几条数据确认字段一致。尤其注意JOIN产生的重复行该加DISTINCT就加DISTINCT。再次EXPLAIN和计时确认执行路径真的变了比如不再物化、驱动表更合理。第4步是绝大多数人跳过的。我见过好几次团队把IN改成JOIN后忘了去重返回结果翻了几倍因为业务侧没有直接报错直到BI报表对不上才发现。改SQL不是改作文语义一致性永远是第一位的。5.3 一点个人心得索引没到位改JOIN也救不了你最后聊个很现实的事。我复盘过的子查询慢SQL里真正需要靠改写语法解决的不到一半更多时候根因是索引缺失。比如内层子查询在orders.user_id上有索引但status和amount没有联合索引导致每次过滤都要回表或走文件排序。这种问题你改成JOIN一样慢因为重路径只是换了件马甲。所以我现在看到同事写子查询第一反应不是让他改成JOIN而是让他先把EXPLAIN发过来一起看索引和rows。等索引到位之后再判断这个子查询是否还有必要改写。语法是死的人是活的但真正能让执行计划发生质变的是索引、统计信息和数据分布。把这三样搞懂子查询用还是不用你心里自然有数。