IN子查询性能差的关键在于是否相关、结果集大小及优化器能否走半连接；不满足时会物化临时表或逐行执行，导致全表扫描和N+1问题。
IN子查询不是天生慢，但极易触发物化临时表或逐行执行，导致性能断崖式下跌——关键看它是否相关、结果集大小、以及优化器能否走半连接。
IN子查询为什么会退化成全表扫描
MySQL 5.6+虽支持semi-join优化，但实际是否启用取决于隐性条件。一旦不满足，就会 fallback 到 MATERIALIZED 策略：先完整执行子查询（比如 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)），索引直接失效，物化后连哈希查找都变线性扫描。
相关子查询的N+1陷阱比你想象中更常见
当子查询引用外层字段时（即相关子查询），MySQL 很可能对每一条外层记录都执行一次子查询。例如：
SELECT o.order_id FROM orders o WHERE EXISTS ( SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1 );
这看起来是 EXISTS，但只要子查询里带了 GROUP BY、HAVING、聚合函数，或者关联条件无法下推，优化器就放弃半连接改写，退回到逐行执行。假设 orders 有 100 万行，哪怕每次子查询只耗 0.1ms，总耗时也达 100 秒。
容易被忽略的一点：EXISTS 快，是因为它只找第一条匹配就停；而 IN 子查询默认要生成完整结果集再做查找——即使你只关心“是否存在”，它也照取不误。
用JOIN替代IN时必须守住的三个语义边界
语法替换 ≠ 逻辑等价。漏掉任一，结果就错或更慢：
-
去重控制：原
IN天然去重，而INNER JOIN会因一对多关系放大行数。例如users和orders是一对多，直接JOIN后SELECT users.*会返回重复用户行。必须加DISTINCT或改用EXISTS -
NULL 处理一致性：若子查询可能返回
NULL（如SELECT user_id FROM orders WHERE user_id IS NULL），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 是最稳解法
当应用层传来的 ID 列表动辄几千上万，硬编码进 SQL 已经不是“慢一点”的问题，而是机制性失效：
- 超 1000 个值 →
eq_range_index_dive_limit（默认 200）触发，优化器放弃精确 index dive，改用统计估算 → 执行计划大概率选错 - 超 10000 个值 → 极易超
max_allowed_packet（默认 4MB），报Packets too large错误
推荐做法：把 ID 列表转成一张临时表，再 JOIN 查询：
CREATE TEMPORARY TABLE temp_ids (id BIGINT PRIMARY KEY USING HASH); INSERT INTO temp_ids VALUES (1),(2),...,(999); -- 批量插入，别单条循环 SELECT o.* FROM orders o INNER JOIN temp_ids t ON o.id = t.id;
注意：ENGINE=Memory + PRIMARY KEY USING HASH 是单值查找的 O(1) 保障；每次使用前加 DROP TEMPORARY TABLE IF EXISTS temp_ids，避免连接池复用下残留；别写 SELECT *，字段名冲突时务必加表别名。
复杂点往往藏在细节里：驱动表顺序不对、临时表没建索引、JOIN 条件用了函数或类型不一致——这些都会让本该快的改写反而更慢。
