做后端这几年，被IN查询“背刺”的次数可不算少。尤其是权限过滤、批量 ID 查询、批量导出这类业务，产品一句话“把选中的 ID 都查出来”，SQL 里就甩进来几百甚至上千个 ID。数据量小的时候，IN用着是真爽；可一旦目标表到了千万级，IN列表超过几百，查询时长直接从毫秒级变成秒级，严重的时候能把从库拖出明显延迟。MySQLIN查询在大数据量业务无法避免的情境下，优化从来不是一句“改成JOIN就行”能糊弄过去的。
这篇文章聊的，就是在“必须用IN、业务不能改、数据量又很大”这种夹缝里，还能做哪些有实际效果的优化。我会先讲清楚IN到底慢在哪里，再给出一套从分片、索引、覆盖索引到临时表JOIN的落地方案，最后用一个批量导出的真实案例串起来。适合正在排查慢 SQL、被 DBA 追问、或者在设计查询方案时想少走弯路的后端同学。
1. 先搞清楚：IN 查询到底慢在哪一步
1.1 慢 SQL 日志里的假象：不要只看扫描行数
很多同学拿到慢 SQL，第一件事是EXPLAIN，看到type=range、rows=几万，就以为问题不大。实际上IN查询最容易骗人的就是这一行rows。
举一个我踩过的例子。业务表orders有 3000 万行，SQL 长这样：
SELECT * FROM orders WHERE user_id IN (1001, 1002, 1003, ... ) -- 几百个 ORDER BY create_time DESC LIMIT 20;EXPLAIN显示type=range，rows=8000左右。当时我也差点被糊弄过去，结果这个查询在从库上跑了 2 秒多。问题出在哪？rows只是优化器估算的“索引扫描范围”，而真正要命的是ORDER BY create_time和LIMIT组合。MySQL 需要把所有命中的行先找出来，再按create_time排序，最后才取 20 条。这个排序过程会用到内存临时表，数据量大一点就会落到磁盘临时表，性能瞬间崩掉。
所以解读IN查询的慢，不要只盯着rows。要看全链路：索引定位、回表次数、排序、临时表、行数放大，这些环节里任何一个都可能成为瓶颈。
1.2 三个容易被忽略的隐形瓶颈
除了排序和临时表，还有三个隐形瓶颈，平时看EXPLAIN看不出来，但实际影响非常大。
第一是回表放大效应。假如IN列表里有 500 个值，每个值都能命中索引，那么就要进二级索引找 500 次位置，再回聚簇索引取 500 次完整行。如果每行数据很宽，或者二级索引区分度不高，回表成本会被成倍放大。这个放大效应呈线性，列表越长越明显。
第二是 binlog 和主从复制放大。在基于行（row）格式的 binlog 下，一条UPDATE ... WHERE id IN (...)或者DELETE ... WHERE id IN (...)，主库执行时会逐行产生 binlog 事件。主库可能 1 秒执行完，从库要回放几万条事件，延迟就出来了。很多同学只测主库耗时，忽略了从库延迟，上线之后才被报警打脸。
第三是优化器对超长IN列表的处理。MySQL 6.0 之前的优化器比较“死板”，列表太长时，它会把这个范围条件展开成一个巨大的range扫描，或者因为代价估算过高而放弃最优索引。虽然 8.0 之后的优化器改进了不少，但列表超过一定长度，执行计划依然可能“抽风”。
2. 先别急着改 SQL：你的业务到底属于哪一种“无法避免”
2.1 多大才算“大数据量 IN”
我自己的经验阈值是这样的：目标表行数过百万，IN列表超过 500，或者EXPLAIN里的估算行数超过全表的 5%，就要高度警惕。
500 这个数字不是拍脑袋拍出来的。它和优化器的一个行为有关：IN列表会被转换成一堆OR条件的范围扫描，列表越长，优化器在评估执行计划时的代价估算越高，越可能出现执行计划抖动。另外，IN列表过长时，整个 SQL 文本会变得很大，极端情况下还会碰到max_allowed_packet的限制。
比列表长度更关键的是筛选项的区分度。IN用的是主键、唯一索引、普通二级索引，还是完全没有索引的普通列？如果是普通列且没有索引，那不管IN写得多短都白搭。如果字段区分度很差，比如状态字段只有两三个值，即使命中行数很少，回表成本和排序成本也一点都不会少。所以在优化之前，先确认索引和区分度，否则后面的方案都建立在沙滩上。
2.2 表面上“无法避免”，其实可以绕开的场景
我见过很多项目，嘴上说着“必须用IN，改不了”，分析一圈之后发现其实是能绕开的。
第一种是多租户权限过滤。这种场景通常可以先按租户和角色，把数据范围“圈定”成一个更小的集合，再通过JOIN去关联过滤，而不是一上来就用一个大IN列表去WHERE里硬筛。
第二种是大量 ID 来自外部接口。比如上游系统给你返回了 5000 个 ID，业务上必须用这 5000 个 ID 去本地库查明细。这种完全可以先把 ID 落地成本地临时表，再JOIN，而不必在 SQL 里塞一个巨型IN列表。
第三种是分页查询的 ID 集合。很多时候，业务先查出符合条件的 ID 分页，然后拿着当前页的 ID 去查完整数据。这种场景适合用延迟关联，先只查主键 ID 分页，再回表组装数据。
IN本身不是原罪，损耗出在“使用方式”上。上面这三种场景，优化思路其实是“改变数据流向”，而不是硬扛IN。
2.3 真正躲不掉 IN 的典型业务长什么样
真正无法避免的IN，通常同时满足这么几个条件：ID 列表来自用户选择或上游系统，业务上必须按这个集合精确过滤；集合本身很大，比如批量审核、批量导出、标签人群圈选；而且无法用JOIN或临时表替换，比如上游数据不在本库、格式频繁变化、不想引入额外表结构。
这种时候，优化方向就要变一变：不是“不用IN”，而是“让IN查询尽量少吃资源、少放大回表、少制造临时表”。下面的优化手段，就是围绕这个目标展开的。
3. 直接能落地的优化手段：分片、索引、临时表 JOIN
3.1 分片 IN：把大 IN 拆成小批，最朴素也最有效
分片IN是我在实战里用得最多、见效最快的一招，没有之一。做法很简单，把一个大IN列表拆成若干个几百一批的小IN，分批查询，最后在业务层合并结果。
我一般把批量大小控制在 500 到 1000 之间。为什么是这个区间？主要是让优化器处理起来更舒服，执行计划更容易走range，回表次数和临时表压力都能显著下降。分片大小不用死记，线上压测一下就能找到自己的阈值。
一个简单的分片查询模板：
def batch_in_query(conn, id_list, batch_size=500): result = [] for i in range(0, len(id_list), batch_size): batch = id_list[i:i + batch_size] placeholders = ",".join(["%s"] * len(batch)) sql = f"SELECT * FROM orders WHERE user_id IN ({placeholders})" result.extend(conn.execute(sql, batch)) return result这里要注意：如果原始 SQL 里有ORDER BY和LIMIT，分片之后不能直接拼结果，因为每片各自排序、各自LIMIT，整体结果就错了。正确做法是每片只查候选数据，在业务内存里做全局排序和截断。如果数据量实在太大，内存排序也扛不住，就用UNION ALL把各片查出来再统一排序，但UNION ALL也可能引入临时表，需要压测验证。
分片带来的额外好处是：单次查询的锁范围更小、错误重试粒度更小、对主库和从库的压力更平滑。缺点是代码稍微复杂一点，需要多几次网络往返。为了抵消这点，可以用并发，但要克制，一般并发数别超过 4，否则就是把一个慢查询变成多个快查询，总资源开销实际上可能更高。
3.2 让 IN 查询走对索引：复合索引与 ICP
分片是“减量”，索引是“定向”。IN查询能不能快，索引的设计非常关键。
先说复合索引。如果你的 SQL 是WHERE status IN (...) ORDER BY create_time DESC，那么建立一个(status, create_time)的复合索引，就能让 MySQL 在索引内部完成排序，避免filesort。这是最典型的“索引对齐排序键”的用法。如果漏了create_time这一列，即使status的IN筛选很快，最后的排序照样会把性能拖下来。
再看 ICP，也就是 Index Condition Pushdown，索引条件下推。MySQL 5.6 之后默认开启，它能把IN条件直接下推到存储引擎层，在读取索引的时候就过滤掉不符合条件的记录，减少回表。判断方法很简单，看EXPLAIN的Extra列，如果出现Using index condition，说明 ICP 生效了。如果你的表引擎和版本都支持，通常不需要额外配置，但要注意不要在IN列上套函数，比如WHERE DATE(create_time) IN (...)，一旦套了函数，索引就废了。
复合索引也不是越多越好。每个索引都要占空间、拖慢写入。建索引之前先看看这个表是不是写多读少，如果是线上高并发写入的表，那么宁愿花点功夫做分片和延迟关联，也不要为每一个查询单独建一个宽索引。
3.3 覆盖索引和延迟关联：让查询尽量别回表
覆盖索引是容易被低估的救星。简单说，如果查询需要的所有列都在二级索引里，那么 MySQL 根本不用回表，直接在索引上就能拿到全部数据。EXPLAIN的Extra列显示Using index，就是覆盖索引生效。
举个例子，批量导出时经常只需要id, user_id, order_no, create_time这几列，那么建一个(user_id, create_time, id, order_no)的复合索引，查询就直接在索引里完成，回表次数降为 0。注意不要为了覆盖而把大量文本字段塞进索引，那会让索引页过大、占用大量内存，反而拖慢全局性能。
延迟关联则是另一种思路。当业务确实需要查出完整行数据，但排序和过滤又很重时，可以先只查主键 ID：
-- 原始写法（慢） SELECT * FROM orders WHERE user_id IN (...) ORDER BY create_time DESC LIMIT 50; -- 延迟关联写法（快） SELECT t.* FROM orders t JOIN ( SELECT id FROM orders WHERE user_id IN (...) ORDER BY create_time DESC LIMIT 50 ) tmp ON t.id = tmp.id ORDER BY t.create_time DESC;子查询里只查id和排序列，可以走覆盖索引，先拿回 50 个主键 ID，再回表取完整行。这个时候回表只有 50 次，而不是原来的一次性几百上千次，效果立竿见影。
3.4 临时表 JOIN 替代大 IN：从集合运算的角度破解
如果IN列表大到一个离谱的程度，比如上万甚至几万，那分片和索引都还不够，我推荐用临时表JOIN来替代。这个方案的最强形态，是让 MySQL 把 ID 列表当成一张小表，和业务大表做JOIN，由优化器决定驱动顺序和连接算法。MySQL 8.0 之后支持 hash join，对这种场景尤其友好。
具体步骤：
- 建一张临时表，比如
tmp_ids，字段就是id，最好加主键索引。 - 把业务拿到的 ID 列表批量插入这张临时表。插入方式用
INSERT INTO ... VALUES (...), (...), ...，几千条一批，或者用客户端 load data。 - 执行业务查询，把
WHERE id IN (...)改成JOIN tmp_ids t ON t.id = o.id。 - 用完就
DROP TEMPORARY TABLE，避免影响后续连接。
临时表方案为什么能赢？因为一个个走索引定位的IN是重复随机 IO，而JOIN可以让优化器选择把小表作为驱动表，用 hash join 或 block nested loop 做一次扫描匹配，整体 IO 更有规律。尤其是当驱动表小、被驱动表大且连接字段是主键时，性能差距会非常明显。
但临时表方案不是银弹。临时表如果太大，超过tmp_table_size和max_heap_table_size的限制，MySQL 会把内存临时表转成磁盘临时表，性能照样崩。所以无论IN还是临时表，都要控制单批数据量，必要时分批处理。另外，MySQL 8.0 里可以用EXPLAIN FORMAT=TREE看执行计划是否真的用了 hash join，不要凭感觉。
3.5 业务缓存兜底：减少同样的“大 IN”反复执行
技术手段都做完了，还有一个容易被忽略的层面：业务重复查询。很多慢 SQL 之所以可恨，是因为同样的IN列表反复出现，每次都把数据库拖下水。比如标签系统里，同一批人群 ID 被不同的报表反复查询；权限系统里，同一组角色 ID 被多个请求反复过滤。
这种场景适合加一层业务缓存，把“ID 集合 + 版本号”缓存起来，配合合理的 TTL，在版本号变化时重建缓存。比如用 Redis 缓存一份查询结果或者 ID 集合，下次同样的IN进来时直接命中缓存，数据库连看都不用看。
要提醒的是，缓存解决的是“重复的大查询”，解决不了“真正的大数据量查询”。如果每次请求的 ID 列表都不同，缓存意义不大。另外，别尝试缓存全表数据，那会把内存打爆；缓存“结果集 + 版本号”这种细粒度数据就好。
4. 一个真实案例：批量导出场景的 IN 查询优化
4.1 背景和原始 SQL
这个案例是我在实际项目中处理过的一个批量导出需求。业务场景是后台管理员选了一批用户 ID，按 ID 列表导出订单明细，一次最多选 5000 个 ID。订单表有 3000 万行，SQL 大概是这样的：
SELECT * FROM orders WHERE user_id IN (…5000 个 ID…) AND create_time BETWEEN '2023-01-01' AND '2023-06-30' ORDER BY id;这个查询在从库上要跑 2 到 8 秒，而且并发一高，从库延迟就飙升。一开始 DBA 给的建议是加索引，但加了(user_id, create_time)索引后，效果十分有限，该慢还是慢。
4.2 执行计划诊断：问题到底在哪
EXPLAIN的结果是这样的：type=range，key=idx_user_create，rows显示 12 万，Extra里有Using index condition; Using filesort。
看到Using filesort，我基本就锁定了核心矛盾。5000 个IN值，每个值都需要到二级索引定位，再回表取完整行；取完 12 万行之后，还要在临时表里按id排序，最后才输出。回表 12 万次，加上 12 万行的排序，这个成本是非常可观的。
单纯的索引已经救不了它，因为问题不只是字段上没索引，而是“回表次数”和“结果集处理方式”都出了问题。
4.3 优化方案组合：分片 + 覆盖索引 + 内存排序
针对这个案例，我用了三招组合。
第一招是分片。把 5000 个 ID 拆成每批 500 个，一共 10 批。每批的索引定位次数从 5000 降到了 500，单次查询的压力立刻下来。
第二招是覆盖索引。导出业务其实只需要id, user_id, order_no, create_time, amount这几个字段。我调整了查询，让每批只查这几列，而不是SELECT *，这样查询可以在覆盖索引内完成，回表次数降为 0。建一个合适的复合索引来覆盖查询和排序列。
第三招是内存排序。由于导出的最终结果要按照id排序输出，而分片后每批只能保证局部有序，所以最后在导出服务的内存里做一次全局排序，再落盘或写 CSV。内存排序对 10 批、每批几百上千条结果来说毫无压力。
优化后的结果是：单批查询耗时 50 毫秒左右，10 批加上内存排序，总耗时不到 300 毫秒，主库和从库的压力都明显下降。这个效果比单纯加索引好了一个数量级。
4.4 给类似导出场景的参考模板
导出类业务有一个通用模板，可以套用：
- 先明确最终需要哪些字段，别一股脑
SELECT *。 - 根据字段设计覆盖索引，尽量让查询在索引里完成。
- 把大
IN拆成每批 500 到 1000。 - 每批只查必要字段，必要时延迟关联回表。
- 结果在业务侧合并、排序、分页。
- 如果业务允许异步，把导出任务丢到队列，避免用户长时间等待。
这套模板不复杂，但每一步都是在减少数据库需要处理的数据量，属于“积小胜为大胜”的思路。
5. 常见问题与排查技巧实录
5.1 IN 列表到底多长应该分片?别信网上的阈值
网上经常看到“超过 1000 必须分片”之类的说法，说实话意义不大。分片的阈值和表结构、数据量、索引设计、服务器配置都有关系，真正靠得住的判断方法，是实测。
用EXPLAIN ANALYZE（MySQL 8.0.18+）或者performance_schema里的语句统计信息，对比一下 500、1000、2000 三档列表长度下的执行时间和资源消耗。哪个长度出现耗时拐点，就把分片阈值定在那里。我在不同项目里定过的最优值有 300 的，也有 1500 的，完全不一样。
除了耗时，还要看一个容易被忽略的指标：临时表落盘次数。如果EXPLAIN里显示Using temporary，且状态变量里Created_tmp_disk_tables在上涨，说明临时表已经转磁盘了，这时候就该缩小分片或者调整排序方式。
5.2 IN 和 EXISTS、JOIN 能互相替换吗
IN、EXISTS、JOIN这三者在语义上并不完全等价，但 MySQL 8.0 的优化器已经足够聪明，很多时候会自动做子查询转换。8.0.16 之后，优化器会把IN子查询改写成半连接（semijoin），执行计划里能看到相关提示。
这里要特别提醒一句：如果IN列表不是来自一个常量列表，而是来自子查询，比如WHERE user_id IN (SELECT id FROM vip_users WHERE level = 1)，这时候优先考虑改写成JOIN或EXISTS。因为子查询结果可能很大，MySQL 会把子查询结果物化成一张临时表，物化过程本身就是一大开销。
最理想的写法是明确告诉优化器你的数据关系。比如子查询返回的结果集很大，但过滤后很小，可以先JOIN；如果子查询很快、外层表很大，且只要能判断存在性，就用EXISTS。这需要结合实际执行计划判断，不要背口诀。
5.3 参数配置和连接层还有哪些坑
第一个坑是max_allowed_packet。当IN列表特别长，预编译 SQL 语句本身就可能超过这个限制，客户端会直接报错。解决方法要么改大这个参数，要么分片。从稳定性角度，我更推荐分片，因为你永远不知道业务下次会传进来多少个 ID。
第二个坑是临时表相关的参数。MySQL 8.0 默认内存临时表引擎是TempTable，tmp_table_size和max_heap_table_size控制内存临时表大小上限。查询里如果有ORDER BY、GROUP BY、DISTINCT或UNION，并且结果集超出限制，就会转磁盘。优化时可以适当调大这两个值，但要清楚这是用内存换性能，内存不是无限的。
第三个坑是连接池的查询超时。有些框架默认查询超时只有几秒，大IN查询一旦超过就会抛异常，表现为“时好时坏”。排查慢 SQL 时，不要只盯着数据库，客户端的超时配置也要一起看。
5.4 大数据量 IN 更新和删除，比查询更危险
最后重点讲一个大家都在踩、但很少被写进文档的坑：对大数据量IN做UPDATE或DELETE。
查询再慢，顶多拖慢一条链路；但更新和删除会锁行、会产生大量 binlog、会拖慢从库。我处理过一次事故：一条DELETE FROM t WHERE id IN (...)在主库执行只要 5 秒，结果从库延迟了 20 分钟，线上读请求直接雪崩。
正确的姿势是分批、带条件、逐步推进。比如：
-- 每次只删 1000 行，避免大事务 DELETE FROM t WHERE id IN (…) AND id > last_id ORDER BY id LIMIT 1000;然后在事务里循环执行，直到影响行数为 0。这样每一批的锁范围小、binlog 事件少，从库压力平滑。如果业务允许，还可以在低峰期执行，或者用异步队列慢慢清理。
看到这里你会发现，IN查询优化没有一招鲜，关键在于把问题拆开：是回表太多，还是排序太重，或者是写放大太吓人？我个人的体会是，数据库优化最忌凭感觉改 SQL，一定要先用EXPLAIN ANALYZE和状态变量把耗时分布看清楚，再决定分片、索引、临时表还是缓存。分片、覆盖索引、延迟关联这三个组合，已经能覆盖绝大多数“无法避免的大 IN”场景。最后分享一个小习惯：每次给IN查询做完优化，我会把优化前后的执行计划和耗时记录在案，下次再有人质疑“为什么不一次查完”，直接拿数据说话。这样既是对别人负责，也是在给自己画一条安全边界。