我图省事把上万个 ID 一股脑塞进 SQL 的 WHERE id IN (...) 里去批量查询、小批量测的时候快得飞起,结果生产环境列表一大这条查询就慢成狗、有时还直接报参数过多的错、DBA 看监控说这一条 SQL 把库都拖垮了,排查很久才搞懂一个在小规模下完美的 IN 写法放大到上万个值时会在解析执行计划缓存好几个地方同时崩坏的深度复盘

这次踩的坑,教训特别朴素却特别深刻:一段在小数据量下跑得又快又好的代码,不代表它在大数据量下还成立。规模,本身就是一个会改变一切的变量。

故障现场:小批量飞快,列表一大就崩

我有个需求:根据一批 ID 去数据库批量查记录。我写得特别直白——把所有 ID 拼进一个 IN 列表:

-- 我的写法:有多少 ID 就往 IN 里塞多少
SELECT * FROM orders WHERE id IN (1, 2, 3, ..., /* 上万个 */);

开发和测试时,我手头的 ID 也就几十个,查询快得飞起,毫无问题,顺利上线。可生产环境跑起来,问题就接二连三冒出来了:

• 列表一大就奇慢:某些场景下 ID 列表有上万个,这条查询就慢得离谱,从毫秒级飙到几秒甚至十几秒,而同样的表、查单条飞快。
• 有时直接报错:在某些数据库上,IN 列表一旦超过某个数量,直接报错——参数太多 / 表达式过长 / SQL 文本超限,查询根本执行不了。
• 把整个库拖慢:DBA 找来说,有一条 SQL 占用大量资源、还反复出现各种各样的版本,把共享池/计划缓存搞得乱七八糟,影响了其它查询。
• 执行计划还变差了:我 explain 看,小 IN 列表时走索引查得好好的,可超大 IN 列表时,优化器居然放弃索引改走全表扫描了。

"小批量飞快、大列表又慢又报错还拖垮库、执行计划还变差"——这几个现象合起来,指向一个我完全没考虑过的维度:问题不在 IN 这个写法本身(它在小列表下完美),而在于当列表的规模急剧放大时,这条 SQL 在好几个我没注意到的地方同时开始劣化、甚至崩坏。我得去搞清楚,一个超大的 IN 列表,到底在数据库内部捅了哪些娄子。

第一件事:搞懂超大 IN 列表会在解析、限制、优化器、缓存多处同时劣化

带着"规模放大引发多处问题"这条线去深挖,我才明白,一个超大的 IN (...) 列表,远不是"多查几个值"那么简单,它会在数据库处理 SQL 的多个环节同时制造麻烦:

• 解析变慢、SQL 文本巨大:IN 列表里每多一个值,SQL 文本就长一截。上万个值拼出来的 SQL 可能有几百 KB,数据库光是解析(parse)这条巨型 SQL 就要耗费可观的 CPU 和时间。
• 撞上硬性限制:很多数据库对 IN 列表的元素个数、SQL 文本长度、绑定参数个数有上限(比如 Oracle 的 IN 列表上限 1000、各种参数/包长度限制),超了就直接报错。
• 优化器可能放弃索引:面对一个有成千上万个值的 IN,优化器评估"逐个走索引查再合并"的成本可能高过全表扫描,于是改走全表扫描;大表全表扫描自然慢得吓人。
• 计划缓存被冲垮:如果用的是拼接字面量(每次列表长度、内容都不同),那么每一次查询都是一条"全新的、独一无二"的 SQL,数据库无法复用已编译的执行计划,只能反复硬解析,还把共享池/计划缓存塞满大量只用一次的计划,拖累整个实例。

这下所有现象全说通了:我的 IN (上万个 ID),不是单一地"慢",而是同时触发了"解析慢 + 撞限制报错 + 优化器弃用索引 + 计划缓存失效"这一连串问题;而这些问题在小列表下统统不存在——所以开发测试时一点征兆都没有。我一直以为 IN 的成本就是"线性地多查几个",却没想到它在规模这个维度上,会撞上一堆非线性的墙。我把规模影响验证了一下:

-- 小列表: 解析快、走索引、计划可复用 -> 飞快
SELECT * FROM orders WHERE id IN (1, 2, 3);          -- 毫秒级

-- 超大列表(拼字面量): SQL 文本巨大、可能超限、可能全表扫、计划用一次就废
SELECT * FROM orders WHERE id IN (1, 2, ... 上万个);  -- 几秒~十几秒 / 报错
-- explain 一看: 小列表 Using index; 超大列表 退化成 ALL(全表扫描)

真相大白:不是 IN 错了,而是我把一个"只在小规模下成立"的写法,直接用到了"规模会暴涨到上万"的场景里,完全没考虑规模放大后,它会在解析、限制、优化器、缓存这些隐藏的维度上一起崩坏。规模,从来不是一个可以忽略的细节,它是一个会让"小数据下的最优解"彻底失效的关键变量。

