Help us learn about your current experience with the documentation. Take the survey.

分页性能指南

本文档提供了一些优化分页（排序）性能的建议。这些建议同时适用于 offset 和 keyset 分页。

区分列

对列进行排序时，建议仅按唯一列排序。考虑以下示例：

`id`	`created_at`
`1`	`2021-01-04 14:13:43`
`2`	`2021-01-05 19:03:12`
`3`	`2021-01-05 19:03:12`

如果仅按 created_at 排序，结果可能取决于记录在磁盘上的位置位置。

当数据通过明确定义的接口暴露并由自动化进程（如 API）消费时，建议使用区分列。没有区分列时，行的顺序可能发生变化（数据重新导入），这会导致难以调试的问题，例如：

用于比较行以确定变更的集成功能中断。
E-tag 缓存值变化，需要完全重新下载。

SELECT issues.* FROM issues ORDER BY created_at;

我们可以通过在 ORDER BY 中添加第二列来修复此问题：

SELECT issues.* FROM issues ORDER BY created_at, id;

此更改使排序结果唯一，从而实现"稳定"排序。

为使查询高效，我们需要覆盖两列的索引：(created_at, id)。列的顺序 应匹配 ORDER BY 子句中的列。

增量排序

PostgreSQL 13 引入了增量排序功能，这有助于在 ORDER BY 子句中添加区分列，而无需添加或替换索引。此外，通过增量排序，可以在构建新索引之前引入新的键集分页数据库查询（异步索引）。增量排序默认启用。

考虑以下数据库查询：

SELECT *
FROM merge_requests
WHERE author_id = 1
ORDER BY created_at ASC
LIMIT 20

查询将使用以下索引读取 20 行：

"index_merge_requests_on_author_id_and_created_at" btree (author_id, created_at)

由于 created_at 列不唯一，无法使用此查询进行键集分页。让我们添加区分列：

SELECT *
FROM merge_requests
WHERE author_id = 1
ORDER BY created_at ASC, id ASC
LIMIT 20

执行计划：

 Limit  (cost=1.99..30.97 rows=20 width=910) (actual time=1.217..1.220 rows=20 loops=1)
   Buffers: shared hit=33 read=2
   I/O Timings: read=0.983 write=0.000
   ->  Incremental Sort  (cost=1.99..919.33 rows=633 width=910) (actual time=1.215..1.216 rows=20 loops=1)
         Sort Key: merge_requests.created_at, merge_requests.id
         Buffers: shared hit=33 read=2
         I/O Timings: read=0.983 write=0.000
         ->  Index Scan using index_merge_requests_on_author_id_and_created_at on public.merge_requests  (cost=0.57..890.84 rows=633 width=910) (actual time=0.038..1.139 rows=22 loops=1)
               Index Cond: (merge_requests.author_id = 1)
               Buffers: shared hit=24 read=2
               I/O Timings: read=0.983 write=0.000

如您所见，查询使用相同索引读取了 22 行。数据库比较了 created_at 列的第 20、21 和 22 个值，并确定第 22 个值不同，因此无需检查下一条记录。在此示例中，第 20 和 21 列具有相同的 created_at 值。

增量排序适用于时间戳列，因为此类列重复值较少。当列的值非常少（如枚举类型）时，增量排序性能会变差或完全不被使用。

例如，当禁用增量排序时，数据库会按作者读取所有合并请求记录并在内存中排序。

