编程

关于keyset分页

1730 2021-12-25 11:29:14

你知道吗,使用 offset 进行分页非常烦人,却很容易可以避开。

offset 指示数据库跳过前 N 条数据进行查询。然而,数据库仍然需要从磁盘读取记录,排序…

这不是执行问题,而是 offset 设计方式:

…the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…

SQL:2016, Part 2, §4.15.3 Derived tables

译:…记录先按照 order by 条件排序,然后通过丢弃掉 offset 条件指定数量的行数…

 

这里的关键点是,offset 只有一个参数:丢掉的记录数,没有更多内容。数据库能做的只有获取和丢掉许多记录,换句话说,大的 offset 会带来数据库大量的工作,无论是 SQL 还是 NoSQL.

不仅如此:想象一下在读取两个页面的之间插入了一条新数据?

 

 

 

我们使用 offset➌ 来跳过前面获取的 entries❶, 你会同时得到副本以防在获取两个页面之间插入新数据➋。同时可能还有其他异常,这只是最常见的问题之一。

这甚至不是数据库问题,而是框架实现分页的方式问题:他只会说明要获取那一页或者要跳过多少行。只有这些信息,任何数据库都差不多。

同时请注意 offset 问题可能来自于不同的语法:

  • offset 关键词
  • 双参数 limit [offset,] limit(单参数 limit 没问题) 
  • 基于行数的 Lower-bound 筛选  (e.g. row_number(), rownum, …).
  • 所有这些方法都有一个普遍的根本问题,它们只提供一个数字即需要丢掉的行数——而没有其他更多内容了。在此文中,我使用 offset 代指这些方法。

没有 OFFSET 的世界

想象一下没有这些问题。事实上,没有 offset 其实挺简单:只需要使用 where 条件选取你还没看过的记录。

为此,我们利用一个事实是我们以有序集合进行工作——你已经按照 order by 条件进行了排序。一旦给定排序,我们可以使用简单筛选器选取你已有的之后的数:

SELECT ...
  FROM ...
 WHERE ...
   AND id < ?last_seen_id
 ORDER BY id DESC
 FETCH FIRST 10 ROWS ONLY

这是基本的方法。当在多字段排序时变得更加有趣,不过理念相同。该方法同样适用于 NoSQL 系统。

这个方法——称为 seek method 或者 keyset 分页(keyset pagination)——解决了以上图示显示的漂移问题,甚至比 offset 更快。如果你想知道使用 offset 或者 keyset 分页时,数据库内部发生了什么

可以查看以下的幻灯片:

(幻灯片略)

在 43 页,你会发现 keyset 翻页也有以下限制:很主要的一点是你不能直接导航到任意页面。当然这不会是问题如果使用的是滚屏加载。显示页码供点击不是好的导航接口—— IMHO.

如果你想要读取更多关于怎样在 SQL 中更好地实现 keyset分页, 请读本文。即便你没有涉及 SQL,此文也值得一读。

至于框架…

更喜欢选择 offset 而不是 keyset 主要是缺少工具支持。大部分提供分页的工具都是基于 offset, 而没有提供便捷的方式使用 keyset 分页。

请注意,keyset 分页影响了整个技术栈直到浏览器中中的的 JavaScript 发起 AJAX 请求用来加载滚屏数据,不再是发送一个简单的页码数字给服务器,你必须传送整个 keyset(通常是多列数据)给服务器。

支持 keyset 分页的框架有:

JavaScript

Node.js: bookshelf-cursor-pagination

Massive.js: Keyset Documentation

Python

Django Infinite Scroll Pagination

Django chunkator

SQL Alchemy sqlakeyset.

Java

jOOQ — Java Object Oriented Querying

blaze-persistence

PHP

Laravel 8.0+: CursorPaginator

.NET

DRF Like Paginations

MR.EntityFrameworkCore.KeysetPagination

Ruby

Sequel::SeekPagination

nexter

order_query

Perl

DBIx::Class::Wrapper