分页查询稳定性：排序确定性与集合快照一致性解析

1. 先说结论：分页的稳定性由“排序确定性”和“集合快照一致性”共同决定

1.1 一次让我印象深刻的线上事故：工单列表“重复又不重复”

去年在维护一个工单管理系统时，后台日志里翻到一条看似不起眼的反馈：客服人员在翻看“待处理工单”列表时，偶尔会发现第一页和第二页出现了同一条工单，与此同时，本该出现在第二页的某条工单却怎么都找不到。当时第一反应是接口写错了，或者是前端组件缓存没清干净，但排查下来接口逻辑没有任何问题，SQL也是看起来再正常不过的 LIMIT + OFFSET 分页。

这个问题断断续续出现，不是每次都复现，但一出现就是线上投诉。后来我意识到，很多开发同学默认“分页查询”是一个已经被数据库解决得很好的基础能力，只要把 LIMIT 和 OFFSET 写对，数据就必然按照预期切成一块一块，既不会多也不会少。但事实是，分页查询的稳定性，从来不是“写对 SQL 语法”就能保证的，它踩的是两个更深层的认知：排序是否确定，以及两次查询之间的数据集合是否一致。

这篇文章就围绕这两个核心点展开，把“数据重复”和“数据遗漏”这两类稳定性问题的底层机制、生产环境中的真实触发场景、以及对应的修复方案，完整梳理一遍。适合正在写列表接口的后端开发、DBA、以及所有被线上分页稳定性问题折磨过的同学参考。

1.2 拆开LIMIT/OFFSET这层皮

我们先看一条最典型的分页 SQL：

SELECT id, title, status, created_at FROM work_order WHERE status = '待处理' ORDER BY created_at DESC LIMIT 10 OFFSET 20;

这条 SQL 的逻辑用大白话说就是：先把所有“待处理”工单按创建时间倒序排好，然后跳过前 20 条，取接下来 10 条。听起来丝毫没毛病。

但这里藏着两个隐含假设：

第一，“排好”的这个顺序必须是稳定不变的。也就是说，同样的数据，你第一次查和第二次查，返回的行顺序要完全一致。如果顺序变了，OFFSET 20 就不再是“跳过前 20 条”，而是“跳过前 20 条在某种不确定顺序下的位置”，于是某条数据可能被重复取出，也可能被跳过去。

第二，从第一页到第二页之间，整个数据集合不能发生变化。OFFSET 本身是基于“位置”的，它的语义是“跳过 N 条”，而不是“跳过 ID 大于某个值的那几条”。如果两次请求之间有人插入了一条新记录，或者删掉了一条旧记录，整个集合的行位置就会重新洗牌，OFFSET 计算出来的位置自然就不准了。

这两条假设，在“单用户、单查询、静态数据”的演示环境里几乎总是成立。但在真实的生产环境里，数据是多用户并发写入的，数据库的查询计划也不是一成不变的，于是分页查询就变成了一个随时可能“翻车”的功能。下面我逐个拆解具体的崩溃机制。

2. 元凶一：排序字段不唯一，数据库返回顺序像薛定谔的猫

2.1 用8条数据手把手演算一遍“漏数据”过程

先说最常见的罪魁祸首：ORDER BY 后面的字段在表里并不是唯一的。

比如一张工单表，我们习惯按created_at倒序排列。工单创建时间的精度通常是秒级，甚至毫秒级。在高并发写入的场景下，同一个秒级时间戳里插入几条记录，再正常不过。考虑下面 8 条数据：

我执行ORDER BY created_at DESC LIMIT 3 OFFSET 0，预期拿到前 3 条。因为 id=1、2、3 的创建时间完全相同，数据库返回这 3 条的顺序到底是谁在前谁在后？结果很可能是(3, 2, 1)，也可能是(2, 1, 3)，甚至在不同时间点执行同一个查询，顺序都不一样。