第二件事:正解——大列表别直接 IN,用临时表 JOIN 或分批,控制单次规模

根因是"超大 IN 列表多处崩坏",那正解的核心就一句话:当要按一大批值查询时,别把它们直接拼进 IN,而要换成不随规模线性膨胀 SQL 的方式——临时表 JOIN、或分批查询,把单次进入 SQL 的值控制在小规模。几种做法:

-- 正解 1(首选, 量大):把这批 ID 批量插入临时表, 再 JOIN
CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY);
-- 批量(或 COPY/批量 insert)把上万个 id 灌进 tmp_ids
INSERT INTO tmp_ids (id) VALUES (1),(2),... ;   -- 一次性批量灌
SELECT o.* FROM orders o JOIN tmp_ids t ON o.id = t.id;
-- SQL 文本固定不膨胀, 优化器能用索引做高效 join, 计划可复用

-- 正解 2(简单, 量中等):分批, 每批几百个分多次查再合并
-- ids 切成每批 500 个: IN (...500个...), 多查几次, 应用层合并结果

// 应用层分批的骨架(每批 BATCH 个, 多次查询合并)
int BATCH = 500;
List<Order> all = new ArrayList<>();
for (int i = 0; i < ids.size(); i += BATCH) {
    List<Long> sub = ids.subList(i, Math.min(i + BATCH, ids.size()));
    all.addAll(orderMapper.selectByIds(sub));   // 每批一条 IN(<=500), 规模可控
}
// 单条 SQL 的 IN 列表永远 <=500: 不超限、解析快、能走索引、计划可复用

这里的关键判断是按数据量选方案:量很大(上千上万)首选临时表 + JOIN,SQL 文本固定、优化器能高效用索引连接、计划可复用;量中等就分批,把单批 IN 控制在几百以内(既不撞限制也利于走索引、计划复用);此外用参数绑定(预编译占位符)而非拼字面量,能让相同结构的 SQL 复用计划缓存。核心就一条:别让 SQL 的体量随数据规模线性膨胀,用临时表/分批把单次进入数据库的规模摁住。

更根本的,是在写任何"处理一批数据"的代码时,就先问一句"这批最大可能有多少?"——如果可能很大,从一开始就别用会随规模膨胀的写法。

第三件事:同一类"小规模完美、大规模崩坏"的坑,我后来又撞见好几个

这次踩坑让我对"规模"这个维度彻底敏感了起来:太多写法,在小数据量下简单、直观、飞快,以至于我们根本想不到它有什么问题;可一旦数据规模上去,它就会在某个(往往是隐藏的)维度上非线性地劣化甚至崩溃。这种坑到处都是:

• 一次性把全部数据 load 进内存:小数据时 findAll() 全捞出来很方便,数据一多直接 OOM。
• 循环里逐条查数据库(N+1):几条数据时 for 循环里查没事,几千条就是几千次往返、慢成灾难。
• 深度分页 OFFSET 越翻越慢:前几页 LIMIT OFFSET 很快,翻到几十万页时数据库要扫过前面所有行。
• 字符串拼接拼大文本:拼几段没事,循环拼几万段就是 O(n²) 的灾难。
• 正则/算法在小输入上够快:小输入跑得欢,遇到超长输入触发指数级回溯/复杂度爆炸。

它们的内核是同一个:很多方案的成本,并不随规模线性增长;在小规模时,这些成本小到可以忽略,方案显得"完美";可规模一放大,某个被忽略的维度(内存、往返次数、扫描行数、解析开销、复杂度)就会非线性地暴涨,把"小规模的最优解"变成"大规模的灾难"。而最坑的是,开发测试往往用的是小数据,这些问题根本暴露不出来,直到生产环境的真实规模把它引爆。所以,写任何代码时,都要把"规模"当成一个一等的考量:问清楚数据可能的最大规模、想清楚方案在那个规模下的行为、用不随规模恶化的写法。我把这套判断画成了一张图(见后文)。

第四件事:大批量查询的几种方案——一张对照表

这次事故逼我把"按一批值查询"的几种方案摆成一张表,以后按数据量选:

看清这张表,选择就有了准绳:几十个值直接 IN 没问题;几百个用参数绑定或分批;上千上万一律临时表 JOIN——别让 SQL 文本和参数个数随数据规模无限膨胀。同样是"按一批 ID 查",规模不同,正确的方案完全不同。

第五件事:我曾经对 IN 和规模想当然的几个误区

这场"大列表拖垮库"的事故,把我对 IN 和数据规模的一堆想当然照得清清楚楚:

