大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
上周讲了派生表的性能陷阱，这周继续SQL改写的话题——子查询 vs JOIN。
你一定听过这句话：“能不用子查询就不用，改成JOIN就快了。”但在MySQL 5.6+的环境下，这个说法已经不完全成立了。很多IN子查询，优化器会自动转成半连接（Semi-Join） ，性能和JOIN几乎没差别。
但问题是——优化器不是什么时候都会转。有时候它转了，有时候不转。看懂它什么时候转、什么时候不转，你才能真正理解“为什么这条SQL快、那条SQL慢”。
今天把半连接优化彻底拆开讲一遍。
一、先搞清楚：子查询为什么慢？
在MySQL 5.5及更早的版本中，IN子查询的执行方式是物化：先完整执行子查询，把结果集存在临时表里，外层查询再跟临时表匹配。子查询结果集大了，临时表写磁盘，性能就崩了。
更糟的是关联子查询——外层每扫描一行，子查询就执行一次。10万行订单，子查询跑10万次，这就是“N+1查询问题”。
从MySQL 5.6开始，优化器引入了半连接（Semi-Join）优化。当满足一定条件时，优化器会把IN子查询转换成类似JOIN的执行计划，性能大幅提升。所以“子查询慢”这句话在MySQL 5.6+已经不成立了——但前提是优化器成功做了半连接转换。
二、半连接是什么？
半连接（Semi-Join）是数据库内部的一种特殊连接操作。它的核心逻辑很简单：只关心左表记录在右表中是否存在匹配，不关心匹配了多少条，也不返回右表的任何数据。
举个例子：
SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders);
写成半连接的语义是：对于users表的每一行，只要在orders表中能找到至少一条匹配记录，就返回该行。至于匹配了几条，不重要。
半连接 vs 内连接（INNER JOIN）的关键区别：
内连接：如果右表匹配了5条，左表那行会返回5次（需要
DISTINCT去重）半连接：只返回一次，天然去重
三、优化器的5种半连接执行策略
MySQL优化器会根据查询特征、数据分布、索引情况，从5种策略中选择一种来执行半连接：
策略一：Table Pullout（表上拉）
子查询中的表有唯一索引时，直接把子查询表“拉”到外层做JOIN。这是最高效的策略——代价最小，执行最快。适用条件：子查询表关联字段有唯一索引或主键。
策略二：FirstMatch（首次匹配）
对于外层表的每一行，在子查询中找到第一条匹配记录后立即停止扫描，不再继续找。特别适合EXISTS子查询。适用条件：EXISTS或IN子查询，关联字段有索引。
策略三：LooseScan（松散扫描）
利用子查询表的索引进行“松散”扫描——跳过重复值，每个值只扫一次。适用条件：子查询表有关联字段的索引，且索引前缀匹配查询条件。
策略四：DuplicateWeedout（重复值消除）
通过临时表消除可能的重复记录。当半连接可能产生重复行时，优化器用临时表去重。适用条件：半连接可能产生重复行，但无法用其他策略处理。
策略五：Materialization（物化）
先把子查询结果物化成临时表（自动建索引），再跟外表做连接。当子查询结果集较小，或者子查询表本身没有合适的索引时，优化器会选这个策略。适用条件：子查询结果集较小，或子查询表无有效索引。
四、执行计划怎么看半连接？
用EXPLAIN看执行计划，Extra列会显示半连接相关的信息：
| Extra列内容 | 含义 |
|---|---|
Using semijoin |
使用了半连接优化 |
FirstMatch |
使用了FirstMatch策略 |
LooseScan |
使用了松散扫描策略 |
Materialize |
使用了物化策略 |
Start temporary / End temporary |
使用DuplicateWeedout策略 |
五、半连接什么时候会失效？
半连接优化不是万能的。以下情况优化器无法做半连接转换：
子查询包含
GROUP BY、聚合函数或LIMITNOT IN子查询（NOT IN走的是反连接，不是半连接）子查询是相关子查询，且关联条件复杂
关联字段没有有效索引（优化器评估成本过高时放弃）
如果EXPLAIN中Extra列没有出现半连接相关字样，说明优化器没有做半连接转换——这时候才需要考虑手动改写成JOIN。
六、真实案例：半连接生效 vs 失效
案例一：半连接生效
SELECT * FROM users u
WHERE u.user_id IN (SELECT o.user_id FROM orders o WHERE o.status = 'PAID');
user_id有索引，子查询无聚合无GROUP BY，优化器自动转为半连接，采用FirstMatch策略。执行时间：0.12秒。EXPLAIN显示Extra: FirstMatch。
案例二：半连接失效
SELECT * FROM users u
WHERE u.user_id IN (
SELECT o.user_id FROM orders o
WHERE o.status = 'PAID'
GROUP BY o.user_id
);
子查询包含GROUP BY，无法做半连接转换。优化器选择了物化策略——先执行子查询，把结果集存在临时表里，再跟users表做连接。执行时间：2.3秒。
案例三：半连接无法使用
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.user_id
AND o.amount > (SELECT AVG(amount) FROM orders)
);
嵌套子查询导致优化器放弃了半连接，采用逐行执行相关子查询。执行时间：8.5秒。
七、实践建议：什么时候该手动改写？
| 情况 | 建议 | 原因 |
|---|---|---|
EXPLAIN显示半连接 |
不用改 | 优化器已经帮你优化了 |
EXPLAIN没有半连接，且子查询简单 |
改写成JOIN |
手动引导优化器走更好的路径 |
子查询包含GROUP BY |
改写成派生表+JOIN | 半连接无法处理，物化代价可能很大 |
NOT IN子查询 |
改写成NOT EXISTS |
避免NULL陷阱，性能更好 |
| 嵌套子查询 | 拆分成多层CTE | 降低复杂度，让优化器更容易处理 |
八、总结
半连接是MySQL优化器处理IN/EXISTS子查询的核心机制。MySQL 5.6+引入后，很多子查询已经不需要手动改写成JOIN了。
关键在于学会看执行计划。用EXPLAIN确认优化器是否做了半连接转换——如果做了，说明优化器已经帮你处理好了；如果没做，再去考虑手动改写。
下次写IN子查询之前，先跑一遍EXPLAIN。看懂优化器在做什么，比背一百条“最佳实践”有用得多。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
