IN子查询并非天生慢，而是易触发MATERIALIZED或DEPENDENT SUBQUERY导致全表扫描和重复计算；JOIN更可能走高效嵌套循环连接，但需正确改写、索引到位。
IN子查询在MySQL中不是“天生就慢”，而是容易触发MATERIALIZED策略或DEPENDENT SUBQUERY执行路径，导致临时表、全表扫描和重复计算；JOIN则更可能走Index Nested-Loop Join或Block Nested-Loop Join，索引复用率高、IO可控——但前提是改写正确、索引到位。
IN子查询为什么常被优化器降级为全表扫描
MySQL 5.6+虽支持semi-join优化，但实际是否启用取决于多个隐性条件。一旦不满足，就会退化为物化临时表：先执行整个子查询（比如SELECT user_id FROM orders WHERE status = 'paid'），结果存入无索引的内存/磁盘临时表，再对外层每行做哈希查找。此时EXPLAIN里会明确出现：
-
type: ALL或type: index（外层扫描失控） -
Extra: Using where; Using temporary（物化信号） -
rows值远大于实际匹配数（说明扫描了冗余数据）
更隐蔽的问题是：若子查询字段未建索引，或存在隐式类型转换（如orders.user_id是BIGINT，但users.id被写成CAST(id AS CHAR)），索引直接失效，物化后连哈希查找都变线性扫描。
把IN改成JOIN时必须保留的三个语义细节
语法替换 ≠ 逻辑等价。以下三点漏掉任一，结果就可能错或更慢：
-
去重控制：原
IN天然去重，而INNER JOIN会因一对多关系放大行数。例如users和orders是一对多，直接JOIN后SELECT users.*会返回重复用户行。必须加DISTINCT或改用EXISTS -
NULL处理一致性：若子查询可能返回
NULL（如SELECT user_id FROM orders WHERE ...中user_id允许为空），IN会整体判为UNKNOWN，而JOIN自动过滤NULL连接值，行为一致；但NOT IN遇到NULL则恒为FALSE，此时必须用LEFT JOIN ... IS NULL并额外加AND right.user_id IS NOT NULL -
条件下推位置：子查询里的
WHERE条件（如status = 'paid'）不能只留在ON里——它应尽量下推到被驱动表的访问路径上。正确写法是ON u.id = o.user_id AND o.status = 'paid'，而非ON u.id = o.user_id WHERE o.status = 'paid'，否则可能干扰索引选择
JOIN能快的前提：驱动表顺序和索引必须对得上
优化器选错驱动表，JOIN也会比IN还慢。关键看两件事：
-
谁该当驱动表？通常让结果集更小、过滤性更强的表作驱动表。比如
users有50万行，加了vip_level = 3后只剩2千行；而orders有500万行。这时应让users驱动orders，而不是反过来。可用STRAIGHT_JOIN强制，但更推荐靠统计信息和索引引导优化器自动选择 -
被驱动表的连接字段必须有索引：比如
orders.user_id必须有单列索引，或复合索引(user_id, status)（如果status也在条件里）。没有索引，JOIN就退化为BNL甚至Nested-Loop全扫，Extra: Using join buffer就是警告
检查方式很简单：EXPLAIN FORMAT=JSON里找"key": "idx_user_id"和"rows_examined_per_scan"是否接近预期过滤后行数。
真正卡住性能的从来不是IN或JOIN的写法本身
最常被忽略的是索引覆盖与回表成本：即使你把IN完美改成了JOIN，如果SELECT *需要回表取全部字段，而主表又没建覆盖索引，IO照样翻倍。同样，子查询里SELECT id虽快，但JOIN后主表若没idx_id覆盖输出字段，还是得回表。所以别急着改SQL，先看EXPLAIN里的key_len和Extra: Using index有没有出现——这才是决定快慢的临界点。
