MySQL子查询为什么慢？从执行计划到改写方案全解析

在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是ALL，Extra里还有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值时，存在一个语义层面的“隐形杀器”。

看这两条SQL：

SELECT * 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 can't 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 UPDATE：

UPDATE 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能给出明确答案。重点看这几个字段：

只要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 = 'materialization=off';

这类操作通常只建议在会话级别做，用来对比不同计划下的耗时，不适合直接全局改。全局改参数容易引发连锁反应：关掉materialization会迫使优化器走FirstMatch或DuplicateWeedout，某些子查询可能变快，另一些可能反而更慢。定位问题后，更合理的处理方式是优化索引或改写SQL，而不是靠调整优化器开关来迁就一条语句。

5.4 改写后的压测与行数回归验证

子查询改写成JOIN或EXISTS之后，必须做两项验证。第一项是执行计划对比，核心指标是rows估算总量，也就是把EXPLAIN输出里各节点rows乘以调用次数再求和，改后的估算总量要比改前低一个数量级以上才算成功。

第二项是结果集数量对比。JOIN改写最怕行数放大，我建议改写后分别执行一次原来的子查询版本和改写后的JOIN版本，对比返回行数是否一致。这一步虽然繁琐，但能避免“性能好了，数据错了”这种最尴尬的情况。

压测时不要只看一两次耗时，至少跑三轮取中位数。MySQL的查询受缓冲池命中率影响很大，第一次跑可能冷缓存，第二次可能热缓存，取中位数更有参考价值。如果条件允许，最好在接近生产数据的量级下压测，开发环境几万行数据和生产几千万行数据，执行计划可能会完全不同。

我个人在实际操作中的体会是：与其背“不用子查询”的结论，不如把它当成一个排查信号。看到SELECT列表带子查询，看到EXPLAIN里的DEPENDENT SUBQUERY，看到NOT IN配合可能出现NULL数据的列，停下脚步跑一次EXPLAIN，多花两分钟验证执行计划，能省下后续调优的几小时，也能避免那些改了之后数据对不齐的尴尬事故。真正的高手，不是从不写子查询，而是知道每一种写法在什么版本、什么数据量下会变成什么样。