如何优化带分页需求的大数据量PostgreSQL查询性能

我使用Spring Boot结合PostgreSQL数据库，现有三张表：两张数据源表存储实际交易记录，第三张输出表存储前两张表的对账结果。输出表结构如下：

CREATE TABLE output_table (
  id int4 NOT NULL,
  trxn_a_ids text,
  trxn_b_ids text,
  account_id int4,
  status varchar(100),
  date date
);

输出表的trxn_a_ids或trxn_b_ids可存储1个或多个数据源表ID，对应1:1、1:M、M:1或M:M的交易对账关系。正常流程中，我会按日期、account_id从输出表分页取数，再通过这两个字段从数据源表获取实际交易记录作为API响应。

现在需要对数据源表的列执行LIKE模糊匹配，但无法直接确定符合条件的数据源记录对应输出表的页码。曾尝试在Java中全量拉取输出表和数据源表数据后分页，但每次API请求都要拉取数百万条记录再过滤，性能极差。

我编写了如下PostgreSQL查询解决该问题，但大数据量下性能不佳，希望获得优化建议，同时保留分页功能：

SELECT outputTable.*
FROM output_table outputTable
WHERE outputTable.date = '2023-01-12' 
  AND outputTable.account_id = '0001'
    
  AND (coalesce(outputTable.trxn_a_ids, '')='' OR string_to_array(outputTable.trxn_a_ids, ',')::int[] <@ ARRAY(SELECT id FROM dsA WHERE date = '2023-01-12' and account_id='0001'
 and (columnA like '%3200404%' or columnB like '%3200404%')))
 
      AND (coalesce(outputTable.trxn_b_ids, '')='' OR string_to_array(outputTable.trxn_b_ids, ',')::int[] <@ ARRAY( SELECT id FROM dsB WHERE date = '2023-01-12' and account_id='0001'
 and (columnA like '%3200404%' or columnB like '%3200404%')))
 
 offset 0 limit 10; 

1. 重构输出表的ID存储方式 #

把trxn_a_ids和trxn_b_ids的逗号分隔文本改成数组类型（int[]），或者拆分成关联表：

• 用数组类型：直接存储int[]，避免每次查询调用string_to_array做类型转换，减少计算开销。
• 拆成分关联表：新建output_trxn_a、output_trxn_b两张关联表，每条输出记录对应多条数据源ID记录，用JOIN替代数组包含查询，利用索引大幅提升性能：

  CREATE TABLE output_trxn_a (
      output_id int4 REFERENCES output_table(id),
      trxn_a_id int4 REFERENCES dsA(id),
      PRIMARY KEY (output_id, trxn_a_id)
  );
  CREATE TABLE output_trxn_b (
      output_id int4 REFERENCES output_table(id),
      trxn_b_id int4 REFERENCES dsB(id),
      PRIMARY KEY (output_id, trxn_b_id)
  );
  

  给关联表创建(trxn_a_id, output_id)、(trxn_b_id, output_id)复合索引，查询时可直接通过JOIN过滤。

2. 优化数据源表的LIKE查询 #

开头带通配符的LIKE '%xxx%'无法利用普通B树索引，可通过以下方式优化：

• 创建GIN trigram索引：给需要模糊匹配的列创建GIN_trgm_ops索引，支持任意位置的模糊匹配：

  CREATE INDEX idx_dsa_columnA_trgm ON dsA USING GIN (columnA gin_trgm_ops);
  CREATE INDEX idx_dsa_columnB_trgm ON dsA USING GIN (columnB gin_trgm_ops);
  CREATE INDEX idx_dsb_columnA_trgm ON dsB USING GIN (columnA gin_trgm_ops);
  CREATE INDEX idx_dsb_columnB_trgm ON dsB USING GIN (columnB gin_trgm_ops);
  

• 优先使用前缀匹配：如果业务允许，将模糊匹配改为'xxx%'格式，可直接利用普通B树索引，性能更优。

3. 重写查询逻辑，避免子查询数组包含 #

基于重构后的结构重写查询，替代原有的数组包含逻辑：

关联表版本 #

SELECT DISTINCT ot.*
FROM output_table ot
LEFT JOIN output_trxn_a ota ON ot.id = ota.output_id
LEFT JOIN output_trxn_b otb ON ot.id = otb.output_id
LEFT JOIN dsA a ON ota.trxn_a_id = a.id
LEFT JOIN dsB b ON otb.trxn_b_id = b.id
WHERE ot.date = '2023-01-12' 
  AND ot.account_id = '0001'
  AND (
    ota.trxn_a_id IS NULL 
    OR (a.date = '2023-01-12' AND a.account_id='0001' AND (a.columnA LIKE '%3200404%' OR a.columnB LIKE '%3200404%'))
  )
  AND (
    otb.trxn_b_id IS NULL 
    OR (b.date = '2023-01-12' AND b.account_id='0001' AND (b.columnA LIKE '%3200404%' OR b.columnB LIKE '%3200404%'))
  )
OFFSET 0 LIMIT 10;

数组类型版本 #

SELECT ot.*
FROM output_table ot
WHERE ot.date = '2023-01-12' 
  AND ot.account_id = '0001'
  AND (
    ot.trxn_a_ids = '{}'::int[] 
    OR EXISTS (
      SELECT 1 FROM dsA a 
      WHERE a.id = ANY(ot.trxn_a_ids) 
        AND a.date = '2023-01-12' 
        AND a.account_id='0001' 
        AND (a.columnA LIKE '%3200404%' OR a.columnB LIKE '%3200404%')
    )
  )
  AND (
    ot.trxn_b_ids = '{}'::int[] 
    OR EXISTS (
      SELECT 1 FROM dsB b 
      WHERE b.id = ANY(ot.trxn_b_ids) 
        AND b.date = '2023-01-12' 
        AND b.account_id='0001' 
        AND (b.columnA LIKE '%3200404%' OR b.columnB LIKE '%3200404%')
    )
  )
OFFSET 0 LIMIT 10;

用EXISTS替代数组包含<@，配合数组字段的GIN索引，性能远优于原查询。

4. 给输出表创建复合索引 #

给output_table创建(date, account_id, id)复合索引，分页时可快速定位数据：

CREATE INDEX idx_output_date_account_id ON output_table (date, account_id, id);

5. 避免Java层全量过滤 #

所有过滤逻辑下推到数据库，利用数据库的索引和查询优化器处理，只返回分页后的结果，禁止在Java层拉取全量数据再过滤。

内容的提问来源于stack exchange，提问作者Muzaffar Ali