做 SQL 优化久了你会发现MySQL 子查询是一个很有意思的战场。很多人一听到“嵌套 SQL”就头皮发麻觉得子查询又慢又难调其实子查询本身不是洪水猛兽关键是你得知道它到底怎么执行、什么时候慢、为什么慢。这篇文章我会围绕 MySQL 子查询的常见写法、慢查询成因、执行计划判读、索引设计以及几个我实际踩过的坑展开适合正在被慢 SQL 困扰的开发、DBA 和数据运营同学参考。如果你只是想让某个嵌套查询跑得快一点这里面的思路可以直接抄作业。我先把一件事说清楚子查询不是不能用而是不能瞎用。同一个业务需求有人写出来要跑十几秒有人改写 Join 之后不到一百毫秒差异往往不在于数据库多强而在于写法和索引是否匹配。下面我会从执行原理讲起再给几类高频场景的改写方案最后附上我自己排查慢查询时的判断流程。1. 子查询到底是什么先看清它是谁1.1 子查询的定义与常见分类子查询就是嵌套在另一个查询里的完整 SELECT它可以出现在 WHERE、FROM、SELECT、HAVING、JOIN ON 甚至 UPDATE 和 DELETE 的条件里。从返回结果维度来分子查询可以分成三类标量子查询返回单行单列行子查询返回单行多列表子查询返回多行多列。从是否依赖外层查询来分又可以分成非相关子查询和相关子查询。非相关子查询比较好理解它可以独立执行比如“查出所有类型为 1 的分类”这种先跑内层再把结果给外层用。相关子查询则是指内层语句引用了外层查询的字段比如“查出每个用户最近一次下单金额”这类内层查询没法单独跑它必须知道外层当前处理的是哪一行。拿生活场景来类比非相关子查询就像你先去后厨把今天能用的食材清单打印出来然后照着菜单配菜相关子查询则是每点一道菜后厨都要跑去冷藏室翻一遍食材外层有多少行内层就要执行多少次。这个类比虽然粗糙但能解释大部分性能问题的根源相关子查询一旦外层结果集很大内层被反复执行的代价会非常恐怖。1.2 为什么慢执行计划和优化器的视角MySQL 处理子查询时并不是简单地把“先算内层、再算外层”当成固定流程。优化器会根据版本、统计信息、索引情况在多种策略里选一种包括半连接优化、物化、改写成 EXISTS、派生表合并等。看到这里你就明白了子查询慢不慢很多时候取决于优化器选对了没有以及我们有没有给它足够的索引信息。执行计划里如果出现 DEPENDENT SUBQUERY就意味着这是一个相关子查询外层每处理一行内层都会重新执行一次。这种执行模型的时间复杂度接近外层行数乘以内层扫描行数一旦两边数据量都上来慢是必然的。如果出现 MATERIALIZED说明优化器把子查询结果物化成了临时表临时表如果没有索引外层匹配的时候就得全表扫描同样可能很慢。还有一种常见情况是子查询里有 ORDER BY 和 LIMIT比如“先排个序再取前 10 条”这种子查询往往会被物化而且物化后的临时表不一定能用上我们精心设计的索引。所以不要一看到慢 SQL 就问“子查询是不是不能用”正确的思路是先看执行计划搞清楚这个子查询到底是被当作独立查询执行、被物化成临时表还是被逐行执行然后再决定怎么改。2. 实战场景五种高频子查询的改写与优化2.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 WHERE u.status 1;这个 SQL 结构很清晰但性能上有一个隐患当 users 表返回 1 万行时子查询就可能执行 1 万次。如果 orders 表的 user_id 上没有索引每次执行还要扫全表100 万行的订单表就会被扫 1 万遍这种慢查询翻车只是时间问题。即使加了索引1 万次索引查找也未必比一次聚合划算。我常用的改写方案是先做分组聚合再通过 JOIN 关联SELECT u.id, u.name, t.max_amount FROM users u LEFT JOIN ( SELECT o.user_id, MAX(o.amount) AS max_amount FROM orders o GROUP BY o.user_id ) t ON t.user_id u.id WHERE u.status 1;这样内层只对 orders 表扫描一次算好每个用户的最高金额外层再关联。大多数场景下这个改写能让执行时间从秒级降到几十毫秒。但我也要说清楚标量子查询并不是绝对不能碰如果外层经过 WHERE 过滤后只剩下十几行而且关联字段有索引直接保留也完全没问题。优化没有银弹一切以 EXPLAIN 和实际耗时为标准。2.2 IN 子查询改 JOIN 还是 EXISTSIN 子查询应该是我见过最常见的嵌套查询类型。比如查出所有属于启用分类的商品SELECT * FROM product p WHERE category_id IN ( SELECT id FROM category WHERE type 1 );很多老文章会直接告诉你“IN 慢改成 EXISTS”但这个结论在 MySQL 5.7 和 8.0 里已经不完全准确。MySQL 对非相关 IN 子查询做半连接优化时可能选择物化子查询结果也可能把它改写成半连接执行甚至自动去重。因此实际性能好不好必须用 EXPLAIN 看。如果 EXPLAIN 显示内层物化临时表很大、外层匹配时扫描行数很多我会优先尝试 EXISTS 写法SELECT p.* FROM product p WHERE EXISTS ( SELECT 1 FROM category c WHERE c.id p.category_id AND c.type 1 );需要注意IN 和 EXISTS 的执行路径不同IN 的子查询结果会被去重而 EXISTS 是逐行检查组内是否至少存在一条记录。正常情况下不会造成结果集差异但如果你在 GROUP BY、聚合或者多表 JOIN 的场景里改写一定要先验证返回行数是否一致。还有个更稳的写法是把子查询改成 JOIN DISTINCT。不过这里有个大坑如果外层表和被关联的子查询结果存在一对多关系直接 JOIN 会让结果行数变多所以要么在子查询里做 DISTINCT要么在外层加 DISTINCT。我给你的建议是先看执行计划再决定改法千万不要凭感觉。2.3 EXISTS 与 NOT EXISTS相关子查询的正确姿势EXISTS 子查询经常被写成这样判断某条记录在另一个表里是否有对应记录。比如查所有有过下单记录的用户SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );这里我习惯在 EXISTS 里写 SELECT 1而不是 SELECT *。从 MySQL 优化器角度讲写什么其实差别不大因为 EXISTS 只关心“有没有行”不关心具体投影列但 SELECT 1 让阅读者一眼就明白意图算是一种好习惯。真正需要注意的是 NOT EXISTS 和 NOT IN 的区别。NOT IN 子查询里如果结果集中包含 NULL 值外层查询会直接返回空结果这点非常容易踩坑。比如SELECT * FROM users u WHERE u.id NOT IN ( SELECT user_id FROM orders WHERE user_id IS NOT NULL );如果子查询返回的 user_id 里有 NULLNOT IN 的语义会变成“既不是某个值也不是 NULL”最终什么也查不到。改成 NOT EXISTS 就安全很多SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );当然也可以改成 LEFT JOIN IS NULL 的写法SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.id IS NULL;这种写法在数据量大、关联字段有索引时通常很快但我提醒一句如果 orders 表里同一个 user_id 有多条记录LEFT JOIN 会产生重复行最终结果集可能会被放大。相比之下NOT EXISTS 不会出现这个问题所以当我没法确定关联表的唯一性时会优先选择 NOT EXISTS。2.4 FROM 子查询派生表和物化的陷阱FROM 后面的子查询在 MySQL 里也叫派生表。比如我想统计平均工资大于 5000 的部门SELECT dept_id, avg_salary FROM ( SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id ) t WHERE avg_salary 5000;这种写法逻辑非常直观但它有个性能隐患MySQL 早期版本里派生表都会先物化成临时表临时表上不会有我们原来建的索引外层查询再从这里筛选时只能全表扫描。虽然 8.0 对简单派生表会做合并和条件下推但包含聚合函数、窗口函数、LIMIT 的派生表仍然可能物化。我的优化思路是尽量把外层过滤条件压到内层去。比如上面的例子可以先在 emp 表里做部门聚合然后用 HAVING 过滤SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id HAVING AVG(salary) 5000;这样既省掉了派生表物化又能让 MySQL 走常规的聚合执行路径。如果业务逻辑复杂到必须用派生表那你就要特别留意执行计划里是否出现 DERIVED以及它的 rows 是不是很夸张。MySQL 8.0 的优化器比我早期用的 5.6 强很多但也有前提统计信息要准确索引要合理否则再强的优化器也救不了不合理的写法。2.5 子查询里的排序和 LIMIT别让排序被“吃掉”有些同学喜欢在子查询里先排序再取前几条然后拿来和主表 IN 或 JOIN比如“取最近下单的 10 个商品分类”。这种 SQL 经常出问题因为 MySQL 优化器不保证子查询里的 ORDER BY 一定按你期望的方式生效尤其是旧版本里内层 ORDER BY 可能被忽略掉。一个经典案例是查每个分类下最新发布的商品。比较偷懒的写法是在子查询里对每个分类取最大 ID再关联主表SELECT * FROM product WHERE id IN ( SELECT MAX(id) FROM product GROUP BY category_id ) ORDER BY id DESC;这样写其实没问题因为聚合函数 MAX 的结果是稳定的。但如果是下面这种写法问题就来了SELECT * FROM product WHERE id IN ( SELECT id FROM product WHERE category_id IN (1, 2, 3) ORDER BY create_time DESC LIMIT 10 );内层到底取的是“按 create_time 排序后的前 10 条”还是“任意 10 条”在部分场景下是不确定的。MySQL 8.0 对物化子查询里的 ORDER BY 会保留但排序经常带来 filesort如果 create_time 上没有索引性能依然很差。更稳妥的方案是改用窗口函数MySQL 8.0 以上版本可以直接这样写SELECT * FROM ( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY create_time DESC) AS rn FROM product p ) t WHERE rn 1;这种写法虽然看起来更复杂但语义非常清晰每个分类按 create_time 排序后取第一名。窗口函数在 MySQL 8.0 里已经非常成熟配合合适索引比多层嵌套子查询靠谱得多。如果你的版本还停留在 5.7那就要考虑用 JOIN 自己关联或者接受“先取一部分再处理”的方案。3. 从执行计划到索引设计让子查询跑快的底层逻辑3.1 看懂 EXPLAIN 里的关键字段改 SQL 之前先看执行计划这是我给自己定的规矩。EXPLAIN 里几个关键字段你必须养成条件反射id 表示执行顺序select_type 告诉我们子查询是普通 SUBQUERY、DEPENDENT SUBQUERY、DERIVED 还是 MATERIALIZEDtype 则反映访问类型。type 字段里常见级别从好到差大致是 system const eq_ref ref range index ALL。如果看到 ALL基本意味着全表扫描这时候要想着加索引或者改写关联逻辑。rows 字段是优化器估算的扫描行数有时和实际差很远但至少能用来判断两个方案哪个量级更小。Extra 里如果出现 Using temporary 或 Using filesort说明这条 SQL 可能产生了临时表或额外排序通常需要警惕。MySQL 8.0.18 及以上版本还提供了 EXPLAIN ANALYZE它比普通 EXPLAIN 更进一步直接输出每一步实际执行时间、实际行数等信息。我一般在排查疑难慢查询时会用它用法也很简单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 WHERE u.status 1;执行后会看到每个算子的耗时和返回行数比干猜要靠谱得多。不过 EXPLAIN ANALYZE 会真正执行 SQL生产环境大查询慎用建议先在小数据量或从库上跑。3.2 子查询命中索引覆盖索引怎么用子查询慢很多时候不是 MySQL 不会优化而是缺索引。拿最典型的关联查询来说子查询里的关联字段必须建索引。比如SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status 1 );orders 表的 user_id 和 status 字段至少要有一个组合索引。很多 DBA 会建议建 (user_id, status)这样无论是根据用户找订单还是判断某个用户是否存在有效订单都能走索引。关于覆盖索引我再多说一句。如果子查询只关心某几个字段比如 COUNT 或 MAX二级索引往往就能覆盖不需要回表。你可以这样验证在 EXPLAIN 的 Extra 列看到 Using index说明查询完全在索引里完成这是很理想的状态。索引字段的类型一致性也很重要。比如 users.id 是 INTorders.user_id 是 VARCHAR那关联查询时 MySQL 会做隐式类型转换很可能导致索引失效。我见过不少线上慢查询排查到最后根本不是 SQL 写错而是两个表的关联字段类型不一致这种问题建再多索引都白搭。所以建表阶段就要统一关联字段的类型和字符集别等出问题了才后悔。3.3 临时表与物化Optimizer 的取舍MySQL 优化器在决定某个子查询怎么执行时会对比物化临时表和逐行执行哪个更便宜。物化方案会把子查询结果先存到临时表里临时表可以由 MySQL 自动加索引也可以不加直接全表扫。半连接物化时MySQL 通常会为物化表创建隐藏索引来加速查找但如果是普通派生表物化外层可能很难利用到我们手工建的索引。想弄清楚某个子查询到底走了哪条路可以打开优化器追踪SET optimizer_trace enabledon; SELECT ... 你的慢查询 ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;这个方法能看到优化器比较了哪些方案、最终选了哪个是我调参时的常用手段。但我要提醒你不要轻易去关掉某个优化器特性比如直接 SET GLOBAL optimizer_switch materializationoff这种操作影响的是整个实例的所有查询测试环境玩玩可以生产环境极容易翻车。升级 MySQL 版本有时比改 SQL 更有效。同样的子查询在 5.6 和 8.0 里的执行路径可能完全不同。如果公司有条件升级我强烈建议把子查询相关慢 SQL 在 8.0 上重新跑一遍经常能发现惊喜。4. 常见问题与排查技巧实录4.1 慢查询定位的通用套路遇到慢查询我一般按下面的顺序排查。第一步开启慢查询日志把 long_query_time 设置成 1 秒或更短先找出哪些 SQL 是真正的痛点。第二步单独执行这条 SQL记录返回行数和耗时然后立刻看 EXPLAIN。第三步对比执行计划和表结构确认是不是缺索引、字段类型不一致或者驱动表选错了。第四步尝试至少两种改写方案分别看执行计划和实际耗时选优。有时候统计信息过期也会导致优化器选错执行计划我遇到过一次明明 SQL 很简单但优化器就是不走索引后来 ANALYZE TABLE 之后瞬间恢复正常。所以遇到“莫名其妙慢”的 SQL先 ANALYZE TABLE再动手改写。慢查询日志的开启方式很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这只是全局配置重启后会丢失生产环境建议写入配置文件。另外慢查询日志要记得定期清理否则日志文件越来越大反而拖累数据库。4.2 五个容易踩的坑我把这几年遇到的高频问题整理了一下方便你对照排查。现象原因解法NOT IN 查不到任何结果子查询结果里包含 NULLNOT IN 整体返回空集改成 NOT EXISTS 或 LEFT JOIN ... IS NULL返回行数变多IN 改写 JOIN 时子查询结果有重复记录子查询加 DISTINCT或外层加 GROUP BY子查询里 ORDER BY 不生效优化器忽略了内层排序或物化顺序和你预期不同用窗口函数或把排序和 LIMIT 放到外层派生表扫描特别慢FROM 子查询被物化临时表没有可用索引尽量把过滤条件下推或把派生表改写成 JOIN相同数据量但忽快忽慢统计信息不准或优化器选错了执行路径ANALYZE TABLE必要时用 optimizer_trace 查看决策这些坑有一个共同点只看 SQL 文本很难发现问题必须结合执行计划和返回结果判断。我见过太多同学改完 SQL 不验证行数上线后才发现结果集对不上这种事故比慢查询本身更麻烦。所以任何改写我都要求执行前后对比两条 SQL 的结果行数和关键聚合值。4.3 子查询安全改写的决策清单如果你不想每次都被慢查询牵着鼻子走可以把下面这套判断流程内化成习惯子查询结果集很小比如几百行以内保留 IN 或原始写法问题不大。外层经过过滤后行数很少哪怕有相关子查询也可以接受。子查询结果集大且外层表也大优先改成 JOIN 聚合或 EXISTS。遇到 NOT IN 且子查询可能带 NULL无条件改 NOT EXISTS。子查询里出现 ORDER BY 和 LIMIT先确认排序是否被保留能不用就不用。所有改写都要跑一次 EXPLAIN确认 type 不是 ALL、Extra 里没有明显的 Using filesort。在线修改前先在测试环境比对时间、行数和资源消耗。这套清单不是银弹但它能避免你陷入“看到子查询就改 JOIN”的另一个极端。子查询和 JOIN 没有绝对的好坏只有是否适合当前的数据分布和执行环境。5. 关于子查询优化的几点体会我在实际项目里见过太多因为“过度优化”把 SQL 改得面目全非的案例。有些子查询本身只有几十毫秒非要多层嵌套改 JOIN 改出重复行最后还得加班排查数据问题得不偿失。我的经验是先把慢查询日志拉出来按耗时排序只优化真正影响业务的那几条不要为了整洁而重构所有 SQL。还有一次让我印象特别深的案例一条带相关子查询的报表 SQL 跑了 12 秒我一开始以为是子查询问题结果深入排查后发现是关联字段上的一张索引被业务误删了。重新建索引后SQL 直接掉到 0.3 秒。这个经历让我更加坚信顺序很重要先看索引、再看执行计划、最后才动 SQL 结构。很多时候慢的根本原因不是 SQL 写法而是数据结构或索引设计出了问题。如果你正在用 MySQL 5.7迁移到 8.0 之前建议对这些子查询 SQL 做一轮回归测试因为优化器策略变化可能带来完全不同的执行路径。以后遇到让人头皮发麻的嵌套查询心态放平按本文这套流程走一遍大概率能稳稳地把查询效率提上来。