你真的很懂分页查询吗？-51CTO.COM

本文转载自微信公众号「红石PG」，作者红石PG 。

背景

分页的基础知识在其他地方有详细介绍，但是类似以下方式实现的偏移分页，会带来与偏移量的大小成比例的性能成本，这对于大的偏移量是相当大的。这是因为 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 分页有两个大问题，结果不一致和偏移效率低下。一致性是指，遍历结果集的过程中，每个行应精准检索一次，而不会遗漏或重复。偏移效率低下是指将结果偏移较大时会产生的延迟。

键集分页速度很快，而且也是一致的。在当前页面之前插入/删除任何内容都不会影响结果。这种方法的两个缺点是，对随机访问支持不友好，以及客户端和服务器之间可能存在耦合。