大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
上周那篇关于子查询优化的文章发出来后，评论区炸了。有人说“用JOIN是错的”，有人说“EXISTS才是正解”。今天我不站队，直接用实测数据说话，从执行计划层面彻底拆解这三种写法。
1 问题背景：开发中的常见困惑
在日常开发里，IN、EXISTS和JOIN的争论我听过无数遍：
- “数据量不大的时候用
IN最直观，为什么网上都说要改成EXISTS？” - “我的
IN子查询明明有索引，为什么EXPLAIN还是显示全表扫描？” - “
NOT IN和NOT EXISTS结果一样吗？性能差多少？” - “子查询改写为
JOIN后为什么结果里多了重复行？”
这些困惑的根源在于：不同写法在数据库优化器中的处理逻辑截然不同，而且优化器的选择还受到MySQL版本、数据分布、索引设计和统计信息的影响。今天我们就从底层执行路径开始讲起。
2 核心概念：理解IN、EXISTS、JOIN的执行逻辑
要判断谁更快，必须先理解优化器是如何执行这三种写法的。
2.1 IN：物化子查询或半连接
SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders);
在MySQL 5.5及更早版本中，IN子查询的执行方式是物化：先完整执行子查询，将结果集存储在内部临时表中，然后外层查询再与该临时表进行匹配。这种方式在子查询结果集较大时，临时表的构建和磁盘I/O会成为主要瓶颈。
从MySQL 5.6开始，优化器引入了半连接优化。当满足一定条件（子查询无GROUP BY、无聚合、非相关子查询等）时，优化器会将IN子查询转换为类似JOIN的半连接执行路径，性能得到显著提升。
2.2 EXISTS：半连接与匹配即停
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);
EXISTS子查询是相关子查询：它会将外层查询的每一行代入子查询，一旦在内层表中找到匹配记录，就会立即停止扫描并返回结果。这使得EXISTS在处理存在性检查时非常高效。但它的开销高度依赖于外层数据量：如果外层表很大，内层索引够快，EXISTS依然高效；如果外层表巨大且内层索引不佳，逐行代入的成本也会很高。
2.3 JOIN：笛卡尔积与去重代价
SELECT DISTINCT u.* FROM users u JOIN orders o ON u.user_id = o.user_id;
JOIN的执行路径是先将两张表按关联条件进行匹配，然后通过索引快速筛选出符合条件的行。如果users表中一个用户有多个订单，JOIN会产生重复行，因此必须使用DISTINCT去重。而DISTINCT在MySQL中通常需要创建临时表进行去重操作，当数据量较大时临时表可能溢出到磁盘，带来额外的性能损耗。
3 实测对比：同一场景下的三种写法
3.1 测试环境
- 数据库：MySQL 8.0.33
users表：10万行，user_id为主键orders表：100万行，user_id有二级索引- 目标：查询所有下过单的用户信息
3.2 三种写法的执行时间
| 写法 | 平均耗时（3次运行） | 执行计划特点 |
|---|---|---|
IN |
128ms | 半连接，使用物化或索引 |
EXISTS |
95ms | 半连接，匹配即停 |
JOIN + DISTINCT |
236ms | DISTINCT产生临时表，大表时可能写磁盘 |
3.3 结果分析
在本测试环境中，EXISTS性能最优，IN次之，JOIN最慢。JOIN的DISTINCT临时表开销和重复行匹配是其主要性能瓶颈。但需要注意的是，这一结论依赖于特定数据分布和索引设计——如果子查询结果集极小，IN可能更快；如果需要同时返回两表的字段，JOIN则是唯一合理的选择。
4 执行计划深度解码：为什么会有这样的结果？
我们通过EXPLAIN和EXPLAIN FORMAT=TREE来观察优化器的决策过程。
4.1 EXPLAIN输出对比
-- EXPLAIN for IN
EXPLAIN SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders);
输出中关键信息：select_type为PRIMARY的type=ALL（外层全表扫描），SUBQUERY的type=index（子查询索引扫描）。这意味着MySQL先执行子查询拿到所有user_id，再逐行匹配外层。
4.2 EXPLAIN关键列解读
| 列名 | 作用 | 本次对比中的表现 |
|---|---|---|
select_type |
标识子查询类型 | IN和EXISTS均为半连接优化 |
type |
访问类型，ALL=全表扫描，ref=索引查找 |
IN的驱动表为ALL，EXISTS的驱动表为ref |
Extra |
附加信息 | JOIN版本可能显示Using temporary |
filtered |
过滤后剩余比例 | 影响回表代价估算 |
5 选型决策指南：三条铁律
基于以上分析，我总结出三条实用的选择策略：
- 存在性检查（业务逻辑为“是否有订单”）：优先使用
EXISTS。其“匹配即停”的机制和半连接优化，使其在大多数场景下性能最优且语义最清晰。 - 子查询结果集非常小（如几十行）且不重复：
IN的可读性最好，由于结果集极小，物化临时表的代价几乎可以忽略。 - 需要同时返回A表和B表的字段：必须使用
JOIN。但需通过业务逻辑判断是否需要DISTINCT去重，尽可能避免不必要的去重操作。
6 总结
没有绝对的“最快写法”，只有基于场景和数据特征的“最合适写法”。
EXISTS适合存在性检查，匹配即停，通常稳定性最好。IN在子查询结果集极小时可读性最佳，性能也可接受。JOIN在需要两表字段时不可替代，但需关注去重成本。
以后遇到这类问题，先问自己三个问题：业务是要判断存在还是取数据？子查询结果集大不大？能不能接受去重临时表？想清楚再写，比抄网上的“最佳实践”靠谱得多。
小耶在手，SQL不愁。
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
