MySQL大批量IN查询优化：从全表扫描到临时表方案

一、问题的提出

在日常开发中，WHERE id IN (...) 是一种极为常见的查询写法。当ID列表只有几十个时，它工作得很好；但当列表规模上升到数千甚至上万时，很多开发者会发现查询响应时间出现数量级的恶化。更令人困惑的是，明明 id 上有主键索引，执行计划却显示走了全表扫描。

本文基于一张数据量超过一亿条的库存流水表，实际演示这一现象，并系统性地给出可落地的优化方案。需要说明的是，本文涉及的虚拟表（派生表）优化路径要求 MySQL 版本不低于 8.0.19，较早版本对派生表的物化与索引条件下推处理尚不完善。

二、现象复现：一万个ID的IN查询

先确认表的数据规模。通过信息模式库查看表的估算行数：

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

返回结果确认该表行数已超过一亿。接着构造一个包含一万个自增主键的 IN 查询：

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

执行计划给出的结论非常明确：优化器没有选择主键索引，而是选择了另一个索引，并且扫描行数约等于全表总行数。也就是说，这个查询实质上退化为全表扫描。

为什么会这样

这并非优化器的”缺陷”，而是基于成本模型的理性选择。MySQL 优化器在评估 IN 查询时，会考虑以下因素：

1. 索引范围合并的成本：一万个离散的等值条件需要合并成范围扫描，优化器需要评估这种合并后的范围数量与回表成本。
2. 回表代价：如果选择主键索引，每个ID都需要一次回表取行数据；当ID数量极大且分布分散时，随机I/O的成本估算会迅速攀升。
3. 替代索引的诱惑：如果存在其他覆盖性或选择性尚可的索引，优化器可能认为”扫一遍该索引 + 过滤”比”一万次随机回表”更便宜。

当估算结果倾向于后者时，全表扫描就出现了。这也是为什么同样的 SQL，在小数据量或 ID 连续紧凑时走索引，而在大批量离散 ID 时却”叛变”。

三、方案一：虚拟表（VALUES 派生表）

思路

把 ID 列表从 IN 的子句位置挪到 FROM 子句中，构造成一张派生表，再与目标表做 JOIN。MySQL 会把 VALUES 构造的派生表当作普通表处理，从而让优化器以标准的连接方式评估执行路径。

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

执行计划的变化

在上述写法下，执行计划显示：<derived2> 是 VALUES 构造的派生表，stock_flow 通过 eq_ref 方式访问，使用主键索引（PRIMARY）。eq_ref 意味着对于派生表中的每一行，目标表最多匹配一行，且通过索引直接定位——这正是我们期望的高效路径。

适用边界

该方案依赖 MySQL 8.0.19 及以上版本对派生表物化与条件下推的改进。在更早的版本中，派生表可能被反复物化或无法有效利用索引，性能收益不稳定。此外，VALUES 列表本身仍然是 SQL 文本的一部分，SQL 长度与解析开销会随 ID 数量线性增长，因此它适合”数千到一万左右”的量级，而非无限扩展。

四、方案二：临时表 + JOIN

当批量规模继续增大，或需要在同一批 ID 上做多次查询时，临时表方案更为稳妥。

基本用法

-- 1. 创建临时表，主键自动建立索引
CREATE TEMPORARY TABLE tmp_ids (
  id BIGINT PRIMARY KEY
) ENGINE = InnoDB;
-- 2. 批量插入 ID
INSERT INTO tmp_ids (id) VALUES (1), (2), ..., (10000);
-- 3. 通过 JOIN 查询，高效利用索引
SELECT t.*
FROM stock_flow AS t
JOIN tmp_ids AS tmp ON t.id = tmp.id;

临时表的列类型必须与目标列严格一致（本例中为 BIGINT），否则可能因隐式类型转换导致索引失效。主键的定义让临时表自身具备索引能力，JOIN 时优化器可以据此选择以临时表为驱动表、目标表走主键的 eq_ref 路径。