假设第一次查询返回了(3, 2, 1)，第一页展示的是这三条。紧接着用户点下一页，执行LIMIT 3 OFFSET 3。如果此时数据库仍然按(3, 2, 1)的顺序排，第二页应该返回(4, 5, 6)，一切正常。但问题是，时间戳相同的三条记录的顺序并没有被数据库承诺固定，第二次执行可能返回(1, 3, 2)的顺序排列，OFFSET 3 跳过的是“当前实际顺序”下的前 3 条——也就是(1, 3, 2)——那么第二页实际取到的就会是(4, 5, 6)吗？

不一定。因为第二页返回结果本身也依赖排序。最麻烦的情况是：第一页返回了(1, 3, 2)，第二页在同样不稳定的排序下返回(5, 4, 6)。对比一下：id=2、3 在第一页出现过，id=4 本该在第一页却没出现，第二页反而出现了 id=5 和 id=4 的位置错乱。最终用户看到的效果就是：某条数据在第一页出现，第二页又出现（重复）；另一条数据两页都没出现（遗漏）。

这个例子的核心在于：当排序键出现重复值时，数据库并不承诺相同排序键的那些行之间有任何固定顺序。不固定的顺序，放在 OFFSET 分页里，等价于每一页的“切分点”都在随机漂移。

2.2 为什么同样的SQL，两次查询结果顺序会不同

很多读者在这里会有个疑问：同一张表、同一个 SQL，为什么数据库返回顺序会变化？我的真实排查经验是，至少有四个层面的原因：

一是执行计划可能不同。数据库优化器会根据统计信息、索引可用性、数据分布选择不同的访问路径。同一张表在刚启动时走索引扫描，数据量变大后可能改走全表扫描，或者并行扫描。不同的扫描方式天然产生不同的物理顺序。

二是索引本身不保证“全局顺序”。MySQL 的 InnoDB 在按二级索引读取时，返回行序与索引键有序性相关，但如果你只对created_at建了索引，而查询需要回表取其他字段，回表过程可能引入不同的物理读取顺序。PostgreSQL 的索引扫描也可能因为可见性映射、并行 worker 等因素产生顺序漂移。

三是排序操作本身的实现策略不稳定。当内存足够时数据库用快速排序，内存不够时改为外部归并排序，甚至用临时文件分批处理。不同排序算法在处理相同键值时的稳定性表现不同，最终输出的行序自然也不同。

四是连接与事务状态不同。不同数据库连接的会话参数、事务隔离级别、锁状态都会影响扫描器的行为，尤其是 MVCC 多版本并发控制机制下，不同时刻的快照中包含的可见版本不同，扫描到的行序也会有差异。

所以千万不要抱着“我本地跑了一百次都一样”的侥幸心理。本地只有几百条数据，一次扫描就排完了；生产环境几百万条数据加上并发写入，任何一次执行计划的细微变化，都可能放大成真实的重复或遗漏。

3. 元凶二：数据集合在翻页之间被增删改，OFFSET的偏移量已经失效

3.1 删除记录：集合缩水，下一页直接跳过一个位置

第二个元凶比第一个更隐蔽，因为即使排序字段是唯一的，只要两次请求之间数据集合发生了变更，OFFSET 分页照样会出问题。

考虑一个非常干净的集合，查询按id DESC排序，顺序是(1, 2, 3, 4, 5, 6, 7, 8)。用户请求第一页LIMIT 3 OFFSET 0，正常返回(1, 2, 3)。就在这一瞬间，业务上有一条 id=1 的记录被删除。数据集合从 8 条变成 7 条，当前顺序变成(2, 3, 4, 5, 6, 7, 8)。

用户请求第二页LIMIT 3 OFFSET 3，数据库在“当前集合”上跳过前 3 条，也就是(2, 3, 4)，返回(5, 6, 7)。对比第一页(1, 2, 3)，用户会惊讶地发现：id=4 从头到尾没有出现在任何一页里，而 id=5、6、7 本来应该由第三页承接，却提前出现在第二页。对于用户来说，他“遗漏”了 id=4。

这个场景我印象特别深。当时是一个订单列表，用户在翻页过程中会有订单被自动取消并隐藏。因为隐藏操作本身就是在修改查询可见集合，所以每一页都会“前移”一位，用户看到的效果就是越翻越觉得“少了什么东西”。

