一条查询从 30ms 变成 2 秒往往是业务量涨了SQL 没跟上。我遇到过最典型的一种就是WHERE条件里挂着一个不断变长的IN列表今天是 200 个 ID下个月 2000 个半年后直接 2 万多个。表面上看 SQL 没写错索引也在但数据库就是越来越慢。后来我把这些IN改成了UNNEST让数据库拿一张“临时清单表”去做连接查询时间直接回到百毫秒级别。这篇文章就围绕 SQL 优化里这个非常实用的小技巧把IN变慢的原因、UNNEST改写原理、实测对比和容易踩的坑完整讲一遍适合正在处理慢 SQL 的后端开发、数据分析师和 DBA 参考。1. 慢查询复盘一条查询在 IN 列表面前是怎么一步步变慢的先说结论IN列表变慢不是某一瞬间崩掉的而是解析、优化、执行三个阶段分别在积累成本。很多人只盯着执行计划看索引有没有生效却忽略了前面两个阶段。1.1 解析阶段SQL 文本膨胀带来的隐形成本数据库执行一条 SQL第一步是解析。你写WHERE id IN (1,2,3)的时候数据库要做词法切分、语法检查、类型推断生成一棵语法树。列表里有 2 万个 IDSQL 文本就有几百 KB解析器要逐个处理这些常量把它们变成执行树里的节点。这个开销在每一次执行时都会发生除非应用层使用了预编译语句。我在优化那个报表服务时发现业务代码是直接把 ID 列表String.join拼进 SQL 的。这意味着每次请求都在重复解析很长的一段文本。更麻烦的是动态拼接还引入了安全风险——虽然这个场景里 ID 都是内部数据但如果有一天列表内容变成用户可控的输入拼 SQL 就约等于把注入漏洞送给对方。UNNEST改写的一个副作用是让列表变成了数组参数查询文本不再随 ID 数量无限膨胀解析成本自然降下来。1.2 优化器阶段IN 列表让“估算”变成“猜谜”解析完之后优化器要决定怎么执行。PostgreSQL 在拿到col IN (v1, v2, ...)时通常会把它转成col ANY(ARRAY[v1, v2, ...])的形式。这种形式本身没问题问题在于优化器需要对“这一大堆值整体匹配多少行”做估算。正常情况下优化器会参考列上的直方图统计信息估算每个值出现的频率再累加成整个列表的选择性。可现实中的 ID 列表是业务方临时给的往往是一批最近才创建的 ID、或者一批外部导入的 ID它们可能压根不在统计信息的直方图里。值越多估算偏差越大。当列表长到一定程度优化器会倾向于认为“你都快把全表的值列出来了不如直接全表扫描算了”。于是它放弃索引选择 Seq Scan 逐行过滤而大表全扫的成本是灾难级的。你可以这样理解IN列表的每个值都是一道判断题优化器要对几万道判断题逐一估价工作量巨大且结果还不准而UNNEST之后列表变成一张行数已知的小表优化器只需要比较两张表的大小选连接策略估价难度完全不在一个量级。1.3 执行阶段大 IN 列表对索引与内存的双重压力当列表还比较小的时候比如几十个值数据库可以用 BitmapOr 把多个索引位图合并起来扫描效果好得惊人。但值一多位图操作本身就成了热点每个值对应一次索引探针几万个探针意味着几万次随机 I/O位图合并还要占用大量内存。更隐蔽的问题是执行时的过滤效率。全表扫描虽然顺序读很快但每一行都要和几万个值做一次成员判断。数据量大时这个过滤操作会把 CPU 跑满内存里又装不下所有值可能还要走哈希或者反复扫描数组慢就慢在这里。所以你会发现IN列表的慢不是单一原因而是解析文本长、优化器估算乱、执行时随机 I/O 和内存压力叠加在一起的结果。理解了这一点你就能明白为什么UNNEST能同时解决好几个问题。2. UNNEST 改写到底改了什么把“值列表”变成“临时表”UNNEST是 PostgreSQL 和 BigQuery 里的数组展开函数作用是把一个数组变成一张表一个元素一行。改写核心思路很朴素与其在WHERE里塞几万个值让数据库做成员判断不如把这一堆值先变成一张临时表然后用最成熟的连接算法去处理。2.1 从集合语义重新理解 IN 与 UNNESTWHERE id IN (1,2,3)是谓词过滤语义是“这一行的 id 是否属于这个集合”。优化器要判断的是“过滤条件的选择性”而选择性估算依赖统计信息。JOIN unnest(ARRAY[1,2,3]) AS t(id) ON t.id orders.id是集合连接语义是“把这两组数据按 id 关联起来”。这时候优化器面对的是两张关系表左边是订单表右边是一个行数完全确定等于数组长度的临时表。行数确定意味着基数估算稳定连接策略可选 Hash Join、Nested Loop、Merge Join并行计划也能参与进来。一句话总结IN把数据写在“条件”里UNNEST把数据变成“关系”。数据库最擅长处理关系所以你要给它关系。2.2 三种常见改写姿势与写法对比我在项目中实际用过的有三种写法你按场景挑。第一种最小改动 ANY(ARRAY[...])。严格说它不算UNNEST但它已经把常量收进了数组查询文本不再膨胀也是很多场景下性价比最高的选择。SELECT * FROM orders WHERE product_id ANY(ARRAY[101, 102, 103]);第二种IN加子查询展开SELECT * FROM orders WHERE product_id IN (SELECT unnest(ARRAY[101, 102, 103]));第三种JOIN UNNEST我最终采用的是这种SELECT o.* FROM orders o JOIN unnest(ARRAY[101, 102, 103]) AS t(product_id) ON t.product_id o.product_id;第三种之所以更推荐是因为它把数组的关系属性利用得最充分。如果你还需要保留元素在数组里的原始顺序可以配合WITH ORDINALITY使用SELECT o.* FROM orders o JOIN unnest(ARRAY[SKU-A,SKU-B,SKU-C]) WITH ORDINALITY AS t(sku, ord) ON t.sku o.sku ORDER BY t.ord;在 BigQuery 里语法更简洁一些官方推荐的写法就是IN UNNESTSELECT * FROM orders WHERE product_id IN UNNEST(product_ids);2.3 改写后优化器为什么能给出更好的计划有三个直接的好处我逐一说明。第一解析和规划时间下降。查询文本从几十 KB 变成几行数组参数化之后文本完全固定数据库可以放心缓存执行计划。我遇到过有些系统开启了plan_cache_mode或者使用预备语句IN列表版本因为每次 SQL 文本都不同计划缓存形同虚设改写后缓存命中率明显上升。第二估算稳定。UNNEST函数的行数预估就是数组长度比如unnest(ARRAY[...])有 2 万个元素优化器就认为右侧表有 2 万行不会再瞎猜。主表的选择性也能通过连接基数准确推导出来。第三连接策略空间更大了。IN列表基本只能走 BitmapOr 或者全表扫描而UNNEST改写后如果列表小、主表大且索引选择性好优化器会选 Nested Loop 并让内侧走索引如果列表很大、主表也大就选 Hash Join用哈希表避免海量随机 I/O。优化器的工具箱一下从两把扳手变成了全套。3. 实测对比我跑过的 IN 与 UNNEST 三组数据说再多原理不如看数据。下面这组对比是我在测试环境里做的环境是 PostgreSQL 14一张 2000 万行的订单表product_id上建有普通 B-tree 索引work_mem64MB开启并行查询每次查询用EXPLAIN (ANALYZE, BUFFERS)执行 5 次取中位数。必须提醒的是不同数据分布、不同硬件、不同work_mem下结果会有差异但量级趋势是稳定的。3.1 测试场景与口径说明为了模拟真实业务我没有用连续 ID而是从订单表里随机抽取不同数量的product_id覆盖高频和低频两类商品。列表长度分别是 500、5000、50000。查询目标是找出这些商品对应的订单记录。所有查询都清空缓存后重新执行避免热缓存掩盖真实的 CPU 成本。3.2 结果解读什么差距是真实的这是一组典型的测试结果写法500 个 ID5000 个 ID50000 个 IDIN 列表动态拼接42ms580ms6.9s ANY(ARRAY[...])40ms470ms5.4sIN (SELECT unnest(...))41ms360ms2.6sJOIN unnest(...) ON ...38ms180ms0.9s列表只有 500 个值时四种写法的差距很小基本都在几十毫秒内这时候改不改无所谓。到了 5000 个值JOIN UNNEST的优势已经很明显耗时只有IN列表的三分之一。到 50000 个值时差距被拉得非常大IN列表耗时接近 7 秒而JOIN UNNEST不到 1 秒。这个差距主要来自执行策略的分化IN列表在 50000 个值的时候优化器估出选择性极高选择了全表扫描加逐行过滤而JOIN UNNEST走的是 Hash Join把 5 万个 ID 放进哈希表2000 万行订单表顺序扫描一遍每行做一次哈希探测整体就是一次稳定的 O(N) 操作不会有随机 I/O 的灾难。3.3 执行计划里的关键差异点抓执行计划能更清楚地看到差异。IN列表版本在 50000 个值时的计划大致长这样Bitmap Heap Scan on orders Recheck Cond: (product_id ANY ({...}::integer[])) - BitmapOr - Bitmap Index Scan on idx_orders_product_id (...) - Bitmap Index Scan on idx_orders_product_id (...) ...大量重复节点JOIN UNNEST版本则干净很多Hash Join Hash Cond: (o.product_id t.product_id) - Seq Scan on orders o - Hash - Function Scan on unnest t第二份计划里右侧的Function Scan行数明确哈希连接能并行执行内存占用可控。这个对比也解释了为什么同样的数据量改写前后差了 7 倍以上。实操建议不要只看耗时一定要用EXPLAIN (ANALYZE, BUFFERS)看计划形态确认BitmapOr节点是不是变成了Hash Join或带索引的Nested Loop这才算真正改到位。4. 容易踩的坑UNNEST 不是无脑替换UNNEST改写虽然好用但坑也不少。我在生产环境里踩过、也帮别人排查过总结出四类最容易出问题的地方。4.1 类型不匹配与隐式转换数组的元素类型必须能和列类型匹配。比如列是bigint你传入text[]PostgreSQL 在比较时会尝试隐式转换转换失败直接报错转换成功也可能导致索引失效。正确做法是显式声明类型SELECT o.* FROM orders o JOIN unnest($1::bigint[]) AS t(product_id) ON t.product_id o.product_id;应用层传参时也要注意。JDBC 里可以用connection.createArrayOf(bigint, list)Python 的 psycopg2 能把 list 自动适配成数组Go 用 pgx 时直接传[]int64。关键是要让数据库收到的就是数组类型而不是字符串再让数据库去拆。4.2 NULL 语义差异一个容易在深夜出事的点IN和UNNEST在 NULL 处理上有一个非常容易被忽略的差异。x IN (1, 2, NULL)在x不等于 1 或 2 时结果不是 FALSE而是 UNKNOWN会直接被 WHERE 过滤掉。换句话说列表里有 NULL 不会让结果变多最多是让匹配变模糊。但如果你写成x NOT IN (SELECT unnest(...))而数组里包含 NULL问题就大了整条NOT IN链会因为 UNKNOWN 而永远不返回任何行。这是一个经典陷阱。正确做法是改用NOT EXISTSSELECT o.* FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM unnest($1::bigint[]) AS t(product_id) WHERE t.product_id o.product_id );NOT EXISTS是精确的集合差语义不受 NULL 干扰。只要你的业务列表可能包含空值就不要用NOT IN的UNNEST版本。4.3 重复值放大了结果行数IN (1,1,1)和id ANY({1,1,1})语义上只匹配一次但JOIN unnest({1,1,1})会匹配三行。如果业务上列表来自用户勾选、接口去重没做好结果集可能被成倍放大尤其当主表行数多时这个放大效应会污染统计结果或者让前端分页出问题。我一般用两种方式规避一是展开时先去重SELECT o.* FROM orders o JOIN (SELECT DISTINCT product_id FROM unnest($1::bigint[]) AS t(product_id)) t ON t.product_id o.product_id;二是直接用EXISTS语义保留原始的成员判断逻辑还不会放大行数SELECT o.* FROM orders o WHERE EXISTS ( SELECT 1 FROM unnest($1::bigint[]) AS t(product_id) WHERE t.product_id o.product_id );这个写法和IN的语义最接近我日常用得最多。还要注意空数组的情况JOIN unnest(ARRAY[]::int[])会得到零行结果为空这和id ANY({})的结果一致是符合预期的不用担心。4.4 小列表别折腾索引问题也别焦虑不是所有IN都值得改。列表只有三五个值或者查询本身低频执行改写不会有任何收益反而因为多了一次函数调用和连接运算可能比原来慢一点点。我的经验阈值是列表长度超过几百、并且会持续增长才值得系统性改写。几百以下IN的表现已经足够好。还有人担心JOIN UNNEST会丢索引。实际上优化器在 Nested Loop 内侧完全可以对t.product_id o.product_id走索引关键取决于统计信息和成本。如果你的场景过滤性极强比如列表里只有几个 ID但要匹配几千万行的表优化器却选了 Hash Join 全表扫描这通常和统计信息不新有关跑一次ANALYZE或者调整work_mem让哈希更便宜计划就会变化。不要迷信某一种计划形态验证执行计划才是唯一标准。另外补充一句如果你是 SQL Server 用户UNNEST不可用等价的方案是OPENJSON拆数组或者表值参数MySQL 8 则用JSON_TABLE。这些是另一套玩法不在本文范围但思路一致把条件里的值列表变成表再去连接。5. 我的最终建议什么时候该把 IN 换成 UNNEST这篇文章写到这里核心内容已经讲完了。最后结合我自己的实战经验给出一份可以直接抄的选型清单。5.1 适用场景清单适合改写的场景有三个特征IN列表长度会超过千级且业务上可能继续增长同一类查询高频执行解析和计划缓存成本不可忽视列表内容动态生成来源是上游系统的 ID 集合、用户勾选结果或接口参数。不适合改写的场景也明确一下列表只有几个到几十个值完全没压力数据库不支持数组和UNNEST需要另找替代方案列表值与主表数据高度重叠几乎覆盖全表任何写法都救不了这时该想的是业务逻辑问题。5.2 工程化实践从一次改写变成一套规范如果你决定在项目里推广这个方案我建议在代码层面做统一封装而不是让每个开发自己拼 SQL。我在团队里做了一个简单的查询构造方法接收ListLong参数内部统一生成JOIN unnest($1::bigint[])语句并对列表长度做上限保护超过 5 万就自动分批避免一次数组过大占用过多内存。同时在慢查询日志里加了监控凡是BitmapOr节点数量超过 50 的查询都会被单独标记提示排查是否有IN列表未改。并发场景下还要注意work_mem的预算。Hash Join 的哈希表占用的是会话内存work_mem设得太小会落盘太大在并发高时会拖垮机器。我会先拿真实列表长度压测一轮观察EXPLAIN (ANALYZE, BUFFERS)里有没有Temp File节点再决定是调work_mem还是控制单批列表长度。最后再分享一个小技巧如果线上已经上线了IN列表版本想改又不敢改可以先用 ANY(ARRAY[...])过渡。它的解析收益和索引行为与UNNEST接近改动量却小得多。等确认执行计划稳定了再逐步迁移到JOIN UNNEST。这种小步快走的改法比一次性重写 SQL 安全得多。优化 SQL 这事从来都不是炫技而是找到数据库真正擅长的工作方式然后把它交给数据库。