这些误区的根子是同一个:我在写这段代码时,脑子里默认的是"开发测试时手头那几十个 ID"的小规模情景,而完全没有去设想"生产环境这批 ID 最大可能有多少";我把一个在小规模下成本可忽略的写法,当成了在任何规模下都成立的通用方案。正因为我从没把"规模"当成一个需要主动考量的变量,我才会对它放大后会撞上的那一堆非线性的墙毫无防备。写代码时只设想手头测试的小规模、不去预估和应对生产可能的大规模,是这类"小数据完美、大数据崩坏"问题的共同根源。

第六件事:按一批数据查询、写处理批量的代码时,我现在的自检习惯

现在每当我要按一批值查询、或写任何处理"一批数据"的代码,我都会先问"这批最大可能多少"。先看清超大 IN 为什么多处崩坏:

然后用这张自检图决定按一批值查询怎么写:

这套习惯的精髓,是"按一批查先问最大多少、几十直接 IN、几百分批、上千上万临时表 JOIN、用接近生产规模的数据压测"。它让我从"有多少 ID 就 IN 多少",变成了"先估规模再选写法"——核心始终是:在 SQL 里用 WHERE col IN (值列表) 按一批值查询时,这个写法的成本并不是随列表元素个数线性增长的简单关系、而是当列表规模急剧放大(到上千上万)时会在数据库处理 SQL 的多个环节同时非线性地劣化甚至崩坏——IN 列表里每多一个值 SQL 文本就长一截、上万个值拼出的巨型 SQL 光解析就耗费大量 CPU 和时间,很多数据库对 IN 列表元素个数(如 Oracle 上限 1000)、SQL 文本长度、绑定参数个数有硬性上限超了直接报错,面对成千上万个值的 IN 优化器评估逐个走索引再合并的成本可能高过全表扫描于是放弃索引改走全表扫描使大表查询极慢,而如果是拼接字面量(每次列表长度内容都不同)则每次查询都是一条全新独一无二的 SQL 数据库无法复用已编译的执行计划只能反复硬解析还把共享池/计划缓存塞满大量只用一次的计划拖累整个实例;这些问题在小列表下统统不存在所以开发测试时(手头往往只有几十个值)毫无征兆、直到生产环境真实的大规模把它一次性引爆;正解是当要按一大批值查询时别把它们直接拼进 IN 而要换成不随数据规模线性膨胀 SQL 的方式——数据量很大(上千上万)首选把这批值批量插入临时表再与目标表 JOIN(SQL 文本固定不膨胀、优化器能用索引做高效连接、计划可复用),数据量中等就分批把单批 IN 控制在几百以内多次查询再在应用层合并结果,并用参数绑定预编译占位符而非拼字面量让相同结构的 SQL 复用计划缓存,把单次进入数据库的规模摁住;更根本更一般地,很多方案的成本并不随规模线性增长——在小规模时这些成本小到可忽略方案显得简单直观完美,可规模一放大某个被忽略的维度(内存、网络往返次数、扫描行数、解析开销、算法复杂度)就会非线性地暴涨把小规模的最优解变成大规模的灾难(IN 大列表、findAll 全 load 内存 OOM、循环逐条查 N+1、OFFSET 深度分页越翻越慢、循环字符串拼接 O(n²)、正则指数级回溯),而最坑的是开发测试往往用小数据这些问题根本暴露不出来直到生产真实规模引爆,所以写任何代码时都要把规模当成一等的考量——主动问清楚数据可能的最大规模、想清楚方案在那个规模下的行为、选用不随规模恶化的写法、并尽量用接近生产规模的数据去压测验证而不是只拿手头的小数据测完就以为万事大吉。

我立下的几条规矩

这场"大列表拖垮库"的事故,换来了我写批量查询时,刻进骨子里的几条铁律:

1. IN 的成本不是线性的,大列表会撞解析/限制/优化器/缓存多堵墙。
2. 按一批值查询前,先问这批最大可能有多少。
3. 几十个直接 IN、几百个分批、上千上万用临时表 JOIN。
4. 单条 SQL 别随数据规模无限膨胀,把单次规模摁住。
5. 用参数绑定而非拼字面量,保住计划缓存复用。
6. 测试用小数据不暴露规模问题,要用接近生产规模压测。
7. 规模是一等变量:任何写法都想想它放大 100 倍会怎样。

附:一段把超大 IN 改成临时表 JOIN / 分批的对照清单

最后留一段我自己把超大 IN 整改成临时表 JOIN 或分批时照着用的清单:

-- ❌ 危险:有多少 ID 就 IN 多少(拼字面量)
-- SELECT * FROM orders WHERE id IN (1,2,...,上万个);
--   -> 解析慢 / 撞 IN 上限报错 / 优化器弃索引全表扫 / 计划缓存被冲垮