3.2 新增记录：集合膨胀，OFFSET把已读数据又推了回来

新增记录的影响恰好相反。假设当前顺序是(1, 2, 3, 4, 5, 6)，第一页LIMIT 3 OFFSET 0返回(1, 2, 3)。此时用户本人或他人操作插入了一条记录，按照排序规则它排在了最前面。新的集合变成(0, 1, 2, 3, 4, 5, 6)。

用户请求第二页LIMIT 3 OFFSET 3，这次跳过的是当前集合中的前三条(0, 1, 2)，返回(3, 4, 5)。id=3 在第一页已经显示过了，第二页又出现，这就叫重复。与此同时 id=6 被挤到了后面。

这类情况在“下拉加载更多”的 C 端信息流里尤其常见。用户往下刷的同时，后台可能每秒都在接收新的内容。如果没有专门的处理机制，用户越往下刷，重复看到的旧内容越多，且永远刷不到列表底部。

3.3 更新排序字段：一条记录在前后页之间“反复横跳”

增删之外，更新操作同样可以破坏分页稳定性。而且更新比增删更隐蔽的地方在于：数据总量没变，但是行与行之间的相对位置变了。

还是那个 8 条有序数据(1, 2, 3, 4, 5, 6, 7, 8)。第一页返回(1, 2, 3)。此时用户操作把 id=3 的记录的排序字段做了更新，使它排到了最后。新的顺序变成(1, 2, 4, 5, 6, 7, 8, 3)。

第二页LIMIT 3 OFFSET 3跳过前三条(1, 2, 4)，返回(5, 6, 7)。用户看到的是：id=4 没出现过，id=3 在第一页之后彻底消失了，id=5、6、7 本来应该是第三页的内容。这比“删除”造成的遗漏更加让人困惑，因为记录明明还存在于列表里，但你就是翻不到它。

在一些业务里，用户对某条记录的状态变更（比如完结工单、修改优先级）会直接触发排序字段更新。列表功能做得越动态，这类问题暴露得越频繁。

3.4 隔离级别：换了一个隔离级别不代表换了一种结果

有些同学可能会想：把事务隔离级别调高，比如 MySQL 默认的 REPEATABLE READ 或者更高的 SERIALIZABLE，是不是就能保证两次查询看到同一个快照，从而避免集合变化的问题？

这里必须澄清一个误区：REPEATABLE READ 保证的是“同一个事务内”多个语句看到一致的快照，但分页请求的两次独立 HTTP 请求，天然是两个独立事务。第一页请求在事务 A 中执行，第二页请求在事务 B 中执行，事务 B 同样可以看到新提交的数据。也就是说，事务隔离级别根本管不了跨请求的集合一致性。

PostgreSQL 的默认隔离级别是 READ COMMITTED，它的行为是每个语句单独获取快照，那么集合变化的影响会更加明显。但也别指望把隔离级别调到 REPEATABLE READ 就能解决分页问题——除非你把两次请求的所有分页查询塞进同一个长事务里，但这样做会导致长事务持有快照、锁资源难以释放、连接池被占满，属于典型的“饮鸩止渴”。

真正的解法，还是要从分页算法本身入手，下面专门用一节来讲。

4. 为什么开发环境测不出来，一上生产就爆

4.1 数据规模太小，掩盖了排序字段重复

这个问题几乎在所有开发团队里上演过。本地开发库里只有几十条测试数据，可能created_at字段连一秒内的并发都造不出来，排序字段唯一的假设天然成立。你在本地上跑一百遍分页接口，结果都是稳定的，于是你默认为“代码没问题、SQL 没问题”。

到生产环境，数据量膨胀到几十万上百万，同一时间戳内插入多条记录的概率大幅上升，唯一性假设瞬间崩塌。更麻烦的是，当数据表有旧数据批量导入的情况时，大量记录可能会拥有完全相同的created_at，这种“集中重复”比随机并发更为致命。

4.2 没有并发写入，集合变化根本不存在

