MySQL子查询性能优化实战：从线上CPU事故到JOIN改写-育师

1. 一次让我对子查询产生警惕的线上故障

1.1 故障背景：一条不算复杂的SQL把数据库CPU打满

先讲一件真事。前两年我负责的一个电商系统出了一次线上事故，用户反馈订单列表打开极慢，后台监控MySQL CPU直接飙到95%以上。我抓出慢查询日志，罪魁祸首是一条看起来完全"人畜无害"的SQL：

SELECT * 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的结果大概是这样的：

表	type	key	rows	Extra
orders	ALL	主键	45万	Using where
user_coupons	ALL	NULL	3万	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来说也是一个负担，如果重复率很高，不如改成EXISTS：

SELECT 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（原始子查询、JOIN+DISTINCT、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 Join

MySQL 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来干预执行路径。比如我遇到过一个低版本MySQL，IN (SELECT ...)死活不启半连接，我给外层表加了STRAIGHT_JOIN提示强制驱动顺序，效果立竿见影。但这类手段必须配合业务SQL和索引设计一起调优，否则换个环境可能就失效。

从更宏观的角度看，子查询慢不慢，说到底不是"能不能用"的问题，而是数据量、索引、优化器版本三者共同决定的问题。一个成熟的开发者在写SQL时应该习惯性查看执行计划，而不是背下"子查询禁用"这种过于绝对的结论。

最后再分享一个实操习惯：我在测试环境跑任何涉及子查询改写的SQL之前，都会先用一个统一的小数据量样本对比改写前后的结果集数量，然后再到压测环境对比执行时间。这套流程已经从根上帮我避开过好几次"优化完性能但结果错了"的线上事故。改SQL不光是改语法，更重要的是验证语义一致性，以及理解优化器到底会怎么执行它。