你真的很懂分页查询吗?

数据库 其他数据库
分页查询被广泛应用于很多应用系统中,然而,对查询语句返回的结果集进行分页,其实并没有表面上看起来那么简单。
本文转载自微信公众号「红石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 分页有两个大问题,结果不一致和偏移效率低下。一致性是指,遍历结果集的过程中,每个行应精准检索一次,而不会遗漏或重复。偏移效率低下是指将结果偏移较大时会产生的延迟。

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

责任编辑:武晓燕 来源: 红石PG
相关推荐

2018-07-17 16:26:17

大数据营销消费者

2021-08-31 10:52:30

容量背包物品

2016-04-21 09:43:33

编程音乐

2018-07-09 08:35:45

Windows 10WindowsBug

2010-03-02 17:22:46

Android技术

2010-02-23 16:21:24

Python Win

2010-03-10 11:14:56

智能交换机

2010-03-17 14:50:06

智能交换机

2010-02-06 10:34:11

Android生命周期

2010-01-21 17:14:40

C++兼容

2010-01-20 10:14:53

C++程序

2022-09-14 08:11:06

分页模糊查询

2017-02-07 15:25:39

5Gwifi上网

2012-07-11 13:35:53

代码

2022-02-14 21:31:00

用户身份验证

2010-03-02 15:22:40

Android手机

2019-01-21 13:56:52

2019-11-05 09:20:06

SQLiteLinux

2016-06-01 15:42:58

Hadoop数据管理分布式

2014-04-17 16:42:03

DevOps
点赞
收藏

51CTO技术栈公众号