分页指南

本文档概述了当前的功能，并提供了在 GitLab 中对数据进行分页的最佳实践，特别是针对 PostgreSQL。

为什么需要分页？

分页是一种避免在单个 Web 请求中加载过多数据的常用技术。这通常在我们渲染记录列表时发生。一个常见的场景是在 UI 上可视化父子关系（一对多）。

示例：列出项目中的 issues

随着项目中 issues 数量的增长，列表会变得越来越长。为了渲染这个列表，后端执行以下操作：

1. 从数据库加载记录，通常是按特定顺序。
2. 在 Ruby 中序列化记录。构建 Ruby（ActiveRecord）对象，然后构建 JSON 或 HTML 字符串。
3. 将响应发送回浏览器。
4. 浏览器渲染内容。

我们有两种渲染内容的方式：

• HTML：后端处理渲染（HAML 模板）。
• JSON：客户端（客户端 JavaScript）将有效负载转换为 HTML。

渲染长列表可能会显著影响前端和后端性能：

• 数据库从磁盘读取大量数据。
• 查询结果（记录）最终被转换为 Ruby 对象，增加了内存分配。
• 大型响应需要更多时间通过网络发送到用户的浏览器。
• 渲染长列表可能会冻结浏览器（糟糕的用户体验）。

使用分页，数据被分成相等的块（页面）。首次访问时，用户只收到有限数量的项目（页面大小）。用户可以通过向前分页查看更多项目，这会产生新的 HTTP 请求和新的数据库查询。

分页通用指南

选择合适的方法

让数据库处理分页、过滤和数据检索。在后端（Kaminari 的 paginate_array）或前端（JavaScript）实现内存分页可能适用于几百条记录。如果没有定义应用限制，事情可能会很快失控。

降低复杂度

当我们在页面上列出记录时，我们通常提供额外的过滤器和不同的排序选项。这可能会显著增加后端的复杂性。

对于 MVC 版本，考虑以下内容：

• 将排序选项的数量减少到最少。
• 将过滤器（下拉列表、搜索栏）的数量减少到最少。

为了使排序和分页高效，对于每个排序选项，我们至少需要两个数据库索引（升序、降序）。如果我们添加过滤选项（按状态或按作者），我们可能需要更多索引来保持良好的性能。索引不是免费的，它们可以显著影响 UPDATE 查询的执行时间。

不可能让所有过滤器和排序组合都具有高性能，因此我们应该尝试通过使用模式来优化性能。

为扩展做准备

基于偏移量的分页是记录分页的最简单方法，但它对于大型数据库表的可扩展性不佳。作为长期解决方案，基于键集的分页 是首选。在满足以下条件的情况下，在偏移量和键集分页之间切换通常是直接的，并且不会影响最终用户：

• 避免显示总数，优先限制计数。
  • 示例：最多计数 1001 条记录，然后在 UI 上显示 1000+（如果计数是 1001），否则显示实际数量。
  • 更多信息请参见 徽章计数器方法。
• 避免使用页码，使用下一页和上一页按钮。
  • 键集分页不支持页码。
• 对于 API，建议不要手动构建下一页的 URL。
  • 推荐使用 Link header，其中下一页和上一页的 URL 由后端提供。
  • 这样可以在不破坏向后兼容性的情况下更改 URL 结构。

分页选项

偏移量分页

分页列表最常见的方式是使用基于偏移量的分页（UI 和 REST API）。它由流行的 Kaminari Ruby gem 支持，该 gem 提供了方便的辅助方法来实现 ActiveRecord 查询的分页。

基于偏移量的分页利用 LIMIT 和 OFFSET SQL 子句从表中取出特定的切片。

在我们的项目中查找 issues 的第二页时的数据库查询示例：

SELECT issues.* FROM issues WHERE project_id = 1 ORDER BY id LIMIT 20 OFFSET 20

1. 在表行上移动一个假想的指针并跳过 20 行。
2. 取接下来的 20 行。

注意查询也按主键（id）对行进行排序。对数据进行分页时，指定顺序非常重要。没有它，返回的行是不确定的，可能会让最终用户感到困惑。

