做后端开发这些年被问得最多的一类问题就是 SQL 写法上的取舍其中MySQL 里 where 嵌套和 join 到底谁效率高这个问题出现的频率相当高。有意思的是问这个问题的人往往已经写出了能跑的 SQL但心里没底总觉得换个写法性能会差一大截。我自己早年也在这个问题上纠结过很久翻过执行计划、压过数据、踩过索引失效的坑最后发现答案远不是一句join 更快或者子查询更慢能概括的。先把结论的方向说清楚where 嵌套尤其是 IN/EXISTS 子查询和 JOIN 的效率高低取决于 MySQL 版本、优化器行为、数据分布、索引设计以及子查询的具体形态。在 MySQL 5.6 之前很多子查询确实会被优化器坑成相关子查询逐行执行而 5.6 之后引入了半连接semi-join优化8.0 又在派生表合并、条件下推上做了大量改进很多过去慢的写法现在其实已经不慢了。这篇文章我会把这两类写法的执行逻辑、优化器怎么选、什么场景谁更占优、怎么用执行计划验证以及我自己踩过的坑全部摊开讲清楚。适合已经会写基本 SQL、想进一步搞懂性能取舍的后端和 DBA 读者也适合刚入行、被子查询一定慢这类说法搞晕的朋友。1. 先搞清楚这两类写法到底在干什么在比较效率之前得先把where 嵌套和join这两个词对应的实际 SQL 形态对齐否则讨论就是鸡同鸭讲。很多人嘴上说嵌套实际写的是相关子查询说join实际写的是隐式连接。概念不清后面的性能分析全是空中楼阁。1.1 where 嵌套的几种真实形态所谓 where 嵌套落到 SQL 上通常有三种典型写法。第一种是IN 子查询比如从订单表里筛出下过某类商品订单的用户写成WHERE user_id IN (SELECT user_id FROM orders WHERE ...)。第二种是EXISTS 子查询语义上判断是否存在满足条件的关联行写法是WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id users.id AND ...)。第三种是派生表嵌套也就是把子查询放在 FROM 后面当临时表用再在外层 where 里过滤。这三种形态在优化器眼里完全不是一回事。IN 子查询和 EXISTS 子查询在满足条件时会走**半连接semi-join优化而派生表则涉及派生表合并derived merge**或者物化materialization。我见过不少同学把三种写法混为一谈然后得出嵌套就是慢的结论其实他测的那个慢查询可能只是派生表没被合并而已。提示判断你写的是不是真嵌套看子查询是否引用了外层表的列。引用了就是相关子查询没引用就是非相关子查询两者的执行路径差别很大。1.2 join 的等价语义与执行模型JOIN 的本质是把两张或多张表按连接条件拼在一起MySQL 里常见的连接算法有Nested Loop JoinNLJ、Block Nested LoopBNL8.0.18 之后还引入了Hash Join。注意这里的 NLJ 和嵌套循环字面意思接近但它和 where 嵌套子查询不是一回事——前者是连接算法后者是 SQL 语法结构。举个语义等价的例子SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100)和SELECT DISTINCT users.* FROM users JOIN orders ON users.id orders.user_id WHERE orders.amount 100在结果集上基本等价前提是处理好重复行。既然语义等价优化器理论上可以把前者改写成后者这正是半连接优化的核心思路。所以谁效率高这个问题很多时候答案取决于优化器有没有成功完成这个改写。1.3 为什么大家会有join 一定更快的印象这个印象主要来自 MySQL 5.5 及更早的时代。那时候子查询优化能力弱IN子查询经常被改写成相关子查询外层每扫一行就要执行一次内层查询数据量一大直接爆炸。而 JOIN 至少能走索引嵌套循环看起来稳定。这个历史包袱一直流传到现在导致很多人不加验证就默认 join 更优。实际上从 5.6 开始优化器已经能把大量 IN/EXISTS 子查询转成半连接执行路径和手写 JOIN 几乎一致性能差距被大幅抹平。2. 优化器视角MySQL 到底怎么处理这两种写法要判断效率不能只看 SQL 表面得看优化器把它变成了什么。这一节我拆开讲半连接、物化、派生表合并这几个关键机制理解了它们你就能预判大部分场景的走向。2.1 半连接优化IN 和 EXISTS 的救星半连接的核心思想是对于外表某行只要在内表找到至少一个匹配就保留这类语义不需要真正做笛卡尔积再 DISTINCT而是用更聪明的方式判断存在性。MySQL 实现了五种半连接策略FirstMatch、LooseScan、Materialization、DuplicateWeedout、Table Pullout。优化器会根据成本估算挑一个。以WHERE user_id IN (SELECT user_id FROM orders WHERE amount 100)为例如果 orders 表在 amount 上有索引且过滤后数据量小优化器可能选 Materialization把子查询结果物化成临时表并建唯一索引然后和外层做连接如果 orders 的 user_id 上有索引可能选 FirstMatch逐行去内表探测到第一个匹配就停。这两种路径的效率都不差和手写 JOIN 基本在同一量级。关键在于半连接要生效子查询必须是非相关或者能被解相关的形态。如果你的 EXISTS 子查询里带了复杂的相关条件、聚合、LIMIT优化器可能放弃半连接退化成逐行执行的相关子查询这时候性能就会明显掉下来。2.2 派生表合并与物化的分水岭派生表FROM 后面的子查询的处理更微妙。MySQL 会先尝试合并merge到外层查询相当于把子查询展开如果合并条件不满足比如子查询里有 GROUP BY、DISTINCT、LIMIT、UNION就会退而求其次做物化生成临时表。物化的代价是实打实的要把子查询完整跑一遍、写进临时表可能在磁盘上、再参与外层查询。我实测过一个场景把带 GROUP BY 的派生表改写成 JOIN 子查询过滤后耗时从 1.2 秒降到 80 毫秒差距就出在物化上。所以看到派生表写法时第一反应应该是看执行计划里有没有出现derived2这样的物化标记。2.3 条件下推决定效率的隐形手条件下推condition pushdown是指优化器把外层的过滤条件推进子查询里提前执行减少中间结果。这个能力在 8.0 里增强了不少尤其是对派生表和 UNION 的处理。如果条件下推成功派生表物化的数据量会小很多效率自然上去如果没推下去子查询先把全量数据物化外层再过滤那就白白浪费了。判断条件下推是否生效可以看EXPLAIN输出里的Extra列或者用EXPLAIN FORMATJSON看attached_condition字段。这个细节很多人忽略但它经常是同样写法、不同版本、性能天差地别的根源。3. 分场景实测谁快谁慢不是一句话的事理论讲完得落到数据上。下面这几个场景是我在实际项目里反复遇到的典型形态每个都给出等价写法和实测倾向。需要说明的是具体数字会随硬件、数据量、索引变化我给出的是相对趋势和判断依据你复现时重点看执行计划和扫描行数而不是死记耗时。3.1 场景一小结果集过滤大表IN 子查询通常不亏假设要从百万级用户表里筛出最近下过大额订单的用户。子查询SELECT user_id FROM orders WHERE amount 1000过滤后可能只有几千行。这种内层结果集小、外层表大的形态IN 子查询走半连接物化后相当于拿几千行的临时表去驱动外层效率很高。我实测过类似结构IN 写法和 JOIN 写法的执行计划几乎一致都是先物化子查询再走索引连接耗时差异在 5% 以内基本可以忽略。这种情况下纠结谁快没意义选可读性更好的那个就行。我个人偏好 IN因为语义直白筛出属于这个集合的行读起来比 JOIN DISTINCT 更贴近业务意图。3.2 场景二大结果集关联JOIN 更可控反过来如果子查询过滤后结果集很大比如几十万行物化临时表的成本就上来了临时表可能落盘还要建唯一索引去重。这时候手写 JOIN 往往更可控因为你可以精确控制连接顺序、驱动表选择甚至用 STRAIGHT_JOIN 强制走某个顺序。这里有个经验当内层结果集超过外层表的 20% 左右时我开始倾向 JOIN。这个比例不是铁律但作为直觉判断挺管用。原因是结果集一大半连接的物化和去重开销就压过了它带来的便利而 JOIN 的流式处理更省内存。3.3 场景三EXISTS 与 IN 的微妙差异很多人不知道NOT IN和NOT EXISTS在遇到 NULL 时行为不同这直接影响优化器能否改写。NOT IN的子查询如果返回 NULL整个条件会变成 UNKNOWN结果集为空——这不是性能问题是正确性问题但很多人为了性能把 NOT EXISTS 改成 NOT IN结果数据错了。性能上EXISTS天然适合存在性判断优化器更容易做 FirstMatch找到第一条就停IN则倾向物化整个子查询。所以在只要存在就保留的语义下EXISTS 往往更省。我一般建议判断存在用 EXISTS判断属于集合用 IN语义和性能都对得上。3.4 场景四多表关联下的嵌套陷阱最坑的是多层嵌套。比如三层 IN 子查询套在一起优化器可能只对最内层做了半连接中间层退化成相关子查询。这种写法执行计划会非常难看扫描行数呈指数级。我的处理原则是嵌套超过两层就考虑改写成 JOIN 或者拆成临时表分步处理。分步处理虽然多了几次往返但每步都能走索引、都能单独优化整体反而更快也更好排查。4. 用执行计划说话怎么验证到底谁快光靠猜没用MySQL 给了我们足够的工具去验证。这一节讲清楚怎么看执行计划、看哪些字段、怎么对比两种写法。4.1 EXPLAIN 关键字段速读EXPLAIN输出里我最关注这几列type访问类型从好到坏大致是 system const eq_ref ref range index ALL、key实际用的索引、rows预估扫描行数、Extra额外信息出现 Using temporary、Using filesort 要警惕。对比两种写法时重点看rows的乘积和Extra里有没有物化、临时表标记。下面是一个对比示例的骨架你可以套用到自己的表上-- 写法 AIN 子查询 EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 1000); -- 写法 BJOIN EXPLAIN SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id o.user_id WHERE o.amount 1000;两条语句分别跑一遍把rows和Extra列出来对比。如果写法 A 的Extra出现Start temporary/End temporary说明走了物化半连接写法 B 如果出现Using temporary做 DISTINCT说明去重有开销。谁的临时表操作更少通常谁更快。4.2 EXPLAIN FORMATJSON 看优化细节普通 EXPLAIN 不够用时上 JSON 格式。它能告诉你半连接用了哪种策略semijoin相关字段、条件下推有没有生效attached_condition、物化表的成本估算。我排查为什么这个子查询没走半连接时基本都靠它定位。EXPLAIN FORMATJSON SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 1000);输出里找strategy或者using_temporary_table这类字段能直接看出优化器的决策。如果发现子查询被标成DEPENDENT SUBQUERY那就是退化成相关子查询了得改写法。4.3 用 profiling 和实际耗时交叉验证执行计划是估算最终还得看真实耗时。SHOW PROFILES或者 8.0 的performance_schema能给出各阶段实际耗时。我的习惯是先用 EXPLAIN 筛掉明显不合理的写法再对剩下的两三种跑真实数据对比取多次执行的中位数避免缓存干扰。测试时记得先FLUSH STATUS或者换不同的参数值防止结果被查询缓存8.0 已移除但 5.7 还有美化。5. 索引设计才是真正的胜负手聊到这里必须强调一句大部分嵌套 vs join的性能差异本质是索引没设计好。同样的两种写法索引到位时差距微乎其微索引缺失时两种都慢只是慢的方式不同。5.1 连接列和过滤列都要有索引JOIN 的连接列比如orders.user_id必须有索引否则就是全表扫描做连接。子查询里的过滤列比如orders.amount也最好有索引让子查询本身能快速收敛。我见过太多案例争论半天写法结果user_id上根本没索引两种写法都在做全表扫描谁也救不了。5.2 联合索引的顺序很讲究如果过滤条件经常是某状态 某时间范围联合索引的顺序要按区分度高、过滤性强的列放前面。比如(status, created_at)和(created_at, status)在范围查询下的表现可能差很多。这个和嵌套/join 无关但它决定了子查询或连接时能过滤掉多少行间接决定了两种写法的实际差距。5.3 覆盖索引减少回表如果子查询只需要返回user_id这一列而索引正好覆盖它比如(amount, user_id)那子查询可以只扫索引不回表速度提升明显。这种优化对 IN 子查询尤其友好因为物化的数据量直接变小。我经常建议先想清楚子查询要返回什么列再设计覆盖索引这比纠结写法收益大得多。6. 常见问题与排查速查表实际排查中问题往往集中在几个固定套路。我把高频问题和处理思路整理成表方便对照。现象可能原因排查动作处理建议子查询写法明显慢于 JOIN退化成 DEPENDENT SUBQUERY看 EXPLAIN 的 select_type改写为非相关子查询或 JOIN派生表查询慢派生表被物化且数据量大看 Extra 有无derived尝试合并条件或改 JOIN两种写法都慢连接列/过滤列缺索引看 key 列是否为 NULL补索引优先覆盖索引JOIN 后结果重复一对多关系未去重检查是否需要 DISTINCT用 EXISTS 替代或加 DISTINCTNOT IN 结果为空子查询含 NULL检查子查询列是否可空改用 NOT EXISTS执行计划估算行数离谱统计信息过期看 rows 与实际差异ANALYZE TABLE 更新统计除了表里的再补两个我踩过的坑。第一个是子查询里用了函数导致索引失效比如WHERE DATE(created_at) 2024-01-01这种写法在子查询和 JOIN 里都会让索引失效改成范围查询created_at 2024-01-01 AND created_at 2024-01-02才行。第二个是隐式类型转换连接列一边是 INT 一边是 VARCHARMySQL 会做类型转换导致索引失效这种问题在两种写法里都会出现排查时用SHOW WARNINGS能看到转换提示。注意不要迷信改写就一定变快。每次改写后都要用真实数据量验证小表上测出来的结论在大表上可能完全反过来。7. 我的实践结论与选型建议绕了一大圈回到最初的问题。基于这些年的实际使用我的选型逻辑是这样的先看语义再看数据量最后看执行计划。语义上判断存在用 EXISTS、判断集合用 IN、需要返回多表字段用 JOIN这是第一优先级因为语义错了性能再好也没意义。数据量上内层结果集小的时候 IN/EXISTS 完全够用结果集大的时候倾向 JOIN 以便控制连接顺序。最后一定要用 EXPLAIN 验证别凭感觉。还有一个容易被忽略的点可维护性也是效率的一部分。一个五层嵌套的子查询三个月后连写的人自己都看不懂排查问题时浪费的时间远超那点性能差异。我现在的习惯是嵌套超过两层就拆开用临时表或者 CTE8.0 支持分步写每步注释清楚既好优化又好交接。最后分享一个我常用的验证小技巧把两种写法都放进EXPLAIN ANALYZE8.0.18里跑它会给出实际执行时间和各算子耗时比单纯看估算准得多。实测下来大部分所谓效率差异巨大的案例在索引补齐、统计信息更新之后差距都收敛到可以忽略的程度。真正拉开差距的从来不是选 IN 还是选 JOIN而是有没有把索引和执行计划当回事。 
长河君 · 2026/11/2 8:06:22 · 阅读 1.2k MySQL中where嵌套与join效率对比：优化器原理与实战选型
做后端开发这些年被问得最多的一类问题就是 SQL 写法上的取舍其中MySQL 里 where 嵌套和 join 到底谁效率高这个问题出现的频率相当高。有意思的是问这个问题的人往往已经写出了能跑的 SQL但心里没底总觉得换个写法性能会差一大截。我自己早年也在这个问题上纠结过很久翻过执行计划、压过数据、踩过索引失效的坑最后发现答案远不是一句join 更快或者子查询更慢能概括的。先把结论的方向说清楚where 嵌套尤其是 IN/EXISTS 子查询和 JOIN 的效率高低取决于 MySQL 版本、优化器行为、数据分布、索引设计以及子查询的具体形态。在 MySQL 5.6 之前很多子查询确实会被优化器坑成相关子查询逐行执行而 5.6 之后引入了半连接semi-join优化8.0 又在派生表合并、条件下推上做了大量改进很多过去慢的写法现在其实已经不慢了。这篇文章我会把这两类写法的执行逻辑、优化器怎么选、什么场景谁更占优、怎么用执行计划验证以及我自己踩过的坑全部摊开讲清楚。适合已经会写基本 SQL、想进一步搞懂性能取舍的后端和 DBA 读者也适合刚入行、被子查询一定慢这类说法搞晕的朋友。1. 先搞清楚这两类写法到底在干什么在比较效率之前得先把where 嵌套和join这两个词对应的实际 SQL 形态对齐否则讨论就是鸡同鸭讲。很多人嘴上说嵌套实际写的是相关子查询说join实际写的是隐式连接。概念不清后面的性能分析全是空中楼阁。1.1 where 嵌套的几种真实形态所谓 where 嵌套落到 SQL 上通常有三种典型写法。第一种是IN 子查询比如从订单表里筛出下过某类商品订单的用户写成WHERE user_id IN (SELECT user_id FROM orders WHERE ...)。第二种是EXISTS 子查询语义上判断是否存在满足条件的关联行写法是WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id users.id AND ...)。第三种是派生表嵌套也就是把子查询放在 FROM 后面当临时表用再在外层 where 里过滤。这三种形态在优化器眼里完全不是一回事。IN 子查询和 EXISTS 子查询在满足条件时会走**半连接semi-join优化而派生表则涉及派生表合并derived merge**或者物化materialization。我见过不少同学把三种写法混为一谈然后得出嵌套就是慢的结论其实他测的那个慢查询可能只是派生表没被合并而已。提示判断你写的是不是真嵌套看子查询是否引用了外层表的列。引用了就是相关子查询没引用就是非相关子查询两者的执行路径差别很大。1.2 join 的等价语义与执行模型JOIN 的本质是把两张或多张表按连接条件拼在一起MySQL 里常见的连接算法有Nested Loop JoinNLJ、Block Nested LoopBNL8.0.18 之后还引入了Hash Join。注意这里的 NLJ 和嵌套循环字面意思接近但它和 where 嵌套子查询不是一回事——前者是连接算法后者是 SQL 语法结构。举个语义等价的例子SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100)和SELECT DISTINCT users.* FROM users JOIN orders ON users.id orders.user_id WHERE orders.amount 100在结果集上基本等价前提是处理好重复行。既然语义等价优化器理论上可以把前者改写成后者这正是半连接优化的核心思路。所以谁效率高这个问题很多时候答案取决于优化器有没有成功完成这个改写。1.3 为什么大家会有join 一定更快的印象这个印象主要来自 MySQL 5.5 及更早的时代。那时候子查询优化能力弱IN子查询经常被改写成相关子查询外层每扫一行就要执行一次内层查询数据量一大直接爆炸。而 JOIN 至少能走索引嵌套循环看起来稳定。这个历史包袱一直流传到现在导致很多人不加验证就默认 join 更优。实际上从 5.6 开始优化器已经能把大量 IN/EXISTS 子查询转成半连接执行路径和手写 JOIN 几乎一致性能差距被大幅抹平。2. 优化器视角MySQL 到底怎么处理这两种写法要判断效率不能只看 SQL 表面得看优化器把它变成了什么。这一节我拆开讲半连接、物化、派生表合并这几个关键机制理解了它们你就能预判大部分场景的走向。2.1 半连接优化IN 和 EXISTS 的救星半连接的核心思想是对于外表某行只要在内表找到至少一个匹配就保留这类语义不需要真正做笛卡尔积再 DISTINCT而是用更聪明的方式判断存在性。MySQL 实现了五种半连接策略FirstMatch、LooseScan、Materialization、DuplicateWeedout、Table Pullout。优化器会根据成本估算挑一个。以WHERE user_id IN (SELECT user_id FROM orders WHERE amount 100)为例如果 orders 表在 amount 上有索引且过滤后数据量小优化器可能选 Materialization把子查询结果物化成临时表并建唯一索引然后和外层做连接如果 orders 的 user_id 上有索引可能选 FirstMatch逐行去内表探测到第一个匹配就停。这两种路径的效率都不差和手写 JOIN 基本在同一量级。关键在于半连接要生效子查询必须是非相关或者能被解相关的形态。如果你的 EXISTS 子查询里带了复杂的相关条件、聚合、LIMIT优化器可能放弃半连接退化成逐行执行的相关子查询这时候性能就会明显掉下来。2.2 派生表合并与物化的分水岭派生表FROM 后面的子查询的处理更微妙。MySQL 会先尝试合并merge到外层查询相当于把子查询展开如果合并条件不满足比如子查询里有 GROUP BY、DISTINCT、LIMIT、UNION就会退而求其次做物化生成临时表。物化的代价是实打实的要把子查询完整跑一遍、写进临时表可能在磁盘上、再参与外层查询。我实测过一个场景把带 GROUP BY 的派生表改写成 JOIN 子查询过滤后耗时从 1.2 秒降到 80 毫秒差距就出在物化上。所以看到派生表写法时第一反应应该是看执行计划里有没有出现derived2这样的物化标记。2.3 条件下推决定效率的隐形手条件下推condition pushdown是指优化器把外层的过滤条件推进子查询里提前执行减少中间结果。这个能力在 8.0 里增强了不少尤其是对派生表和 UNION 的处理。如果条件下推成功派生表物化的数据量会小很多效率自然上去如果没推下去子查询先把全量数据物化外层再过滤那就白白浪费了。判断条件下推是否生效可以看EXPLAIN输出里的Extra列或者用EXPLAIN FORMATJSON看attached_condition字段。这个细节很多人忽略但它经常是同样写法、不同版本、性能天差地别的根源。3. 分场景实测谁快谁慢不是一句话的事理论讲完得落到数据上。下面这几个场景是我在实际项目里反复遇到的典型形态每个都给出等价写法和实测倾向。需要说明的是具体数字会随硬件、数据量、索引变化我给出的是相对趋势和判断依据你复现时重点看执行计划和扫描行数而不是死记耗时。3.1 场景一小结果集过滤大表IN 子查询通常不亏假设要从百万级用户表里筛出最近下过大额订单的用户。子查询SELECT user_id FROM orders WHERE amount 1000过滤后可能只有几千行。这种内层结果集小、外层表大的形态IN 子查询走半连接物化后相当于拿几千行的临时表去驱动外层效率很高。我实测过类似结构IN 写法和 JOIN 写法的执行计划几乎一致都是先物化子查询再走索引连接耗时差异在 5% 以内基本可以忽略。这种情况下纠结谁快没意义选可读性更好的那个就行。我个人偏好 IN因为语义直白筛出属于这个集合的行读起来比 JOIN DISTINCT 更贴近业务意图。3.2 场景二大结果集关联JOIN 更可控反过来如果子查询过滤后结果集很大比如几十万行物化临时表的成本就上来了临时表可能落盘还要建唯一索引去重。这时候手写 JOIN 往往更可控因为你可以精确控制连接顺序、驱动表选择甚至用 STRAIGHT_JOIN 强制走某个顺序。这里有个经验当内层结果集超过外层表的 20% 左右时我开始倾向 JOIN。这个比例不是铁律但作为直觉判断挺管用。原因是结果集一大半连接的物化和去重开销就压过了它带来的便利而 JOIN 的流式处理更省内存。3.3 场景三EXISTS 与 IN 的微妙差异很多人不知道NOT IN和NOT EXISTS在遇到 NULL 时行为不同这直接影响优化器能否改写。NOT IN的子查询如果返回 NULL整个条件会变成 UNKNOWN结果集为空——这不是性能问题是正确性问题但很多人为了性能把 NOT EXISTS 改成 NOT IN结果数据错了。性能上EXISTS天然适合存在性判断优化器更容易做 FirstMatch找到第一条就停IN则倾向物化整个子查询。所以在只要存在就保留的语义下EXISTS 往往更省。我一般建议判断存在用 EXISTS判断属于集合用 IN语义和性能都对得上。3.4 场景四多表关联下的嵌套陷阱最坑的是多层嵌套。比如三层 IN 子查询套在一起优化器可能只对最内层做了半连接中间层退化成相关子查询。这种写法执行计划会非常难看扫描行数呈指数级。我的处理原则是嵌套超过两层就考虑改写成 JOIN 或者拆成临时表分步处理。分步处理虽然多了几次往返但每步都能走索引、都能单独优化整体反而更快也更好排查。4. 用执行计划说话怎么验证到底谁快光靠猜没用MySQL 给了我们足够的工具去验证。这一节讲清楚怎么看执行计划、看哪些字段、怎么对比两种写法。4.1 EXPLAIN 关键字段速读EXPLAIN输出里我最关注这几列type访问类型从好到坏大致是 system const eq_ref ref range index ALL、key实际用的索引、rows预估扫描行数、Extra额外信息出现 Using temporary、Using filesort 要警惕。对比两种写法时重点看rows的乘积和Extra里有没有物化、临时表标记。下面是一个对比示例的骨架你可以套用到自己的表上-- 写法 AIN 子查询 EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 1000); -- 写法 BJOIN EXPLAIN SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id o.user_id WHERE o.amount 1000;两条语句分别跑一遍把rows和Extra列出来对比。如果写法 A 的Extra出现Start temporary/End temporary说明走了物化半连接写法 B 如果出现Using temporary做 DISTINCT说明去重有开销。谁的临时表操作更少通常谁更快。4.2 EXPLAIN FORMATJSON 看优化细节普通 EXPLAIN 不够用时上 JSON 格式。它能告诉你半连接用了哪种策略semijoin相关字段、条件下推有没有生效attached_condition、物化表的成本估算。我排查为什么这个子查询没走半连接时基本都靠它定位。EXPLAIN FORMATJSON SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 1000);输出里找strategy或者using_temporary_table这类字段能直接看出优化器的决策。如果发现子查询被标成DEPENDENT SUBQUERY那就是退化成相关子查询了得改写法。4.3 用 profiling 和实际耗时交叉验证执行计划是估算最终还得看真实耗时。SHOW PROFILES或者 8.0 的performance_schema能给出各阶段实际耗时。我的习惯是先用 EXPLAIN 筛掉明显不合理的写法再对剩下的两三种跑真实数据对比取多次执行的中位数避免缓存干扰。测试时记得先FLUSH STATUS或者换不同的参数值防止结果被查询缓存8.0 已移除但 5.7 还有美化。5. 索引设计才是真正的胜负手聊到这里必须强调一句大部分嵌套 vs join的性能差异本质是索引没设计好。同样的两种写法索引到位时差距微乎其微索引缺失时两种都慢只是慢的方式不同。5.1 连接列和过滤列都要有索引JOIN 的连接列比如orders.user_id必须有索引否则就是全表扫描做连接。子查询里的过滤列比如orders.amount也最好有索引让子查询本身能快速收敛。我见过太多案例争论半天写法结果user_id上根本没索引两种写法都在做全表扫描谁也救不了。5.2 联合索引的顺序很讲究如果过滤条件经常是某状态 某时间范围联合索引的顺序要按区分度高、过滤性强的列放前面。比如(status, created_at)和(created_at, status)在范围查询下的表现可能差很多。这个和嵌套/join 无关但它决定了子查询或连接时能过滤掉多少行间接决定了两种写法的实际差距。5.3 覆盖索引减少回表如果子查询只需要返回user_id这一列而索引正好覆盖它比如(amount, user_id)那子查询可以只扫索引不回表速度提升明显。这种优化对 IN 子查询尤其友好因为物化的数据量直接变小。我经常建议先想清楚子查询要返回什么列再设计覆盖索引这比纠结写法收益大得多。6. 常见问题与排查速查表实际排查中问题往往集中在几个固定套路。我把高频问题和处理思路整理成表方便对照。现象可能原因排查动作处理建议子查询写法明显慢于 JOIN退化成 DEPENDENT SUBQUERY看 EXPLAIN 的 select_type改写为非相关子查询或 JOIN派生表查询慢派生表被物化且数据量大看 Extra 有无derived尝试合并条件或改 JOIN两种写法都慢连接列/过滤列缺索引看 key 列是否为 NULL补索引优先覆盖索引JOIN 后结果重复一对多关系未去重检查是否需要 DISTINCT用 EXISTS 替代或加 DISTINCTNOT IN 结果为空子查询含 NULL检查子查询列是否可空改用 NOT EXISTS执行计划估算行数离谱统计信息过期看 rows 与实际差异ANALYZE TABLE 更新统计除了表里的再补两个我踩过的坑。第一个是子查询里用了函数导致索引失效比如WHERE DATE(created_at) 2024-01-01这种写法在子查询和 JOIN 里都会让索引失效改成范围查询created_at 2024-01-01 AND created_at 2024-01-02才行。第二个是隐式类型转换连接列一边是 INT 一边是 VARCHARMySQL 会做类型转换导致索引失效这种问题在两种写法里都会出现排查时用SHOW WARNINGS能看到转换提示。注意不要迷信改写就一定变快。每次改写后都要用真实数据量验证小表上测出来的结论在大表上可能完全反过来。7. 我的实践结论与选型建议绕了一大圈回到最初的问题。基于这些年的实际使用我的选型逻辑是这样的先看语义再看数据量最后看执行计划。语义上判断存在用 EXISTS、判断集合用 IN、需要返回多表字段用 JOIN这是第一优先级因为语义错了性能再好也没意义。数据量上内层结果集小的时候 IN/EXISTS 完全够用结果集大的时候倾向 JOIN 以便控制连接顺序。最后一定要用 EXPLAIN 验证别凭感觉。还有一个容易被忽略的点可维护性也是效率的一部分。一个五层嵌套的子查询三个月后连写的人自己都看不懂排查问题时浪费的时间远超那点性能差异。我现在的习惯是嵌套超过两层就拆开用临时表或者 CTE8.0 支持分步写每步注释清楚既好优化又好交接。最后分享一个我常用的验证小技巧把两种写法都放进EXPLAIN ANALYZE8.0.18里跑它会给出实际执行时间和各算子耗时比单纯看估算准得多。实测下来大部分所谓效率差异巨大的案例在索引补齐、统计信息更新之后差距都收敛到可以忽略的程度。真正拉开差距的从来不是选 IN 还是选 JOIN而是有没有把索引和执行计划当回事。