如果你维护过一套 MySQL 业务系统大概率见过这种 SQLSELECT * FROM user WHERE id IN (123, 456, 789...)。几十个 ID 的时候查询可能毫秒级返回但 ID 列表一旦从几百涨到几千甚至几万接口会突然卡住慢日志里飘红。我第一次遇到这个问题时条件反射地抱怨业务不想办法把 ID 分批传进来后来跟业务同学对需求才发现那一批 ID 是推荐系统实时计算出的候选集合必须先全部拿到才能做后续筛选。这其实就是标题说的“大数据量 IN 查询且业务无法避免”的场景。这篇文章我会把这类场景下我试过、验证过的优化手段都摊开讲清楚从执行原理到改造方案再到一次生产事故复盘适合正在被慢 SQL 困扰的后端开发、DBA 和对 IN 查询机制感兴趣的读者。1. 为什么几千个 ID 的 IN 查询会突然把数据库打挂1.1 IN 查询的执行计划是怎么生成的MySQL 处理IN时本质上会把列表展开成一组等值条件的组合类似于把id IN (1,2,3)转换成id 1 OR id 2 OR id 3。如果字段上有索引优化器理论上会走索引扫描多个等值区间执行计划常见的是range类型ID 少的时候效率很高。问题出在列表数量变大之后。你可以把它想象成去超市购物购物清单上只有 3 样东西很快就能买完但清单上列了 3000 样东西你需要在货架之间反复折返而且还没出门可能就要花十分钟把清单完整读一遍。数据库也一样IN里的每个值都会参与优化器的成本评估列表越长解析 SQL、构造执行计划、访问索引的成本就越高。更麻烦的是MySQL 优化器在遇到大量等值条件时并不是每一个值都会精确探测索引而是会走到一套估算逻辑上这套逻辑一旦失真就可能直接放弃索引退化成全表扫描。另外一个容易被忽略的点是很多大数据量 IN 查询并不是 SQL 里优雅地写着子查询而是业务代码把几万个 ID 拼成一个长字符串塞进 SQL。这样的 SQL 文本可能达到几百 KBMySQL 需要先完整解析这个巨大的语法树然后才能开始优化。你说它不是慢在查询本身而是慢在“读懂”这句话上其实也成立。1.2 两个经常被忽略的隐藏参数eq_range_index_dive_limit 和 max_allowed_packeteq_range_index_dive_limit是 MySQL 优化器的一个阈值参数默认值是 200。它的含义是当等值条件的数量超过这个值时MySQL 不再对每个索引区间做精确的“潜入式”估算index dive而是改用索引统计信息来估算行数。这个设计本来是为了降低优化器成本但代价是估算精度下降。举个例子某个索引的统计信息已经滞后实际数据分布又非常不均匀优化器可能把几万行的 IN 结果估算成几百行于是选择了全表扫描也可能把几百行估算成几百万行选择了一个错误的索引。很多慢 SQL 排查到最后都会在这个参数上栽跟头。所以我在优化大数据量 IN 查询时会刻意把每批 ID 控制在 200 个以内就是为了尽量让优化器走精确估算路径。max_allowed_packet是另一个坑。它限制的是单个网络数据包的大小默认可能只有 4MB 或 64MB取决于你的版本和配置文件。几万个 ID 拼成的 SQL 文本往往能到几百 KB虽然不一定超过 4MB但加上协议头、参数绑定、网络传输会让包变得很大。如果超过限制数据库会直接报错断开连接表现为接口突然 500而不是慢查询。1.3 用 EXPLAIN 判断是不是真的慢在 IN 上拿到一条慢 IN 查询后不要急着改 SQL先执行 EXPLAINEXPLAIN SELECT * FROM user WHERE id IN (1,2,3,...,5000)\G主要看四个字段type如果出现ALL说明在做全表扫描这是大 IN 列表最常见的灾难现场。key为NULL说明没有使用任何索引possible_keys里即使有索引也可能被优化器放弃。rows优化器估算扫描的行数如果接近整个表的行数基本可以判定执行计划失败。Extra如果出现Using filesort或Using temporary说明后续还要做额外排序或临时表性能会进一步恶化。我曾经见过一条大 IN 查询type显示ALLrows显示 500 万但业务表总共也就 500 万行。这种情况再怎么调 SQL 参数都没用核心问题是优化器选择了错误的路径。2. 先分清哪个是能绕开的场景2.1 查询直接来自另一张表能绕开就绕开如果 IN 列表是通过子查询从另一张表生成的那这就算不上“无法避免”的业务场景。比如下面这个 SQLSELECT * FROM user WHERE id IN (SELECT user_id FROM order_detail WHERE status 1);完全可以改写成 JOINSELECT DISTINCT u.* FROM order_detail d JOIN user u ON u.id d.user_id WHERE d.status 1;这种改写等于把“先查出一批 ID再回到主表过滤”变成了“从子表出发直接关联主表”。数据库可以选择先访问order_detail再根据索引回表取user数据完全绕开大 IN 列表的存在。如果担心重复数据可以在 SELECT 加 DISTINCT或在外层 GROUP BY 主键。相比让优化器去猜测几万个 IN 值的执行路径JOIN 的语义更明确执行计划也更稳定。2.2 查询来自外部系统或实时计算结果这类真的绕不开更多时候ID 集合不是 SQL 子查询能推导出来的而是应用层从其他服务拿到的结果。比如推荐系统算出的候选集、数据中台下发的用户名单、搜索引擎返回的商品 ID 列表。这些 ID 在上游已经生成了业务必须原封不动地交给数据库层做二次过滤这就是典型的“无法避免的大数据量 IN 查询”。这种场景下你不能简单改写 JOIN因为数据库里没有一张表可以天然生成这批 ID。你能做的是在拿到这批 ID 之后用更合理的方式把它喂给 MySQL。后面章节说的分批、临时表 JOIN主要针对的就是这种业务形态。2.3 一次“人群圈选”查询的典型形态运营后台经常有“标签圈选人群”的功能运营选了三个标签系统要把同时满足这三个标签的所有 user_id 查出来再回到 user 表里取用户姓名、手机号、状态等字段。这个 user_id 集合可能是几千、几万而且必须一次性展示给前端或者用于后续批量触达。再比如批量导出功能用户在页面上勾选了几万条订单点击导出时代码把选中的订单 ID 拼成一个大 IN 查询。业务上你就是不能漏一条也舍不得做成分页查询因为需要一次性导出全量数据。这种需求下大 IN 是真的绕不开。结论是优化之前先做一次“能不能改写为 JOIN”的判断。如果 IN 列表能通过另一张表的过滤条件推导出来那就别犹豫优先改 JOIN如果确实是从外部传入的散装 ID再去走分批、临时表这些方案。3. 不改数据模型的三种提速改造方案3.1 分批 IN 并发控制最简单的方案是把大 IN 切分成多个小 IN在应用层并发查询最后合并结果。ListListLong batches Lists.partition(userIds, 200); ExecutorService pool Executors.newFixedThreadPool(8); ListFutureListUser futures new ArrayList(); for (ListLong batch : batches) { futures.add(pool.submit(() - userMapper.selectByIds(batch))); } ListUser result new ArrayList(); for (FutureListUser future : futures) { result.addAll(future.get()); }为什么推荐每批 200 个前面说过eq_range_index_dive_limit默认是 200控制在这个范围内优化器更可能走精确估算路径SQL 文本也不会太长。但分批方案有代价多次查询之间不是一个一致的快照如果数据在查询过程中发生变更结果会有偏差并发数也可能打满数据库连接池。我的实践体会是这个方案适合 ID 总量在几万以内、对一致性要求不高的查询。如果你的业务对数据一致性要求很高或者一次要处理几十万 ID最好不要用分批而是往前面的临时表 JOIN 方案走。3.2 临时表 JOIN让优化器回到索引查找的舒适区临时表 JOIN 是我处理大 IN 查询时最推荐的一种方式核心思路是不把几万个 ID 塞进一个长 IN 列表而是先把 ID 写入一张临时表再用 JOIN 让数据库通过主键等值连接来精确取数。-- 1. 创建临时表主键同时用来去重 CREATE TEMPORARY TABLE tmp_ids ( id BIGINT NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB; -- 2. 批量写入 ID建议每 500 个一组多 VALUES 插入 INSERT IGNORE INTO tmp_ids (id) VALUES (1), (2), ..., (500); -- 3. 与原表 JOIN让数据库按主键精确查找 SELECT u.id, u.name, u.mobile FROM tmp_ids t INNER JOIN user u ON u.id t.id WHERE u.status 1;为什么这样更快临时表里有主键JOIN 时优化器可以稳定地走主键等值访问执行计划通常是eq_ref每一行都能通过聚簇索引精确定位。而长 IN 列表会让优化器陷入成本估算的泥潭临时表 JOIN 等于直接把一条高效路径喂给了优化器。需要注意的是临时表是会话级别的使用完毕要尽快DROP TEMPORARY TABLE避免连接池复用连接时留下垃圾数据。如果 ID 可能有重复就用INSERT IGNORE配合临时表的主键约束自动去重。如果 ID 量特别大可以先不建主键批量插完数据之后再ALTER TABLE tmp_ids ADD PRIMARY KEY (id)这样插入过程不会被索引维护拖慢。3.3 子查询改写从 IN 到 EXISTS 或 JOIN针对WHERE id IN (SELECT ...)的场景MySQL 会尝试把子查询改写成半连接semi-join但优化器并不总是选择最优方案有时候它会先物化子查询结果生成一个内部临时表再从主表去探针匹配。如果这个内部临时表没有合适的索引性能就会很差。这时可以手动改写为 EXISTSSELECT * FROM user u WHERE EXISTS ( SELECT 1 FROM order_detail d WHERE d.user_id u.id AND d.status 1 );EXISTS 会从外层 user 表逐行驱动判断每个用户是否有满足条件的订单。如果 user 表很大且大部分用户都有订单EXISTS 不一定比 JOIN 快但如果我们能从业务侧确定驱动表的过滤条件能显著缩小范围这个方案会非常稳定。相比之下改写为 JOIN 通常更直接SELECT DISTINCT u.* FROM order_detail d JOIN user u ON u.id d.user_id WHERE d.status 1;JOIN 可以借助 order_detail 上的条件过滤选择以订单表为驱动表再回查 user 表。实际改的时候光看经验判断没用必须用 EXPLAIN 对比改写前后的rows、type和Extra以实际执行计划为准。4. 数据模型层面的根治让 IN 查询不再需要存在4.1 把 ID 集合物化成一张可索引的映射表如果某类大 IN 查询高频出现不能每次都从应用层传几万个 ID 进去。一种更治本的做法是把“ID 集合”提前落库变成一张带索引的中间表。举个例子运营圈选完成后可以把圈选结果写入一张关联表CREATE TABLE campaign_user_map ( campaign_id BIGINT NOT NULL, user_id BIGINT NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (campaign_id, user_id), KEY idx_user_id (user_id) ) ENGINEInnoDB;查询时就不再需要 IN 列表了直接 JOIN 这张表SELECT u.* FROM campaign_user_map m INNER JOIN user u ON u.id m.user_id WHERE m.campaign_id 12345;这样做的价值在于几千上万行的 ID 集合变成了一张带索引的物理表。MySQL 可以根据campaign_id快速找到对应集合然后通过主键等值回表查 user 信息。数据量越大这个方案的优势越明显。当然它带来的是数据写入成本的增加。每次圈选集合更新时需要在事务里先 DELETE 旧数据再 INSERT 新数据但对比查询接口动不动慢几秒这点写入成本通常完全可以接受。4.2 用聚合表和冗余字段绕开“大批量集合”这个魔咒有些大 IN 查询并不是真的要取明细数据而是要对一批 ID 做统计。比如“按一批部门 ID 查员工总数”“按一批商品 ID 查累计销售额”。这类场景可以维护一张聚合表在写入的时候实时累加查询时直接对聚合表做少量行扫描。假设有一个商品日汇总表CREATE TABLE product_daily_summary ( product_id BIGINT NOT NULL, stat_date DATE NOT NULL, sale_amount DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (product_id, stat_date) ) ENGINEInnoDB;要查一万个商品在某天的销售额总和直接SUM(sale_amount) WHERE product_id IN (...)可能还是很大的查询但如果把这一万个商品再进一步聚合成一张批次汇总表或者把结果写入缓存查询压力会小很多。聚合表和冗余字段的核心思想和数据库设计里的反范式一样明确什么数据是可以提前算好的。能用预计算解决的事不要在查询时临时算。4.3 冷热分离和归档别让大数据量表自身卡住索引数据量太大的表即使走临时表 JOIN也可能因为索引树过高、回表路径太长而变慢。这里有一个很现实的优化方向把历史数据归档缩小热表体积。很多业务表里真正频繁查询的是最近半年或一年的数据老数据只是躺在那里占空间。可以按时间维度做冷热分离把超过一定时间的数据迁移到历史库或归档表。热表从几亿行降到几百万行之后同样的 IN 查询、同样的临时表 JOIN性能会有数量级提升。这个方案虽然不直接改动 SQL但它解决的是“大数据量”这个源头。如果一张表本身已经大到影响正常访问什么 IN 优化技巧都是治标不治本。5. 一次生产环境 IN 查询优化复盘从 3 秒到 30 毫秒5.1 事故现场我之前负责过一个“人群明细查询”接口业务方会传入从标签系统计算出的约 2 万个 user_idSQL 大概是这样的SELECT u.id, u.name, u.mobile FROM user u WHERE u.id IN (...约20000个数字...) AND u.status 1 ORDER BY u.create_time DESC LIMIT 200;user 表 500 万行日常接口 P99 大概 800ms那天突然涨到 3.2 秒数据库连接池被打满上游服务陆续超时。5.2 诊断过程拿到慢日志里的完整 SQL 后先做 EXPLAIN执行计划是这样的字段值typeALLkeyNULLrows5000000ExtraUsing where; Using filesort看到typeALL和rows5000000基本可以确定是全表扫描。为什么一个大主键 IN 查询会退化成全表扫描核心原因是 2 万多个 ID 远远超过eq_range_index_dive_limit默认的 200优化器基于统计信息估算成本后认为全表扫描比逐个查 2 万个索引区间更“划算”。再加上ORDER BY create_time LIMIT 200优化器发现按主键索引扫描再排序的成本太高干脆选择了全表扫描。这个诊断过程也提醒我不要以为主键 IN 查询就一定走索引在大列表 排序 过滤条件的组合下优化器很容易选错路。5.3 改造实施临时表 JOIN 延迟聚合我没有直接调参数而是把 SQL 改成了临时表 JOIN。先把 2 万个 user_id 写入临时表再和 user 表做连接查询。CREATE TEMPORARY TABLE tmp_ids ( id BIGINT PRIMARY KEY ) ENGINEInnoDB; INSERT IGNORE INTO tmp_ids (id) VALUES (...);第一条查询先只取主键配合排序和 limitSELECT u.id FROM tmp_ids t INNER JOIN user u ON u.id t.id WHERE u.status 1 ORDER BY u.create_time DESC LIMIT 200;这时因为只需要返回主键覆盖索引和ORDER BY的处理压力小了很多。拿到这 200 个主键之后再用一个非常小的 IN 查询回表取完整明细SELECT * FROM user WHERE id IN (...200个id...);最终整个接口从 3.2 秒降到了 90ms 左右第一条大 SQL 的执行时间基本在 30-60ms。临时表插入 2 万行的开销非常小因为批量 INSERT 是顺序写入而且我们用的是INSERT IGNORE不需要在应用层做复杂去重。5.4 上线前容易被忽略的细节临时表方案看着简单真正落地有几个坑临时表是会话级的用同一个数据库连接才能访问到。连接池环境下必须确保执行完 SQL 后仍然持有同一个连接并在 finally 里DROP TEMPORARY TABLE。如果 ID 来源里本身有大量重复临时表主键会自动去重但重复行会导致INSERT报错所以记得用INSERT IGNORE。如果是高并发接口临时表写入和查询都会消耗临时表空间需要关注tmp_table_size、max_heap_table_size避免临时表落到磁盘反而更慢。大事务里不要用临时表 JOIN 一次处理几十万 ID行锁范围会非常夸张后面的写操作很容易阻塞。6. 优化中容易踩的坑6.1 IN ORDER BY LIMIT 的排序陷阱IN 只是过滤条件排序字段如果没有出现在合适的索引里MySQL 就要对所有命中的行做排序再取前 N 条。哪怕命中的行数只有几万排序的代价也不小。最简单的优化是建立一个覆盖索引比如(status, create_time, id)让排序字段走索引。但注意IN 列表很大时MySQL 可能不会老老实实按这个索引顺序扫描因为它要同时匹配多个等值条件。更稳妥的做法是先用临时表 JOIN 只取主键排序后 limit最后再回表。6.2 事务中的 IN 查询锁和一致性如果大 IN 查询出现在一个长事务里并且后续还有写操作问题会放大。尤其是SELECT ... FOR UPDATE这种加锁读2 万个 ID 就锁 2 万个主键行事务越长锁等待概率越高。我遇到过的情况是一个批量任务先查出一批用户然后逐个更新用户状态结果用户列表越来越大任务执行时间越来越长最终锁等待超时。解决办法是拆事务一个事务只处理一小批数据不要让大 IN 查询和写更新长时间占用事务资源。6.3 max_allowed_packet 和 PreparedStatement 的坑长 SQL 超过max_allowed_packet时MySQL 会直接抛错连接断掉。很多人第一反应是调大这个值但这不是根治方案只是把水缸加大而已。还有一个常见问题很多 ORM 框架不允许IN直接绑定整个列表底层会把参数展开成IN (?,?,?,...)几百上千个参数也很容易触发数据库层的限制。更别说 MySQL 的预处理语句本身有参数数量上限。真要传几万 ID老老实实用临时表 JOIN 比费劲调 ORM 参数靠谱得多。6.4 NULL 和去重的语义细节MySQL 里id IN (1, NULL)的语义和直觉不太一样如果表中存在 id1 的行会正常返回 id1 的行如果 id 不匹配 1剩下的是 NULL 比较条件结果是 NULL相当于不生效。所以 IN 列表里有 NULL 时不要以为它会匹配到某种“空值”。另一个问题是重复 ID。优化过程中我曾经看过一条 SQL传入的 ID 列表里有大量重复导致记录数膨胀了好几倍。后来在源头加了 DISTINCT查询行数骤降。你可以用临时表主键去重也可以在应用层先distinct。这虽然不是 MySQL 层面的技巧但经常是压垮大 IN 查询的最后一根稻草。我个人处理这类问题的习惯是只要遇到超过 200 个 ID 的 IN 查询先不看表结构直接 EXPLAIN 看执行计划如果 rows 接近全表就别在参数调优上浪费时间了把大 IN 列表搬进临时表再加 JOIN。这个思路不仅对 MySQL 有效对很多关系型数据库也通用本质上是把一个巨大的“判断集合”变成数据库最擅长处理的“索引查找路径”。