MySQL 深度分页，别再让 LIMIT 白扫几十万行

接口没报错，数据库也没锁，订单列表翻到一万页以后，响应时间却直接上去了。

这种问题我一般先找分页 SQL。十有八九，能看到下面这种写法：

SELECT id, order_no, buyer_id, amount, created_at

FROM biz_order

WHERE tenant_id = 27

ORDER BY id DESC

LIMIT 200000, 20;

这条 SQL 看着只返回 20 行，MySQL 干的活可不止 20 行。

LIMIT 200000, 20的意思是：先找到前 200020 条记录，扔掉前面的 200000 条，再把最后 20 条交给应用。偏移量越大，扫描和丢弃的数据越多。

如果查询字段不在索引里，还可能伴随大量回表。列表页字段一多，这地方就更难看了。

我排这种问题时，不会急着加缓存，先跑执行计划：

EXPLAIN

SELECT id, order_no, buyer_id, amount, created_at

FROM biz_order

WHERE tenant_id = 27

ORDER BY id DESC

LIMIT 200000, 20;

重点看使用的索引、预估扫描行数，以及有没有filesort。偏移量越往后，扫描行数跟着涨，问题基本就坐实了。

最省事的改法，是把页码分页换成游标分页。

上一页最后一条数据的id是 785421，下一页不要再计算 offset，直接从这个位置继续查：

SELECT id, order_no, buyer_id, amount, created_at

FROM biz_order

WHERE tenant_id = 27

AND id < 785421

ORDER BY id DESC

LIMIT 20;

对应索引别漏：

CREATE INDEX idx_order_tenant_id

ON biz_order(tenant_id, id);

这种查询不需要从头数到第二十万条。MySQL 定位到id < 785421的索引位置后，向后取 20 条就可以停了。

Java 接口也别再传pageNum，传上一页的最后一个 ID：

public record OrderCursor(Long lastId, Integer size) {

  public int safeSize() {

      if (size == null || size < 1) {

          return 20;

      }

      return Math.min(size, 100);

  }

}

业务代码只保留下一页真正需要的东西：

public CursorResult<OrderView> loadNextPage(

      long tenantId, OrderCursor cursor) {

  int limit = cursor.safeSize();

  long boundary = cursor.lastId() == null

          ? Long.MAX_VALUE

          : cursor.lastId();

  List<OrderView> rows =

          orderMapper.findAfter(tenantId, boundary, limit);

  Long nextCursor = rows.isEmpty()

          ? null

          : rows.get(rows.size() - 1).id();

  return new CursorResult<>(rows, nextCursor);

}

Mapper 里的 SQL 也很直接：

@Select("""

  SELECT id, order_no, buyer_id, amount, created_at

  FROM biz_order

  WHERE tenant_id = #{tenantId}

    AND id < #{boundary}

  ORDER BY id DESC

  LIMIT #{limit}

  """)

List<OrderView> findAfter(long tenantId, long boundary, int limit);

这里有个坑。真实业务不一定按主键排序，更多时候是按创建时间倒序。

只用created_at < 上一页时间不够，因为同一秒可能插入多条数据，翻页时容易重复或者漏数据。排序字段后面要补一个唯一字段兜底：

SELECT id, order_no, amount, created_at

FROM biz_order

WHERE tenant_id = 27

AND (

    created_at < '2026-08-03 10:20:15'

    OR (

        created_at = '2026-08-03 10:20:15'

        AND id < 785421

    )

)

ORDER BY created_at DESC, id DESC

LIMIT 20;

索引顺序也要跟查询条件对上：

CREATE INDEX idx_order_tenant_time_id

ON biz_order(tenant_id, created_at, id);

游标分页适合继续下拉、下一页这类场景，但有些后台系统偏偏要求“跳到第 5000 页”。这种需求改不了，可以用延迟关联先止一下血：

SELECT o.id, o.order_no, o.buyer_id, o.amount, o.created_at

FROM biz_order o

JOIN (

  SELECT id

  FROM biz_order

  WHERE tenant_id = 27

  ORDER BY id DESC

  LIMIT 200000, 20

) p ON p.id = o.id

ORDER BY o.id DESC;

子查询只在较窄的索引上找 20 个主键，再回表读取完整字段。它没有消灭 offset，扫描二十万条索引记录的成本还在，只是避免了二十万次宽字段读取和无意义回表。

所以延迟关联是补丁，不是根治。

线上数据量大以后，我更愿意把“页码”换成“位置”。用户真正想要的通常不是第 7362 页，而是继续查看上一批数据之后的内容。

还在坚持超大页码的接口，数据库只能一页一页替它数过去。