做后端开发这几年和 MySQL 子查询较劲的次数已经数不过来了。刚开始学 SQL 的时候很喜欢用子查询去表达业务逻辑一条 SELECT 里套着多层 IN、EXISTS、标量子查询看着逻辑特别清晰。直到某个系统上线半年后数据从几万行涨到数百万行那条“逻辑清晰”的查询从几百毫秒直接飙到十几秒才真正开始研究 MySQL 不使用子查询的原因。这篇文章不只聊“子查询慢”这个结论而是把物化临时表、相关子查询逐行执行、优化器改写策略这几件事拆开讲清楚。适合正在被慢查询困扰、想系统梳理 SQL 优化知识或者准备面试时被问到“为什么尽量不用子查询”的开发者。1. 先别急着下结论子查询的“罪”与“冤”1.1 一条流传多年的“行业经验”你先回忆一下是不是在很多团队的 SQL 规范里都见过这么一条“禁止使用子查询一律改 JOIN。”我见过最夸张的例子是同事把一个三层的嵌套子查询原封不动搬到了另一个数据量翻倍的表上查询直接跑出十几秒。但如果你真去问他为什么不能这么写他往往也只能说“大家都说子查询慢”。这条经验不是没道理只是不能只记结论、不讲条件。如果你把 MySQL 5.7、8.0 上的一个小表子查询也随手改写成 JOIN结果很可能是两个查询执行计划长得一模一样白折腾一场。所以这篇文章首先要做的是把“子查询慢”这个模糊结论落到几个具体机制上让你以后能自己判断这个子查询到底该不该改、该怎么改。1.2 MySQL 子查询优化能力的时间线MySQL 子查询的问题很大程度上是历史包袱造成的。子查询在 MySQL 4.1 才引入当时的实现非常原始很多查询会被优化器改写成一个极其糟糕的执行计划。之后每个大版本都在补课补课的进度直接决定了你今天该不该“畏惧”子查询。4.1 到 5.5 这段时间子查询只是“能用”优化器几乎没有成本决策能力。很多场景下IN 会被固定改写成 EXISTS或者把子查询结果物化成一张没有索引的临时表。5.6 引入了半连接优化semi-join针对 IN/EXISTS 的等值场景提供了 FirstMatch、LooseScan、DuplicateWeedout、物化查找等策略派生表也开始支持合并。5.7 增加了派生表条件下推物化临时表的处理进一步改善。8.0 加入了哈希连接8.0.18和 Lateral 派生表窗口函数落地很多过去只能靠相关子查询实现的分析需求有了更优解。既然 8.0 已经这么强了为什么“不要用子查询”的说法还这么流行原因有两个。一是存量系统大把还跑在 5.6、5.7 上生产环境和理想版本之间永远有时间差二是即便在 8.0 里只要子查询是相关子查询、或者子查询里带了 LIMIT、聚合、UNION优化器照样可能退化成逐行执行或大临时表物化。换句话说这条经验在今天仍然成立只是需要你学会判断什么时候会被坑。2. 三个核心原因物化、相关子查询、优化器改写失效2.1 物化临时表的隐形代价第一个核心原因是子查询结果经常要被物化Materialization。所谓物化就是优化器把子查询先执行一遍把结果固化到一张临时表里再拿这张临时表去参与外层查询。物化本身不是问题问题出在历史版本里这张临时表往往没有索引。我举个例子外层表有十万行子查询物化出五万行的一张临时表。外层每一行都要去这张无索引临时表里做匹配代价就是十万乘五万的量级性能直接爆炸。即便到了 5.6半连接的物化策略会给临时表建立索引可如果你的子查询不在半连接适用范围内依然可能生成一张无索引的临时表。更隐蔽的是临时表一旦超过内存阈值就会落盘。MySQL 对临时表内存的控制主要看 tmp_table_size 和 max_heap_table_size超过之后会从 MEMORY 引擎切换到磁盘临时表操作从“内存数组里找”变成“文件读 I/O”。另外 MEMORY 引擎本身不支持 TEXT 和 BLOB 列子查询结果里只要带这种大字段直接就没法在内存里物化。所以看到子查询时我第一反应不是骂它而是问三个问题子查询结果集有多大会不会被物化物化出来的临时表有没有索引这三个问题基本决定了你会不会被坑。2.2 相关子查询每次外层行都执行一遍第二个核心原因也是性能危害最大的一种相关子查询Dependent Subquery。所谓相关子查询就是子查询内部引用了外层表的字段。你写的明明是“查询这批订单”实际执行却是“外层每一行都拿着这一行的值去跑一遍子查询”。这个模式就像送快递不按小区集中派送每一个件都单独回一次仓库取地址回来送一单然后再回仓库取下一单的地址。外层表如果扫描出了五十万行子查询就会被执行五十万次。如果子查询里面的表有合适索引每次执行还能控制在微秒级别如果没索引每次都是一次全表扫描五十万乘几百万的扫描量根本不是查询是灾难。这和 ORM 里的 N1 查询是同一个问题只不过发生在数据库内部。EXPLAIN 里如果出现 DEPENDENT SUBQUERY我基本直接锁定它是慢查询头号嫌疑。典型的例子是SELECT o.id, (SELECT COUNT(*) FROM order_items i WHERE i.order_id o.id) FROM orders o。表面看只扫了一张订单表实际上每个订单都要到明细表里做一次聚合。订单一多总耗时就是次方级别增长。2.3 优化器改写不成IN 被当成 EXISTS 的“历史事故”第三个核心原因是优化器的改写策略并不总是可靠。MySQL 早期对IN (SELECT ...)的处理很粗暴很多场景会直接改写成EXISTS。这个改写本身不是错的但方向没选对就会要命。比如子查询的内表数据量远大于外表把它改成逐行探测的 EXISTS等于把本来一次物化能解决的事变成了逐行执行反过来如果内表小且有索引改成 EXISTS 反而更优。当时 MySQL 没有足够的成本估算能力只能靠固定规则或粗糙的启发式决定改写方向所以经常“好心办坏事”。5.6 引入半连接优化后等值的 IN/EXISTS 场景总算有了成本决策但也只覆盖“简单模式”。一旦子查询里带了 LIMIT、UNION、聚合函数、GROUP BY或者关联条件不是等值半连接就会失效重新退回物化或逐行执行的老路。还有一个比性能更可怕的逻辑坑NOT IN配合可空字段。比如WHERE id NOT IN (SELECT b_id FROM b)只要子查询结果里出现任何一个 NULL整个查询结果就会变成空集。原因是 SQL 三值逻辑里“不等于 NULL”的结果是 UNKNOWNWHERE 只保留 TRUE。数据量小的时候这条 NULL 可能一直不存在测试完全通过等线上脏数据一进来查询结果直接悄悄错了。3. 各种子查询写法的真实表现3.1 IN (SELECT...) 的陷阱与半连接兜底IN (SELECT ...)是日常写得最多的子查询用法也是最容易被慢查询命中、也最容易在 5.6 被优化器救回来的写法。5.6 之后优化器可以把这类查询转成半连接执行。半连接的意思是外层表每行只要和内层结果匹配上就停止不需要管匹配了几次也不需要把内层结果完整体现在最终结果里。常见策略包括 FirstMatch、LooseScan、DuplicateWeedout、物化后按索引探测等。这套机制比“先把 IN 结果算出来再逐行判断”高效得多。但要注意半连接不是万能钥匙。子查询包含聚合函数、LIMIT、UNION、非等值关联时优化器会放弃半连接。比如WHERE order_id IN (SELECT order_id FROM order_items WHERE quantity 5 LIMIT 100)不在半连接适用范围内优化器只能物化整个子查询结果。很多人的本意是“我只想要前 100 个”但执行计划根本不是这么回事——如果 order_items 在 quantity 5 下筛出几十万行LIMIT 100 并不一定能截断物化的规模该物化的还是会物化。3.2 EXISTS 什么时候真正快EXISTS 通常被当成 IN 的替代品来讨论但它和 IN 的性能特征不完全一样。EXISTS 天生就是半连接语义只要子查询返回至少一行外层这一行就判定通过。在 5.6 之前的年代EXISTS 往往比 IN 靠谱因为它和内层索引配合得更好不需要物化整张临时表。但相关 EXISTS 在外层行数很大、内表又不满足索引条件时同样会变成逐行执行。判断 EXISTS 能不能快核心看两件事外层表有没有被过滤条件缩小范围内层的关联列有没有索引。比如WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id o.id)只要 order_items(order_id) 上有索引并且订单表已经通过时间、状态条件大幅缩小了范围这个 EXISTS 就高效。相反如果外层两张百万级表不加过滤就套相关 EXISTS效果就是“全表扫一次再加几百万次子查询”和全表扫描两次差不多。3.3 FROM 子句里的派生表把子查询放在 FROM 里就是派生表。5.6 之前派生表基本是直接物化没有任何条件下推5.6 开始如果派生表里没有聚合、分页、UNION 等破坏性操作优化器可以把派生表和外层查询合并DERIVED_MERGE让过滤条件直接打到基表的索引上。5.7 又增加了条件下推能力外层 WHERE 的过滤条件可以落到派生表内部执行进一步减少参与 JOIN 前的数据量。所以现在派生表本身不一定慢真正危险的是两类场景一类是派生表里带着 GROUP BY 大分组物化出来的临时表没有适合外层 JOIN 的索引外层表又很大另一类是派生表 SELECT 了一堆不用的列整张内表被完整物化内存和 IO 双双被打满。我一般会先看 EXPLAIN 里这个派生表显示的是 DERIVED 还是 MERGED。MERGED 说明已经合并成功问题不大DERIVED 说明它要被物化就得认真掂量物化行数。3.4 SELECT 列表和 WHERE 中的标量子查询标量子查询是在单个值的位置上写的子查询比如 SELECT 列表里的(SELECT MAX(product_price) FROM products p WHERE p.order_id o.id)。这类写法最大的问题是它几乎必然是相关子查询。外层有多少行符合条件它就执行多少次。除非外层结果集已经被过滤到很小否则这个写法就是 N1 查询的 SQL 版本。不过有个隐蔽细节标量子查询如果出现在 WHERE 里做等值比较比如WHERE total (SELECT MAX(score) FROM record WHERE user_id 1)MySQL 能在优化阶段把它当成常量处理只执行一次。但如果放到 SELECT 列表里跟着每一行跑就没有这个待遇。所以我的规范是SELECT 列表里不允许出现任何不带唯一性保证的标量子查询能用 JOIN GROUP BY 或窗口函数替代的一律改写。4. 改写方案从子查询到 JOIN 的迁移路线4.1 首选JOIN 预处理聚合写 JOIN 不是为了语法好看而是为了让优化器把更多条件、索引综合进同一个执行计划。子查询往往会切断优化器的视野JOIN 则把多张表的关系摊在同一个连接计划里优化器可以用 join buffer、索引嵌套循环、哈希连接等方式整体决策。最典型的替代是把 SELECT 列表里的相关聚合子查询改写成派生表再 JOIN-- 原写法每个订单都要去明细表聚合一次 SELECT o.id, (SELECT SUM(i.amount) FROM order_items i WHERE i.order_id o.id) AS total FROM orders o; -- 改写后明细表只扫描一次完成分组聚合 SELECT o.id, agg.total FROM orders o LEFT JOIN ( SELECT order_id, SUM(amount) AS total FROM order_items GROUP BY order_id ) agg ON agg.order_id o.id;改写之后order_items 只需被扫描一次完成分组而不是每个订单各扫一遍。原写法中没买任何商品的订单返回 NULLLEFT JOIN 保持了这种语义如果业务确实不需要空订单改成 INNER JOIN 还能顺便把大部分行过滤掉。4.2 NOT IN 用 NOT EXISTS 顶掉NOT IN 的问题前面已经说过一个 NULL 就能让整个查询返回空集。为了安全和性能我建议 NOT IN 一律改成 NOT EXISTS-- 危险且可能超慢 SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE status CANCELLED); -- 安全且更稳 SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status CANCELLED );这样既避开了 NULL 三值逻辑的坑也让优化器更容易利用索引。orders(user_id, status) 建好之后NOT EXISTS 的探测就是一次极快的索引返回。如果你的业务必须保留 NOT IN 的原意也要记得在子查询里加WHERE user_id IS NOT NULL至少在逻辑上先堵住 NULL。4.3 派生表 手动临时表兜底有些子查询本身的逻辑很合理只是结果集太大、外层 JOIN 又没有合适索引。这种情况我建议不要继续硬怼 SQL而是把中间结果落成真正的临时表再显式建索引CREATE TEMPORARY TABLE tmp_digital_items ( order_id BIGINT NOT NULL, PRIMARY KEY (order_id) ) ENGINEInnoDB; INSERT INTO tmp_digital_items (order_id) SELECT order_id FROM order_items oi JOIN products p ON p.id oi.product_id WHERE p.category 数码;然后外层直接 JOIN 这张临时表。这个操作把“优化器不理解的复杂子查询”拆成了“两个能独立优化的步骤”索引完全由自己控制临时表也不会被莫名物化成无索引状态。生产环境如果担心临时表内存开销可以直接落成普通中间表再加个清理任务定期维护。4.4 8.0 窗口函数把相关子查询“拍死”MySQL 8.0 最值得单独讲的替代方案是窗口函数。很多“分组内取 Top N”“分组内累计求和”“找每组最大值对应的行”这些经典相关子查询场景窗口函数都能用更清晰、对优化器更友好的方式完成。举个常见例子查出每个分类价格最高的商品SELECT category_id, product_name, price FROM ( SELECT category_id, product_name, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn FROM products ) t WHERE t.rn 1;底层虽然也要做一次分组排序但执行计划的可预测性好得多不再对外层行数敏感。注意这个能力只有 8.0 起才有生产库是 5.7 的话就回到 JOIN 派生表那套方案。5. 实操案例一次从 14 秒到 0.6 秒的慢查询治理5.1 原始查询与执行计划解读用一个我处理过的模拟场景来说明。某订单系统里 orders 表约 220 万行order_items 约 800 万行products 约 40 万行。业务要求统计“2023 年 6 月付款成功、且从华东仓发货的订单以及每个订单的商品数量和某品牌最高单价”。初始版本叠了三个子查询SELECT o.order_id, o.customer_id, o.paid_amount, (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id o.order_id) AS item_cnt, (SELECT MAX(oi.amount) FROM order_items oi JOIN products p ON p.id oi.product_id WHERE oi.order_id o.order_id AND p.brand 某品牌) AS max_brand_amount FROM orders o WHERE o.pay_time 2023-06-01 00:00:00 AND o.pay_time 2023-07-01 00:00:00 AND o.status 2 AND o.order_id IN ( SELECT oi3.order_id FROM order_items oi3 WHERE oi3.warehouse 华东仓 );这条 SQL 单次跑完约 14 秒。用 EXPLAIN 看orders 走了一次全表扫描过滤后约 8.6 万行后面跟着两个 DEPENDENT SUBQUERY还有一个 MATERIALIZED 的 IN 子查询。问题很清楚。两个相关子查询8.6 万行外层order_items 虽然有 order_id 索引但第二个子查询还要再 JOIN products每行都在 800 万行明细表和 40 万行商品表里反复探测。IN 子查询先筛华东仓的 order_items物化出一张很大的临时表再和目标订单做 IN 判断。叠加之后整个查询时间基本都耗在“逐行跑子查询”和“大临时表物化”上。5.2 逐步改写聚合下推、JOIN 替换、EXISTS 兜底改法分三步。第一步把两个相关子查询合并成一个订单级聚合派生表。这样 order_items 只需要扫一次所有 COUNT、MAX 都在 GROUP BY order_id 这一层完成SELECT oi.order_id, COUNT(*) AS item_cnt, MAX(CASE WHEN p.brand 某品牌 THEN oi.amount END) AS max_brand_amount FROM order_items oi JOIN products p ON p.id oi.product_id WHERE oi.order_id IN ( SELECT order_id FROM orders WHERE pay_time 2023-06-01 00:00:00 AND pay_time 2023-07-01 00:00:00 AND status 2 ) GROUP BY oi.order_id第二步把 IN 子查询改成 EXISTS 探测。因为外层订单已经被时间 状态条件过滤到 8.6 万行华东仓的匹配用 order_items(warehouse, order_id) 之类的索引可以快速探测逐行成本可以接受SELECT o.order_id, o.customer_id, o.paid_amount, agg.item_cnt, agg.max_brand_amount FROM orders o LEFT JOIN ( SELECT oi.order_id, COUNT(*) AS item_cnt, MAX(CASE WHEN p.brand 某品牌 THEN oi.amount END) AS max_brand_amount FROM order_items oi JOIN products p ON p.id oi.product_id WHERE oi.order_id IN ( SELECT order_id FROM orders WHERE pay_time 2023-06-01 00:00:00 AND pay_time 2023-07-01 00:00:00 AND status 2 ) GROUP BY oi.order_id ) agg ON agg.order_id o.order_id WHERE o.pay_time 2023-06-01 00:00:00 AND o.pay_time 2023-07-01 00:00:00 AND o.status 2 AND EXISTS ( SELECT 1 FROM order_items oi4 WHERE oi4.order_id o.order_id AND oi4.warehouse 华东仓 );第三步确认索引到位。orders(pay_time, status) 用于范围过滤order_items(order_id) 服务聚合order_items(warehouse, order_id) 服务 EXISTS 探测products(id) 走主键就够了。这一步不需要改 SQL但少了任何一个索引执行计划可能立刻回到逐行扫描状态。5.3 前后对比与验证改写后在同样的数据范围内查询从 14 秒降到 0.6 秒左右。EXPLAIN 里不再出现 DEPENDENT SUBQUERY剩下 PRIMARY DERIVED 加上几次索引查找。这轮优化的核心并不是“少写了子查询”而是把两处逐行执行的相关子查询合并成一次扫描把 IN 的大物化改成带索引的探测并且让每一步都落到索引上。说穿了最终执行计划决定一切SQL 写法只是影响执行计划的手段。6. 实战速查EXPLAIN 标识、经验法则与例外情况6.1 EXPLAIN 子查询标识速查表EXPLAIN 标识含义处理建议DEPENDENT SUBQUERY子查询依赖外层行逐行执行优先改写考虑 JOIN 或窗口函数替代SUBQUERY非相关子查询可能物化执行一次检查结果集大小评估物化临时表代价MATERIALIZED子查询结果被物化为临时表看临时表是否落盘、有没有索引尽量转半连接或 JOINDERIVED派生表被物化确认是否能 MERGE不能就缩小结果集或建临时表UNCACHEABLE SUBQUERY子查询结果无法缓存和每行状态相关当成相关子查询处理基本必须改写6.2 同行的 5 条血泪经验第一写子查询之前先问“这个子查询会被执行几次”。如果是相关子查询外层行数就是它的执行次数。第二IN 后面子查询结果集很大时半连接不一定救你EXPLAIN 里 rows 字段动辄几十万就该考虑改写。第三SQL 规范里不要写“禁止子查询”这种一刀切规则应该写“禁止相关子查询、禁止 SELECT 列表内标量子查询、禁止子查询里乱用 LIMIT”。第四改完一定要看执行计划而不是只看耗时有时耗时下来了纯粹是 MySQL 查询缓存或并发干扰。第五索引是改写能否生效的前提条件没有合适索引JOIN 也可能比子查询更慢。6.3 哪些子查询可以放心用子查询不全是洪水猛兽。我自己在以下场景会保留子查询子查询结果很小比如配置表、分类字典表几百到几千行物化开销极低。EXISTS 相关子查询且外层已经过滤得很小、内层探测索引明确命中。8.0 里简单的等值 IN 子查询能被优化器稳定转成半连接。WHERE 中用来取常量的标量子查询比如WHERE created_at (SELECT MAX(created_at) FROM order_log)优化器会在预处理阶段算成常量。反过来相关子查询出现在 SELECT 列表、子查询里带聚合加大分组、子查询结果集可能超过内存临时表阈值这些才是真正需要动手改写的对象。我个人这些年治理慢查询的体会是子查询本身没有原罪真正的原罪是“相关”和“大物化”这两个特征没有被及时发现。把 EXPLAIN 里出现 DEPENDENT SUBQUERY、MATERIALIZED 当成报警信号比背一百条 SQL 规范都有用。另外还有个小技巧遇到拿不准的写法先在小数据量上把两种方案的执行计划并排对比重点看 rows、key、type 三个字段基本就能预判生产环境的性能走势。这个习惯帮我躲过了不少线上事故也推荐你试试。