MySQL 中 IN 大量数据导致查询变慢的优化思路与实践

一、问题背景：为什么 IN 大量值会变慢

在业务开发中，经常需要根据一批主键或业务编号查询明细数据，例如：

SELECT * FROM stock_flow WHERE id IN (1, 2, 3, ..., 10000);

当 IN 列表只有几个、几十个值时，MySQL 通常能够正常使用主键索引或普通索引进行等值查询。但当 IN 列表膨胀到几千、几万甚至更多时，查询计划可能发生明显变化，甚至由索引查询退化为全表扫描。

这背后的原因主要来自优化器的成本估算与执行策略：

1. IN 列表过大时，优化器可能认为回表成本过高。即使 IN 中的值都能命中索引，MySQL 仍需要根据索引记录逐条回表读取完整行数据。当值过多时，优化器可能估算“大量随机回表”的代价高于直接扫描全表。
2. MySQL 对 IN 子句的内部处理存在上限与策略变化。不同版本对 IN 列表的展开、去重、排序方式不同，部分场景下会触发全表扫描或索引选择异常。
3. 内存与临时表压力。IN 列表在解析与优化阶段可能需要占用额外内存，列表过长时可能触发内部临时表或排序操作，进一步影响执行计划选择。

因此，IN 大量值并不是一个简单的“值多了一点”的问题，而是可能导致执行计划发生质变的问题。

二、实战环境说明

为了更直观地说明问题，本文基于一张生产环境中的库存流水表 stock_flow 进行演示。该表数据量超过一亿行，主键为自增 id，并且为 id 建立了主键索引。

首先通过以下语句查看表的统计信息：

SELECT table_rows
FROM information_schema.tables
WHERE table_schema = 'stock'
  AND table_name = 'stock_flow';

结果显示该表数据量已经超过一亿行。接下来使用一万个自增 id 进行 IN 查询，对比不同方案下的执行计划与查询性能。

需要特别说明的是，MySQL 8.0.19 及以上版本对 VALUES 构造派生表的支持更加完善，因此本文方案建议在 MySQL 8.0.19 及以上版本中实践。

三、直接 IN 一万个值的执行计划分析

首先测试最直接的写法：

SELECT *
FROM stock_flow
WHERE id IN (1, 2, 3, ..., 10000);

通过 EXPLAIN 查看执行计划，可以发现一个明显问题：MySQL 并没有选择主键索引，而是选择了另一个二级索引，并且扫描行数接近全表行数。也就是说，此时实际上已经退化为全表扫描。

这种现象说明，当 IN 列表达到一定规模后，优化器会重新评估成本模型。它可能认为：

• 通过主键索引逐条回表读取一万条数据，需要一万次随机 IO；
• 直接扫描全表或使用其他索引进行范围扫描，虽然扫描行数多，但顺序 IO 成本更低。

因此，优化器选择了扫描行数更多的方案，但这并不代表查询真的变快了。相反，在生产环境中，这种全表扫描会消耗大量 IO 与 CPU 资源，可能直接拖慢整个数据库实例。

四、使用虚拟表 VALUES 的优化方案

为了避免 IN 大列表导致的全表扫描，可以将这一批 id 构造成一个虚拟表，再与目标表进行关联查询。例如：

SELECT t.*
FROM stock_flow t
JOIN (
    VALUES ROW(1), ROW(2), ROW(3), ..., ROW(10000)
) AS tmp(id)
ON t.id = tmp.id;

此时，MySQL 会将 VALUES 构造的内容视为一个派生表，也就是虚拟表。它与目标表进行关联时，可以更高效地使用主键索引。

通过 EXPLAIN 可以看到：

• derived2 表示 VALUES 构造的虚拟表被当作普通表处理；
• stock_flow 表通过 eq_ref 访问，使用了主键索引；
• 查询计划与直接 IN 相比发生了明显改善。

这种方案的核心优势在于，MySQL 对关联查询的成本估算与 IN 列表不同。虚拟表经过内部处理后，优化器更容易选择索引关联路径，从而避免全表扫描。

但需要注意的是，VALUES 虚拟表方案更适合中等规模的 ID 列表。如果 ID 数量非常庞大，例如几十万甚至上百万，虚拟表本身也会占用较多内存，并且关联过程中的临时表处理成本仍然存在。

五、临时表 + JOIN 的更稳定方案

当 ID 列表规模很大，或者同一批次 ID 需要多次复用时，临时表 + JOIN 是更稳定的选择。

实现步骤如下：

1. 创建临时表