-- ✅ 方案 A(量大, 首选):临时表 + JOIN
CREATE TEMPORARY TABLE tmp_ids(id BIGINT PRIMARY KEY);
-- 批量灌入(用批量 INSERT / COPY / 框架批处理), 一次性进表
SELECT o.* FROM orders o JOIN tmp_ids t ON o.id = t.id;   -- 走索引、计划稳定
-- 用完即弃, 会话级临时表通常自动清理

-- ✅ 方案 B(量中等):分批, 每批 <= 500, 多次查询合并
-- 应用层把 ids 切成 500 一批, 每批: WHERE id IN (?,?,...) 参数绑定

-- ✅ 通用:一律用参数绑定占位符, 别拼字面量(保住计划缓存复用)
--   MyBatis:  控制单批大小;  JDBC: PreparedStatement + 批

// 分批查询的可复用工具方法(每批 batch 个, 合并结果)
static <T, R> List<R> queryInBatches(List<T> ids, int batch,
                                       Function<List<T>, List<R>> query) {
    List<R> result = new ArrayList<>();
    for (int i = 0; i < ids.size(); i += batch) {
        result.addAll(query.apply(ids.subList(i, Math.min(i + batch, ids.size()))));
    }
    return result;   // 单批 IN 永远 <= batch, 规模可控、不撞墙
}
// 用法: queryInBatches(allIds, 500, mapper::selectByIds);

这段清单的核心就一句:量大用临时表 JOIN(SQL 固定、走索引、计划稳)、量中等分批(单批几百、参数绑定)、永远别让 IN 列表随数据规模无限膨胀。把"有多少塞多少"换成"按规模选方案、把单次规模摁住",那条拖垮库的巨型 SQL 就再也不会出现了。

写在最后

回头看,这场由"超大 IN 列表"引发的"拖垮库"事故,真正教给我的,远不止"大列表用临时表 JOIN"这一个技巧。它让我对"'规模'是一个能从根本上改变一件事性质的变量;一个在小尺度下成立、甚至堪称完美的方案,放大到大尺度时,可能不是'慢一点',而是'彻底失效、性质突变'",有了一次刻骨的体会。我栽跟头,是因为我把"规模"当成了一个无关紧要、只影响快慢程度的次要因素——在我朴素的直觉里,数据多一点无非就是"多花点时间",方案该成立还成立,只是慢一档而已;我完全没想到,规模的放大,会让一个方案撞上一连串在小规模下根本不存在的墙:解析的墙、参数上限的墙、优化器改变策略的墙、计划缓存的墙——这些墙不是"让它慢一点",而是"让它直接报错、让它退化、让它拖垮整个系统";我用一个为"几十个"设计的方案,去硬扛"上万个",就像拿一座为小溪设计的木桥,去承载一条大河的洪流。这让我领悟到一个关于"规模与质变"的深刻认知:规模(数量、大小、并发、频率)从来不只是一个"程度"的标尺,它常常是一个能引发"质变"的开关——量变到一定程度,事情的性质就变了;一个方案在小规模下的"成立",往往依赖着一些只在小规模下才被满足的隐含前提(数据装得进内存、SQL 短到能快速解析、个数没超上限、成本小到可忽略);当规模突破这些前提,方案赖以成立的地基就一块块塌掉,而它不会温柔地、线性地变慢,而是会在某个临界点猝然崩坏;更隐蔽的是,我们的开发、测试、直觉,几乎都是在小规模下形成的,这让我们对"大规模会发生什么"有一种系统性的盲区——我们验证过的"它能跑",其实只是"它在小规模下能跑";所以,真正的工程素养,是把"规模"从一个事后才想起的因素,提升为设计之初就必须主动追问的一等变量:这东西最大会有多大?到那个量级,我这个方案还成立吗?它会在哪个维度先撞墙。这给了我一种面对"一切'处理数据、承载负载'之事"时的清醒:每当我写下一段处理数据的代码,我都会强迫自己跳出手头测试的小数据,去想"这东西在生产、在最坏情况下,规模会到多大?我这个写法,放大 100 倍、1000 倍还成立吗?它会先在哪儿崩"——把规模当成设计变量、按最大规模选方案、用接近真实规模的数据验证;"规模会引发质变、为最大规模而设计",是写对 SQL、也是构建一切能扛住真实负载的系统的关键。认清 IN 大列表会多处崩坏、要用临时表 JOIN 或分批、规模是一等变量——这,是我用一次"一条 IN 查询把整个库拖垮"的事故,换来的、关于数据库、也关于如何敬畏规模的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写下 WHERE id IN ( 时、或写任何处理一批数据的代码时,先停一下问自己"这批最大会有多少?",那我对着那条把库拖垮的巨型 SQL 熬的那个夜,就值了。

—— 别看了 · 2026