游标分页避坑指南：排序字段不唯一与索引设计全解析

看到标题我就想起上个月刚处理过的一个线上事故内容社区的分页接口在发布新版后运营同学反馈第二页和第一页重复了三条数据同时还有一批内容怎么翻都翻不出来。查到最后锅不在接口逻辑而在那个被团队公认最稳定的游标分页方案上。今天就把这类问题的根源、复现过程以及真正能落地的写法一次讲清楚希望能帮你少踩几个我已经踩过的坑。1. 看似完美的真正边界在哪里游标分页到底解决了什么问题先说清楚游标分页为什么会被推到银弹的位置。传统的偏移分页写法是LIMIT offset, size它的性能瓶颈在深翻页你请求第 100 万条数据数据库要先扫描并丢弃前面 100 万行再返回目标行。随着页码增大IO 成本线性上升到后期一个简单的列表页能把 CPU 打满。更麻烦的是动态数据下的不一致——第一页和第二页之间如果有人插入了一条新记录第二页的所有数据整体往后错一位用户会看到重复或者遗漏。游标分页的解决思路很直接不再用跳过多少行来定位而是用上一批最后一条记录的位置继续往后查。核心 SQL 长这样-- 按主键 id 升序翻页 SELECT * FROM articles WHERE id :last_id ORDER BY id LIMIT 20;代码量极小但效果显著不管翻到多深只要索引命中每次查询的代价基本恒定能稳定支撑几十万甚至上亿行的深翻页。而且由于定位锚点是最后一条记录的 ID而不是相对偏移量在翻页间隙插入的数据不会影响后面几页的顺序和连续性。单从这个角度看它确实比偏移分页更适合瀑布流、无限加载、消息记录这类场景。但注意上面这一切成立的前提是排序键唯一并且稳定。如果你的排序条件不满足这两点游标分页会以另一种形式把偏移分页的坑全部还给你甚至坑得更隐蔽。我见过太多人拿着ORDER BY created_at DESC就往线上怼游标分页直到某天数据开始莫名重复、缺失才意识到问题的严重性。2. 头号陷阱排序字段不唯一重复和漏数据是怎么同时发生的这是游标分页所有坑里最常见、也最致命的一个排序字段本身不唯一但你只用它做游标。2.1 现场复现一次真实的重复与漏数据假设你的文章表有 6 条数据按发布时间倒序排列如下id标题publish_time1文章A12:00:052文章B12:00:053文章C12:00:054文章D12:00:045文章E12:00:046文章F12:00:03按常见写法第一页取 4 条SELECT * FROM articles ORDER BY publish_time DESC LIMIT 4;拿到的是 A、B、C、D 四条其中 D 是 12:00:04 这一秒里的第一条。游标取最后一条记录的publish_time 12:00:04。第二页查询如下SELECT * FROM articles WHERE publish_time 12:00:04 ORDER BY publish_time DESC LIMIT 4;问题出现了同一秒12:00:04里还有一条记录 E它的 publish_time 等于游标值 12:00:04publish_time 12:00:04直接把它过滤掉了。第二页实际只返回 F 一条E 永远翻不出来。反过来如果把条件改成publish_time 12:00:04E 能查到但 D 会再次出现在第二页造成重复。单字段游标在排序字段有重复值时重复和遗漏必然至少发生一个。2.2 根因分析单字段游标为什么必然出错根本原因在于游标必须能唯一标识上一条记录的精确位置。publish_time这一类业务字段只能表达时间位置表达不了同一时刻下具体是哪一条。当LIMIT的截断点正好落在同一时间值的多条记录中间时单字段游标根本不知道边界切断在哪一行于是只能用或二选一无论选哪个都会错。这就像一个书签只记录了你读到第 220 页但同一页上有两段内容你没法精确说出读到这一段的哪一句话。要精确定位书签上必须再写一行信息比如第 220 页第二段第三行。2.3 解法方向复合游标解法是把排序字段和一个唯一字段组合成复合游标。上面这个场景游标应该是(publish_time, id)第二页查询改成SELECT * FROM articles WHERE (publish_time 12:00:04) OR (publish_time 12:00:04 AND id 4) -- 4 是第一页最后一条 D 的 id ORDER BY publish_time DESC, id DESC LIMIT 4;这样 12:00:04 这一秒中 id 小于 4 的记录即 E会被精准捞出来既不会漏也不会重复。这也是绝大多数游标分页教程最终会给出的标准形态。这里有个细节要提醒第二锚点字段必须本身唯一且与排序位置强相关一般直接用主键 id。如果 id 也是可重复或非单调的复合游标同样会翻车这就是下一节的内容。3. 你以为安全的 ID 游标分布式ID、字符串排序与时间回拨的连环坑知道游标不能只有一个业务时间字段之后很多人会走向另一个极端排序只用ORDER BY id DESC游标直接取 id。看起来主键唯一又单调总该稳了吧真实工程里这里还有四五个连环坑等着你。3.1 分布式ID 不是严格单调的雪花算法Snowflake ID生成的 ID 在单机单毫秒内是有序的但跨实例时全局顺序和时间顺序并不是严格一致的。比如实例 A 在 10:00:00:100 生成了一批 ID实例 B 在同一毫秒内也生成了一批 ID由于两个实例的序列号起点不同后面实例生成的 ID 可能比前面实例生成的 ID 小。如果你把按 ID 排序理解成按创建时间排序来设计业务逻辑用户可能在翻页时看到更早的数据出现在列表后面这种错位感。更极端的场景是分库分表每个分片的自增 ID 各自独立ID 不是全局唯一更谈不上全局单调。此时直接用单表自增 ID 做游标分页结果会跨库交错乱到没法看。3.2 字符串ID 的字典序问题还有一种隐蔽场景主键 是 varchar 类型里面存的是纯数字字符串。100和99按字典序排序时100 99翻页顺序会和你预期的数值顺序完全相反。业务表如果是从旧系统迁移过来、主键 还保留着字符串格式分页时这个坑很容易突然爆出来。3.3 时间回拨与数据迁移使用带时间戳的 ID 生成器时如果发生时钟回拨同一生成器后面产出的 ID 会比前面已经产出的 ID 更小游标分页的单调性假设被直接打破。数据库主从切换、数据迁移后重新导入 ID也可能改变 ID 的全局顺序——原来按 ID 递增就是按时间递增迁移后这条链路就断了。3.4 排序字段被更新后游标指向消失的位置比上面三种更常见的情况是排序字段本身在变。比如列表按updated_at排序游标取(updated_at, id)。第一页返回了某条记录 X用户还没翻到第二页时有人更新了 X 的updated_at把它顶到了列表第一页。等用户发起第二页查询时X 已经不在游标后面的区间里了于是这条记录消失了反过来如果它被顶到用户还没翻到的深页它又会重复出现。这不是游标分页的 bug而是动态排序的固有语义排序键一变页面内容的连续性就无法保证。解决方案取决于产品要什么要么接受实时优先、可能有重复/飘移的实时流语义要么做一个快照游标先把排序结果固化成快照再逐页取后者代价高但能保证严格一致。4. 复合游标的正确写法和索引设计从SQL细节到EXPLAIN验证前面说了复合游标是正解但很多人写了复合游标之后性能反而更差了因为 SQL 写法、排序方向、索引设计三件事没有对齐。这一节把最容易出问题的细节一次性讲透。4.1 两种等价 SQL 写法要表示(created_at, id)这个复合游标的位置有两种常见写法。写法 A展开式兼容所有数据库逻辑直观-- 倒序向下翻页下一页是更旧的数据 WHERE (created_at :last_created_at) OR (created_at :last_created_at AND id :last_id) ORDER BY created_at DESC, id DESC LIMIT 20;写法 B元组比较式SQLite、PostgreSQL、MySQL 8.0 都支持WHERE (created_at, id) (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20;元组写法简洁很多但要注意它在 MySQL 里对优化器不一定友好。我曾遇到过 MySQL 5.7 上写(created_at, id) (...)时优化器没有正确走联合索引而是退化成全表扫描的案例。如果你在用 MySQL 且版本较老优先用写法 A并对两条 SQL 分别跑一遍 EXPLAIN确认执行计划都在走索引。这个习惯我后面还会强调它救过我很多次。4.2 排序方向与比较符号的对应关系这里非常容易搞混。规则一句话游标比较的方向必须和排序方向相反。倒序翻页时游标值越来越小所以要往更小的方向比较正序翻页时游标值越来越大所以要往更大的方向比较。对应关系如下页面排序比较条件实际含义ORDER BY created_at DESC, id DESC(created_at, id) (:t, :id)取比当前游标更旧的记录ORDER BY created_at ASC, id ASC(created_at, id) (:t, :id)取比当前游标更新的记录如果把倒序列表的游标条件写成你会拿到刚翻过的数据而且还会无限循环——第一页的游标区间永远包含旧数据。这个 bug 在单元测试里容易测出来但如果你只是手工点了两页看到能翻页就上生产很容易漏掉。4.3 索引设计为什么不能只建单列索引ORDER BY created_at DESC, id DESC加上WHERE (created_at, id) (:t, :id)这个条件对索引的要求其实很高。理想索引是(created_at, id)联合索引索引键顺序与排序顺序一致这样数据库可以直接按索引正序或倒序扫描每页查询只读取目标区间那一小段数据代价极低。但很多人会犯一个错误只给created_at建单列索引。理由是反正 InnoDB 的二级索引叶子节点自带主键 id。问题在于 MySQL 优化器不会自动把二级索引里隐含的主键当作可参与范围扫描的普通索引列。WHERE created_at :t AND id :last_id的id过滤条件在单列索引下只能回表后再判断性能会退化成一个一个主键 回表再过滤的过程。正确做法是显式创建联合索引ALTER TABLE articles ADD INDEX idx_created_at_id (created_at, id);如果你是 MySQL 8.0 或者 PostgreSQL还可以直接创建方向匹配的索引让排序彻底不走 filesort-- MySQL 8.0 CREATE INDEX idx_created_at_id_desc ON articles (created_at DESC, id DESC); -- PostgreSQL CREATE INDEX idx_created_at_id_desc ON articles (created_at DESC, id DESC);MySQL 5.7 则不需要建方向相反的索引普通正向索引配合倒序扫描也能满足created_at DESC, id DESC的排序需求关键点是两个排序字段方向必须一致如果出现DESC, ASC这种混合方向旧版本就无能为力了只能 filesort。4.4 EXPLAIN 验证的三个检查点写完 SQL 和索引后任何基于经验的判断都不如 EXPLAIN 可靠。你只需要看三个关键位置key是否命中联合索引而不是 NULL。Extra有没有Using filesort有就说明排序方向和索引顺序不一致需要调整索引。rows估算扫描行数是否接近LIMIT值如果接近全表行数说明条件没走下索引。我之前在一个订单列表上就亲眼见过复合游标 SQL 写得完全正确索引也建了但因为 OR 条件的写法问题MySQL 优化器始终不选联合索引而是走了主键扫描 filesort接口 QPS 一高就雪崩。最后把 SQL 拆成 UNION 或者调整 OR 顺序才解决。所以别嫌麻烦上线前把 EXPLAIN 结果截图留档后面排查性能问题会省很多时间。5. JOIN、实时数据与动态排序游标位置不能拿最后一条硬推游标分页在单表简单排序下表现很好一碰到 JOIN、GROUP BY、实时变化的数据集很多人的第一反应还是取最后一条记录的 ID 往下推这里面的坑特别密集。5.1 JOIN 查询中锚点必须和排序字段同表看一个典型场景SELECT ... FROM users u JOIN orders o ON o.user_id u.id ORDER BY u.score DESC。游标如果取(o.created_at, o.id)而排序字段是u.score锚点和排序键不在同一张表上。当一个用户的score变化时它的所有订单行都会整体移动但你手里的游标还停留在旧位置翻页结果会错乱到无法解释。所以 JOIN 场景的第一原则游标锚点所属的表必须就是排序键所属的表。上面的查询如果想做游标分页应该先确定你到底要按用户排序还是按订单排序。如果按用户排序锚点应该取(u.score, u.id)查询时必须用某种方式把用户维度的唯一位置传下去而不是拿结果集最后一行的订单 ID 硬充游标。5.2 GROUP BY 和聚合查询单行锚点失效当查询带GROUP BY或DISTINCT时结果集已经不是底层表的行集合了。比如GROUP BY user_id ORDER BY COUNT(*) DESC聚合结果里没有唯一行标识你是没法用一个底层表的 ID 去定位下一个分组的。这种情况下继续硬做游标分页通常会出现漏数据或者死循环——你以为取到了最后一行但下一批聚合结果可能包含它又可能跳过它。对于聚合结果集我更推荐换个思路能预计算就先物化聚合结果在物化表上做游标分页结果集不大就老老实实用偏移分页几千行的聚合结果分页成本并不高。非要在超大聚合结果集上实时分页那基本是无解的工程上没人会这么干。5.3 实时数据流返回行数不足不代表结束了无限滚动手游标分页还有一个常见误判这一页返回了 15 条小于LIMIT 20就直接把next_cursor置空告诉前端没有更多了。这在静态数据集上没问题但在实时数据流里数据随时在增长返回 15 条只是当前时刻没有更多下一秒可能又多了 20 条。我的做法是只要这一页返回了数据就把next_cursor正常传给前端由前端决定是继续拉取还是显示没有更多提示。如果返回 0 条才真正表示游标位置的查询结果为空。是否结束的判定要交给业务不能由后端基于是否填满一页替用户做主。5.4 筛选条件变化时游标必须重置还有一个高频会踩的坑用户翻到第 5 页时突然把筛选条件从全部改成了只看热度大于 100 的前端还带着旧游标去请求。结果是什么要么查出一堆不符合新筛选条件的数据要么直接空页。原因是游标是基于旧筛选条件下的位置计算的新条件完全失效。严谨的做法是筛选条件变更时前端清空游标从第一页重新拉取。这个约定要在接口文档里写清楚最好在数据结构上用请求参数版本号或筛选条件哈希来做兜底后端发现游标对应的查询条件哈希不一致时直接返回参数错误让客户端重置。6. 游标 token 的权限、过期与双向翻页容易被忽略的工程暗礁技术选型和 SQL 都做对之后游标分页还有一些工程层面非常容易翻车的地方很多人直到被安全测试或用户投诉打爆才发现。6.1 Base64 只是编码不是加密最常见的安全误区把(created_at, id)拼成 JSON 再 Base64 编码就当作不透明 token发给前端。用户随便找个在线 Base64 解码工具就能看到明文还能手动改字段。如果游标跟用户身份有关比如游标里带了user_id攻击者把它改掉再请求就可能读到别人的数据。两步解法任选其一服务端缓存映射游标就一个随机字符串后端存cursor_id - (created_at, id, user_id, expire_at)彻底不泄露原始数据。自包含 签名游标内容包含签名值payload (created_at, id, user_id, expire_at)再加上HMAC(payload, server_secret)后端先验签再使用。签名密钥放服务端用户改一个字节都校验失败。我个人的倾向是数据比较敏感的场景用服务端缓存简单列表用 HMAC 自包含就够了。需要注意无论哪种方案游标都只负责定位不负责授权查询条件里必须仍然带上用户的数据权限范围比如WHERE user_id :current_user_id否则改游标照样能横向越权。6.2 游标过期与数据失效带缓存的游标有过期时间通常 10 分钟到 1 小时过期后要么让用户重新拉第一页要么设计续期机制。自包含游标本身不过期但如果排序字段的值变化了或指向的记录被删除查询时只需要正常按条件过滤即可——游标只是位置不保证记录仍存在这一点是游标分页相对偏移分页更健壮的地方。6.3 双向翻页prev cursor 的实现复杂度很多管理后台不只要求加载更多还要求能点上一页。游标分页做next很容易但做prev就要小心需要额外保存当前页第一条记录的游标然后用相反方向排序去查当前页之前的记录集再把结果反转顺序返回。举例列表排序是created_at DESC, id DESC时要查当前页的前一批记录条件是WHERE (created_at, id) (:first_created_at, :first_id) ORDER BY created_at ASC, id ASC LIMIT 20;拿到结果后反转成倒序返回。这个逻辑本身不难但加上筛选条件、授权校验之后错误率会直线上升。我的建议是面向 C 端的列表优先做 next-only加载更多管理端确实需要页码翻页时就老实评估一下是否应该退回基于排序键快照的偏移分页而不是硬撑着在游标上实现完整的双向导航。双向导航和新数据不断插入组合起来逻辑复杂度会失控。7. 可直接抄作业的模板与自检清单最后再聊什么时候别用前面坑讲了这么多最后给一套我在项目里实际用了很久的通用模板外加一份每次写游标分页都会过一遍的自检清单。7.1 一个可落地的通用模板以 Python/MySQL 为例列表按created_at DESC, id DESC排序def list_items(db, cursorNone, page_size20): filters [] params [] if cursor: last_created_at, last_id decode_and_verify_cursor(cursor) # 倒序向下翻页取比当前游标更旧的记录 filters.append((created_at %s OR (created_at %s AND id %s))) params.extend([last_created_at, last_created_at, last_id]) sql f SELECT id, title, created_at FROM articles WHERE 11 {(AND filters[0]) if filters else } ORDER BY created_at DESC, id DESC LIMIT {page_size 1} rows db.query(sql, params) has_more len(rows) page_size rows rows[:page_size] next_cursor None if has_more: last rows[-1] # 这里必须对游标做签名或改为服务端缓存不要裸编码 next_cursor encode_signed_cursor(last[created_at], last[id]) return rows, next_cursor这个模板有三个关键点多查一条判断has_more、用page_size 1而不是精确LIMIT page_size、游标必须签名。多查一条是游标分页判断是否还有下一页的通用做法能避免刚好填满一页时误判没有更多的问题。7.2 上线前过一遍的自检清单检查项怎么查排序字段是否唯一若否是否已经用 (排序字段, id) 组成复合游标排序字段是否会高频更新更新时页面语义是否接受飘移不接受则考虑快照方案索引是否完整匹配 WHERE ORDER BYEXPLAIN 看 key、rows、Extra 是否有 Using filesort游标是否会被用户篡改Base64 裸编码等于裸奔必须签名或服务端缓存筛选条件变化后游标是否重置前端清空游标或后端做条件哈希校验JOIN/GROUP BY 后锚点是否和排序表一致不一致则不能直接用底层表 ID 硬推双向翻页是否必要前端只做加载更多就尽量别做 prev 游标7.3 什么时候别用游标分页最后必须泼一盆冷水游标分页不是哪里都好用。下面这些场景我建议你直接放弃游标需要跳页管理后台跳转到第 58 页游标分页没有页码概念做不了。必须要总数游标分页本身不返回总数要算 count 就得额外扫整个结果集深翻页的性能优势被 count 吃掉一大半。排序依据高频变化比如按热度权重实时排序每次查询排序都不同游标锚点的位置转瞬即逝分页结果必然跳变。数据量小几千行的列表用偏移分页加主键排序毫无压力引入游标只会增加前后端联调复杂度。我在实际项目里的判断标准就一句话排序键是否天然唯一且稳定是否需要深翻页和实时增量。两个都是是就上游标分页否则就该选偏移分页或混合方案别为了炫技给自己挖坑。拿我自己来说现在每做一个列表接口都会先写一套数据随机插入和删除的自动化测试专门校验跨页数据不重不漏。这套测试在开发期就能把排序键设计的漏洞暴露出来远比上线后被运营通知数据翻不出来再手工查表来得舒服。希望这篇复盘能让你在代码评审时及时挡住那些看似完美的游标方案。