开发环境和测试环境的数据往往是人工造出来的，造完之后基本不动。分页接口每次查询面对的都是一个静态数据集，OFFSET 的偏移量当然不会出现“集合变化导致错位”的问题。

生产环境是另一回事：用户不停地下单、取消订单、修改资料、追加评论，后台任务不停地在更新状态字段。数据集合处于随时变化的状态，用户每翻一页，面对的基本都是一个新的数据集合。前端交互越频繁、用户量越大，这种集合变化就越常见。

4.3 测试只核对页面上看到的，没核对集合完整性与连续性

大多数情况下，测试人员验证分页功能时只做了两件事：第一，点下一页看有没有报错；第二，目测页面上数据是否跟上一页长得不一样。这样的验证方式，本质上是拿“渲染成功”当成了“数据正确”，完全没有校验跨页的数据集合是否完整、是否连续。

真正的分页稳定性测试，至少应该写一个自动化脚本，把所有页拉下来之后归并成一个 ID 集合，再跟数据库里全量可见记录的 ID 集合做对比。只有当分页拉取到的 ID 并集 == 数据库全量可见 ID 集合且每个 ID 只出现一次时，才能认为分页是稳定的。这也是我最推荐大家在开发阶段就执行的校验方式。

我后来把类似的校验脚本加到了接口回归测试里，大概长这样：

def check_pagination(fetcher, total_count): seen_ids = set() page = 1 page_size = 50 while len(seen_ids) < total_count: page_ids = fetcher(page=page, page_size=page_size) if not page_ids: break duplicates = seen_ids & set(page_ids) if duplicates: print(f"第{page}页出现重复ID: {duplicates}") seen_ids.update(page_ids) page += 1 missing_ids = expected_all_ids - seen_ids if missing_ids: print(f"遗漏ID: {missing_ids}")

这个脚本不一定非要等到测试环境才跑，本地写完分页接口顺手跑一遍，就能把 90% 的排序稳定性问题暴露出来。你的开发环境造 100 条数据，其中 20 条用同样的created_at，脚本立刻就能抓到重复和遗漏。

5. 稳定分页的正确姿势：从加tie-breaker到游标分页

5.1 最小改动方案：order by 里加唯一字段兜底

如果你的项目已经到处都是LIMIT + OFFSET风格的接口，且不太方便大改，那么最小成本的修复方案就是给 ORDER BY 增加一个唯一性兜底字段，一般选择主键。

-- 原来的写法（不稳定） SELECT id, title, status, created_at FROM work_order WHERE status = '待处理' ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- 修复后的写法（稳定） SELECT id, title, status, created_at FROM work_order WHERE status = '待处理' ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 20;

原理很简单：created_at DESC相同的情况下，主键id DESC会给出唯一的、确定的顺序。这样整个 ORDER BY 的结果集是全序的，数据库每次执行查询，返回的行序都完全一致。OFFSET 的切分点不再漂移，重复和遗漏的问题从根上被消解。

这里有一个实操细节：很多人以为加个唯一字段就行，但如果主键是自增的，加在主键之前还是之后有讲究。ORDER BY created_at DESC, id DESC是先按创建时间倒序，再按 id 倒序；ORDER BY id DESC, created_at DESC是先按 id 倒序，排序键里 id 已经唯一了，created_at 基本不参与决策。如果你的业务核心排序意图是“时间从新到旧”，就应该把 created_at 放在前面，id 放后面作为 tie-breaker。

这个方案的最大优点是改动成本极低，几乎是加两个字段的事，所有旧代码都可以平滑兼容。但它没有解决“翻页过程中集合变化”的问题，只解决了“排序字段不唯一导致的顺序漂移”问题。如果业务中用户翻页期间还会有大量新增、删除、更新操作，这个方案依然不够。

5.2 游标分页：彻底摆脱OFFSET，让每一页都基于“位置”

如果要更彻底地解决，我优先推荐游标分页，也叫 keyset pagination。它不使用 OFFSET，而是基于上次查询返回的最后一条记录的排序字段值，作为下一页的“游标条件”。

