MySQL中IN子查询默认物化结果集，易致全表扫描；应改用EXISTS或INNER JOIN，并确保关联字段有索引，否则仍会全表扫描。
IN子查询在MySQL中默认会强制物化子结果集，只要子查询返回行数稍多（比如几千以上），优化器就大概率放弃索引、走ALL——这不是你SQL写得“不熟”，而是MySQL的执行机制决定的。必须改写，没有例外。
为什么EXPLAIN显示type=ALL、key=NULL
MySQL对 WHERE col IN (SELECT ...) 的处理流程是：先完整执行子查询，把所有结果存进临时表（Using temporary），再用外层每一行去匹配这个临时表。一旦临时表溢出内存（tmp_table_size 不够），就会落盘，IO飙升；更关键的是，优化器估算“回表N次”比“扫一次外层表”还贵，于是直接放弃索引，EXPLAIN 中 type 变成 ALL，key 为 NULL。
常见现象包括：
-
EXPLAIN的Extra列出现Using temporary或Using filesort - 子查询本身毫秒级，套上
IN后耗时跳到秒级甚至超时 - 监控里
Created_tmp_disk_tables指标突增
用EXISTS替代IN子查询的硬性条件
EXISTS 是半连接语义，优化器能下推关联条件、“找到一个就停”，不物化全集——但写错一行就退化回原样。
- 原写法：
WHERE col IN (SELECT col FROM t2 WHERE cond)→ 必须改为WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col AND cond) - 原写法：
WHERE col NOT IN (SELECT col FROM t2 WHERE cond)→ 必须改为WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col AND cond)；别用LEFT JOIN ... IS NULL，NULL值会导致漏数据 -
SELECT后面写1或*效果一样，但绝不能写SELECT *——字段膨胀干扰优化器判断 - 确保
t2.col = t1.col中的t2.col有索引，否则EXISTS也会退化为全表扫描
用INNER JOIN替代IN子查询的适用边界
INNER JOIN 适合“取交集”场景（即原 IN，非 NOT IN），优势是能走哈希连接或BNL，中间结果可复用。
- 原语句：
SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE status = 'active') - 正确改写：
SELECT o.* FROM orders o INNER JOIN users u ON o.user_id = u.user_id WHERE u.status = 'active' - 必须用
INNER JOIN，别用LEFT JOIN再加WHERE u.user_id IS NOT NULL——优化器可能无法等价转换，仍走嵌套循环 - 如果
users表很大但status = 'active'结果极少，优先在users(status, user_id)建联合索引，而不是依赖JOIN本身
IN后面是静态值列表时也得小心
当 IN 后是固定值（如 WHERE id IN (1,2,3,...,500)），超过约200项时，MySQL常因代价估算失准而放弃索引。
- 值列表过大时，优先考虑用临时表缓存 +
JOIN，并在临时表上建索引 - 确保
IN左侧字段（如id）有索引；如果是主键，天然满足，但也要确认没被隐式类型转换破坏（比如字符串ID没加引号） - 避免
IN中混入NULL，例如子查询未过滤user_id IS NOT NULL，会导致整个条件失效或结果异常
真正容易被忽略的点是：即使你写了 EXISTS 或 JOIN，只要关联字段缺索引、或子查询里用了函数/表达式，照样全表扫描。优化不是换写法就完事，而是每一步都得用 EXPLAIN 验证执行计划是否真走了索引。
