1亿行transfer表双字段排序分页的优化最佳实践

问题场景 #

有一张约1亿行的transfer表，需按db_updated_at DESC, id DESC的顺序遍历数据。当前使用的REST API查询语句执行速度极慢，执行计划显示扫描过程中过滤掉了超过1600万行数据，耗时近3分钟。

表结构 #

create table transfer
(
    id                  bigint                   not null primary key,
    db_updated_at       timestamp with time zone,
    ...
);

当前查询语句 #

SELECT * FROM transfer
WHERE "db_updated_at" < '2022-11-18 23:38:44+03' OR (db_updated_at = '2022-11-18 23:38:44+03' and id < 154998555017734)
ORDER BY "db_updated_at" DESC, "id" DESC LIMIT 100

执行计划 #

Limit  (cost=0.56..26.92 rows=10 width=230) (actual time=182494.092..182494.273 rows=10 loops=1)
  ->  Index Scan using transfer_db_updated_at_id_desc on transfer  (cost=0.56..55717286.28 rows=21142503 width=230) (actual time=182494.089..182494.266 rows=10 loops=1)
        Filter: ((db_updated_at < '2022-11-18 20:38:44+00'::timestamp with time zone) OR ((db_updated_at = '2022-11-18 20:38:44+00'::timestamp with time zone) AND (id < '154998555017734'::bigint)))
        Rows Removed by Filter: 16040385
Planning Time: 0.364 ms
Execution Time: 182494.312 ms

最佳实践 #

1. 重构查询条件，让索引直接生效 #

当前的OR条件导致数据库无法直接利用复合索引定位目标范围，只能先扫描索引再过滤。可以将条件改写为复合范围查询，直接匹配索引的有序性：

SELECT * FROM transfer
WHERE (db_updated_at, id) < ('2022-11-18 23:38:44+03', 154998555017734)
ORDER BY db_updated_at DESC, id DESC LIMIT 100;

这种写法利用PostgreSQL对复合索引的范围查询支持，直接定位到小于目标(db_updated_at, id)的所有行，无需额外过滤，大幅减少扫描行数。

2. 采用键集分页替代传统偏移量分页 #

传统LIMIT/OFFSET在大数据量下会随偏移量增大变慢，键集分页通过上一页最后一条数据的db_updated_at和id作为下一页的查询条件，完全依赖索引有序性直接定位：

• 第一页查询：

  SELECT * FROM transfer
  ORDER BY db_updated_at DESC, id DESC LIMIT 100;
  

• 后续页面查询（以上一页最后一条的db_updated_at='2022-11-18 23:38:44+03'、id=154998555017734为例）：

  SELECT * FROM transfer
  WHERE (db_updated_at, id) < ('2022-11-18 23:38:44+03', 154998555017734)
  ORDER BY db_updated_at DESC, id DESC LIMIT 100;
  

每次查询直接从索引指定位置开始扫描，不会出现大量无效扫描或过滤。

3. 保证复合索引顺序与查询排序一致 #

当前的transfer_db_updated_at_id_desc索引顺序为db_updated_at DESC, id DESC，与查询排序完全匹配，这一点是正确的。若索引顺序不匹配，数据库需额外排序，会大幅增加耗时。

4. 按时间维度分区优化 #

如果db_updated_at的时间分布有明显规律（如按天/月产生数据），可将transfer表按db_updated_at做范围分区，查询时数据库只会扫描目标时间范围内的分区，避免全表索引扫描：

CREATE TABLE transfer (
    id                  bigint                   not null,
    db_updated_at       timestamp with time zone,
    ...
) PARTITION BY RANGE (db_updated_at);

CREATE TABLE transfer_202211 PARTITION OF transfer
    FOR VALUES FROM ('2022-11-01') TO ('2022-12-01');

5. 避免全字段查询，使用覆盖索引 #

若业务不需要所有字段，只查询必要字段可减少数据传输和内存消耗。同时可创建覆盖索引，将查询所需字段包含在内，避免回表查询：

CREATE INDEX idx_transfer_updated_id_cover ON transfer (db_updated_at DESC, id DESC) INCLUDE (col1, col2, col3);

查询时直接从索引获取数据，无需访问主表，提升查询速度。

内容的提问来源于stack exchange，提问作者Roman N