CREATE TEMPORARY TABLE tmp_ids (
    id BIGINT PRIMARY KEY
) ENGINE = InnoDB;

这里将 id 设为主键，目的是在临时表内部自动建立主键索引，便于后续关联时快速查找。

2. 批量插入 ID

INSERT INTO tmp_ids (id)
VALUES (1), (2), (3), ..., (10000);

如果 ID 数量非常多，可以采用分批插入的方式，避免单条 SQL 过长。

3. 使用 JOIN 查询

SELECT t.*
FROM stock_flow t
JOIN tmp_ids tmp ON t.id = tmp.id;

这种写法的好处是：

• 临时表数据落盘或驻留内存后，可以重复使用；
• 关联查询时，MySQL 可以更稳定地选择主键索引；
• 相比 IN 列表，执行计划更可控，不容易出现全表扫描。

六、临时表使用中的关键注意事项

很多开发者知道临时表可以用，但容易忽略它的生命周期管理。

MySQL 中，临时表并不是事务结束或语句执行完就自动删除，而是在数据库连接关闭时才会销毁。也就是说，如果应用程序使用连接池，连接并不会真正关闭，临时表就可能一直存在。

如果每次请求都创建临时表却没有及时删除，会带来两个严重问题：

1. 会话内表数量持续增加，后续请求可能复用同一个连接，导致临时表残留并影响性能；
2. 占用连接资源与内存，长时间运行后可能导致数据库连接状态异常。

因此，在使用临时表时，必须在程序逻辑中显式删除。

以 Java 和 MyBatis 为例，推荐做法如下：

@Transactional
public List<User> queryByLargeIds(List<Long> ids) {
    try {
        userMapper.createTempTable();
        insertInBatches(ids);
        return userMapper.selectByTempTable();
    } finally {
        userMapper.dropTempTable();
    }
}

核心原则是：无论业务查询是否成功，都要在 finally 中清理临时表，避免连接复用后残留临时表。

七、不同方案的适用场景对比

为了帮助开发者在实际业务中做出选择，下面对三类常见方案进行简要对比：

方案	适用规模	执行计划稳定性	是否需要清理	使用复杂度
直接 IN	少量值，通常几十个以内	一般	否	低
VALUES 虚拟表	中等规模，例如几千到几万	较好	否	中
临时表 + JOIN	大批量，且可能需要复用 ID 列表	最稳定	是	中高

如果只是少量 ID 查询，直接 IN 是最简单的选择。但当 ID 数量达到几千以上时，就应当考虑虚拟表或临时表方案。

八、如何从根本上避免 IN 大列表问题

除了优化 SQL 写法，更重要的是从业务与架构层面减少“一次性 IN 大量值”的需求。

以下是一些常见思路：

1. 分批查询

将一万个 ID 拆分为多个小批次，每批 500 或 1000 个，逐批查询后在应用层合并结果。这样可以避免单条 SQL 的执行计划退化，同时减轻数据库瞬时压力。

2. 使用分页或范围条件

如果 ID 本身具有连续性或时间顺序，可以尽量使用范围查询替代 IN。例如：

SELECT * FROM stock_flow
WHERE id BETWEEN 1 AND 10000;

这种写法通常能够稳定使用主键索引。

3. 提前将 ID 写入业务表或中间表

如果同一批 ID 会被多个查询复用，可以提前将它们写入一张普通业务表，并建立索引。后续查询直接关联该表，避免反复构造大 IN 列表。

4. 使用消息队列或异步任务拆分

对于超大批量的 ID 查询，可以将其拆分为多个异步任务并行处理，每个任务只处理一部分数据，降低单条 SQL 的压力。

九、总结

IN 大量数据导致查询变慢，并不是 MySQL 本身无法处理，而是因为执行计划在特定规模下发生了不利于性能的变化。开发者不能只关注 SQL 是否返回结果，还要关注执行计划是否真正走索引、扫描行数是否合理。

从本文的实战结果来看：

1. 直接 IN 一万个值可能触发全表扫描；
2. 使用 VALUES 虚拟表能够明显改善执行计划，并稳定走主键索引；
3. 临时表 + JOIN 是更通用、更适合大批量 ID 的方案，但必须显式清理临时表。

在生产环境中，推荐根据 ID 数量、查询频率、连接管理方式等因素综合考虑。对于一次性查询，优先使用 VALUES 虚拟表；对于大批量或多次复用的场景，优先使用临时表 + JOIN，并做好连接与临时表生命周期管理。

最终目标不是“写出能跑的 SQL”，而是“写出在生产环境也能稳定运行的 SQL”。