mysql 5.7中in子句查大量数据变慢源于物化子查询+外层逐行匹配的双重开销；优化需绕过物化、避免嵌套循环、走索引驱动连接，如用带关联条件的exists、规范in列表类型、或采用临时表+join方案。
MySQL 5.7 中 IN 子句查大量数据变慢，不是“写法不对”，而是执行策略本身在特定条件下触发双重开销：子查询被强制物化 + 外层每行都重复执行匹配。优化方向很明确——绕过物化、避免嵌套循环、让优化器走索引驱动连接。
IN子查询为什么在5.7里会反复执行并落盘
MySQL 5.7 对 IN (SELECT ...) 默认启用物化（materialization）策略：先完整执行子查询，把全部结果写入内部临时表；再拿外层每一行去逐个比对。这带来两个致命问题：
- 子查询返回超
tmp_table_size（默认 16MB）时，临时表立刻转磁盘表，Created_tmp_disk_tables指标飙升，I/O 成瓶颈 - 外层主表有 10 万行 → 就触发 10 万次子查询解析 + 执行 + 写临时表，哪怕子查询本身毫秒级也扛不住
-
EXPLAIN里看到type=ALL或Extra含Using temporary，基本就是这个路径
用EXISTS替代IN时必须注意的三个条件
EXISTS 能规避物化，靠“半连接”和短路语义（找到第一个就停），但不是套上就能快：
- 子查询里必须显式关联外层字段，比如
WHERE orders.user_id = u.id，否则退化为非相关子查询，5.7 仍可能物化全集 -
orders.user_id字段必须有索引，否则EXISTS也会变成对外层每行都扫一遍orders表 - 别混淆
NOT IN和NOT EXISTS：前者遇NULL整个条件恒假，后者行为正常；想用NOT EXISTS替代NOT IN，得先确保子查询结果不含NULL，或加IS NOT NULL过滤
IN后面跟几千个ID时，为什么索引突然失效
这不是索引坏了，是优化器主动放弃——它认为回表几千次比扫一次表更贵。关键阈值由 eq_range_index_dive_limit 控制，默认 200：
- 当
IN列表元素数 ≥ 200，优化器改用粗略的索引统计估算成本，容易选错执行计划，key显示为空，type变成ALL - 字段类型不一致会雪上加霜，比如
user_id是BIGINT，却写成WHERE user_id IN ('1','2','3')→ 隐式转换导致索引完全失效 - 前缀索引、函数包裹（如
UPPER(name) IN (...)）、NULL值干扰，都会让优化器绕过索引
临时表+JOIN是处理超大IN列表最稳的方案
当你要查的 ID 列表来自应用侧（比如导出的 5 万个订单号），或子查询本身复杂无法改写为 EXISTS，临时表是最可控的选择：
- 先建带索引的临时表：
CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY) - 批量插入（别用单条
INSERT）：INSERT INTO tmp_ids VALUES (1),(2),(3),...,(50000) - 用
INNER JOIN替代IN：SELECT o.* FROM orders o JOIN tmp_ids t ON o.id = t.id - 如果原逻辑要求去重，记得加
DISTINCT或GROUP BY；LEFT JOIN ... IS NULL仅用于NOT IN场景，且需确认子查询无NULL
临时表方案绕开了优化器对 IN 列表长度的代价误判，也避免了子查询物化开销，但要注意：临时表生命周期只在当前连接内，应用层需保证插入和查询在同一个连接中完成。