假设我们按created_at DESC, id DESC排序，第一页查询：

SELECT id, title, status, created_at FROM work_order WHERE status = '待处理' ORDER BY created_at DESC, id DESC LIMIT 10;

返回最后一条记录的created_at和id，记为游标(last_created_at, last_id)。第二页的 SQL 就变成：

SELECT id, title, status, created_at FROM work_order WHERE status = '待处理' AND (created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id)) ORDER BY created_at DESC, id DESC LIMIT 10;

这条 SQL 的逻辑是：把查询起点定位在“上一页最后一条记录之后”，而不是“顺序排列后的第 N 条位置”。这样做有两个关键好处：

第一，新增记录不会影响下一页。因为游标基于具体字段值，而不是行号。即便翻页间隙插入了 100 条新记录排在最前面，下一页依然从(last_created_at, last_id)之后继续取，不会有重复也不会有遗漏。

第二，删除记录也不会导致“跳行”。即使上一页的最后一条记录已经被删了，下一页会用这个游标条件继续向后搜，能找到的下一条就是逻辑上的下一条，不存在“位置前移导致跳过中间那条”的问题。

游标分页唯一的痛点在于：它不支持“直接跳转到第 N 页”。如果产品需求里有“点击页码跳转到第 5 页”“回到第 3 页”这种交互，游标分页做不到。它更适合“下拉加载更多”“点击加载更多”这类线性的、顺序向前的浏览场景。好消息是，现代 C 端信息流、工单列表、订单列表，绝大多数都属于这种线性浏览。

5.3 快照分页：对必须保留页码的场景的妥协方案

如果产品就是要求保留传统页码，而数据又是在高频变动的，我见过比较实用的一个折中方案是快照分页：在第一次请求时，把当前符合条件的全部主键 ID 取出来，持久化存储（临时表、Redis、内存都行），后续翻页直接基于这个固定的 ID 快照进行分页。

-- 第一步：生成快照 -- 例如在 Redis 中保存一个符合条件的主键 ID 列表 -- 或建一张临时表 snapshot_order_page (page_range, id) INSERT INTO page_snapshot (user_id, seq, order_id) SELECT :user_id, ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS seq, id FROM work_order WHERE status = '待处理'; -- 第二步：从快照里分页，保证每页都是同一个集合 SELECT wo.* FROM page_snapshot ps JOIN work_order wo ON wo.id = ps.order_id WHERE ps.user_id = :user_id AND ps.seq BETWEEN :offset AND :offset + :page_size - 1 ORDER BY ps.seq;

这种方式相当于把“分页的数据集合”固化成了一张不可变列表，后续所有页码都从这个固定列表读取，天然不存在集合变化和顺序漂移的问题。代价是需要额外的存储空间，并且快照生成那一刻之后的新增数据，在当前会话中看不到。适合那种翻页期间务必保持一致性的场景，比如导出报表、Excel 预览、对账明细查看。

这里我补充一条来自实践的教训：快照方案要记得处理“快照过期清理”和“单用户并发翻页”的问题。否则一张快照表会越积越大，或者同一用户开两个标签页会导致快照互相覆盖、页面错乱。通常需要在快照上记录创建时间和会话标识。

5.4 不同方案的适用边界对比

如果让我给一个通用建议：大部分业务场景优先改成游标分页，它用最小的存储代价换来了最强的稳定性。如果产品非要页码跳转，并且数据量不大，可以继续用LIMIT + OFFSET + tie-breaker的保守方案。如果数据量极大、分页请求极其频繁，且允许短暂数据延迟，快照分页是更稳的选择。

6. 一次订单列表“漏单”事故的完整排查链路复盘

6.1 业务反馈与初步复现

我在某电商项目的订单后台遇到过一起典型事故，现象比工单系统那次还要刁钻。运营人员反馈：在处理“待发货”订单列表时，按状态筛选之后，翻到某一页发现订单少了，但搜索订单号又搜得到，说明订单确实存在，只是列表里翻不到。还有几位用户反馈某些订单在列表里反复出现。

