PostgreSQL 深分页优化：把 OFFSET 200000 的订单查询从 3s 压到 30ms

订单后台有一个很典型的列表页：按租户查订单，按创建时间倒序排列，支持翻页。前几十页一直没问题，到了运营同事导出历史订单时，接口开始明显变慢。页码越靠后，等待时间越长。

数据库是 PostgreSQL 15.6，订单表大约 860 万行，单个租户下有 70 多万行。表结构做过简化：

CREATE TABLE order_event (
    id          BIGSERIAL PRIMARY KEY,
    tenant_id   BIGINT      NOT NULL,
    user_id     BIGINT      NOT NULL,
    status      SMALLINT    NOT NULL,
    amount      NUMERIC(12, 2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    deleted_at  TIMESTAMPTZ,
    remark      TEXT
);

列表页原来的 SQL 是这样写的：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20 OFFSET 200000;

这个写法在业务代码里很常见。前端传 page 和 pageSize，后端换算成：

offset = (page - 1) * pageSize;

前几页没什么问题，真正拖垮接口的是深分页。

OFFSET 慢在“跳过”这一步

先看第 1 页。

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

执行计划大概是这样：

Limit  (cost=0.43..5.92 rows=20 width=34)
       (actual time=0.091..2.147 rows=20 loops=1)
  Buffers: shared hit=26
  ->  Index Scan using idx_order_event_tenant_ctime on order_event
        (cost=0.43..193418.29 rows=704238 width=34)
        (actual time=0.089..2.139 rows=20 loops=1)
        Index Cond: (tenant_id = 42)
        Filter: (deleted_at IS NULL)
        Buffers: shared hit=26
Planning Time: 0.342 ms
Execution Time: 2.181 ms

第 1 页只需要沿着索引拿 20 条，速度很正常。

再看第 10001 页，也就是 OFFSET 200000：

Limit  (cost=54928.63..54934.12 rows=20 width=34)
       (actual time=3087.421..3087.771 rows=20 loops=1)
  Buffers: shared hit=183911 read=24166
  ->  Index Scan using idx_order_event_tenant_ctime on order_event
        (cost=0.43..193418.29 rows=704238 width=34)
        (actual time=0.083..3077.894 rows=200020 loops=1)
        Index Cond: (tenant_id = 42)
        Filter: (deleted_at IS NULL)
        Rows Removed by Filter: 18391
        Buffers: shared hit=183911 read=24166
Planning Time: 0.287 ms
Execution Time: 3087.812 ms

这里的关键是：

actual time=0.083..3077.894 rows=200020

虽然最终只返回 20 条，PostgreSQL 仍然要先按顺序找到前 200020 条，再丢掉前 200000 条。

OFFSET 的语义就是跳过指定数量的结果。跳过不等于数据库可以瞬间定位到第 200000 条。尤其在有过滤条件、MVCC 可见性判断、软删除条件时，数据库需要一行一行确认哪些记录属于结果集。

页码越深，前面被跳过的行越多，耗时自然越来越长。

这类慢查询还有一个麻烦点：执行计划看起来并不离谱。它用了索引，也没有额外排序。问题藏在扫描行数里。

原来的索引只能缓解，不能解决深分页

当时表上有这个索引：

CREATE INDEX idx_order_event_tenant_ctime
ON order_event (tenant_id, created_at DESC);

它能支持下面这个排序：

WHERE tenant_id = 42
ORDER BY created_at DESC

如果没有这个索引，查询还要额外排序，情况会更糟。索引已经让 PostgreSQL 可以按时间倒序扫描，但 OFFSET 200000 依然要求它跳过 20 万条结果。

软删除条件也影响了扫描效率：

AND deleted_at IS NULL

如果租户下有不少已删除订单，PG 沿着索引扫描时还要把这些记录过滤掉。为了减少这部分无效扫描，我先把索引改成部分索引：

CREATE INDEX CONCURRENTLY idx_order_event_tenant_ctime_alive
ON order_event (tenant_id, created_at DESC, id DESC)
INCLUDE (user_id, status, amount)
WHERE deleted_at IS NULL;

这里多加了 id DESC。原因是 created_at 可能重复，单独按时间排序不够稳定。同一秒内插入多条订单时，如果只按 created_at DESC，不同查询之间的返回顺序可能抖动，分页时容易出现重复或遗漏。

列表 SQL 也同步改成稳定排序：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;

加完部分索引后，深分页有改善，但不彻底：

Limit  (cost=38421.55..38425.39 rows=20 width=34)
       (actual time=1264.802..1265.119 rows=20 loops=1)
  Buffers: shared hit=82105 read=6412
  ->  Index Scan using idx_order_event_tenant_ctime_alive on order_event
        (cost=0.43..135182.80 rows=704238 width=34)
        (actual time=0.061..1255.483 rows=200020 loops=1)
        Index Cond: (tenant_id = 42)
        Buffers: shared hit=82105 read=6412
Execution Time: 1265.156 ms

从 3s 降到 1.2s，但页面深了之后还是慢。索引减少了过滤和回表压力，OFFSET 需要跳过大量记录的问题还在。

改成游标分页

后台列表后来拆成两种场景。

普通查看保留页码分页，因为用户一般只翻前几页。

历史导出和连续加载改成游标分页，也叫 keyset pagination。它不再传 page=10001，而是传上一页最后一条记录的排序键。

第一页查询：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 20;

假设第一页最后一条记录是：

created_at = 2026-01-06 10:21:35.812+08
id         = 9812731

下一页就带上这两个值：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND deleted_at IS NULL
  AND (created_at, id) < ('2026-01-06 10:21:35.812+08', 9812731)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这里用的是 PostgreSQL 的行值比较。排序是：

ORDER BY created_at DESC, id DESC

下一页要取“排在上一页最后一条之后”的数据，所以条件是：

(created_at, id) < (:lastCreatedAt, :lastId)

这条 SQL 能继续使用前面那条索引：

CREATE INDEX CONCURRENTLY idx_order_event_tenant_ctime_alive
ON order_event (tenant_id, created_at DESC, id DESC)
INCLUDE (user_id, status, amount)
WHERE deleted_at IS NULL;

改完后的执行计划：

Limit  (cost=0.43..6.12 rows=20 width=34)
       (actual time=0.841..31.627 rows=20 loops=1)
  Buffers: shared hit=48 read=9
  ->  Index Scan using idx_order_event_tenant_ctime_alive on order_event
        (cost=0.43..126104.79 rows=471982 width=34)
        (actual time=0.839..31.619 rows=20 loops=1)
        Index Cond: ((tenant_id = 42)
                 AND (ROW(created_at, id)
                      < ROW('2026-01-06 10:21:35.812+08'::timestamp with time zone, 9812731)))
        Buffers: shared hit=48 read=9
Planning Time: 0.391 ms
Execution Time: 31.662 ms

这次数据库没有扫描 200020 行。它从游标位置继续往后读，拿够 20 条就停。

前后对比很明显：

查询方式	执行时间	扫描行数	buffer 数量	适合场景
LIMIT 20 OFFSET 0	2ms 左右	20	26	首页
LIMIT 20 OFFSET 200000	3s 左右	200020	20 万左右	不适合深分页
部分索引 + OFFSET 200000	1.2s 左右	200020	8 万左右	只能缓解
游标分页	30ms 左右	20	50 左右	连续翻页、导出

游标分页把性能从“页码越深越慢”改成了“每页查询成本接近固定”。这对订单流水、消息列表、操作日志、账单明细这类时间线数据很合适。

排序字段必须稳定

游标分页最容易踩的坑是排序字段不唯一。

如果只用 created_at 做游标：

WHERE created_at < :lastCreatedAt
ORDER BY created_at DESC
LIMIT 20;

同一时间点有多条记录时，分页会出问题。

比如第一页最后一条是：

created_at = 2026-01-06 10:21:35.812+08

这个时间点下其实还有 10 条订单没有返回。下一页用了：

created_at < '2026-01-06 10:21:35.812+08'

这些同时间订单会被直接跳过。

所以我这里用了两个字段：

ORDER BY created_at DESC, id DESC

对应游标条件：

AND (created_at, id) < (:lastCreatedAt, :lastId)

created_at 负责业务排序，id 负责兜底排序。只要 id 唯一，整个排序结果就是稳定的。

如果页面是正序加载，条件也要跟着变：

ORDER BY created_at ASC, id ASC

下一页条件要写成：

AND (created_at, id) > (:lastCreatedAt, :lastId)

方向不能反。这个地方一旦写错，轻则重复数据，重则第一页之后查不到结果。

Java 里的分页参数也要换掉

原来的接口参数是：

{
  "page": 10001,
  "pageSize": 20
}

游标分页改成：

{
  "pageSize": 20,
  "lastCreatedAt": "2026-01-06T10:21:35.812+08:00",
  "lastId": 9812731
}

第一页不传游标：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = :tenantId
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT :pageSize;

下一页传游标：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = :tenantId
  AND deleted_at IS NULL
  AND (created_at, id) < (:lastCreatedAt, :lastId)
ORDER BY created_at DESC, id DESC
LIMIT :pageSize;

如果用 MyBatis，可以把条件写成动态片段：

<select id="listOrderByCursor" resultType="OrderListItem">
    SELECT
        id,
        user_id,
        status,
        amount,
        created_at
    FROM order_event
    WHERE tenant_id = #{tenantId}
      AND deleted_at IS NULL
    <if test="lastCreatedAt != null and lastId != null">
      AND (created_at, id) &lt; (#{lastCreatedAt}, #{lastId})
    </if>
    ORDER BY created_at DESC, id DESC
    LIMIT #{pageSize}
</select>

返回结果时，把最后一条记录带回给前端：

public CursorPage<OrderListItem> listByCursor(OrderQuery query) {
    int pageSize = Math.min(query.getPageSize(), 100);

    List<OrderListItem> rows = orderMapper.listOrderByCursor(
        query.getTenantId(),
        query.getLastCreatedAt(),
        query.getLastId(),
        pageSize + 1
    );

    boolean hasNext = rows.size() > pageSize;
    if (hasNext) {
        rows = rows.subList(0, pageSize);
    }

    Cursor nextCursor = null;
    if (hasNext && !rows.isEmpty()) {
        OrderListItem last = rows.get(rows.size() - 1);
        nextCursor = new Cursor(last.getCreatedAt(), last.getId());
    }

    return new CursorPage<>(rows, nextCursor, hasNext);
}

这里多查 1 条，是为了判断后面还有没有下一页。实际返回时仍然只返回 pageSize 条。

过滤条件变化时，游标要重新开始

游标只对当前筛选条件有效。

比如第一页查询的是：

tenant_id = 42
status = 2

下一页也必须继续使用同样的过滤条件。如果用户切换了订单状态、修改了时间范围、换了关键字，之前的游标就不能继续用。

我后来在接口里做了一个处理：游标里不只放 lastCreatedAt 和 lastId，还放一份查询条件摘要。后端收到游标后，会校验当前筛选条件和游标对应的条件是否一致。

简化后的游标结构类似这样：

{
  "lastCreatedAt": "2026-01-06T10:21:35.812+08:00",
  "lastId": 9812731,
  "filterHash": "tenant=42&status=2&deleted=false"
}

真实项目里不会直接把这个 JSON 明文暴露出去，而是做一次 Base64 编码，必要时再加签，避免前端随意改游标内容。

public String encodeCursor(CursorPayload payload) {
    String json = objectMapper.writeValueAsString(payload);
    return Base64.getUrlEncoder()
            .withoutPadding()
            .encodeToString(json.getBytes(StandardCharsets.UTF_8));
}

如果游标里的条件摘要和当前查询条件对不上，后端直接按第一页处理，或者返回参数错误。这个规则要提前和前端约定好。

有些页面仍然适合保留 OFFSET

游标分页也有不方便的地方。

它适合“上一页、下一页、继续加载”，不适合直接跳到第 500 页。运营后台有时会要求输入页码跳转，这时游标分页就不顺手。

我的处理方式是分场景：

普通管理后台保留浅分页，限制最大页码或最大 offset。

LIMIT 20 OFFSET 1000

超过阈值后，接口直接提示缩小筛选范围，或者切到导出任务。

连续滚动、历史导出、日志拉取、账单流水统一走游标分页。

这个取舍比较实际。很多后台页面虽然有页码，但用户很少真的跳到几千页之后逐页查看。为了一个低频动作，让所有列表接口都承受深分页成本，并不划算。

当用户真的要拿很深的数据，通常也不是为了“看第 10001 页”，而是为了导出某段时间内的订单。这时游标分页更适合做批量拉取。

导出任务里可以这样循环：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = :tenantId
  AND deleted_at IS NULL
  AND created_at >= :startTime
  AND created_at < :endTime
  AND (
      :lastCreatedAt IS NULL
      OR (created_at, id) < (:lastCreatedAt, :lastId)
  )
ORDER BY created_at DESC, id DESC
LIMIT 1000;

每批 1000 条，记录最后一条的 created_at 和 id，下一批继续往后扫。这样导出几十万条数据时，每一批的查询耗时都比较稳定。

时间范围能加就加

订单列表还有一个额外优化：尽量带时间范围。

如果接口允许用户选择时间，SQL 就变成：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND deleted_at IS NULL
  AND created_at >= '2026-01-01 00:00:00+08'
  AND created_at <  '2026-02-01 00:00:00+08'
  AND (created_at, id) < ('2026-01-06 10:21:35.812+08', 9812731)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这条 SQL 仍然可以使用同一个索引：

ON order_event (tenant_id, created_at DESC, id DESC)
WHERE deleted_at IS NULL;

时间范围的价值在于减少候选数据。如果一个租户有 70 万条订单，最近一个月只有 8 万条，查询路径会短很多。导出场景尤其建议强制选择时间范围，不要允许一次性从历史第一天扫到现在。

最后落地的规则

这次改完后，我给订单、账单、日志这几类列表定了几条规则。

前台用户列表可以保留页码，但最大页数要受控。超过范围后，让用户缩小筛选条件。

后台导出不再使用 OFFSET 翻批次，统一改成游标方式。

所有按时间排序的游标分页，都必须带唯一字段兜底：

ORDER BY created_at DESC, id DESC

索引顺序要和筛选、排序对齐：

CREATE INDEX CONCURRENTLY idx_order_event_tenant_ctime_alive
ON order_event (tenant_id, created_at DESC, id DESC)
INCLUDE (user_id, status, amount)
WHERE deleted_at IS NULL;

游标只在当前筛选条件下有效。筛选条件变化后，从第一页重新查。

这些规则落地后，订单导出接口从原来的越跑越慢，变成每批稳定在几十毫秒到一百多毫秒之间。前端列表页的体验也更稳定，至少不会因为某个人点到很深的页码，把数据库拖出一条 3 秒慢查询。

总结

PostgreSQL 的深分页问题，一般不会出现在前几页。等数据量上来，OFFSET 的成本会按跳过行数持续增加。索引能减少排序和过滤成本，但不能消除跳过大量结果这件事本身。

对时间线类数据，游标分页更稳。它需要一个稳定排序键，常见写法是 created_at + id。再配合部分索引、时间范围和筛选条件校验，就能把深分页查询从秒级拉回到几十毫秒。