页码

示例分页栏：

Kaminari gem 在 UI 上渲染了一个漂亮的分页栏，包含页码和可选的快速快捷方式（下一页、上一页、第一页、最后一页按钮）。为了渲染这些按钮，Kaminari 需要知道行数，为此会执行一个计数查询。

SELECT COUNT(*) FROM issues WHERE project_id = 1

性能

索引覆盖

为了获得良好的性能，ORDER BY 子句需要被索引覆盖。

假设我们有以下索引：

CREATE INDEX index_on_issues_project_id ON issues (project_id);

让我们尝试请求第一页：

SELECT issues.* FROM issues WHERE project_id = 1 ORDER BY id LIMIT 20;

我们可以在 Rails 中生成相同的查询：

Issue.where(project_id: 1).page(1).per(20)

SQL 查询最多从数据库返回 20 行。但这并不意味着数据库只从磁盘读取 20 行来生成结果。

发生的情况是：

1. 数据库尝试根据表统计信息和可用索引以最有效的方式规划执行。
2. 规划器知道我们有一个覆盖 project_id 列的索引。
3. 数据库使用 project_id 上的索引读取所有行。
4. 此时的行尚未排序，因此数据库对这些行进行排序。
5. 数据库返回前 20 行。

如果项目有 10,000 行，数据库会读取 10,000 行并在内存中（或磁盘上）对它们进行排序。这在长期内可扩展性不佳。

为了解决这个问题，我们需要以下索引：

CREATE INDEX index_on_issues_project_id ON issues (project_id, id);

通过将 id 列作为索引的一部分，之前的查询最多读取 20 行。无论项目中有多少 issues，查询都能良好执行。因此，通过这个更改，我们还改进了初始页面加载（当用户加载 issues 页面时）。

已知问题

大数据集上的 COUNT(*)

Kaminari 默认执行计数查询来确定用于渲染页面链接的页数。对于大型表，计数查询可能相当昂贵。在不幸的情况下，查询可能会超时。

为了解决这个问题，我们可以在不调用计数 SQL 查询的情况下运行 Kaminari。

Issue.where(project_id: 1).page(1).per(20).without_count

在这种情况下，不执行计数查询，分页不再渲染页码。我们只看到下一页和上一页的链接。

大数据集上的 OFFSET

当我们对大型数据集进行分页时，我们可能会注意到响应时间变得越来越慢。这是由于 OFFSET 子句逐行查找并跳过 N 行。

从用户的角度来看，这并不总是明显的。当用户向前分页时，前面的行可能仍在数据库的缓冲缓存中。如果用户与他人共享链接并在几分钟后或几小时后打开，响应时间可能会显著更高，甚至超时。

当请求大页码时，数据库需要读取 PAGE * PAGE_SIZE 行。这使得偏移量分页不适合大型数据库表，但是，通过优化技术，数据库查询的整体性能可以略微改善。

示例：在 Admin 区域列出用户

使用非常简单的 SQL 查询列出用户：

SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 20 OFFSET 0

查询执行计划显示该查询是高效的，数据库只从数据库读取了 20 行（rows=20）：

 Limit  (cost=0.43..3.19 rows=20 width=1309) (actual time=0.098..2.093 rows=20 loops=1)
   Buffers: shared hit=103
   ->  Index Scan Backward using users_pkey on users  (cost=0.43..X rows=X width=1309) (actual time=0.097..2.087 rows=20 loops=1)
         Buffers: shared hit=103
 Planning Time: 0.333 ms
 Execution Time: 2.145 ms
(6 rows)

有关阅读执行计划的更多信息，请参见理解 EXPLAIN 计划。

让我们访问第 50,000 页：

SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 20 OFFSET 999980;

计划显示数据库读取了 1,000,000 行来返回 20 行，执行时间非常长（5.5 秒）：

