1. 一次让我对子查询产生警惕的线上故障1.1 故障背景一条不算复杂的SQL把数据库CPU打满先讲一件真事。前两年我负责的一个电商系统出了一次线上事故用户反馈订单列表打开极慢后台监控MySQL CPU直接飙到95%以上。我抓出慢查询日志罪魁祸首是一条看起来完全人畜无害的SQLSELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM user_coupons WHERE coupon_status 1 );逻辑很清楚找出所有有可用优惠券的用户的订单。我当时的第一个念头是检查索引。结果发现orders(user_id)和user_coupons(user_id)都有索引coupon_status也有索引数据量也就几十万行怎么都不该把CPU打满。直到我把这条SQL单独拎出来执行发现耗时居然要8秒多。真正的问题在EXPLAIN里暴露了MySQL对IN (SELECT ...)的默认处理方式是先执行子查询把结果存到一个内部临时表然后再和orders表做连接。问题在于这个临时表上没有索引外层orders表的每一行都要去全表扫描临时表匹配复杂度直接变成外层行数乘以内层结果集行数。数据量一上来CPU不炸才怪。1.2 我的排查思路从表面SQL到执行计划我当时的排查顺序很老套但非常有效先看SHOW PROFILE或者慢查询日志确认这条SQL确实是瓶颈。用EXPLAIN看执行计划重点看type字段、key字段、Extra字段里有没有Using temporary。对比改写前后两条SQL的EXPLAIN输出看扫描行数和访问类型的变化。最后在实际测试环境用小数据量验证结果一致性再在大数据量下压测。那次EXPLAIN的结果大概是这样的表typekeyrowsExtraordersALL主键45万Using whereuser_couponsALLNULL3万Using where; Using temporary两张表全是全表扫描user_coupons的子查询结果被物化成临时表后还带着Using temporary这已经是明显的危险信号。我顺手把改写后的JOIN版本拿过来一测SELECT o.* FROM orders o INNER JOIN user_coupons uc ON o.user_id uc.user_id WHERE uc.coupon_status 1;执行计划瞬间变成orders走user_id索引user_coupons走coupon_status索引两条索引各扫各的再合并耗时直接从8秒降到0.2秒。那一刻我对MySQL的子查询默认不可信这句话有了实感。1.3 为什么第一反应是改写而不是继续优化子查询说实话很多时候遇到子查询慢第一反应是给子查询里的表加索引我试过确实有一定作用但遇到需要物化临时表的场景索引作用有限。MySQL的优化器在子查询物化之后临时表就是一张全新的表原来的索引全都用不上。除非你手动给临时表建索引但那不是普通SQL能做到的。所以我现在的处理原则是先看执行计划如果子查询被物化成临时表并且没有索引基本不用想怎么优化直接改写。改写成JOIN不仅对优化器更友好代码的可读性也没有变差唯一的负担是需要注意去重和语义等价这个我在后面会详细讲。2. 子查询性能瓶颈的本质优化器与执行引擎的短板2.1 相关子查询的重复执行代价子查询慢的第一个根源是相关子查询。所谓相关子查询就是内层子查询引用了外层查询的列比如SELECT * FROM products p WHERE price ( SELECT AVG(price) FROM products WHERE category_id p.category_id );这种SQL乍一看好像没问题但MySQL的执行逻辑是先拿外层表的一行算出p.category_id然后把这个值代入内层子查询执行一次得到平均价再比较。外层表有多少行内层子查询就要执行多少次。假设外层有10万行内层表有100万行理论上的扫描次数就是10万乘以100万这个量级没有哪个数据库能扛得住。底层原因是相关子查询没办法像普通子查询那样先独立执行一遍并保存结果。它必须依赖外层每一行的上下文所以优化器几乎没有任何提前物化的空间。虽然MySQL 5.7之后对某些相关子查询有依赖缓存优化但覆盖场景非常有限通常只适用于内层查询完全是唯一索引等值匹配的情况绝大多数场景仍然会退化成逐行执行。2.2 IN子查询的临时表与去重问题前面说的订单故障就是典型的不相关子查询也被物化成临时表的问题。具体来说MySQL优化器在遇到IN (SELECT ...)时通常有两种处理策略一是把子查询转成半连接semi-join二是把子查询结果物化成一张临时表。半连接在5.6之后才有而且不是所有场景都能触发。一旦走了物化路径临时表就存在以下几个问题临时表默认没有索引除非结果集大小达到优化器认为值得建索引的阈值否则外层查询每匹配一行都要扫临时表。临时表需要额外的内存或磁盘I/O来创建和销毁数据量一大内存临时表会升级成磁盘临时表性能直接掉一个量级。IN子查询的语义是存在即可所以临时表要去重去重本身也是开销。这才是IN子查询被长期诟病的核心不是说它的逻辑不好而是执行引擎在处理去重 临时表这两个环节时效率太低。2.3 FROM子查询派生表的无索引问题另一种常见场景是把子查询放到FROM子句后面作为派生表SELECT * FROM ( SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ) AS t JOIN users ON t.user_id users.id;这不比IN快甚至可能更慢。问题出在派生表物化之后没有索引外层JOIN访问它时只能直接全表扫。MySQL 5.7之前几乎无解5.7之后才引入了派生表合并优化即把简单的派生表直接合并到外层查询里避免物化。但要注意这个合并只在派生表**没有聚合、没有去重、没有LIMIT**等限制条件时才会触发。一旦你用GROUP BY或DISTINCT又回到物化临时表的命运。2.4 子查询优化器的历史局限性如果把视角拉远一点MySQL的子查询优化能力长期落后于PostgreSQL和SQL Server。原因不复杂MySQL早期版本5.5甚至更早几乎没有任何子查询重写机制执行子查询就是最原始的嵌套循环思路外层一行内层全扫。后来Oracle团队接手MySQL从5.6开始才逐步引入半连接、物化、派生表合并等优化手段。这也解释了为什么网上老派DBA有一句口头禅MySQL不要用子查询用JOIN改写。这句结论放在10年前完全正确放在5.7之后就要打折扣放在8.0时代已经不能一概而论。了解这个历史背景你才不会被网上互骂MySQL子查询能用/不能用的帖子带偏。3. 哪些子查询场景最需要警惕类型化拆解3.1 IN (SELECT ...)看数据量小表无妨大表是坑IN子查询是最常见的背锅侠但并不意味着所有IN子查询都该死。如果子查询的结果集很小比如几百行MySQL走物化路径时临时表很小扫描一次也就几百行开销完全没问题。我在实际开发中也经常用简单IN子查询比如SELECT * FROM product WHERE id IN (1, 2, 3, 4);这种是明确值列表和子查询两回事。真正要警惕的是内层结果集达到几万甚至几十万行、外层也是几十万行以上的场景。一旦两层数据量都上来临时表的全表扫描会成为灾难。判断标准很简单子查询结果集远大于外层参与匹配的行数时通常更适合JOIN。3.2 相关子查询逐行执行的灾难相关子查询是我个人最不推荐的一种写法因为它不是优化器改不改进的问题而是执行模型本身决定了它必然是逐行循环。看这个经典场景SELECT name FROM employees e WHERE salary ( SELECT AVG(salary) FROM employees WHERE department_id e.department_id );如果每个部门平均20人10万员工那就是5000个部门相当于执行5000次嵌套查询。你可以优化内层子查询的department_id索引但依然避免不了外层一行、内层一次的循环。这种查询最合适的改法是SELECT e.name FROM employees e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) d ON e.department_id d.department_id WHERE e.salary d.avg_salary;把逐行执行改成一次分组聚合再用JOIN关联扫描次数从N N变成1次全表分组 1次索引关联性能通常提升几个数量级。3.3 FROM (SELECT ...) AS t无索引派生表FROM子查询往往被开发人员当成逻辑封装的手段比如把一串聚合和处理后的结果集当临时表用。但MySQL执行的时候这个中间结果需要物化物化后没有索引后续所有JOIN都变成对临时表的全表扫描。尤其是在派生表数据量变大以后磁盘I/O和临时表交换会双重打击性能。有几种情况必须警惕派生表里有GROUP BY、DISTINCT、聚合函数时合并优化失效。派生表被多个外层表引用时无法合并。外层查询有LIMIT或ORDER BY时合并策略也可能改变。这部分我的经验是如果派生表的数据量预计超过几千行且后续要参与JOIN最好直接改写为显式临时表并手动建索引或者拆成两条SQL在业务代码里分步处理。3.4 子查询与JOIN语义不完全等价什么时候不能直接改这里必须泼盆冷水不是所有子查询都能无脑转JOIN。语义坑主要在三处IN子查询的结果集有重复值转成内连接JOIN后会导致外层行重复匹配。比如子查询返回(1, 1, 2)JOIN会得到两行重复的订单。改写时通常需要加DISTINCT。NOT IN子查询在遇到NULL值时行为完全不同。NOT IN (SELECT ...)只要子查询结果里有NULL整体结果就全部是空集而NOT EXISTS不会这样。所以NOT IN转LEFT JOIN ... WHERE ... IS NULL时要额外小心。子查询用了LIMIT或ORDER BY时没有办法直接JOIN比如找某分类销量前三的商品这只能用相关子查询或窗口函数做。所以我的建议是改写前先用两条SQL在测试数据上跑一遍保证结果集行数完全一致。这一步看似浪费时间但能避免最危险的线上数据错误。4. 改写实战四类典型改写的步骤与验证4.1 IN子查询转JOIN去重是关键坑拿前面订单的例子继续。原来的IN子查询改JOIN时最简单的方式是这样SELECT DISTINCT o.* FROM orders o JOIN user_coupons uc ON o.user_id uc.user_id WHERE uc.coupon_status 1;但这里有个隐患如果user_coupons表里同一个用户有多条coupon_status 1的优惠券JOIN会产生重复行。所以必须加DISTINCT。不过DISTINCT对MySQL来说也是一个负担如果重复率很高不如改成EXISTSSELECT o.* FROM orders o WHERE EXISTS ( SELECT 1 FROM user_coupons uc WHERE uc.user_id o.user_id AND uc.coupon_status 1 );EXISTS的语义是只要存在一条匹配即可停止扫描配合(user_id, coupon_status)联合索引性能往往比JOIN加DISTINCT更好尤其是外层结果集需要返回大量完整行时。我在实践中的验证方法是分别把三条SQL原始子查询、JOINDISTINCT、EXISTS都跑一遍对比结果行数和执行时间。通常EXISTS和JOIN都比原始子查询快但EXISTS写起来更符合原语义维护起来也更好懂。4.2 EXISTS与IN的取舍MySQL中EXISTS为何相对稳定很多人分不清EXISTS和IN在MySQL里的性能差异。根据实际经验我总结如下IN (SELECT ...)在结果集较小且外层表较大时可能表现不错但依赖优化器是否启用半连接。半连接开启时它会转成类似EXISTS的处理但有时也会触发物化。EXISTS相关子查询虽然理论上也是逐行执行但MySQL对EXISTS有专门的半连接优化常常能做到找到第一条就停。并且在子查询条件里如果用了等值连接和外层列优化器会把它转成EXISTS优化器可以处理的策略。更关键的是EXISTS不会因为子查询结果集中有NULL而改变行为NOT EXISTS也不会像NOT IN那样出现NULL导致全部为空的坑。所以我现在的默认规则是能用EXISTS表达的关系查询优先用EXISTS除非子查询结果集特别小且外层有索引才考虑IN。不过要注意这里的EXISTS指的是相关子查询也就是子查询的WHERE里带上了外层表的关联条件。如果你写EXISTS (SELECT ... FROM 表 WHERE 固定条件)它和不相关子查询一样也是先执行一次再判断没有逐行执行的性能优势了。4.3 派生表改写为JOIN把子查询提升为原始表派生表改写最常见的是聚合后再关联这种场景。改之前SELECT u.name, t.total_orders FROM users u JOIN ( SELECT user_id, COUNT(*) AS total_orders FROM orders GROUP BY user_id ) t ON u.id t.user_id WHERE t.total_orders 10;如果这个SQL慢降级方案是把聚合单独执行把结果存一张临时表并给user_id加索引CREATE TEMPORARY TABLE tmp_user_orders ( user_id INT PRIMARY KEY, total_orders INT, INDEX idx_user_id (user_id) ); INSERT INTO tmp_user_orders (user_id, total_orders) SELECT user_id, COUNT(*) FROM orders GROUP BY user_id; SELECT u.name, t.total_orders FROM users u JOIN tmp_user_orders t ON u.id t.user_id WHERE t.total_orders 10;这样做的好处是临时表能被索引覆盖而且如果同样的聚合后续还会被多条SQL复用业务代码里可以把这步抽成公共逻辑。代价是要多写几条SQL但换来的是执行计划的稳定性和可控性。对于MySQL这种优化器不够智能的数据库能让执行计划保持稳定本身就是一种优化。4.4 使用临时表手动物化的场景当优化器不靠谱的时候在8.0时代依然有些场景优化器处理不好比如IN子查询的结果集非常大上百万行物化临时表本身可能比直接JOIN更慢。子查询里有多层嵌套优化器选择了一个离谱的执行路径。子查询中包含UNION、LIMIT、窗口函数时改写空间受限。这时手动物化是最后的杀手锏。我通常的流程是先用EXPLAIN ANALYZE看实际执行时间和每一步的耗时占比。如果子查询物化步骤耗时占比超过60%并且临时表没有索引就手动把子查询结果导到临时表。临时表建好索引后再跑外层JOIN。最后对比改写前后整个业务接口的P95延迟确认收益。手动物化还有一个附加好处如果你在存储过程或业务代码里多次用到同一个中间结果集只用一次查询后续复用省掉的重复执行时间非常可观。5. 版本差异与优化器新特性5.7和8.0还该不该无脑抵制子查询5.1 5.7引入的优化半连接和派生表合并MySQL 5.6开始引入半连接5.7做了大量增强其中两项直接影响子查询性能半连接优化和派生表合并Derived Condition Pushdown。半连接优化可以让IN (SELECT ...)和EXISTS相关子查询被优化器改写成类似JOIN的半连接结构从而利用索引和外层表驱动。派生表合并则能把简单的派生表直接展开到外层查询里避免物化临时表。也就是说如果我用的是MySQL 5.7很多简单的子查询其实已经不再需要手工改写。我在5.7上测试过类似SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE ...)这种查询执行计划经常显示Using index和Using join buffer速度完全能接受。5.2 8.0的改进查询重写和Hash JoinMySQL 8.0的优化器改动更大。首先8.0.18开始支持Hash Join这意味着某些等值连接场景可以用哈希算法替代传统的嵌套循环对无索引连接有巨大提升。其次8.0重写了优化器的大量转换规则子查询被变换成JOIN的概率更高。但我建议不要过度乐观8.0的Hash Join并不总是能救子查询。它主要针对JOIN优化中无法使用索引的等值连接如果子查询被物化后依然没有索引Hash Join可能还是会选择全表扫描临时表只是比以前快一点。另外8.0的默认优化器开关里半连接和物化是同时存在的具体走哪条路还是要看统计信息。我见过不少MySQL 8.0就可以随便用子查询的言论实际测试后只能说绝大多数简单场景确实不用改但复杂子查询、多层嵌套、AND/OR混用的场景仍然会生成糟糕的执行计划。所以我现在的态度是先用子查询写但跑EXPLAIN发现长扫描或临时表再改写而不是一刀切禁止。5.3 我现在的判断标准何时继续用子查询基于两年的8.0生产环境经验我给自己定了一套子查询使用标准简单不相关子查询返回结果集很小几百行以内放心用IN不需要改。相关子查询筛选少量行且内层有唯一索引可以用EXISTS或IN性能可接受。相关子查询做聚合比较或者内层结果集很大直接改写为JOIN或临时表。FROM子查询只要没被合并并且后续需要外层连接尽量改写成临时表。窗口函数能替代的分组取前N这类子查询优先用ROW_NUMBER()比相关子查询简单得多。另外我特别想提醒生产环境执行计划不稳定是比子查询性能更隐蔽的问题。有时同一行SQL在数据分布变化后执行计划会突变子查询可能会导致原本走索引变成临时表全扫。遇到这种情况手写改写不如直接用优化器提示固定执行计划更直接。5.4 优化器提示与执行计划固定一条被低估的出路如果查询必须保持子查询的写法又担心性能可以尝试用optimizer_switch或者FORCE INDEX来干预执行路径。比如我遇到过一个低版本MySQLIN (SELECT ...)死活不启半连接我给外层表加了STRAIGHT_JOIN提示强制驱动顺序效果立竿见影。但这类手段必须配合业务SQL和索引设计一起调优否则换个环境可能就失效。从更宏观的角度看子查询慢不慢说到底不是能不能用的问题而是数据量、索引、优化器版本三者共同决定的问题。一个成熟的开发者在写SQL时应该习惯性查看执行计划而不是背下子查询禁用这种过于绝对的结论。最后再分享一个实操习惯我在测试环境跑任何涉及子查询改写的SQL之前都会先用一个统一的小数据量样本对比改写前后的结果集数量然后再到压测环境对比执行时间。这套流程已经从根上帮我避开过好几次优化完性能但结果错了的线上事故。改SQL不光是改语法更重要的是验证语义一致性以及理解优化器到底会怎么执行它。