收到反馈后我们没有急着改代码，而是先拉出后台接口日志，复原用户当时的请求参数。分页接口用的是status=待发货&page=2&page_size=20这种典型参数，SQL 是ORDER BY status ASC, amount DESC——注意，这个排序字段组合本身就不唯一：状态只有“待发货”一种取值，金额在同一订单总额上大量重复。

当时测试环境没有复现，因为测试数据里同一状态的金额几乎各不相同。但线上订单有大量相同金额的订单，比如很多 9.9 元、19.9 元、99 元的促销订单，同金额数据一多，顺序漂移立刻显现。

6.2 对账脚本定位到“跨页重复/遗漏”的具体行

我们把线上的分页接口接入了一个简单的对账脚本，脚本模仿用户连续翻页，把每一页返回的 order_id 收集起来，跟数据库全量“待发货”订单 ID 集合做比对。结果非常直观：

• 第 2 页和第 3 页之间出现了同一批金额相同的订单 ID；
• 某些金额组的订单 ID 在第 4 页之后彻底消失；
• 遗漏的订单数量与重复出现的订单数量基本一致，说明就是“位置漂移”导致的错位。

这个脚本帮我们排除了很多干扰项，比如缓存、前端翻页参数错误、接口并发覆盖等，把问题直接锁定在 SQL 结果集的不确定性上。

6.3 根因确认：status/amount并列分组下的不确定排序

进一步验证的方法是直接在 MySQL 里反复执行原始 SQL，不加任何分页条件，只看排序结果的前 40 条行序。连续执行十次，确实发现相同status + amount的订单分组内部，行顺序不固定，有时订单 A 在前，有时订单 B 在前。这就坐实了排序字段不唯一的结论。

补充一个我在数据库原理层面理解到的细节：MySQL InnoDB 在没有显式唯一排序条件时，虽然物理上可能按主键聚簇索引顺序扫描，但一旦优化器选择了临时表排序或 filesort，输出顺序就不再依赖主键顺序。order_id 主键是完全唯一的，却没有出现在 ORDER BY 里，等于把最后一道“稳定性保险”主动放弃了。

6.4 修复与回归：通过连续7天对账脚本验证稳定性

修复方案很简单：SQL 改成ORDER BY status ASC, amount DESC, order_id DESC，用主键做最终的 tie-breaker。改完以后，同样的对账脚本连续跑了 7 天，每天定时在线上模拟翻页 100 次，再与全量 ID 集合比对，重复和遗漏均为 0。

这次事故给我的收获比方案本身更大：只要你敢在核心列表接口上用不唯一的排序字段，分页稳定性问题就只是时间问题，不是概率问题。它可能在数据量小的时候潜水好几个月，但一定会在订单量、用户量达到某个临界点时集中爆雷。把“排序确定性”写进分页接口的设计规范里，比事后救火划算得多。

顺带说一句，上面那个对账脚本后来被我常态化保留了下来，每次大促前都会针对核心列表接口跑一遍。它不能保证修复所有分页问题，但至少能把“重复、遗漏”这一类稳定性问题挡在上线之前。

7. 最后再分享一点实际操作中的习惯

我在写分页相关代码时，现在默认就会做两件事：第一，任何 ORDER BY 都检查排序字段是否唯一，不唯一就加主键兜底；第二，只要接口是线性浏览场景，优先考虑游标分页而非常规的 OFFSET 分页，哪怕当前数据量还很小。分页稳定性的坑，本质上都是“排序确定性”和“集合快照一致性”这两件事没做到位。我见过太多项目在数据量小的时候觉得改造成本高、优先级低，等到线上出了事再回头改，代价至少翻一倍。

另一个值得养成的习惯是：不要把分页验证停留在“点了下一页不报错”的层面，每次改动涉及列表查询时，顺手跑一下对账脚本。代码里可能只有几十行的改动，但它能把那些只在生产环境出现的偶发问题，转化成开发阶段就能发现和修复的确定性缺陷。数据重复和数据遗漏看起来是小概率事件，但对于用户来说，每一次漏看一条关键订单或工单，都是实打实的信任流失。