子查询非天生慢，但易被降级为逐行执行或物化临时表；join更可能走索引嵌套循环，前提为改写正确且索引到位；in子查询常因不满足semi-join条件而物化，导致全表扫描及性能下降。
子查询不是“天生慢”，而是容易被优化器降级为逐行执行或物化临时表；JOIN 更可能走索引嵌套循环（Index Nested-Loop Join），但前提是改写正确、索引到位。
IN 子查询为什么常退化为全表扫描
MySQL 5.6+ 虽支持 semi-join，但实际是否启用取决于隐性条件。一旦不满足，就会触发 MATERIALIZED 策略：先执行整个子查询（如 SELECT user_id FROM orders WHERE status = 'paid'），结果存入无索引的临时表，再对外层每行做线性查找。
EXPLAIN 中典型信号包括：
-
type: ALL或type: index（外层失控扫描） -
Extra: Using where; Using temporary（物化发生） -
rows值远超实际匹配数（说明冗余扫描）
更隐蔽的问题是字段类型不一致：比如 orders.user_id 是 BIGINT，而外层写成 CAST(id AS CHAR)，索引直接失效，物化后连哈希查找都退化为全表扫描。
把 IN 改成 JOIN 时必须保留的三个语义细节
语法替换 ≠ 逻辑等价。漏掉任一，结果就可能错或更慢：
-
去重控制：原
IN天然去重；INNER JOIN因一对多关系（如 users → orders）会放大行数。若只取用户信息，必须加DISTINCT或用EXISTS -
NULL 处理一致性：若子查询可能返回
NULL（如SELECT user_id FROM orders WHERE user_id IS NULL），IN判为UNKNOWN，JOIN自动过滤；但NOT IN遇NULL恒为FALSE，此时必须用LEFT JOIN ... ON ... IS NULL AND right.user_id IS NOT NULL -
条件下推位置：子查询里的
WHERE条件（如status = 'paid'）不能只留在ON子句里——它应下推到被驱动表访问路径上。正确写法是：ON u.id = o.user_id AND o.status = 'paid'
EXISTS 和 JOIN，哪个更适合替代 IN
三者语义不同：IN 和 EXISTS 是存在性判断，JOIN 是关联取值。选型要看真实需求：
- 仅需过滤（如“查有上海订单的用户”）→ 优先试
EXISTS：不受NULL影响，且 MySQL 对非相关EXISTS有较好物化优化；找到第一条即终止，比JOIN构造完整中间结果更轻量 - 需要取子查询字段（如
c.name）、或后续要GROUP BY/ 聚合 → 才真正需要JOIN - 外层表小、子查询表大 →
EXISTS往往更快；反之若子查询结果极小且稳定，JOIN可能更可控
别凭经验猜，用 EXPLAIN FORMAT=JSON 查看是否有 semi_join 或 hash_join 节点——文本版 EXPLAIN 容易误判。
改写后仍慢？大概率卡在索引和执行计划验证上
改写只是第一步，真正决定快慢的是执行路径是否用了索引、是否避免了临时表。重点检查：
- 子查询中用于过滤的字段（如
customers.city）必须有索引，否则JOIN前的右表扫描仍是全表 - 连接字段（如
orders.customer_id和customers.id）两边类型必须严格一致，且至少一边有索引 - 用
EXPLAIN对比改写前后：关注key是否命中索引、rows是否显著下降、Extra是否还有Using temporary或Using filesort
最常被忽略的一点：即使语法改对了，如果子查询本身含聚合、ORDER BY + LIMIT、或用了 STRAIGHT_JOIN，优化器会直接跳过 semi-join 转换——这种时候，JOIN 反而是更稳的选择。
