1. 先说结论分页的稳定性由“排序确定性”和“集合快照一致性”共同决定1.1 一次让我印象深刻的线上事故工单列表“重复又不重复”去年在维护一个工单管理系统时后台日志里翻到一条看似不起眼的反馈客服人员在翻看“待处理工单”列表时偶尔会发现第一页和第二页出现了同一条工单与此同时本该出现在第二页的某条工单却怎么都找不到。当时第一反应是接口写错了或者是前端组件缓存没清干净但排查下来接口逻辑没有任何问题SQL也是看起来再正常不过的 LIMIT OFFSET 分页。这个问题断断续续出现不是每次都复现但一出现就是线上投诉。后来我意识到很多开发同学默认“分页查询”是一个已经被数据库解决得很好的基础能力只要把 LIMIT 和 OFFSET 写对数据就必然按照预期切成一块一块既不会多也不会少。但事实是分页查询的稳定性从来不是“写对 SQL 语法”就能保证的它踩的是两个更深层的认知排序是否确定以及两次查询之间的数据集合是否一致。这篇文章就围绕这两个核心点展开把“数据重复”和“数据遗漏”这两类稳定性问题的底层机制、生产环境中的真实触发场景、以及对应的修复方案完整梳理一遍。适合正在写列表接口的后端开发、DBA、以及所有被线上分页稳定性问题折磨过的同学参考。1.2 拆开LIMIT/OFFSET这层皮我们先看一条最典型的分页 SQLSELECT 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 条数据idcreated_at110:00:01210:00:01310:00:01410:00:02510:00:02610:00:03710:00:04810:00:05我执行ORDER BY created_at DESC LIMIT 3 OFFSET 0预期拿到前 3 条。因为 id1、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)。对比一下id2、3 在第一页出现过id4 本该在第一页却没出现第二页反而出现了 id5 和 id4 的位置错乱。最终用户看到的效果就是某条数据在第一页出现第二页又出现重复另一条数据两页都没出现遗漏。这个例子的核心在于当排序键出现重复值时数据库并不承诺相同排序键的那些行之间有任何固定顺序。不固定的顺序放在 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)。就在这一瞬间业务上有一条 id1 的记录被删除。数据集合从 8 条变成 7 条当前顺序变成(2, 3, 4, 5, 6, 7, 8)。用户请求第二页LIMIT 3 OFFSET 3数据库在“当前集合”上跳过前 3 条也就是(2, 3, 4)返回(5, 6, 7)。对比第一页(1, 2, 3)用户会惊讶地发现id4 从头到尾没有出现在任何一页里而 id5、6、7 本来应该由第三页承接却提前出现在第二页。对于用户来说他“遗漏”了 id4。这个场景我印象特别深。当时是一个订单列表用户在翻页过程中会有订单被自动取消并隐藏。因为隐藏操作本身就是在修改查询可见集合所以每一页都会“前移”一位用户看到的效果就是越翻越觉得“少了什么东西”。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)。id3 在第一页已经显示过了第二页又出现这就叫重复。与此同时 id6 被挤到了后面。这类情况在“下拉加载更多”的 C 端信息流里尤其常见。用户往下刷的同时后台可能每秒都在接收新的内容。如果没有专门的处理机制用户越往下刷重复看到的旧内容越多且永远刷不到列表底部。3.3 更新排序字段一条记录在前后页之间“反复横跳”增删之外更新操作同样可以破坏分页稳定性。而且更新比增删更隐蔽的地方在于数据总量没变但是行与行之间的相对位置变了。还是那个 8 条有序数据(1, 2, 3, 4, 5, 6, 7, 8)。第一页返回(1, 2, 3)。此时用户操作把 id3 的记录的排序字段做了更新使它排到了最后。新的顺序变成(1, 2, 4, 5, 6, 7, 8, 3)。第二页LIMIT 3 OFFSET 3跳过前三条(1, 2, 4)返回(5, 6, 7)。用户看到的是id4 没出现过id3 在第一页之后彻底消失了id5、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(pagepage, page_sizepage_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 不同方案的适用边界对比方案解决排序不唯一解决集合变化性能表现支持页码跳转改造成本加 tie-breaker 字段完全解决不解决深分页仍然慢支持极低游标分页/keySet完全解决完全解决深分页依然快不支持中等快照分页完全解决完全解决自建快照有成本支持较高限制并发修改数据完全解决仅约束场景有效依赖业务支持业务约束如果让我给一个通用建议大部分业务场景优先改成游标分页它用最小的存储代价换来了最强的稳定性。如果产品非要页码跳转并且数据量不大可以继续用LIMIT OFFSET tie-breaker的保守方案。如果数据量极大、分页请求极其频繁且允许短暂数据延迟快照分页是更稳的选择。6. 一次订单列表“漏单”事故的完整排查链路复盘6.1 业务反馈与初步复现我在某电商项目的订单后台遇到过一起典型事故现象比工单系统那次还要刁钻。运营人员反馈在处理“待发货”订单列表时按状态筛选之后翻到某一页发现订单少了但搜索订单号又搜得到说明订单确实存在只是列表里翻不到。还有几位用户反馈某些订单在列表里反复出现。收到反馈后我们没有急着改代码而是先拉出后台接口日志复原用户当时的请求参数。分页接口用的是status待发货page2page_size20这种典型参数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 分页哪怕当前数据量还很小。分页稳定性的坑本质上都是“排序确定性”和“集合快照一致性”这两件事没做到位。我见过太多项目在数据量小的时候觉得改造成本高、优先级低等到线上出了事再回头改代价至少翻一倍。另一个值得养成的习惯是不要把分页验证停留在“点了下一页不报错”的层面每次改动涉及列表查询时顺手跑一下对账脚本。代码里可能只有几十行的改动但它能把那些只在生产环境出现的偶发问题转化成开发阶段就能发现和修复的确定性缺陷。数据重复和数据遗漏看起来是小概率事件但对于用户来说每一次漏看一条关键订单或工单都是实打实的信任流失。