数据库分页策略

[xiayulu]

分页需求：共100页，每页50条数据，页号从 1 开始。

PostgreSQL 数据库分页主要有三种策略：

1. offset + limit
2. WITH HOLD cursors
3. keyset

第一种方法

SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
OFFSET 4950 LIMIT 50;

计算公式：offset = (page - 1) * pageSize

第二种方法

先声明游标：

START TRANSACTION;
 
/*
 * Don't use SCROLL unless you need to scroll backwards,
 * for example to get the total count (see below).
 */
DECLARE c SCROLL CURSOR WITH HOLD FOR
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;
 
/* this will calculate the whole result set */
COMMIT;

然后

/* get page number 100 */
MOVE ABSOLUTE 4950 IN c;
FETCH 50 FROM c;

最后

CLOSE c;

第三种方法

思路是记录下上一次最后一个记录的 id 值，显然下一页的 id 值都会大于这个 id 值

SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
  AND (created, id) > ('2022-01-01 01:27:35+01', 5256)
ORDER BY created, id
LIMIT 50;

参考文章

https://www.cybertec-postgresql.com/en/pagination-problem-total-result-count/