Limit  (cost=137878.89..137881.65 rows=20 width=1309) (actual time=5523.588..5523.667 rows=20 loops=1)
   Buffers: shared hit=1007901 read=14774 written=609
   I/O Timings: read=420.591 write=57.344
   ->  Index Scan Backward using users_pkey on users  (cost=0.43..X rows=X width=1309) (actual time=0.060..5459.353 rows=1000000 loops=1)
         Buffers: shared hit=1007901 read=14774 written=609
         I/O Timings: read=420.591 write=57.344
 Planning Time: 0.821 ms
 Execution Time: 5523.745 ms
(8 rows)

我们可以争论说典型用户不会访问这些页面。但是，API 用户可能会到达非常高的页码（抓取、收集数据）。

键集分页

键集分页解决了在请求大页时"跳过"前面行的性能问题，但它不是偏移量分页的直接替代品。当将 API 端点从基于偏移量的分页移动到基于键集的分页时，两者都必须支持。完全移除一种类型的分页是破坏性更改。

键集分页在 GraphQL API 和 REST API 中都有使用。

考虑以下 issues 表：

让我们对整个表按主键（id）排序进行分页。第一页的查询与偏移量分页查询相同，为简单起见，我们使用 5 作为页面大小：

SELECT "issues".* FROM "issues" ORDER BY "issues"."id" ASC LIMIT 5

注意我们没有添加 OFFSET 子句。

要到达下一页，我们需要从最后一行中提取属于 ORDER BY 子句的值。在这种情况下，我们只需要 id，即 5。现在我们构建下一页的查询：

SELECT "issues".* FROM "issues" WHERE "issues"."id" > 5 ORDER BY "issues"."id" ASC LIMIT 5

查看查询执行计划，我们可以看到这个查询只读取了 5 行（偏移量分页会读取 10 行）：

 Limit  (cost=0.56..2.08 rows=5 width=1301) (actual time=0.093..0.137 rows=5 loops=1)
   ->  Index Scan using issues_pkey on issues  (cost=0.56..X rows=X width=1301) (actual time=0.092..0.136 rows=5 loops=1)
         Index Cond: (id > 5)
 Planning Time: 7.710 ms
 Execution Time: 0.224 ms
(5 rows)

已知问题

没有页码

偏移量分页提供了一种请求特定页面的简单方法。我们可以编辑 URL 并修改 page= URL 参数。键集分页无法提供页码，因为分页逻辑可能依赖于不同的列。

在之前的示例中，列是 id，所以我们可能会在 URL 中看到类似这样的内容：

id_after=5

在 GraphQL 中，参数被序列化为 JSON 然后编码：

eyJpZCI6Ijk0NzMzNTk0IiwidXBkYXRlZF9hdCI6IjIwMjEtMDQtMDkgMDg6NTA6MDUuODA1ODg0MDAwIFVUQyJ9

键集分页只能提供下一页、上一页、第一页和最后一页。

复杂性

当按单列排序时构建查询非常简单，但是如果使用决胜局或多列排序，事情会变得更复杂。如果列可为空，复杂性会增加。

示例：按 id 和 created_at 排序，其中 created_at 可为空，获取第二页的查询：

SELECT "issues".*
FROM "issues"
WHERE (("issues"."id" > 99
        AND "issues"."created_at" = '2021-02-16 11:26:17.408466')
       OR ("issues"."created_at" > '2021-02-16 11:26:17.408466')
       OR ("issues"."created_at" IS NULL))
ORDER BY "issues"."created_at" DESC NULLS LAST, "issues"."id" DESC
LIMIT 20

工具

GitLab 项目中有一个通用的键集分页库，在大多数情况下可以轻松替换现有的基于 Kaminari 的分页，在处理大型数据集时能显著提高性能。

示例：

# 第一页
paginator = Project.order(:created_at, :id).keyset_paginate(per_page: 20)
puts paginator.to_a # 记录

# 下一页
cursor = paginator.cursor_for_next_page
paginator = Project.order(:created_at, :id).keyset_paginate(cursor: cursor, per_page: 20)
puts paginator.to_a # 记录

有关全面的概述，请查看键集分页指南页面。

性能

键集分页提供了稳定的性能，无论我们向前移动了多少页。为了实现这种性能，分页查询需要一个覆盖 ORDER BY 子句中所有列的索引，类似于偏移量分页。

通用性能指南

请参见分页通用性能指南页面。