set enable_incremental_sort=off;

 Limit  (cost=907.69..907.74 rows=20 width=910) (actual time=2.911..2.917 rows=20 loops=1)
   Buffers: shared hit=1004
   ->  Sort  (cost=907.69..909.27 rows=633 width=910) (actual time=2.908..2.911 rows=20 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 52kB
         Buffers: shared hit=1004
         ->  Index Scan using index_merge_requests_on_author_id_and_created_at on merge_requests  (cost=0.57..890.84 rows=633 width=910) (actual time=0.042..1.974 rows=1111 loops=1)
               Index Cond: (author_id = 1)
               Buffers: shared hit=1111
 Planning Time: 0.386 ms
 Execution Time: 3.000 ms
(11 rows)

在此示例中，数据库读取了 1111 行并在内存中对行进行了排序。

按关联表列排序

我们经常需要按关联数据库表的列对数据进行排序。以下示例按 first_mentioned_in_commit_at 指标列对 issues 记录进行排序：

SELECT issues.* FROM issues
INNER JOIN issue_metrics on issue_metrics.issue_id=issues.id
WHERE issues.project_id = 2
ORDER BY issue_metrics.first_mentioned_in_commit_at DESC, issues.id DESC
LIMIT 20
OFFSET 0

在 PostgreSQL 11 版本中，规划器首先查找所有匹配 project_id 过滤条件的 issues，然后关联所有 issue_metrics 行。行的排序在内存中进行。如果关联关系始终存在（1:1 关系），数据库会读取 N * 2 行，其中 N 是匹配 project_id 过滤条件的行数。

出于性能考虑，在指定 ORDER BY 子句时应避免混合不同表的列。

在此特定情况下，没有简单的方法（如创建索引）来改进查询。我们可能会认为将 issues.id 列更改为 issue_metrics.issue_id 有帮助，但这可能使查询性能更差，因为它可能强制数据库处理 issue_metrics 表中的所有行。

解决此问题的一个思路是反规范化。在 issue_metrics 表中添加 project_id 列可以使过滤和排序更高效：

SELECT issues.* FROM issues
INNER JOIN issue_metrics on issue_metrics.issue_id=issues.id
WHERE issue_metrics.project_id = 2
ORDER BY issue_metrics.first_mentioned_in_commit_at DESC, issue_metrics.issue_id DESC
LIMIT 20
OFFSET 0

查询需要在 issue_metrics 表上创建索引，列配置为：(project_id, first_mentioned_in_commit_at DESC, issue_id DESC)。

过滤

按项目过滤

按项目过滤是常见用例，因为许多功能在项目级别实现。例如：合并请求、问题、看板、迭代。

这些功能在其基础查询中都有 project_id 过滤条件。加载项目的 issues：

project = Project.find(5)

# 按内部 ID 排序
issues = project.issues.order(:iid).page(1).per(20)

为使基础查询高效，通常会有覆盖 project_id 列的数据库索引。这显著减少了数据库需要扫描的行数。没有索引时，数据库将读取整个 issues 表（全表扫描）。

由于 project_id 是外键，我们可能有以下索引可用：

"index_issues_on_project_id" btree (project_id)

按组过滤

不幸的是，在组级别进行排序和分页没有高效的方法。数据库查询执行时间会根据组中的记录数量增加。

当组级别实际包含组及其子组时，情况会更糟。要加载第一页，数据库需要查找组层次结构、找到所有项目，然后查找所有 issues。

组级别查询效率低下的主要原因是数据库架构的设计方式；我们的核心领域模型与项目关联，而项目与组关联。这并不意味着数据库结构不好，它只是处于一种不适合高效组级别查询的良好规范化形式。长期来看，我们可能需要研究反规范化。

示例：列出组中的 issues

group = Group.find(9970)

Issue.where(project_id: group.projects).order(:iid).page(1).per(20)

生成的 SQL 查询：

SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     WHERE "projects"."namespace_id" = 5)
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

执行计划显示我们读取的行数远多于请求的 20 行，并且行在内存中排序：

 Limit  (cost=10716.87..10716.92 rows=20 width=1300) (actual time=1472.305..1472.308 rows=20 loops=1)
   ->  Sort  (cost=10716.87..10717.03 rows=61 width=1300) (actual time=1472.303..1472.305 rows=20 loops=1)
         Sort Key: issues.iid
         Sort Method: top-N heapsort  Memory: 41kB
         ->  Nested Loop  (cost=1.00..10715.25 rows=61 width=1300) (actual time=0.215..1331.647 rows=177267 loops=1)
               ->  Index Only Scan using index_projects_on_namespace_id_and_id on projects  (cost=0.44..3.77 rows=19 width=4) (actual time=0.077..1.057 rows=270 loops=1)
                     Index Cond: (namespace_id = 9970)
                     Heap Fetches: 25
               ->  Index Scan using index_issues_on_project_id_and_iid on issues  (cost=0.56..559.28 rows=448 width=1300) (actual time=0.101..4.781 rows=657 loops=270)
                     Index Cond: (project_id = projects.id)
 Planning Time: 12.281 ms
 Execution Time: 1472.391 ms
(12 rows)

同一数据库表中的列

可以通过索引改进位于同一数据库表的列的过滤。如果我们希望支持按 state_id 列过滤，可以添加以下索引：

"index_issues_on_project_id_and_state_id_and_iid" UNIQUE, btree (project_id, state_id, iid)

Rails 中的示例查询：

project = Project.find(5)

# 按内部 ID 排序
issues = project.issues.opened.order(:iid).page(1).per(20)

SQL 查询：

SELECT "issues".*
FROM "issues"
WHERE
  "issues"."project_id" = 5
  AND ("issues"."state_id" IN (1))
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

上述索引不支持以下项目级别查询：

SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 5
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

特殊情况：机密标志

在 issues 表中，我们有一个布尔字段 (confidential)，用于标记问题为机密。这使得非成员用户无法看到（过滤掉）该问题。

示例 SQL 查询：

SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 5
AND "issues"."confidential" = FALSE
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

