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

Offset 分页优化

在许多 REST API 端点中，我们使用基于偏移量的分页（offset-based pagination），它使用 page URL 参数来分页浏览结果。基于偏移量的分页会线性扩展，页码越高，数据库查询就越慢。这意味着对于高页码，数据库查询可能会超时。这通常发生在第三方集成和脚本与系统交互时，因为用户不太可能故意访问高页码。

处理基于偏移量分页相关可扩展性问题的理想方法是切换到基于键集的分页，但这意味着 API 的破坏性更改。为了提供临时的过渡措施，你可以使用 Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan 类。在存在高 OFFSET 值的情况下，这种优化可以在某些情况下帮助提高基于偏移量分页查询的性能。性能改进意味着查询将继续线性扩展，同时改进查询时间，这意味着即使发生数据库超时，也会在高得多的 page 数时才会出现。

使用优化的要求

该优化在根据 ORDER BY、OFFSET 和 LIMIT 子句确定记录时避免调用 SELECT *，并尝试使用仅索引扫描来减少数据库 I/O。要使用此优化，必须满足与基于键集分页相同的要求：

存在 ORDER BY 子句。
ORDER BY 子句唯一标识一个数据库列。
- 好，使用主键：ORDER BY id
- 不好，created_at 不唯一：ORDER BY created_at
- 好，有决胜列：ORDER BY created_at, id
查询有良好的数据库索引覆盖。

如何使用优化器类

优化器类可以与 ActiveRecord::Relation 对象一起使用，结果将返回一个优化的、kaminari 分页的 ActiveRecord::Relation 对象。如果无法应用优化，将使用原始的 ActiveRecord::Relation 对象进行分页。

基本用法：

scope = Issue.where(project_id: 1).order(:id)
records = Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan.new(scope: scope, page: 5, per_page: 100).paginate_with_kaminari
puts records.to_a

优化应始终通过功能标志推出，你也可以在满足特定条件时针对优化使用进行设置。

# - 仅对高页码查找应用优化
# - 当提供 label_names 过滤参数时，优化将不会生效（复杂的 JOIN）
if params[:page] > 100 && params[:label_names].blank? && Feature.enabled?(:my_optimized_offet_query)
  Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan.new(scope: scope, page: params[:page], per_page: params[:per_page]).paginate_with_kaminari
else
  scope.page(params[:page]).per(params[:per_page])
end

优化如何工作

该优化接收传入的 ActiveRecord::Relation 对象并将其移入 CTE（公共表表达式）。在 CTE 内部，原始查询被修改为只选择 ORDER BY 列。这将使数据库能够使用仅索引扫描。

当查询执行时，CTE 内的查询首先被评估，CTE 将包含所选列的 LIMIT 数量的行。使用 ORDER BY 值，LATERAL 查询将逐个定位完整的行。这里使用 LATERAL 查询是为了强制执行嵌套循环：对于 CTE 中的每一行，在表中查找一个完整的行。

原始查询：

从索引读取 OFFSET + LIMIT 数量的条目。
从表中读取 OFFSET + LIMIT 数量的行。

优化查询：

从索引读取 OFFSET + LIMIT 数量的条目。
从表中读取 LIMIT 数量的行。

判断优化是否有帮助

通过在数据库查询上运行 EXPLAIN (buffers, analyze) 并使用高（100_000）OFFSET 值，我们可以清楚地看到优化是否有帮助。

查找以下内容：

优化的查询计划必须有一个仅索引扫描节点。
比较缓存缓冲区计数和时间应该更低。
- 这可以通过执行相同查询 2 或 3 次来完成。

考虑以下查询：

SELECT issues.*
FROM issues
ORDER BY id
OFFSET 100000
LIMIT 100

它产生一个使用索引扫描的执行计划：

 Limit  (cost=27800.96..27828.77 rows=100 width=1491) (actual time=138.305..138.470 rows=100 loops=1)
   Buffers: shared hit=73212
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using issues_pkey on public.issues  (cost=0.57..26077453.90 rows=93802448 width=1491) (actual time=0.063..133.688 rows=100100 loops=1)
         Buffers: shared hit=73212
         I/O Timings: read=0.000 write=0.000


Time: 143.779 ms
  - planning: 5.222 ms
  - execution: 138.557 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 73212 (~572.00 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

优化后的查询：

WITH index_only_scan_pagination_cte AS MATERIALIZED
  (SELECT id
   FROM issues
   ORDER BY id ASC
   LIMIT 100
   OFFSET 100000)
SELECT issues.*
FROM
  (SELECT id
   FROM index_only_scan_pagination_cte) index_only_scan_subquery,
     LATERAL
  (SELECT issues.*
   FROM issues
   WHERE issues.id = index_only_scan_subquery.id
   LIMIT 1) issues

执行计划：

 Nested Loop  (cost=2453.51..2815.44 rows=100 width=1491) (actual time=23.614..23.973 rows=100 loops=1)
   Buffers: shared hit=56167
   I/O Timings: read=0.000 write=0.000
   CTE index_only_scan_pagination_cte
     ->  Limit  (cost=2450.49..2452.94 rows=100 width=4) (actual time=23.590..23.621 rows=100 loops=1)
           Buffers: shared hit=55667
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan using issues_pkey on public.issues issues_1  (cost=0.57..2298090.72 rows=93802448 width=4) (actual time=0.070..20.412 rows=100100 loops=1)
                 Heap Fetches: 1063
                 Buffers: shared hit=55667
                 I/O Timings: read=0.000 write=0.000
   ->  CTE Scan on index_only_scan_pagination_cte  (cost=0.00..2.00 rows=100 width=4) (actual time=23.593..23.641 rows=100 loops=1)
         Buffers: shared hit=55667
         I/O Timings: read=0.000 write=0.000
   ->  Limit  (cost=0.57..3.58 rows=1 width=1491) (actual time=0.003..0.003 rows=1 loops=100)
         Buffers: shared hit=500
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using issues_pkey on public.issues  (cost=0.57..3.58 rows=1 width=1491) (actual time=0.003..0.003 rows=1 loops=100)
               Index Cond: (issues.id = index_only_scan_pagination_cte.id)
               Buffers: shared hit=500
               I/O Timings: read=0.000 write=0.000


Time: 29.562 ms
  - planning: 5.506 ms
  - execution: 24.056 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 56167 (~438.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0