基于检索到的最后一条记录的 SQL 分页

Question

我需要实现分页，它对分页之间的数据更改具有半弹性。标准分页依赖于 SQL 的 LIMIT 和 OFFSET，但是随着新数据点的创建或它们在排序中的排名变化，偏移量可能会变得不准确。

一个想法是保留从 API 请求的最后一个数据点并获取以下元素。我真的不知道 SQL(我们使用的是 postgres)，但这是我(当然有缺陷)尝试做类似的事情。我正在尝试将最后一个元素的位置存储为“rownum”，然后在以下查询中使用它。

WITH rownum AS (
 SELECT *, ROW_NUMBER() OVER (ORDER BY rank ASC, id) AS rownum 
 WHERE id = #{after_id}
 FROM items )
SELECT * FROM items
OFFSET rownum
ORDER BY rank ASC, id
LIMIT #{pagination_limit}

我可以看到一些问题，比如最后一项的排名是否发生了显着变化。如果有人能想到另一种方法来做到这一点，那就太好了。但如果可能的话，我想将其限制为单个数据库查询，因为这是最常访问 API 的应用程序。

Answer 1

您的整个语法不太行得通。 OFFSET 在 ORDER BY 之后。 FROM 出现在 WHERE 等之前

这个更简单的查询将执行我认为您的代码应该执行的操作:

SELECT *
FROM   items
WHERE (rank, id) > (
   SELECT (rank, id)
   FROM   items
   WHERE  id = #{after_id}
   )
ORDER  BY rank, id
LIMIT  #{pagination_limit};

比较复合类型 (rank, id) 保证相同的排序顺序。

确保你有两个索引:

• (rank, id) 上的多列索引。

• 另一个仅在 (id) 上 - 您可能已经对列执行了 pk 约束。 (具有前导 id 的多列索引也可以完成这项工作。)

关于索引的更多信息:
Is a composite index also good for queries on the first field?

如果 rank 不是 volatile 的，那么另外对其​​进行参数化而不是动态检索它会更有效 - 但 rank 的易变性似乎成为您审议的重点......