MySQL优化器仅在满足全部条件时启用semi-join：子查询非相关、无聚合/LIMIT/ORDER BY、关联字段有索引、结果集可物化；否则退化为DEPENDENT SUBQUERY；NOT IN因NULL敏感性几乎不改写；验证需用EXPLAIN FORMAT=TREE。
MySQL 优化器不会“主动想把子查询改成 JOIN”，它只在满足特定语义和结构约束的前提下，启用 semi-join 策略，把 IN 或 EXISTS 子查询重写为物理上更高效的执行路径——这本质是执行计划层面的等价替换，不是语法层面的改写。
什么条件下会触发 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，是语义安全的保守选择。
为什么 NOT IN 几乎从不被改写
NOT IN 对 NULL 敏感：只要子查询返回任意 NULL，整个条件恒为 FALSE。而 LEFT JOIN ... IS NULL 无法安全复现该逻辑（它只判断右表无匹配，不关心右表是否有 NULL 值）。所以优化器宁可慢，也不冒错的风险。
同理，含 ORDER BY + LIMIT 的子查询（如“取每个用户的最新订单”）也无法被 semi-join 处理——排序必须在每组内完成，无法批量合并。
如何验证 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，只是类型标签没更新
真正决定是否改写的，从来不是“写法”，而是“能否保证等价”
优化器反复权衡的是：改写后返回的行是否和原 SQL 完全一致？是否仍满足所有约束（NULL 行、重复行、一对多关系）？一旦存疑，就退回到最稳妥的逐行执行。这也是为什么你手动把 IN 改成 JOIN 后结果不对——语义没对齐，优化器不敢动，你也不能乱动。
