mysql 5.6+ 半连接优化仅在子查询非相关、无聚合/limit/order by、关联字段有索引且结果集可物化时自动将in/exists转为join；否则退化为dependent subquery，可通过explain format=tree验证。
MySQL 优化器不是“主动想改写”，而是当子查询结构满足特定条件时，它会启用内置的半连接（semi-join）策略，把 IN 或 EXISTS 自动重写为物理上更高效的 JOIN 执行路径——这本质上是执行计划层面的优化，不是语法替换。
MySQL 5.6+ 的 semi-join 优化何时生效
这个改写只在满足全部以下条件时才可能触发：
- 子查询是非相关或弱相关：即关联条件仅出现在
WHERE中（如t1.id = t2.t1_id），不参与GROUP BY、ORDER BY、LIMIT、UNION - 子查询不含聚合函数（如
COUNT(*)）、无副作用（如NEXTVAL）、不引用多层外层别名 - 关联字段有可用索引：比如
orders.user_id和users.id都建了索引，否则优化器无法下推条件或选择哈希连接 - 子查询结果集不大或可物化：优化器会评估成本，若预估物化临时表比嵌套循环更优，才会走
Materilize或DuplicateWeedout策略
不满足任一条件，优化器就会放弃改写，老老实实执行 DEPENDENT SUBQUERY ——这不是 bug，是语义安全的保守选择。
如何验证 semi-join 是否真的发生了
不能只看 EXPLAIN 的传统输出，得用新版诊断方式：
- 运行
EXPLAIN FORMAT=TREE SELECT ...，搜索输出中是否出现Semi-join、Materilize、DuplicateWeedout等关键词 - 对比加提示禁用后的差异：
SELECT /*+ NO_SEMIJOIN() */ ...，再跑一次FORMAT=TREE，观察执行结构是否退回嵌套循环 - 注意
EXPLAIN中的select_type：如果仍是DEPENDENT SUBQUERY，但type是ref且Extra显示Using where; Using join buffer，大概率已走 semi-join，只是类型标签没更新
为什么 NOT IN 很少被改写为 JOIN
NOT IN 在语义上对 NULL 敏感：只要子查询返回任意 NULL，整个条件恒为 FALSE。而 LEFT JOIN ... IS NULL 无法安全复现该逻辑（它只判断右表无匹配，不关心右表是否有 NULL 值）。所以优化器几乎从不尝试改写 NOT IN，宁可慢，也不冒错的风险。
同理，含 ORDER BY + LIMIT 的子查询（如“取每个用户的最新订单”）也无法被 semi-join 处理——排序必须在每组内完成，无法批量合并。
真正决定是否改写的，从来不是“写法”，而是“能否保证等价”
优化器不会为了快而牺牲正确性。它反复权衡的是：改写后返回的行是否和原 SQL 完全一致？是否仍满足所有约束（NULL 行、重复行、一对多关系）？一旦存疑，就退回到最稳妥的逐行执行。这也是为什么你有时看到 EXPLAIN 里明明有索引，却还是 DEPENDENT SUBQUERY——不是优化器懒，是它不敢动。
