适用场景
本文适用于按时间倒序展示订单、流水、审计日志或消息列表的接口。典型特征是：第一页很快，翻到几千页后响应时间明显上升；数据库 CPU 和磁盘读取随页码增长；接口仍使用 LIMIT offset, size。
示例使用 MySQL 8.0，表结构如下：
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
status TINYINT UNSIGNED NOT NULL,
amount DECIMAL(12, 2) NOT NULL,
created_at DATETIME(6) NOT NULL,
PRIMARY KEY (id),
KEY idx_status_created_id (status, created_at DESC, id DESC)
) ENGINE=InnoDB;
查询目标是从已支付订单中，按 created_at、id 倒序取 50 条。
现象描述
接口最初通常写成：
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 500000, 50;
LIMIT 0, 50 可能只需几毫秒，但 LIMIT 500000, 50 可能需要数百毫秒甚至数秒。增加应用实例无法解决问题，因为代价发生在数据库内部；页码越深，每个请求需要跳过的记录越多。
为什么有索引仍然会慢
联合索引可以避免额外排序，却不能让 MySQL 瞬间跳到“第 500001 条业务记录”。为了返回最后 50 条，执行器仍需沿索引扫描并丢弃前 500000 条符合条件的记录。
如果查询列不全在二级索引中，还可能对大量候选记录回表。即使最终只返回 50 行，也不代表执行过程只读取了 50 行。
深分页常见的三个放大因素是：
offset随页码线性增长；- 查询列较多，导致大量回表；
- 排序字段不唯一，相邻请求在并发写入时出现重复或漏读。
先用执行计划确认成本
在测试环境或可控的只读查询上使用：
EXPLAIN ANALYZE
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 500000, 50;
重点关注实际扫描行数、循环次数和各节点耗时。EXPLAIN ANALYZE 会真正执行语句，不要直接用于具有副作用的 SQL，也不要在生产高峰对超重查询反复执行。
还可以比较不同偏移量的耗时：
SELECT SQL_NO_CACHE id
FROM orders
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 0, 50;
SELECT SQL_NO_CACHE id
FROM orders
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 500000, 50;
SQL_NO_CACHE 在 MySQL 8.0 中不再影响已移除的查询缓存，因此压测时更应通过多轮执行、预热缓冲池和固定数据集减少偶然误差，而不能把它当成可靠的“禁用所有缓存”开关。
方案一：用游标分页替代 OFFSET
列表只需要“上一页、下一页”时，优先使用 keyset pagination，也称 seek pagination。第一页查询：
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 51;
多取一条用于判断是否还有下一页。假设本页最后一条记录为：
created_at = 2026-09-02 10:15:30.123456
id = 918273645
下一页不再传页码，而是传最后一条记录的位置：
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 2
AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 51;
绑定参数依次为上一页最后一条记录的 created_at 和 id。在倒序列表中使用 <；正序列表则使用 >。
id 是必要的稳定排序键。只比较 created_at 会漏掉与边界记录时间相同的其他订单。排序条件、游标字段和索引顺序必须保持一致：
CREATE INDEX idx_status_created_id
ON orders (status, created_at DESC, id DESC);
如果线上已经存在同定义索引，不要重复创建。可先检查：
SHOW INDEX FROM orders;
Python 接口实现示例
游标不应让客户端自行拼接 SQL。服务端解码后仍需校验格式，并始终使用参数化查询：
from __future__ import annotations
import base64
import binascii
import json
from datetime import datetime
PAGE_SIZE_MAX = 100
def encode_cursor(created_at: datetime, order_id: int) -> str:
"""把排序边界编码为不透明游标。"""
payload = {
"created_at": created_at.isoformat(timespec="microseconds"),
"id": order_id,
}
raw = json.dumps(payload, separators=(",", ":")).encode("utf-8")
return base64.urlsafe_b64encode(raw).decode("ascii").rstrip("=")
def decode_cursor(cursor: str) -> tuple[datetime, int]:
"""解析并校验客户端提交的游标。"""
if not cursor or len(cursor) > 512:
raise ValueError("游标长度不合法")
padding = "=" * (-len(cursor) % 4)
try:
raw = base64.b64decode(cursor + padding, altchars=b"-_", validate=True)
payload = json.loads(raw.decode("utf-8"))
created_at = datetime.fromisoformat(payload["created_at"])
order_id = int(payload["id"])
except (binascii.Error, ValueError, TypeError, KeyError) as exc:
raise ValueError("游标格式不合法") from exc
if order_id <= 0:
raise ValueError("订单 ID 不合法")
return created_at, order_id
def build_page_query(
status: int,
page_size: int,
cursor: str | None,
) -> tuple[str, tuple[object, ...]]:
"""构建参数化分页查询和绑定参数。"""
if status < 0 or status > 255:
raise ValueError("订单状态超出范围")
if page_size < 1 or page_size > PAGE_SIZE_MAX:
raise ValueError("分页大小超出范围")
base_sql = """
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = %s
"""
params: tuple[object, ...] = (status,)
if cursor is not None:
created_at, order_id = decode_cursor(cursor)
base_sql += " AND (created_at, id) < (%s, %s)"
params += (created_at, order_id)
base_sql += " ORDER BY created_at DESC, id DESC LIMIT %s"
params += (page_size + 1,)
return base_sql, params
Base64 只是传输编码，不提供防篡改能力。如果游标中包含租户、权限范围或其他安全边界，应使用服务端密钥进行 HMAC 签名，或者只保存随机游标 ID 并在服务端查回真实边界。无论是否签名，查询都必须重新应用当前用户的权限条件。
方案二：必须跳到指定页时延迟回表
后台管理系统有时必须直接跳到第 N 页。这种需求无法完全消除深偏移，但可以先在覆盖索引中定位少量主键，再回表读取完整行：
SELECT o.id, o.user_id, o.amount, o.created_at
FROM orders AS o
JOIN (
SELECT id
FROM orders
WHERE status = 2
ORDER BY created_at DESC, id DESC
LIMIT 500000, 50
) AS page_ids ON page_ids.id = o.id
ORDER BY o.created_at DESC, o.id DESC;
这个方案仍要扫描并跳过 500000 个索引项，因此复杂度没有根本改变；收益是减少大范围回表。它适合低频后台查询，不应被包装成无限制开放的高并发公共接口。
还应设置最大可跳页数。更深的数据可改用筛选条件、时间范围或异步导出，避免一次交互请求承担全表历史检索成本。
并发写入下的边界行为
游标分页保证的是稳定遍历，不是自动获得全程一致的数据库快照。
- 新插入且排序位置在游标之前的数据不会突然挤进下一页，因此比 OFFSET 更少出现重复；
- 已读取记录的排序字段若被修改，仍可能改变其后续位置；
- 记录被删除后，列表自然缩短；
- 若业务要求导出期间绝对一致，应固定查询快照或记录截止时间，而不是让长事务跨多个 HTTP 请求保持打开。
实践中可以在第一页生成 snapshot_at，后续统一增加边界：
WHERE status = 2
AND created_at <= :snapshot_at
AND (created_at, id) < (:cursor_created_at, :cursor_id)
这样可以排除分页开始后新增的数据。snapshot_at、筛选条件和排序方向应一起写入签名游标，防止客户端混用上下文。
上线步骤与验证
建议按以下顺序灰度：
- 用慢查询日志和接口指标确认深分页的真实调用量、最大偏移量和 P95/P99 延迟；
- 检查联合索引是否匹配等值过滤列与排序列；
- 新增游标参数，暂时保留旧页码接口以便兼容；
- 对第一页、时间相同的边界记录、空页、非法游标和最大页大小编写测试；
- 灰度比较扫描行数、数据库 CPU、接口延迟和重复记录率；
- 客户端全部迁移后，下线或严格限制深页码接口。
可重点测试以下边界：
同一 created_at 下有 100 条记录，连续翻页不得重复或遗漏
两次请求之间插入一条最新记录，下一页不得重复上一页数据
游标被截断、字段缺失或 ID 为负数时返回 400
page_size 超过上限时拒绝请求
切换筛选条件时不得复用旧游标
预防措施
- 所有列表排序都增加唯一的确定性字段，常用组合为
created_at, id； - 把最大分页大小和最大 OFFSET 作为服务端约束，不能只依赖前端；
- 索引按真实过滤条件设计，不要为每种可选筛选项盲目创建宽索引；
- 监控 examined rows、慢查询数量、接口 P95/P99 和数据库缓冲池命中情况；
- 大规模导出走异步任务和对象存储，不与在线分页共用同步请求链路；
- 变更索引前评估磁盘空间、写放大和 DDL 对线上实例的影响。
总结
深分页慢的核心不是返回行数，而是数据库必须扫描并丢弃大量位于 OFFSET 之前的记录。联合索引只能减少排序和回表成本，不能消除深偏移。
面向连续浏览的接口，应使用带唯一排序键的游标分页，让数据库从上次边界继续扫描；必须随机跳页的低频后台场景，可以使用覆盖索引延迟回表并限制最大深度。配合稳定排序、参数校验、权限重验和灰度指标，才能把一次 SQL 优化变成可长期维护的接口契约。
Discussion
评论