PostgreSQL 使用IN与OR做多列匹配长列表查询效率低如何优化

性能问题核心原因 #

当前写法存在两个核心性能瓶颈：

• 多字段OR条件会导致数据库无法高效利用单字段索引，即便三个字段都单独创建了索引，数据库大概率要么选择全表扫描，要么需要分别扫描三个索引后做结果合并，开销极高
• 长度在1000以上的长IN列表本身解析、匹配的开销很大，数据库优化器也很难对长IN列表做准确的成本估算，容易生成低效的执行计划

可落地的优化方案 #

方案1：改写OR为UNION ALL，拆分查询命中索引 #

将三个OR分支拆分为三个独立的查询，用UNION ALL合并结果。如果业务场景允许返回重复数据（比如同一个记录同时匹配两个字段）直接用UNION ALL，需要去重则用UNION。改写后每个独立查询都可以单独命中对应字段的索引，避免全表扫描。
示例SQL：

SELECT "id", "parcel_number", "alternate_parcel_number", "parcel_tax_number"
FROM "addresses"
WHERE "parcel_number" IN ('A080100', ... 'A0368895224')
UNION ALL
SELECT "id", "parcel_number", "alternate_parcel_number", "parcel_tax_number"
FROM "addresses"
WHERE "alternate_parcel_number" IN ('A080100', ... 'A0368895224')
UNION ALL
SELECT "id", "parcel_number", "alternate_parcel_number", "parcel_tax_number"
FROM "addresses"
WHERE "parcel_tax_number" IN ('A080100', ... 'A0368895224');

方案2：替换长IN列表为临时表关联 #

把需要匹配的1000~10000个值提前写入临时表，再通过表关联代替IN匹配，可大幅降低长列表的解析开销，还可以给临时表的匹配字段加索引进一步提升关联效率。
示例流程（以PostgreSQL为例）：

-- 1. 创建临时表存储匹配值
CREATE TEMP TABLE tmp_search_values (val VARCHAR(255) PRIMARY KEY);
-- 2. 插入所有需要匹配的值（可以用批量插入或者COPY命令）
INSERT INTO tmp_search_values (val) VALUES ('A080100'), ... ('A0368895224');
-- 3. 关联查询，配合UNION ALL命中索引
SELECT a.* FROM "addresses" a
JOIN tmp_search_values s ON a.parcel_number = s.val
UNION ALL
SELECT a.* FROM "addresses" a
JOIN tmp_search_values s ON a.alternate_parcel_number = s.val
UNION ALL
SELECT a.* FROM "addresses" a
JOIN tmp_search_values s ON a.parcel_tax_number = s.val;

方案3：创建覆盖索引避免回表 #

如果当前仅创建了三个字段的普通单值索引，查询时还需要回表获取其他字段数据，额外增加了IO开销。可以创建覆盖索引，直接从索引中返回所有需要的查询字段，彻底避免回表。
不同数据库的覆盖索引创建示例：

• PostgreSQL：CREATE INDEX idx_parcel_num ON addresses (parcel_number) INCLUDE (id, alternate_parcel_number, parcel_tax_number); 另外两个字段也按相同规则创建索引
• MySQL：CREATE INDEX idx_parcel_num ON addresses (parcel_number, id, alternate_parcel_number, parcel_tax_number); 另外两个字段也按相同规则创建索引

方案4：适配数据库特性简化查询 #

如果使用PostgreSQL等支持数组操作的数据库，可以将匹配参数替换为数组，用ANY语法替代长IN列表，优化器对数组的处理效率远高于长IN列表：

SELECT * FROM addresses WHERE parcel_number = ANY(ARRAY['A080100', ... 'A0368895224']::VARCHAR[])
-- 配合UNION ALL写法效果更好

验证方法 #

优化后执行EXPLAIN ANALYZE（PostgreSQL）/EXPLAIN（MySQL）查看执行计划，确认以下两点即可判定优化生效：

• 没有出现Seq Scan on addresses全表扫描逻辑
• 三个查询分支都命中了对应字段的索引

内容的提问来源于stack exchange，提问作者Jaime Valdez