MySQL分页查询稳定性的坑与keyset游标方案实战

先交代个真实场景某个运营后台的订单列表分页参数 page2size20测了三天没问题上线第二天就有人反馈“第二页和第一页出现了同一笔订单”再过一会儿又有用户说“第三页漏了两单”。这还不是最诡异的等你去数据库里把那条SQL原样跑一遍结果完全正常。一旦加上真实流量和并发重复、遗漏就轮着来。这类问题十有八九不在SQL语法上而在分页查询的稳定性上。所谓稳定不是指数据库不报错而是指同一份数据在分页遍历过程中每一行恰好被返回一次不多不少。今天就把这个坑彻底拆开讲清楚根因、方案和排查手法。1. 分页查询的稳定性陷阱到底是什么1.1 一个看似正常却悄悄出错的案例先还原一个我实际排查过的简化版业务。订单表结构大致这样CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_create_time (create_time) ) ENGINEInnoDB;列表接口的分页SQL长这样SELECT id, order_no, user_id, status, create_time FROM t_order WHERE status 1 ORDER BY create_time DESC LIMIT 20 OFFSET 0;单看没什么问题。但注意排序字段是create_time在秒级精度下同一秒创建几十条甚至几百条订单很正常。也就是说ORDER BY create_time对很多行来说排序值是相同的。MySQL 在不指定主键的情况下遇到排序值相等的行它返回的顺序取决于执行计划、索引扫描方向、并行度甚至内存排序时的临时状态。你说它是随机的它不是随机它是不稳定。同一行在第一次查询时排在同等值组的前面第二次查询时可能排在后面。于是翻页时这一行一会儿出现在第一页一会儿出现在第二页——这就是重复的来源。1.2 重复和遗漏分别是怎么被“制造”出来的很多人以为重复和遗漏是两个独立问题实际上它们经常是一体的某一行被重复返回必然意味着另一行在相应位置被挤掉从而造成遗漏。举例。假设实际数据按我们期望的稳定顺序应该是第1页: A, B, C, D, E 第2页: F, G, H, I, J但如果排序不稳定第一次查询第二页时F变成了G后续行整体前移结果可能是第1页: A, B, C, D, E 第2页: G, H, I, J, K此时F被遗漏了而K明明应该出现在第三页却提前到了第二页。用户翻到第三页时还会看到K于是K又变成重复数据。如果中间有删除操作情况更严重。假设第一页返回了A~E用户翻了第二页前有人删掉了C。第二页用OFFSET 20去查时实际查的是“跳过前20条后的数据”而当前表中前20条已经包含了原本应该在第21~25位的部分数据于是F又被跳过。这是offset分页在数据变更下的经典盲区。所以说只要排序不稳定或数据集合发生变化offset分页就必然出现重复或遗漏区别只是什么时候触发、影响多大。2. 为什么order by limit会翻车排序不稳定性的根源2.1 数据库不保证无排序键的稳定顺序先打一个比方。你让全班同学按身高排队但只报了“身高”这一个信息。如果两个人身高完全一样老师让谁站前面这取决于老师当时看谁顺眼、先扫到谁这次A在前面下次B在前面完全合理因为老师并没有得到“同身高内部怎么排”的指令。数据库也一样。ORDER BY create_time DESC只告诉它按创建时间倒序没有告诉它创建时间相同怎么办。理论上你可以认为 InnoDB 会按主键顺序作为兜底但这是实现细节不是规范承诺。更关键的是MySQL 在很多情况下并不会老老实实把数据全部读出来排序。比如create_time上有索引优化器可能直接走idx_create_time的倒序扫描然后 filter 掉status ! 1的行最后取20条。这个过程中索引叶子节点里create_time相同的行的物理顺序跟你在另一个索引扫描下看到的不一定一致。它可能受B树页分裂、页合并、缓冲区淘汰等因素影响。这意味着什么一次翻页查询和下一次翻页查询完全可能从不同位置开始取数据。你已经写了正确的ORDER BY但它并没有达到业务上要求的确定性。2.2 常见排序键的“伪唯一”陷阱我见过大量列表接口用create_time、update_time、publish_time这类时间字段做排序。它们的问题很统一精度不够。datetime在MySQL里默认精度是秒高并发写入下同一秒内几十条数据很正常。更麻烦的是很多业务的时间字段是应用层传入的比如new Date()格式化到秒并发请求的时间戳是同一秒那写入后的create_time完全一样。你拿它做排序键排名就是并列的。还有人用status、type这类枚举字段做排序比如“按状态排序处理中在前”这更离谱因为这类字段取值集合通常就几个并列范围更大排序几乎完全不稳定。即便你选了id主键如果业务里有批量导入、脚本回填、历史数据迁移主键大小也不一定跟业务期望的“最新在前”一致。等你知道为什么明明选了id还是出问题的时候线上数据已经错乱很久了。我给一个简单自测方法看你的排序键在查询结果集里有没有大量重复值。有重复就必须附加第二排序键。没有重复也不要掉以轻心还要看并发插入和频繁更新是否会让结果集整体移动。2.3 数据变更带来的页间漂移这一节专门讲一个大家容易忽略的事实即使排序键是稳定的比如主键只要数据集合本身在变化分页结果也会跟着漂移。什么叫漂移假设当前数据主键是1~100按id升序排列。用户查第一页拿到1~20。然后有人删掉了1~10。用户再查第二页OFFSET 20跳过前20条。现在表中的前20条是11~30正好把本来应该出现在第二页的21~30跳过了。结果就是第二页实际返回了31~50原来第二页的数据21~30就整个被漏掉了。这种问题跟排序稳定性无关纯粹是offset分页依赖“上一页的终点是下一页的起点”这个隐含假设但数据删除让起点漂移了。新增数据同理。如果用户在第一页看到的是1~20然后新插入了几条排在更前面的数据比如按创建时间倒序新数据会排在最前面第二页查询时OFFSET 20跳过的已经是“前20条最新数据”而不是用户看第一页时的前20条。结果就是第二页开头几行重复出现。所以遇到分页重复、遗漏时不要只盯着ORDER BY看先问一句这个查询对应的数据集合在用户翻页的几秒内发生变化了吗如果答案是“会”那你就要么接受轻微偏差要么换分页算法。3. 从offset到keyset两种分页方案的本质区别3.1 offset分页为什么天生有盲区OFFSET 100000 LIMIT 20这种写法数据库的真实执行过程是先扫描前面那100000行然后抛弃它们只返回最后20行。所以offset越大越慢这个性能问题大家都知道。但稳定性问题比性能更隐蔽。offset分页的核心逻辑是“跳过前N条取接下来的M条”它隐含了一个前提前N条在两次查询之间保持不变。一旦这个前提被破坏稳定性就崩了。还有一个盲区当数据集合很大、并发很高时即使没有任何删除和新增同样的SQL在两次执行之间也可能因为排序不稳定产生不同的前N条顺序导致边界上的行被挤来挤去。offset分页完全没法防御这个问题因为它的跳过逻辑基于行号而行号是查询时现算的不是固定的。所以我的结论很直接只要是连翻多页的遍历型查询尤其是后台导数据、批量处理、C端长列表offset方案都不适合。它能活下来纯粹是因为简单而且数据量小、并发低的时候不太容易暴露问题。3.2 keyset分页游标分页的原理与实操keyset分页也叫seek分页、游标分页。它不告诉数据库“跳过多少条”而是告诉它“从哪一条开始”。核心SQL形态如下-- 第一页 SELECT id, create_time, order_no FROM t_order WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 20; -- 第二页以上一页最后一条为游标 SELECT id, create_time, order_no FROM t_order WHERE status 1 AND (create_time 2025-01-01 10:05:00 OR (create_time 2025-01-01 10:05:00 AND id 10086)) ORDER BY create_time DESC, id DESC LIMIT 20;原理很直白ORDER BY create_time DESC, id DESC保证结果顺序完全确定。然后下一页直接定位到上一页最后一行游标之后的位置不需要跳过任何行。这方案有几个优势数据集合中途新增或删除不会影响“当前位置”的定位。因为查询条件是create_time ? OR (create_time ? AND id ?)是严格基于值的比较不依赖行号。性能稳定。create_time和id上的复合索引可以直接走索引范围扫描翻到很深也不会越来越慢。天然防重复。因为游标被设计成唯一确定上一页的边界新查询只会从边界之后取数据不会把已经返回的行再捞出来。实操时注意两个细节第一游标字段必须跟排序字段完全一致。你ORDER BY create_time DESC, id DESC那游标也必须是(create_time, id)一个都不能少。少了任何一个边界就没法唯一确定。第二游标本身需要传给前端吗不需要。前端只需要一个不透明的cursor字符串。后端在上一页最后一条记录里拼出游标返回给前端前端翻页时原样传回来。比如{ list: [...], next_cursor: MjAyNS0wMS0wMSAxMDowNTowMCwxMDA4Ng, has_more: true }后端对这个字符串做base64解码得到create_time和id再拼进SQL。这样既安全又整洁。3.3 混合策略什么场景下绕不开offset听到这你可能想问keyset分页这么好是不是所有分页都无脑上不是。它有几个弱点不支持随机跳页。用户点第5页、点第100页用keyset基本没法做。因为跳页需要“跳到第N页的起点”但keyset只认游标不认页码。虽然可以用OFFSET来算但那又回到性能问题了。当排序条件变化时游标要跟着变。用户切换“按价格排序”“按销量排序”原来的游标不能复用需要重新生成。这需要在接口层面做缓存或状态管理。当排序字段被更新时游标可能失效。比如按update_time排序列表展示过程中某一条被更新了它的位置变化了但游标没变可能造成漏数据或重复。所以业界常见做法是混合策略C端信息流、动态列表、后台大批量导出用keyset或类似游标方案追求稳定遍历。管理后台需要跳页的场景用offset但必须接受轻微的不一致或者人为降低并发影响。跳页稳定都想要可以折中第一页用普通查询跳页时用“二分查偏移”或者“记录页面锚点ID”本质上还是游标思想的变种。我自己在多数业务里选型的规则是凡是有人盯着屏幕一页页翻的且数据量可能过万优先keyset。凡是系统自己循环拉取全部数据的比如定时任务分批处理必须keyset因为漏一条都是事故。4. 实战修复一套经得起并发和删除考验的分页方案4.1 第一步为排序键建立唯一约束排序要稳定首先要保证“并列”不存在或者并列时靠后的唯一键能打破平局。我建议每个常查列表的排序键至少是“非唯一排序字段 唯一字段”的组合。比如刚才订单的例子可以建这样一个复合索引ALTER TABLE t_order ADD INDEX idx_create_time_id (create_time, id);这个索引有双重作用一是让ORDER BY create_time DESC, id DESC直接走索引倒序性能好二是让排序逻辑上唯一。为什么加id就够了因为id是主键全表唯一。create_time相等时id一定不相等整个结果顺序就完全确定了。有的业务用user_id做排序按用户分组展示此时不能保证相同user_id只有一条那么建议再加一个业务上不会更新的字段比如创建时间或主键凑成(user_id, create_time, id)。顺序一定是业务排序字段、打破平局的第二字段、终极兜底唯一字段。注意不要单独加一个没有索引的字段做第二排序。如果排序键不在索引里MySQL 就要用文件排序性能直接崩。你要是查出慢查询再回来看这篇文章就晚了。4.2 第二步用额外排序字段打破平局这一步是在SQL层面真正实现稳定排序。只加索引还不够SQL必须这样写SELECT id, order_no, user_id, status, create_time FROM t_order WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 20;这是最基础的稳定排序写法。凡是出现ORDER BY单个字段的我都建议审查一遍哪怕是主键也不要掉以轻心。这里有个经验原则排序键的最后一个字段必须是唯一字段且最好就是主键。原因有两个主键有聚簇索引不需要额外回表性能最稳。主键在业务生命周期内原则上不允许更新天然满足“排序值稳定”的要求。有人问用order_no这种业务唯一字段行不行行但注意order_no通常是字符串字符串排序比较比整型主键慢而且如果业务上允许order_no变更稳定性就废了。能用主键就用主键不能再用别的。4.3 第三步处理中途新增/删除的数据排序稳定了不代表数据集合不变。用户翻页期间别人删了一条、加了一条还是会错位。在keyset方案下这问题基本被天然规避因为游标定位不依赖行号。但如果你必须用offset方案那只能降低错位概率做不到完全免疫。我的做法是加“版本号”或“游标快照”机制。常见做法给查询结果带上一个snapshot_version或者记录第一页查询时的最大最小排序值。翻页时把当前页的排序边界一起传过去SQL中加一个边界条件。例如WHERE status 1 AND create_time 2025-01-01 10:05:00 ORDER BY create_time DESC, id DESC LIMIT 20 OFFSET 20;这样即使中间有新增数据只要新增数据的create_time大于传入的边界就不会混进来。但删除就没辙了因为删除会导致offset跳过的行数减少结果还是可能往后偏移。所以面对删除场景我强烈建议直接用keyset不要自我安慰。keyset方案遇到删除也完全没问题上一页最后一条是id10086你删掉id10080下一页从id10086之后继续取其余数据一个都不会漏。有个折中方案是针对导出功能的一次性把结果全部查出来生成一个临时快照表然后分页读快照表。快照表里的数据是静态的offset完全准确。缺点是额外存储成本和实时性差适合数据量几万到几十万的导出场景。4.4 第四步兼容老接口的平滑升级策略现实中很多老接口已经用offset分页跑了好几年前端传page和size后端返回total和list。直接改成keyset前端接口全要变总条数也没法用同一个SQL算了。怎么办我提供一个平滑升级路径。第一阶段改成稳定排序保留offset。先把所有列表接口的ORDER BY改成“原排序字段 主键”保证排序稳定。这样即使数据集合变化重复/遗漏问题能先解决一半。这一步改动最小风险最低。第二阶段加游标参数双轨运行。保留旧接口不变新增可选参数cursor。前端传cursor就走keyset不传就按老逻辑走offset。后端判断逻辑写在service层SQL里给两套条件。这个阶段让有要求的业务先切换其他业务继续用老的。第三阶段前端改造彻底下线offset。把翻页组件改造成“加载更多”或“上一页/下一页”模式去掉页码跳转或改为“历史位置跳转”。此时后端可以关掉offset兼容逻辑。如果某些后台必须跳页可以单独保留一个快照查询但把total变成“已加载数量”不再严格等于总记录数。改造路上最容易忽略的点是接口有多个排序维度例如列表支持按时间、按价格、按销量切换。keyset游标必须绑定排序维度。我建议前端传sort_keycursor后端对每个排序维度分别生成和校验游标。游标里带上排序维度的标识防止用户用错乱序的游标去查另一个排序的数据。5. 常见问题与排查技巧实录5.1 现象一翻页时同一行数据出现两次第一步不要慌先复现。用同样的参数连续请求接口5次如果用同一页的数据一直在变那就是排序不稳定。如果同一页数据稳定但页与页之间有重叠那是数据集合变动或者游标实现有bug。查出排序不稳定后验证很简单跑一条SQLSELECT create_time, COUNT(*) FROM t_order WHERE status 1 GROUP BY create_time HAVING COUNT(*) 1 LIMIT 10;有结果出来基本坐实了create_time有大量重复排序必然不稳。然后你去看线上SQL十有八九没加主键排序。如果定位到数据集合变动建议查一下业务操作日志看翻页期间有没有对应记录的插入和删除。尤其是删除操作很多团队删除用的是物理删除对分页的影响非常明显。5.2 现象二某一页永远少了数据这种最容易被误判为“丢了数据”。实际上多数是offset漂移。排查思路手动模拟翻页流程先查第一页取到返回的最大id和时间。在代码里模拟一次删除第一页中某一条的操作。再查第二页对比结果。如果你能复现出“第二页的第一条跟期望不一致”那就是典型的漂移。解决办法只能是换keyset或者做快照。还有一种“少数据”是因为LIMIT写错了比如子查询里用了LIMIT然后外层又做了JOIN排序顺序被打乱。这种情况不在分页本身但也经常被当成分页问题处理。所以排查时一定先把SQL简化去掉JOIN和子查询确认基础分页逻辑没问题再逐步加回业务条件。5.3 排查工具与SQL模板我平时排查分页问题会用下面几个固定的SQL模板。先看分页SQL的执行计划EXPLAIN SELECT id, order_no, user_id, status, create_time FROM t_order WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 20 OFFSET 0;重点看type和Extra。出现Using filesort时说明排序键上没有可用的索引要么补索引要么改排序键。出现Using temporary更危险数据量大时会直接慢死。然后验证排序稳定性。连续执行10次同样的分页SQL把每次的id序列打出来SELECT id FROM t_order WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 20 OFFSET 20;如果输出的id序列一模一样说明排序本身稳定。如果不一样那就是数据库层面的排序不稳定。在高并发下验证我会开两个连接模拟两个用户同时翻同一页虽然概率低但能发现问题。之前就用并发脚本抓出过一次OFFSET 无主键排序导致的重复。5.4 自检清单速查我把这些年踩过的坑整理成清单写代码前过一遍至少能避掉八成问题。是否所有列表SQL的ORDER BY最后都跟了主键没有就改。排序字段是否有索引没有就加且索引顺序跟排序顺序一致。排序字段是否为业务上永不更新的字段update_time在排序场景要慎重。分页方案是offset还是keyset连翻多页且数据量大优先keyset。前端是否保存了cursor保存了page但没保存cursor等于没改。翻页期间数据集合是否可能变化可能变化且用offset那就要接受漂移风险。是否有“上一页”功能keyset做上一页比较麻烦需要额外保存历史游标栈。是否所有排序维度都绑定了独立游标多排序场景游标要区分维度。线上是否监控慢SQL分页性能问题多半是慢SQL先报警。这份清单是我做项目评审时必过的。每次有人跟我争论“这个场景不会出问题”我就拿第一条问他“排序键加主键了吗” 十次有八次答案都是没有那后续也不用争了。写在最后的经验我做后端这些年分页问题看着小炸起来一点不含糊。记得有一次夜间数据对账因为某个导出任务用了无主键排序的offset分页导致几万条订单里漏了3000多条第二天上午才被财务发现。查日志看到分页SQL确实执行了但每次拉取的数据都因为新增订单而整体后移最终导出的结果集就缺了一大块。后来我把所有定时批量处理全部改成keyset游标彻底告别这类问题。如果你现在接手的老系统还在用page、size、OFFSET组合不要急着骂当初写的人先把排序稳定性补齐再慢慢往keyset迁移。分页这件事本质上不是技术炫技而是对数据边界有敬畏心。当你把每一行数据的归属位置都定义得清清楚楚重复和遗漏就不再是玄学。