我们可能会尝试在 project_id、confidential 和 iid 上添加索引以改进数据库查询，但在本例中可能没有必要。根据表中的数据分布，机密问题很少见。过滤它们不会显著降低数据库查询速度。数据库可能会多读取几行，性能差异甚至可能对最终用户不可见。

另一方面，如果我们实现了一个特殊过滤器，仅显示机密问题，则需要该索引。查找 20 个机密问题可能需要数据库扫描数百行，或在最坏情况下扫描项目中的所有 issues。

引入新的数据库索引时，请注意数据分布和表访问模式（功能如何工作）。可能需要采样生产数据以做出正确决策。

不同数据库表中的列

示例：按分配者过滤项目中的 issues

project = Project.find(5)

project
  .issues
  .joins(:issue_assignees)
  .where(issue_assignees: { user_id: 10 })
  .order(:iid)
  .page(1)
  .per(20)

SELECT "issues".*
FROM "issues"
INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
WHERE "issues"."project_id" = 5
  AND "issue_assignees"."user_id" = 10
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

示例数据库（过度简化）执行计划：

数据库解析 SQL 查询并检测 JOIN。
数据库将查询拆分为两个子查询。
- SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."user_id" = 10
- SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 5
数据库估算运行这些查询的行数和成本。
数据库首先执行成本最低的查询。
使用查询结果，通过 JOIN 列从另一个表（来自另一个查询）加载行，并进一步过滤行。

在此特定示例中，issue_assignees 查询可能会首先执行。

在 GitLab 项目中运行生产环境的查询会产生以下执行计划：

 Limit  (cost=411.20..411.21 rows=1 width=1300) (actual time=24.071..24.077 rows=20 loops=1)
   ->  Sort  (cost=411.20..411.21 rows=1 width=1300) (actual time=24.070..24.073 rows=20 loops=1)
         Sort Key: issues.iid
         Sort Method: top-N heapsort  Memory: 91kB
         ->  Nested Loop  (cost=1.00..411.19 rows=1 width=1300) (actual time=0.826..23.705 rows=190 loops=1)
               ->  Index Scan using index_issue_assignees_on_user_id on issue_assignees  (cost=0.44..81.37 rows=92 width=4) (actual time=0.741..13.202 rows=215 loops=1)
                     Index Cond: (user_id = 4156052)
               ->  Index Scan using issues_pkey on issues  (cost=0.56..3.58 rows=1 width=1300) (actual time=0.048..0.048 rows=1 loops=215)
                     Index Cond: (id = issue_assignees.issue_id)
                     Filter: (project_id = 278964)
                     Rows Removed by Filter: 0
 Planning Time: 1.141 ms
 Execution Time: 24.170 ms
(13 rows)

查询首先查找分配者，按 user_id (user_id = 4156052) 过滤，找到 215 行。使用这 215 行，数据库通过主键查找 215 个关联的 issue 行。请注意，project_id 列的过滤没有索引支持。

在大多数情况下，关联关系不会返回太多行，我们最终得到一个访问少量行且相对高效的数据库查询。随着数据库的增长，这些查询的行为可能会发生变化。例如，拥有大量 issue_assignees 记录的用户可能导致此连接查询性能不佳并超时。

类似的问题可能是双重连接，其中过滤条件存在于第二个 JOIN 查询中。示例：Issue -> LabelLink -> Label(name=bug)。

没有简单的方法解决这些问题。数据反规范化可能会有所帮助，但它也有负面影响（数据重复和数据保持最新）。

改进 issue_assignees 过滤的建议：

在 issue_assignees 表中添加 project_id 列，以便在执行 JOIN 时，额外的 project_id 过滤条件进一步过滤行。排序可能在内存中进行：

SELECT "issues".*
FROM "issues"
INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
WHERE "issues"."project_id" = 5
  AND "issue_assignees"."user_id" = 10
  AND "issue_assignees"."project_id" = 5
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

在 issue_assignees 表中添加 iid 列。请注意 ORDER BY 列不同，并且 issues 表中的 project_id 过滤条件已移除：

SELECT "issues".*
FROM "issues"
INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
WHERE "issue_assignees"."user_id" = 10
  AND "issue_assignees"."project_id" = 5
ORDER BY "issue_assignees"."iid" ASC
LIMIT 20
OFFSET 0

现在该查询对任意数量的 issue_assignees 记录都能良好运行，但我们为此付出了非常高的代价：

两列被重复，增加了数据库大小。
我们需要保持这两列同步。
我们需要在 issue_assignees 表上创建更多索引以支持查询。
新的数据库查询非常特定于分配者搜索，需要复杂的后端代码来构建它。
- 如果分配者按用户过滤，则按不同列排序，移除 project_id 过滤条件等。

目前我们不在 GitLab 进行此类反规范化操作。