三月 25, 2024
摘要:在本教程中,您将学习如何对 PostgreSQL 中SELECT
语句返回的结果集进行分页。
目录
背景
分页的基础知识在其他地方有详细介绍,但是类似以下方式实现的偏移分页,会带来与偏移量的大小成比例的性能成本,这对于大的偏移量是相当大的。这是因为 PostgreSQL 必须构建包含所需页面的行集,而所需页面可能很大。
select * from transactions
order by created_at desc
limit <page_size> offset 100
解决这个问题的一种方法是使用键集分页;如果行集是在一个唯一列上排序的,我们可以使用该列的值来定位分页,而不是基于偏移量定位分页。只要在行集上有一个可排序的索引列,给定一个列值,我们可以在常量时间内访问到相应的分页。
我们确实会由此失去跳转到第 p 页的能力,但对于大多数应用程序来说,这不是一个问题。
键集分页
键集分页的工作原理是,通过对感兴趣的列和唯一列进行排序,来生成对表的结果集的总排序。在 PostgreSQL 中,order by
语句是不稳定的,这意味着我们需要保证结果集的总排序,以确保用户看到稳定的顺序。我们通过在所有order by
语句中包含一个唯一列作为最低优先级列,来实现这一点。在这里,主键是一个很好的选择。
要获取一个分页的结果,我们可以这样做:
select * from transactions
where created_at, id >= '<cursor_created_at>', '<cursor_id>'
order by created_at, id
limit <page_size>
那么新的问题来了,应该如何获得下一页和上一页的游标(以便服务于 UI 上的前进和后退按钮)?还有如何实现多列排序?
有几点需要考虑:
1. 能否快速检索到当前页面、下一页的游标位置和上一页的游标位置?并且所有这些都应在一个查询中完成。
2. 是否可以轻松地扩展查询,以支持任意排序和筛选?
基本思想是在一个 CTE 表达式中获取当前页面和游标(它们在行集中相邻),在另一个 CTE 表达式中获取前一页的游标,合并结果,相应地标记它们(previous_cursor,current_page,next_cursor)并返回它们,所有这些都是作为一个完整查询的一部分。
第一步是过滤表中的数据。我们不用物化filtered_rows
表达式,这会强制表达式与依赖查询合并(请参阅文档,以了解更多信息)。在这样做的过程中,我们需要付出为每个依赖查询重新计算filtered_rows
查询的额外成本,但由此获得了利用表上索引的能力。这大大加快了后面的操作。
为了获取游标,我们查询id
列以及排序中涉及的任何列。在这个例子中,我们在created_at
上排序,但是如果需要的话,在多个列上排序也是很容易的。请注意,我们将id
列作为低优先级列进行排序。
cursor as
(select created_at, id from rows where id = ? order by created_at, id limit 1)
获取当前页面和下一页的游标是很简单的。关键要注意的是 WHERE 子句,它只选择在总排序中游标行之后的行,还有 ORDER 和 LIMIT 子句,它们联合检索紧接在游标之后的行,形成当前页面和下一页游标:
current_page_and_next_cursor as
(select *
from rows
where (created_at, id) >= (select * from cursor)
order by created_at, id
limit <page_size + 1>
)
获取前一页也同样简单。需要注意的主要事情是,WHERE 子句中相等条件的反转(因为我们想要选择游标之前的行)和order by
子句的反转,以确保我们选择游标之前的 n 行(而不是结果集中的前 n 行)。当我们组合结果时,我们只要从集合中获取第一行,以得到前一页的游标。
previous_page as
(select *
from rows
where (created_at, id) < (select * from cursor)
order by created_at desc, id desc
limit <page_size>
),
构建完相关 CTE 表达式后,最后一项任务是将结果行合并在一起并标记它们:
(select *, 'previous_cursor' as label from previous_page order by created_at, id limit 1)
union all
(select *, 'current_page' as label from current_page_and_next_cursor order by created_at, id limit <page_size>)
union all
(select *, 'next_cursor' as label from current_page_and_next_cursor order by created_at, id limit 1 offset <page_size>);
有几件事需要在这里提一下。首先,使用union all
来保持结果集的顺序。第二点,再次使用 OFFSET 和 LIMIT 从 previous_page 和 current_page_and_next_cursor 的 CTE 表达式中选择单行,它们分别表示上一页游标和下一页游标。最后,添加一个标签,来标识与应用程序相关的行。如果前一页不存在(即当游标指向结果集中的第一行时)或下一页不存在(即当游标指向最后一页的 pageSize 大小窗口范围内的行时),则不返回游标。
总结
Limit-Offset 分页有两个大问题,结果不一致和偏移效率低下。一致性是指,遍历结果集的过程中,每个行应精准检索一次,而不会遗漏或重复。偏移效率低下是指将结果偏移较大时会产生的延迟。
键集分页速度很快,而且也是一致的。在当前页面之前插入/删除任何内容都不会影响结果。这种方法的两个缺点是,对随机访问支持不友好,以及客户端和服务器之间可能存在耦合。