SQL Seek Method 或 Keyset 分页

Last modified: Jul 15, 2021

最后修改日期：Jul 15, 2021

In this article, we are going to see what the SQL Seek Method or Keyset Pagination is and why you should consider it when navigating over large results sets.

在本文中，我们将看到SQL Seek方法或Keyset分页是什么，以及在浏览大型结果集时为什么应该考虑使用它。

The goal of pagination is to avoid fetching large volumes of data since the UI has a limited viewport that could be used to display data.

分页的目标是避免获取大量数据，因为 UI 的视口有限，只能显示一部分数据。

OFFSET Pagination

OFFSET分页

Before discussing Keyset Pagination, let’s see how the default OFFSET pagination works in SQL.

在讨论Keyset分页之前，让我们先看看SQL中默认的OFFSET分页是如何工作的。

Although relational database systems have long been providing specific ways of restricting a query result set, since SQL:2008, there is a standard pagination syntax.

尽管关系数据库系统长期以来一直提供了限制查询结果集的特定方法，但自SQL:2008以来，有了标准的分页语法。

Therefore, a TOP-N query that limits the number of records of a given result set can use the FETCH FIRST N ROWS ONLY directive, as illustrated by the following example:

因此，限制给定结果集记录数量的TOP-N查询可以使用FETCH FIRST N ROWS ONLY指令，如下例所示：

SELECT id
FROM post
ORDER BY created_on DESC
FETCH FIRST 50 ROWS ONLY

And, a NEXT-N query that skips over the first M records and fetches the next N records looks like this:

而且，跳过前M条记录并获取接下来的N条记录的NEXT-N查询如下所示：

SELECT id
FROM post
ORDER BY created_on DESC
OFFSET 150 ROWS
FETCH NEXT 50 ROWS ONLY

OFFSET Pagination Indexing

OFFSET分页索引

Since pagination requires an ORDER BY clause in order to guarantee a consistent sort order, it’s common to index the sorting criteria.

由于分页需要在ORDER BY子句中保证一致的排序顺序，因此通常会对排序条件进行索引。

In our case, we need to create the following index on the created_on column:

在我们的情况下，我们需要在created_on列上创建以下索引：

CREATE INDEX idx_post_created_on ON post (created_on DESC)

When executing the TOP-N query, we can see that the idx_post_created_on is being used, and only 50 records are being scanned:

在执行TOP-N查询时，我们可以看到idx_post_created_on被使用，并且只扫描了50条记录：

SELECT id
FROM post
ORDER BY created_on DESC
FETCH FIRST 50 ROWS ONLY
 
Limit  (cost=0.28..2.51 rows=50 width=16)
       (actual time=0.013..0.0...