IN子查询易触发全表扫描，因MySQL强制物化子结果集且优化器代价估算失准；应改用EXISTS或INNER JOIN，NOT IN必须替换为NOT EXISTS。
IN子查询在MySQL中极易触发全表扫描，不是因为写法“不规范”，而是执行机制强制物化子结果集 + 优化器代价估算失准。直接改写为 EXISTS 或 INNER JOIN 是唯一可控、见效快的解法。
为什么IN子查询会放弃索引走ALL？
MySQL对 IN (SELECT ...) 的默认策略是“物化子查询”：先执行子查询，把全部结果存进临时表，再逐行匹配外层。当子查询返回数万行时，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子查询的边界条件
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常因代价估算放弃索引。
- 单次
IN列表严格控制在200项以内 - 超限时拆成多个查询并用
UNION ALL合并，或写入临时表后JOIN - 确保
IN字段类型与列表值严格一致，避免隐式转换（比如字符串ID写成数字，或带空格） - 字段必须有有效索引——注意前缀索引、NULL值、函数包裹（如
WHERE UPPER(name) IN (...)）都会让索引失效
真正容易被忽略的点是：NOT IN 遇到子查询含 NULL，整个条件恒为 FALSE，不仅逻辑错误，还会让优化器彻底放弃索引。这不是配置问题，是SQL语义本身决定的——所以永远不要用 NOT IN，一律换成 NOT EXISTS。
