我想为大型数据集实现基于光标的分页。
使用OFFSET
基于分页,当用户想要页面时N
,您只需OFFSET
N * page_size
. 查询结果如下:
SELECT *
FROM books
ORDER BY id
OFFSET 100000
LIMIT 10;
但是OFFSET
它给出的值越大越慢,因为 PostgreSQL 必须加载和丢弃前面的行。
基于游标的方法是我们告诉用户“这里是 page N
,并且由于它的最后一条记录id
是X
,要获得下一页,您应该向我询问带有 的记录id > X
”。查询结果如下:
SELECT *
FROM books
WHERE id > 100000
ORDER BY id
LIMIT 10;
在这种情况下,PostgreSQL 只能加载需要的行。
排序时效果很好id
。但我希望能够按其他列排序 - 例如,按title
- 并且仍然分页。
问题是这title
不是唯一的。因此,如果页面上的最终记录N
的标题为“About Weasels”,并且有多本具有该标题的书籍,则请求下一页WHERE title > 'About Weasels'
可能会跳过其中的一些。
我可以通过用户 request 获得唯一值WHERE (title, id) > ('About Weasels', 100000)
,但性能很差,因为 PostgreSQL 必须(title, id)
为每一行计算。
我尝试添加一个索引来预先计算该 2 元组:CREATE INDEX books_title_and_id ON books (title, id);
...但是该索引对查询计划没有影响。
我可以创建一个索引来加快这个查询吗?
这是使用您的索引的示例:
请注意,第一次出现的
title, id
(在 > 比较中)必须在括号中,第二次出现(在 ORDER BY 中)不能在括号中。