mysql 5.7下in子查询变慢主因是强制物化+嵌套循环双重开销：先全量执行子查询写入临时表，超tmp_table_size即落盘io飙升；外层每行再匹配一次，导致10万行触发10万次执行与写入。
MySQL 5.7 下大规模 IN 子查询变慢，核心不是“语法问题”，而是执行策略强制物化 + 嵌套循环双重开销——外层每行都触发一次完整子查询执行，且结果集全量落盘。
为什么 IN 子查询会生成临时表并频繁刷磁盘
5.7 对 IN (SELECT ...) 默认启用物化（materialization）策略：先完整执行子查询，把所有结果写入内部临时表，再拿外层每一行去匹配。一旦子查询返回超 tmp_table_size（默认 16MB），临时表就转为磁盘表，Created_tmp_disk_tables 指标飙升，I/O 成瓶颈。
- 典型现象：
EXPLAIN中type=ALL、Extra含Using temporary -
NOT IN更危险：只要子查询结果含NULL，整个条件恒为FALSE，优化器直接放弃索引，强制全表扫描 - 即使子查询本身毫秒级，外层 10 万行 = 10 万次子查询解析 + 执行 + 临时表写入
IN 列表过大时 MySQL 直接放弃索引走全表扫描
当 IN 后面跟的是显式值列表（如 WHERE id IN (1,2,3,...,5000)），MySQL 会做代价估算：若值数量超过阈值（通常 200–500），它认为“回表 N 次”比“扫一次表”更贵，于是主动跳过索引，key 显示为空，type 变成 ALL。
- 用
EXPLAIN看key是否为NULL、type是否为ALL或index - 字段类型隐式转换也会触发该退化，例如
INT字段查'123'字符串 - 前缀索引、函数包裹（如
WHERE UPPER(name) IN (...)）同样让索引失效
为什么改写成 EXISTS 或 JOIN 能绕过这些开销
EXISTS 是半连接语义，优化器可下推关联条件，且“找到一个即停”，不物化全集；JOIN 则交由连接算法（BNL、Hash Join）统一调度，避免重复执行。
- 改
WHERE u.id IN (SELECT user_id FROM orders WHERE ...)→WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = u.id AND ...) - 确保
orders.user_id有索引，否则EXISTS也会退化为全表扫描 - 用
INNER JOIN替代IN时，若原IN有去重语义（如子查询含DISTINCT），必须加DISTINCT或GROUP BY，否则结果可能膨胀 - 别用
LEFT JOIN ... IS NULL混搭NOT IN逻辑——NULL 处理行为不等价，极易漏数据
升级到 MySQL 8.0 并不能自动解决这个问题
8.0 的 index_condition_pushdown 默认开启，但只对特定访问类型（range、ref）生效；若子查询仍走 ALL 或 index 扫描，ICP 根本不介入。
- 检查是否真启用：
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%'，返回 1 才算有效 - 看
EXPLAIN的Extra列有没有Using index condition—— 这是 ICP 生效的唯一信号 - 联合索引顺序必须匹配子查询
WHERE条件，比如WHERE status = 'active' AND city = 'sh'，需建(status, city, id)，而非(city, status, id) - 字段类型不一致（如
BIGINT UNSIGNEDvsINT）会让 ICP 自动失效
真正卡住性能的，从来不是“要不要用子查询”，而是“MySQL 5.7 在什么条件下会放弃缓存、放弃索引、放弃下推”。这些机制细节藏在 EXPLAIN 的每一列里，而不是 SQL 写法表面。