与虚拟表方案相比，临时表把 ID 数据”物化”到服务端的独立表中，SQL 文本不再膨胀，插入可以分批进行，更适合万级以上甚至十万级的批量场景。

关键陷阱：临时表的生命周期

这是最容易被忽视、也最容易在生产环境埋雷的一点：临时表在事务结束后不会自动删除。它的销毁时机是数据库连接关闭，而现代应用普遍使用连接池，连接会被复用而非销毁。这意味着临时表会一直驻留在该连接上，可能造成：

• 同名临时表重复创建失败；
• 残留数据污染后续查询结果；
• 长期占用服务端内存与临时空间。

因此必须在代码中显式删除，并放在 finally 块中确保异常路径也能执行。

参考实现（以常见的 Java + ORM 组合为例）

@Transactional
public List<User> queryByLargeIds(List<Long> ids) {
    try {
        // 创建临时表
        userMapper.createTempTable();
        // 分批插入，避免单条 SQL 过大
        insertInBatches(ids);
        return userMapper.selectByTempTable();
    } finally {
        // 【关键】显式删除临时表，异常时也要执行
        userMapper.dropTempTable();
    }
}

配套的 Mapper 语句大致如下：

<update id="createTempTable">
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_ids (
    id BIGINT PRIMARY KEY
  ) ENGINE = InnoDB
</update>
<insert id="insertInBatches">
  INSERT INTO tmp_ids (id) VALUES
  <foreach collection="list" item="id" separator=",">
    (#{id})
  </foreach>
</insert>
<select id="selectByTempTable" resultType="User">
  SELECT t.*
  FROM user_table AS t
  JOIN tmp_ids AS tmp ON t.id = tmp.id
</select>
<update id="dropTempTable">
  DROP TEMPORARY TABLE IF EXISTS tmp_ids
</update>

几点补充建议：

1. 分批插入：每批 500～1000 条为宜，既控制单条 SQL 解析开销，也降低锁与日志压力。
2. IF NOT EXISTS 与 IF EXISTS：前者避免残留表导致建表报错，后者保证删除操作的幂等性。
3. 事务边界：临时表的创建与删除应与业务查询处于同一连接上下文；若框架存在多数据源或连接切换，需确认操作落在同一物理连接上。
4. 异常兜底：finally 中删除是底线，必要时可结合连接归还前的钩子做二次清理。

五、方案对比与选型建议

维度	直接 IN	VALUES 派生表	临时表 + JOIN
索引利用	大批量时易失效	可走主键 eq_ref	可走主键 eq_ref
SQL 文本膨胀	严重	严重	轻
适用批量	数十至数百	数千至一万	万级以上
版本要求	无	建议 8.0.19+	无特殊要求
清理成本	无	无	需显式删除

选型时可遵循以下原则：

• 小批量（数十以内）：直接 IN 即可，无需过度设计。
• 中等批量（数千至一万）：优先 VALUES 派生表，改动最小、无状态残留。
• 大批量或需复用 ID 集合：使用临时表 + JOIN，并严格管理生命周期。
• 任何方案都要先看执行计划：用 EXPLAIN 确认访问类型是否为 ref/eq_ref/range，扫描行数是否可控，避免凭直觉优化。

六、总结

大批量 IN 查询变慢的根因，是优化器在成本估算后主动放弃了索引，而非索引本身缺失。理解这一点，才能跳出”加索引就能解决”的思维定式。VALUES 派生表通过改变查询形态让优化器重新评估路径，临时表则通过物化 ID 集合支撑更大规模与复用场景。两者都能把执行计划拉回索引访问的正轨，但临时表方案务必在代码层面显式清理，否则在连接池环境下会留下难以排查的隐患。生产环境中，控制单次 IN 的规模、配合执行计划验证，才是长期稳定的做法。