in子查询性能差源于mysql执行机制强制物化整个子结果集，not in更因null值导致逻辑错误和索引失效；优化首选exists或join替代，避免临时表和全表扫描。
IN子查询在大数据量下性能差，不是因为“写法不优雅”，而是MySQL执行机制导致它必须物化整个子结果集——哪怕你只关心“是否存在”。直接改写为EXISTS或JOIN是最有效、最可控的解法。
为什么IN子查询会生成临时表？
MySQL 5.7 及之前版本对IN子查询（尤其是NOT IN）默认采用“物化策略”：先执行子查询，把全部结果存进内部临时表，再逐行匹配外层。当子查询返回几十万行时，这个临时表极易溢出内存（tmp_table_size限制），转成磁盘表，IO飙升。
更关键的是：NOT IN (subquery)遇到子查询结果含NULL，整个条件恒为FALSE——这不仅逻辑危险，还会让优化器放弃索引使用，强制全表扫描。
常见错误现象：
-
EXPLAIN显示type=ALL且Extra含Using temporary - 子查询本身很快（NOT IN后查询耗时跳到数秒甚至超时
- 监控发现
Created_tmp_disk_tables指标突增
用EXISTS替代IN子查询的关键点
EXISTS是半连接语义，优化器能将其下推、关联驱动，且“找到一个就停”，不物化全集。但要注意写法细节，否则可能白改。
实操建议：
- 把
WHERE col IN (SELECT col FROM t2 WHERE ...)→ 改为WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col AND ...) - 把
WHERE col NOT IN (SELECT col FROM t2 WHERE ...)→ 必须改为WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col AND ...)，不能用LEFT JOIN ... IS NULL混用（易漏NULL） - 子查询里
SELECT后面写1或*没区别，但别写SELECT *——避免字段膨胀干扰优化器判断 - 确保关联条件（如
t2.col = t1.col）中，t2.col有索引；否则EXISTS也会退化为全表扫描
用JOIN替代IN子查询的适用边界
JOIN适合“取交集”场景（即原IN，非NOT IN），优势是能走哈希连接或BNL，且结果可复用中间态。但它不是万能的——尤其当外层数据量远大于内层时，JOIN可能放大结果集。
实操建议：
- 原语句：
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本身 - 注意
DISTINCT问题：原IN天然去重，而JOIN可能因一对多产生重复行，必要时加GROUP BY或DISTINCT
eq_range_index_dive_limit参数对IN性能的真实影响
这个隐藏参数（默认200）决定了MySQL如何估算IN查询成本：值少于200，走精确的索引树遍历（index dive）；≥200，则用粗略的索引统计（index statistics）。后者快但不准，常导致选错执行计划——比如该走索引却选了全表扫描。
这不是调大就能解决的问题。实测中，把eq_range_index_dive_limit从200调到2000，某些IN(1000个ID)查询反而更慢，因为统计偏差放大了。
所以真正可靠的路径只有一条：别依赖IN扛大数据量，该拆就拆，该换就换。临时表、分批、EXISTS、JOIN——选哪个不重要，重要的是跳出“IN必须存在”的思维定式。
