IN 大量数据拖慢查询？根因剖析与两套优化方案

一、问题现场：一亿级大表上的 IN 查询退化

先交代实验环境与数据规模。演示对象是一张生产级的库存流水表 stock_flow，数据量超过一亿行。数据条数可以通过系统元数据视图快速确认：

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

查询结果显示行数在一亿以上，量级足够真实。接下来用最常见的方式做批量查询：把一万个自增 ID 直接拼进 IN 列表。实验结论可以先给出：

• 直接 IN 一万个 ID：执行计划放弃主键索引，退化为全表扫描；
• 用 VALUES 构造虚拟表再 JOIN：稳定走主键索引，性能优异。

也就是说，同样是“按 ID 批量取数”，SQL 写法不同，执行路径可能天差地别。

二、根因剖析：优化器为什么会放弃索引

很多开发者的第一反应是“索引坏了”或者“没建索引”，其实都不是。主键索引一直都在，是优化器在成本估算之后主动“弃用”了它。背后有几层原因：

1. IN 列表本质上是一组 range 条件。列表里的每个值都要参与成本估算，值越多，估算开销越大。当列表膨胀到数千上万个值时，优化器在解析与优化阶段本身就要消耗可观的 CPU 与内存，甚至可能触发内部估算上限，导致统计信息精度下降。

2. 成本对比失衡。走主键索引意味着一万个值的随机回表，产生的随机 IO 在优化器看来可能比顺序扫描全表更贵。于优化器而言，选全表扫描是“理性”的；但对在线业务来说，一亿行的全表扫描足以把缓冲池、磁盘 IO 和连接资源全部打满，是灾难性的。

3. 附加风险同样致命。SQL 文本过长可能触碰报文长度上限直接报错；如果传入的 ID 类型与列类型不一致（比如把数字传成了字符串），还会因隐式类型转换让索引彻底失效——哪怕列表里只有几百个值。

理解了这一点就明白：优化思路不是“调参让优化器听话”，而是改变 SQL 的形态，把“一万个点查”重新组织成优化器擅长执行的连接结构。

三、方案一：VALUES 构造虚拟表 + JOIN（新版本首选）

较新版本的数据库支持用 VALUES 行构造器直接生成一张派生表，并把它当作普通表来连接。注意存在版本门槛：需 8.0.19 及以上，低版本不支持该语法。

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

执行机制上有两个关键点：

• VALUES 构造出来的派生表（即执行计划里的 <derived2> 虚拟表）会被物化，优化器把它当作一张普通小表参与 JOIN；
• 主表 stock_flow 通过 eq_ref 方式访问，使用的是主键索引（PRIMARY），即每一个派生表行都对应一次精准的主键定位，没有多余扫描。

用 EXPLAIN 验证时，能看到派生表与 eq_ref、PRIMARY 的组合，说明执行路径是健康的。这个方案的优势非常明显：

• 单条 SQL 即可完成，不产生任何残留对象；
• 不需要创建临时表的 DDL 权限，对只读账号、只读从库同样友好；
• 实测虚拟表的性能接近临时表方案。

局限也要清楚：ID 特别多时 SQL 依然很长；派生表物化需要消耗内存或磁盘临时空间；老版本无法使用。

四、方案二：临时表 + JOIN（兼容旧版本的通用解法）

如果版本较低，或者批量规模更大，临时表 + JOIN 是更通用的方案，实测性能更好，且支持更大批量的 ID 集合。标准三步走：

-- 1. 创建临时表：列类型与目标列保持一致，主键自动带索引
CREATE TEMPORARY TABLE tmp_ids (
    id BIGINT PRIMARY KEY
);
-- 2. 分批插入 ID，控制单条语句长度
INSERT INTO tmp_ids (id) VALUES (1),(2),/* ... */(10000);
-- 3. JOIN 查询，等值连接可稳定走 ref/eq_ref
SELECT t.*
FROM stock_flow t
JOIN tmp_ids tmp ON t.id = tmp.id;

它相比虚拟表方案还有两个工程上的好处：一是临时表在同一会话内可以复用，一次插入后可供多条 SQL 反复 JOIN；二是插入动作可以自由分批，从根本上规避超长 SQL 问题。

但这里有一个必须刻在脑子里的陷阱：临时表的生命周期绑定的是“连接”，而不是“事务”。事务结束它不会自动删除，只有连接真正断开才会销毁。而生产环境普遍使用连接池，连接会被长期复用、几乎不会主动关闭，于是临时表会一直残留，轻则占用资源，重则导致连接下次被复用建表时报“对象已存在”的错误。

所以必须在程序里显式清理，并把清理动作放进 finally：

// 伪代码：建表、插入、查询、删除必须在同一连接（同一事务）内完成
public List<Order> queryByLargeIds(List<Long> ids) {
    try {
        tmpIdMapper.createTable();             // 1. 建临时表
        for (List<Long> batch : partition(ids, 1000)) {
            tmpIdMapper.batchInsert(batch);    // 2. 分批插入
        }
        return orderMapper.selectJoinTmpIds(); // 3. JOIN 查询
    } finally {
        tmpIdMapper.dropTable();               // 4.【关键】显式删除临时表
    }
}

两个补充细节：删除语句建议写成 DROP TEMPORARY TABLE IF EXISTS，做防御式清理；同时要确保整个流程绑定在同一连接上执行，避免连接池把不同语句路由到不同连接，出现“建了 A 连接的表、却在 B 连接里查”的问题。

五、工程化补充建议

1. 批次仍要控制。即便方案有效，也建议单批 ID 控制在数千以内，更大规模就拆分多次执行，在应用层合并结果。
2. 类型严格一致。绑定参数的类型要与列类型一致，避免隐式转换导致索引失效。
3. 能不传 ID 就不传。如果 ID 集合本身可以由库内条件推导（比如来自另一张业务表的过滤结果），优先用子查询或 JOIN 在数据库内部完成，避免应用层与数据库之间来回搬运海量 ID。
4. 上线前验证。用接近真实量级的数据执行 EXPLAIN 并压测，确认执行计划符合预期，不要等到大表上线后才暴露问题。
5. 建立监控。对扫描行数异常放大、慢查询突增设置告警，尽早发现 IN 退化问题。无论是自建实例，还是百度智能云等云数据库服务，上述思路与监控手段都是通用的。

六、小结

• IN 列表过大时，优化器可能放弃主键索引选择全表扫描，这不是索引问题，而是 SQL 形态问题；
• 版本较新时，优先用 VALUES 派生虚拟表 + JOIN，单条 SQL、无残留、只读友好；
• 版本较旧或批量更大时，用临时表 + JOIN，性能更好、可复用、可分批，但务必在 finally 中显式 DROP——连接池中的连接不会关闭，临时表不会自动销毁；
• 无论哪种方案，都要配合分批控制、类型一致、EXPLAIN 验证与监控告警，才能稳稳扛住生产流量。