外观
08 - 分页越往后越慢:LIMIT 深分页的原理与改写
摘要与阅读定位
同样返回几条订单,为什么第一页很快,后面的页却越来越慢?关键在于数据库怎样找到这一页:按位置分页要先跳过前面的结果,按游标分页则从上一页末行的排序位置继续。本文先用十条记录解释两种方式,再讨论并发修改、必须跳页时的改写,以及需要固定数据版本的导出任务。
- 前置:12 联合索引、10 NULL;验证:14 执行计划。
- 一致性解释:17 隔离级别、18 MVCC;排序成本:15。
- 基线:MySQL 8.0.18+ / 8.4、InnoDB;实验方法见 A。以下查询结果均为预期输出;耗时和扫描计数应在目标环境测量。
初次先读第 1—2 节,完成静态分页即可。第 3 节需要两个独立连接,第 4 节需要事务可见性基础;两者是可选进阶。不能为说明深分页直接给生产生成大量数据。
从十条记录理解两种分页
假设确定排序后的 ID 为 10,9,8,7,6,5,4,3,2,1,每页三条:
| 方式 | 第二页表达的意思 | 静态结果 |
|---|---|---|
| OFFSET / LIMIT 3,3 | 从结果起点跳过三条,再取三条 | 7、6、5 |
| 游标 / Keyset | 从上次最后一行 8 之后继续 | 7、6、5 |
LIMIT offset,count 中,offset 是跳过的结果数量,count 是最多返回的条数。例如 LIMIT 3,3 跳过前三条,再取最多三条;OFFSET 不是主键值。
游标是保存继续位置的信息。排序键是 ORDER BY 用来决定行顺序的字段;若先按时间、再按 ID 排序,游标就必须同时保存时间和 ID,不能只记页码或 ID。本篇把这种以排序键为继续边界的方式称为 Keyset 分页。
静态数据下,两种方式得到相同结果,但访问方式不同:OFFSET 可以直接表达“第几页”,Keyset 需要知道前一页的末行。两者也都不会自动固定后续请求看到的数据版本;这种跨页一致性问题在第 3—4 节展开。
1. 现象与原理:返回五行,可能处理五千零五行
在至少有 5005 条符合条件的结果时,LIMIT 5000,5 通常需要按顺序取得前 5005 条合格结果,丢弃前 5000 条,再返回五条。即使索引已经排好顺序,也通常没有标出“符合这组条件的第 5001 条”在哪里,因此仍要向前遍历。这就是 OFFSET 越大、重复跳过的工作越多的原因。
这里的 5005 是合格结果数量,不一定是原始扫描行数。若过滤条件不能有效利用索引,数据库可能检查更多行才能凑够结果;若不能借助索引顺序,还可能读取整个输入再排序。符合条件的结果不足 5005 条时,也只能取得实际存在的结果,不能把 5005 当作固定扫描计数。
覆盖索引指查询需要的列都能从索引中取得;若还需要索引之外的列,就可能要根据主键读取完整记录,这一步称为回表。因此,深分页除了遍历索引项,还可能为最终不返回的行付出回表成本,具体取决于执行计划。
本例按时间和 ID 降序排列,Keyset 把下一页条件改为“严格小于上一页末行的完整排序键”。有匹配的索引访问路径时,数据库可以从边界附近继续,减少反复从结果起点跳过的工作。但如果边界之后只有少量行符合过滤条件、索引顺序不匹配,或涉及分布式数据搬运,仍会有额外成本,不能保证总工作量只与页大小成正比。
2. 独立初始化:重复时间必须出现在页边界
先结束其他实验事务,再在隔离库初始化 paging_order。下面会删除并重建该表,只能用于可丢弃的教学数据。十条订单每两条共享一个时间,状态全部为 PAID;created_at 和 id 均非 NULL。联合索引按租户、状态、时间和 ID 组织,本例固定前两个字段,再按后两个字段排序。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS paging_order;
CREATE TABLE paging_order (
id BIGINT PRIMARY KEY,tenant_id BIGINT NOT NULL,status VARCHAR(16) NOT NULL,
created_at DATETIME(3) NOT NULL,amount DECIMAL(10,2) NOT NULL,
payload VARCHAR(500) NOT NULL,
KEY idx_paging_feed (tenant_id,status,created_at,id)
) ENGINE=InnoDB;
INSERT INTO paging_order VALUES
(1,1,'PAID','2026-09-01',10,'one'),(2,1,'PAID','2026-09-01',20,'two'),
(3,1,'PAID','2026-09-02',30,'three'),(4,1,'PAID','2026-09-02',40,'four'),
(5,1,'PAID','2026-09-03',50,'five'),(6,1,'PAID','2026-09-03',60,'six'),
(7,1,'PAID','2026-09-04',70,'seven'),(8,1,'PAID','2026-09-04',80,'eight'),
(9,1,'PAID','2026-09-05',90,'nine'),(10,1,'PAID','2026-09-05',100,'ten');
SELECT id,created_at FROM paging_order
WHERE tenant_id=1 AND status='PAID'
ORDER BY created_at DESC,id DESC LIMIT 3;第一页预期 ID 10、9、8;游标取最后实际返回行 (2026-09-04 00:00:00.000,8),不是当前时间或最大的 ID。下一页:
sql
SELECT id,created_at FROM paging_order
WHERE tenant_id=1 AND status='PAID'
AND (created_at<'2026-09-04'
OR (created_at='2026-09-04' AND id<8))
ORDER BY created_at DESC,id DESC LIMIT 3;预期输出为 7、6、5。条件分成两部分:先取时间更早的行;时间与游标相同时,再取 ID 更小的行。ID 7 与上一页末行 8 的时间相同,因此只用 created_at<'2026-09-04' 会漏掉 7。
如果只按时间排序,同时间行之间的先后就没有明确规则。加入唯一 ID 后,时间与 ID 组成完整排序键,足以确定每行的位置。静态数据中,每页都用末行更新游标,遍历全集应为 10,9,8,7,6,5,4,3,2,1,每个 ID 恰好出现一次。
继续验证第三、第四页;每次都使用上一页实际末行,不从当前时间生成游标:
sql
SELECT id, created_at FROM paging_order
WHERE tenant_id = 1 AND status = 'PAID'
AND (created_at < '2026-09-03'
OR (created_at = '2026-09-03' AND id < 5))
ORDER BY created_at DESC, id DESC LIMIT 3;
SELECT id, created_at FROM paging_order
WHERE tenant_id = 1 AND status = 'PAID'
AND (created_at < '2026-09-01'
OR (created_at = '2026-09-01' AND id < 2))
ORDER BY created_at DESC, id DESC LIMIT 3;推导预期第三页为 4、3、2,第四页为 1;再用第四页末行继续会得到空结果,遍历结束。核对完整列表,可以发现只检查第二页时容易遗漏的边界错误。
也可以用行值比较 (created_at,id)<(?,?),其中 ? 表示绑定的游标参数。它按字段顺序比较:时间不同时比较时间,时间相同时比较 ID。对本例非 NULL、同向降序的排序键,这种写法可以表达下一页边界,但具体 MySQL 版本的范围优化不一定与展开的 OR 条件相同,应分别检查 执行计划。
混合方向需要分别处理。例如时间 DESC、ID ASC 时,边界应写成 time<cursor OR(time=cursor AND id>cursor_id);这里是条件示意,time、cursor 和 cursor_id 分别代表时间字段、游标时间和游标 ID。若排序键可为 NULL,还要先定义空值排在何处及如何编码到游标中,不能用普通 < NULL 判断边界。
3. 两会话验证:插入使 OFFSET 重复,Keyset 不形成快照
本节是可选进阶。A 负责分页读取,B 负责修改数据;两者必须使用独立数据库连接,可通过不同的 CONNECTION_ID() 确认。两个连接都先结束旧事务,再进入指定自动提交状态。两个编辑器标签可能复用同一连接,不能据此判断独立性。
用第 2 节初始化状态。A 独立连接、autocommit=1,读取第一页后记录末行。B 独立连接提交:
sql
USE kb_database_lab;
SET autocommit=1;
INSERT INTO paging_order VALUES (11,1,'PAID','2026-09-06',110,'new');A 分别查第二页:
sql
SELECT id FROM paging_order WHERE tenant_id=1 AND status='PAID'
ORDER BY created_at DESC,id DESC LIMIT 3,3;
SELECT id FROM paging_order WHERE tenant_id=1 AND status='PAID'
AND (created_at<'2026-09-04' OR (created_at='2026-09-04' AND id<8))
ORDER BY created_at DESC,id DESC LIMIT 3;插入后,列表变成 11,10,9,8,7,6,…。OFFSET 仍跳过前三条,所以第二页预期为 8、7、6,第一页已经读过的 8 再次出现。Keyset 的边界仍是上一页末行 8,预期输出为 7、6、5;新增的 11 位于边界之前,不进入后续页。因此 Keyset 避免了这次位置移动造成的重复,却没有保证整次遍历包含所有最新数据。
B 再提交一个排序键变化:
sql
UPDATE paging_order SET created_at='2026-09-03 12:00:00' WHERE id=10;A 用原游标再次查询 Keyset 第二页,预期输出为 7、10、6。ID 10 已在第一页出现过,更新时间后却落到边界之后,因此又被读到。反过来,尚未读取的行若移到边界之前,就可能被漏掉;状态或租户等过滤字段变化,也会改变符合条件的行集合。
实时列表是否允许这种变化,需要由产品需求决定。若业务保证排序键和过滤字段不可变,可以减少跨页漏行或重复,但仍不能据此认定所有页来自同一历史版本。
4. 导出:键上界、固定成员与固定版本是三种承诺
本节是可选进阶。实时列表可以允许数据变化,导出任务则可能要求所有页来自同一数据版本。一致视图决定查询能看到哪些已提交行版本;在同一视图中读取,可以避免不同页因并发提交而看到不同版本。它与“保存了下一页游标”是两件事,背景见 17 隔离级别、18 MVCC。
设计导出前,先区分三种承诺:只限制键的上界、固定要导出的成员,还是固定成员及其字段版本。
| 方案 | 能保证什么 | 不能保证什么 |
|---|---|---|
id <= export_max_id | 排除更大 ID | 已有行的字段/状态不变、晚提交的小 ID 不出现 |
| 保存待导出 ID 集合 | 成员集合固定 | 导出这些 ID 时取到同一版本的字段 |
| 一致读事务内完成遍历 | 同一事务一致视图下的行版本 | 跨连接/新事务自动共享视图,外部资源共同一致 |
| 同一视图物化完整导出投影 | 后续读物化数据得到固定内容 | 捕获过程成本低、跨库原子捕获自动成立 |
自增 ID 的分配次序不等于事务提交次序:较小 ID 所在事务可能更晚提交。因此 MAX(id) 不能证明截止时间前的记录已全部提交,也不能冻结已有行的更新与删除。
RR 是 REPEATABLE READ(可重复读)隔离级别。长时间保留 RR 一致视图,会使读取仍需要的旧版本无法及时清理;连接断开后,也不能仅凭游标恢复同一个视图。表中的“物化”指把查询结果实际保存下来,“导出投影”指需要导出的字段集合。大导出可考虑可恢复的物化任务、符合需求的备份或分析快照,以及专用读取负载,并验证恢复方式和数据留存,不应只靠延长事务解决。
另一次独立实验:重新初始化第 2 节后 A 开始 RR 只读事务,B 才做变化:
sql
SET @paging_old_isolation=@@session.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT,READ ONLY;
SELECT id FROM paging_order WHERE tenant_id=1 AND status='PAID'
ORDER BY created_at DESC,id DESC LIMIT 3;B 提交第 3 节插入和更新后,A 在同一事务、普通一致性 SELECT下查询原游标第二页,预期仍为 7、6、5;最终 COMMIT,恢复原隔离设置。不要改为 FOR UPDATE 或在分页中 COMMIT 再开启,否则语义改变。18解释快照与清理压力。
操作顺序必须明确:先结束 A/B 的旧事务并重新初始化,不能沿用第 3 节已修改的数据;A 的第一条 SELECT 完成后,B 才依次插入 11、修改 10,各执行一次。确认 B 已成功提交,再回到 A 查询。A 保持同一事务,才能比较数据变化前后的快照读取结果。
sql
SELECT id FROM paging_order WHERE tenant_id=1 AND status='PAID'
AND (created_at<'2026-09-04' OR (created_at='2026-09-04' AND id<8))
ORDER BY created_at DESC,id DESC LIMIT 3;核对结果后,单独结束成功的只读实验并恢复配置:
sql
COMMIT;
SET SESSION transaction_isolation=@paging_old_isolation;上述 COMMIT 仅用于已确认成功的只读实验。异常或未达到预期时,先在 A 执行 ROLLBACK,再恢复保存的隔离级别并保留结果;不要在事务未结束时尝试改隔离配置。断线重连后旧会话变量不再可靠,应重新确认配置并从头开始。
5. 必须跳页时:延迟关联减少宽行工作,不消除 OFFSET
必须按页码跳转时,可以考虑延迟关联:先在子查询中确定本页 ID,再关联原表获取金额、载荷等字段。若子查询使用覆盖索引,就可以先处理较窄的索引项,只为本页记录读取较宽的完整行,从而减少被跳过行的宽行读取工作。
sql
SELECT orders.id,orders.amount,orders.payload,orders.created_at
FROM paging_order AS orders
JOIN (
SELECT id FROM paging_order
WHERE tenant_id=1 AND status='PAID'
ORDER BY created_at DESC,id DESC LIMIT 6,3
) AS page_ids ON page_ids.id=orders.id
ORDER BY orders.created_at DESC,orders.id DESC;从第 2 节的初始化状态执行,预期输出为 4、3、2,本页只有三条记录需要关联获取完整字段。
子查询仍要跳过六条结果,因此这种改写没有消除 OFFSET。是否真的减少回表和宽行处理,要看实际执行计划。外层仍须写 ORDER BY,因为 JOIN 不保证保留子查询的输出顺序。MySQL InnoDB 同一条普通 SELECT 通常基于同一一致视图;拆成多个请求或涉及跨库关联时,需要另行确认一致性。
小样本只验证结果。评估深 OFFSET 时按可丢弃环境预算扩展数据,保存参数、原始行数、统计、版本及冷热缓存,对比 OFFSET/延迟关联/Keyset 的计划、运行工作和客户端全部消费时间;别为演示直接生成百万行或清缓存。
6. 游标协议与排障验收
这里的 Token 是服务端交给客户端、由客户端在下一页请求中带回的游标凭据。协议设计先保证继续位置和过滤条件一致,再考虑编码与防篡改。
Token 需要携带完整排序键、排序方向、过滤条件及其版本、租户范围和有效期,避免客户端把某个列表的继续位置用于另一个列表。时间应保留数据库精度,BIGINT 应采用不会丢失整数精度的编码,否则边界值变化也可能导致漏行或重复。
签名用于检测 Token 是否被篡改,不能替代每次请求的租户授权检查。Base64 只是编码,不等于签名或加密。过滤条件或排序规则改变时,应拒绝旧游标,或明确定义旧游标如何迁移。
- 若静态数据漏行:查完整唯一排序、比较方向、时间精度与 NULL。
- 若并发重复:区分 OFFSET 位移与排序/成员变化,不能仅换成 Keyset 宣称消失。
- 若深页慢:看跳过量、索引顺序、回表和额外排序,按 12、14验证。
- 若导出清理压力:按 18识别长视图,不把上界当快照。
清理前结束所有会话事务、恢复 A 的隔离级别,再在隔离库删除 paging_order。若要继续分析性能,15解释排序与候选数据量的成本,25介绍慢 SQL 排查和性能回归方法。
7. 名词解释
| 名词 | 含义 |
|---|---|
| OFFSET / LIMIT | 从结果起点跳过多少条 / 返回条数上限,不直接限制原始扫描工作 |
| Keyset / 游标 | 按上次末行的排序键继续 / 保存继续位置的信息 |
| 完整排序键 | 足以决定每一行先后的字段组合,例如时间与唯一 ID |
| 延迟关联 | 先从较窄的候选定位本页 ID,再获取需要展示的宽行 |
| 快照 / 固定成员 | 保留同一可见版本边界 / 只固定要处理的 ID,字段仍可能变化 |
| RR / MVCC | 可重复读隔离级别 / 利用多版本解释读可见性的机制,后续事务文章展开 |
| 游标 Token | 传回客户端的继续凭据,可包含排序键、过滤与授权范围信息 |
参考资料
- MySQL 8.4 Reference Manual:LIMIT Query Optimization、ORDER BY Optimization、Row Constructor Expression Optimization、InnoDB Consistent Nonlocking Reads。
- 本文不把游标上界或唯一排序推广成快照;分页结果、两会话交错及元组范围计划仍需按 A在目标实例留证。