我做分页时用创建时间排序、一页页翻,本地看着没问题,可用户反馈翻页时有的数据重复出现、有的却凭空消失,排查半天才发现我排序用的那个列不唯一、值相同的行之间的顺序压根没保证的深度复盘

这是一次让我对"排序,在'并列'的地方其实是没定准的"有了刻骨认知的事故。我做了个列表分页:按创建时间(create_time)倒序排列,每页 20 条,ORDER BY create_time DESC LIMIT 20 OFFSET ?,一页页往后翻。本地测试时数据不多、翻几页都正常,我就觉得这分页稳了。

可上线后,用户陆续反馈一件诡异的事:翻页的时候,有的数据在第 1 页见过、第 2 页又冒出来一次(重复);而有的数据,明明该在列表里,却怎么翻都找不到(遗漏)。我一开始以为是数据本身有重复、或是缓存问题,反复核对数据都好好的、没有重复记录。直到我盯着出问题的那批数据看,发现它们有个共同点:它们的 create_time 完全一样(同一批被批量导入的、或同一秒创建的数据)。我再深究,才恍然大悟:我用来排序的 create_time 这个列不是唯一的——有很多行的 create_time 完全相同。而数据库在 ORDER BY 一个非唯一列时,对于那些排序值相同的行,它们之间的先后顺序是不保证的(可能随执行计划、数据分布而变)。于是同样一条 ORDER BY create_time 的查询,在翻不同页时,那些 create_time 相同的行的相对顺序可能变了,导致同一行在两页的边界上要么被算了两次(重复)、要么被两页都漏掉(遗漏)。

故障现场:相同 create_time 的行,翻页时顺序漂移

我把这个"翻页重复/遗漏"的机理还原出来,问题一目了然:

-- 我的分页: 只按非唯一列 create_time 排序
SELECT * FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 0;   -- 第1页
SELECT * FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 20;  -- 第2页

-- 问题: 假设有一批行 create_time 完全相同(比如都是 10:00:00):
--   行 A、B、C、D、E ... 它们的 create_time 一模一样
--   数据库对"排序值相同的行", 不保证它们之间的顺序!

-- 第1页查询(OFFSET 0 LIMIT 20)时, 这批同值行恰好排成: ... A B C
-- 第2页查询(OFFSET 20 LIMIT 20)时, 同一批同值行可能排成: ... C D E
--   → C 在第1页末尾出现过, 第2页开头又出现 → 【重复】
--   → 而某行 B 在第1页没排进、第2页也被挤掉 → 【遗漏】

-- 根因: ORDER BY 非唯一列, 同值行的相对顺序"未定义", 两次查询可能不同
--   翻页本质是"在一个稳定有序的序列上切片", 序列不稳定 → 切片就错位

-- 验证: 同一条 SQL 多跑几次, 看相同 create_time 的行顺序是否一致
SELECT id, create_time FROM orders WHERE create_time = '...' ;  -- 顺序可能每次不同

看着"同值行顺序漂移导致翻页错位",我才彻底明白:分页的本质,是"在一个稳定有序的序列上,一段段地切片";它能正确工作的前提,是这个序列每次查询都呈现出完全相同、确定的顺序。可我用一个非唯一的列来排序,对于排序值相同的那些行,数据库不保证它们的相对顺序——于是这个序列在"并列的局部"是不稳定的:这次查 A 在 B 前,下次查可能 B 在 A 前。序列一不稳定,我在它上面切出来的"页"就会在边界处错位:有的行被相邻两页都包含(重复),有的行被相邻两页都跳过(遗漏)。我以为我在一个固定的队列上数着翻页,其实那个队列里有一群"并列、可随意换位"的人,每次查询他们都重新站了一次队。

第一件事:搞懂排序的稳定性——非唯一排序键,并列项顺序不保证

冷静下来,我去把"SQL 排序的确定性与分页"这一课认真补了,才明白这个"翻页错位"的根源:

【为什么"非唯一列排序 + 分页"会重复/遗漏】

排序的一个关键事实:
  - ORDER BY 只保证"按排序键有序";
  - 对于【排序键值相同】的行(并列), 它们之间的相对顺序是【不保证的】
  - 这个顺序可能随执行计划、索引、数据分布、并发写入而变化, 同一 SQL 两次可能不同

分页的前提:
  - 分页 = 在"一个全局确定、稳定有序"的结果序列上, 按 OFFSET/LIMIT 切片
  - 它要求: 不管查第几页, 那个底层序列的顺序【完全一致、可重现】
  - 否则: 第N页和第N+1页基于的序列顺序不同, 边界处就会重复或遗漏

两者一冲突就出事:
  - 排序键不唯一 → 并列行顺序不定 → 序列在并列处不稳定
  - → 翻页切片在并列行的边界错位 → 重复 + 遗漏
  - 数据量越大、相同排序值的行越多、并发写入越频繁, 越容易暴露

核心解法: 让排序结果【全局唯一确定】——加一个"唯一的 tiebreaker"
  - ORDER BY create_time DESC, id DESC
    create_time 相同时, 再按唯一的 id 排, 顺序就完全确定了
  - 关键: ORDER BY 的列组合, 最终要能【唯一确定每一行的位置】
    (通常末尾加上主键 id 这种唯一列即可)

延伸: 深度分页用"游标分页(keyset)"更稳更快:
  WHERE (create_time, id) < (上页最后一行的 create_time, id)
  ORDER BY create_time DESC, id DESC LIMIT 20
  —— 同样依赖"有唯一 tiebreaker 的确定排序"

这一下点醒了我:我把 ORDER BY 理解成了"给出一个完全确定的顺序",可它只保证"按排序键有序";对于排序键相同的并列行,它不承诺任何确定的先后。而分页这个操作,恰恰依赖一个"每次都完全一致"的底层序列——我却给了它一个"在并列处会漂移"的序列,于是翻页在那些并列行的边界上必然错位。不是分页逻辑写错了,是我给分页的那个"排序",在并列的地方根本没定准——而我误以为它处处都定准了。

第二件事:正解——给排序加唯一 tiebreaker,让序列全局确定

找到根因,正解就清晰了:给 ORDER BY 加上一个唯一的"决胜列(tiebreaker)"(通常是主键 id),让排序结果全局唯一确定——排序值相同时再按 id 排,顺序就不会再漂移;深度分页进一步用"游标分页(keyset)"既稳又快。让分页所依赖的那个序列,每次查询都完全一致、可重现。

-- 错误: 只按非唯一列排序, 并列行顺序不定, 翻页重复/遗漏
SELECT * FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 20;   -- ✗

-- 正解1: 加唯一 tiebreaker(主键 id), 排序结果全局确定
SELECT * FROM orders
ORDER BY create_time DESC, id DESC          -- create_time 相同时按唯一 id 定序
LIMIT 20 OFFSET 20;                          -- ✓ 序列稳定, 翻页不再错位

-- 正解2: 深度分页用"游标分页(keyset)"——既稳定又避免大 OFFSET 慢
-- 翻下一页时, 带上"上一页最后一行"的排序键作为游标:
SELECT * FROM orders
WHERE (create_time, id) < (:lastCreateTime, :lastId)   -- 复合游标
ORDER BY create_time DESC, id DESC
LIMIT 20;                                    -- ✓ 稳定、且不用扫过 OFFSET 那么多行

-- 原则: ORDER BY 的列组合, 最终必须能【唯一确定每一行的位置】
--   非唯一列排序时, 末尾补上主键等唯一列作为决胜键
-- 注意: 排序列要有合适的索引(如 (create_time, id) 复合索引)以保证性能

这套做法的精髓,是把分页所依赖的那个序列,从"在并列处会漂移的不确定序列",变成"处处唯一确定、每次查询都完全一致的稳定序列":加一个唯一的决胜列(id),让任意两行都有明确、不变的先后关系,并列的歧义就被彻底消除了;游标分页则在此基础上,用"上页最后一行的排序键"作锚点继续往下取,既稳定又省掉大 OFFSET 的扫描开销。不是不能用 create_time 排序,而是别让排序停在"并列就随意"——给它补一个唯一的决胜键,让顺序彻底定下来。

【分页/排序, 几条原则】

1. ORDER BY 只保证按排序键有序; 排序键相同的并列行, 相对顺序不保证

2. 分页依赖"全局确定、每次一致的序列"; 排序不确定 → 翻页重复/遗漏

3. 必加唯一 tiebreaker: ORDER BY 非唯一列时, 末尾补主键 id 等唯一列

4. 判定: ORDER BY 的列组合, 要能唯一确定每一行的位置(并列必须被打破)

5. 深度分页优先游标分页(keyset): WHERE (排序键) < 上页末值, 既稳又快

6. 排序/游标列要建合适索引(如复合索引), 保证确定性的同时不牺牲性能

第三件事:其他"依赖了一个其实不确定的顺序/结果"的同类坑

顺着"别依赖未被保证的确定顺序"这条线,我把同类的坑都梳理了一遍,它们都源于"把一个'并列时不确定'的顺序,当成了完全确定的":

第一个,不带 ORDER BY 就指望返回有序。SQL 不带 ORDER BY 时返回顺序完全不保证(本地碰巧按主键),换执行计划就乱。要顺序必须显式 ORDER BY。

第二个,排序算法的稳定性假设。有些排序不稳定(相等元素相对顺序会变),若代码依赖"相等元素保持原序",换个排序实现就出 bug。要稳定排序或加 tiebreaker。

第三个,分布式/分片下的全局顺序。数据分散在多个分片,各分片内有序不代表全局有序;不做归并排序、不加全局确定的键,跨分片翻页一样会错位。

第四个,并发写入下分页的"幻读"式漂移。翻页过程中有新数据插入/删除,即使排序确定,页与页之间的内容也会整体偏移。游标分页对此更稳健。

第四件事:几种排序/分页方式的确定性,一张表对照

我把几种排序/分页写法在"顺序是否全局确定、翻页是否会错位"上的差别整理成一张表,这是我现在做分页时的依据:

这张表让我看清:分页能不能正确翻,取决于排序结果是不是全局唯一确定;只要排序键能唯一确定每一行的位置(非唯一列后补主键),翻页就稳;否则在并列处必然错位。不带 ORDER BY 和只按非唯一列排序,都给了分页一个"会漂移的序列";加上唯一 tiebreaker,序列才真正定下来。

第五件事:我对"按某列排序分页"的几个想当然

这次事故,本质是我把"ORDER BY 给出的顺序"当成了"处处完全确定"。把这些想当然列出来,每一条都值得警惕:

第六件事:做分页、依赖排序结果时,我现在的自检习惯

现在每当我做分页、或依赖一个排序结果,或排查"翻页重复/遗漏",我都会先按这张图问自己:

这张图的精髓,是"分页前先确认 ORDER BY 能不能唯一确定每一行的位置;不能就补主键当 tiebreaker,深度分页用游标"。设计就排序键末尾补唯一列、深度分页用 keyset、排序列建复合索引、排查就看翻页重复/遗漏是不是排序键非唯一导致并列行顺序漂移。这套习惯,让我从"排了序分页就没事"变成了"先确认排序是不是全局唯一确定"——核心始终是:ORDER BY 只保证按排序键有序、对排序键值相同的并列行不保证它们的相对顺序(可能随执行计划/数据分布/并发而变、同一 SQL 两次不同);而分页本质是在一个稳定有序的序列上按 OFFSET/LIMIT 切片、要求底层序列每次查询都完全一致;用非唯一列排序使序列在并列处不稳定、翻页就在并列边界重复或遗漏;正解是给 ORDER BY 加唯一 tiebreaker(末尾补主键 id)让排序全局唯一确定、深度分页用游标分页 keyset(WHERE 复合排序键 < 上页末值)既稳又快、并为排序列建合适索引。

我立下的几条规矩

这场"非唯一列排序分页导致翻页重复遗漏"的事故,换来了我做分页时,刻进骨子里的几条铁律:

1. ORDER BY 只保证按排序键有序;排序键值相同的并列行,它们之间的相对顺序不保证。
2. 分页依赖一个"全局确定、每次查询都完全一致"的序列;排序不确定就会翻页重复/遗漏。
3. 用非唯一列排序分页,必须末尾补一个唯一列(主键 id)作 tiebreaker,让顺序全局确定。
4. 判定标准:ORDER BY 的列组合,要能唯一确定每一行的位置,并列的歧义必须被打破。
5. 深度分页优先用游标分页(keyset):WHERE 复合排序键 < 上页最后一行的值,既稳定又避免大 OFFSET 慢。
6. 排序/游标涉及的列要建合适的(复合)索引,在保证确定性的同时不牺牲性能。
7. 推而广之:不带 ORDER BY、不稳定排序、分布式全局顺序,都别当成确定顺序来依赖。

附:我现在做分页固定遵循的两套写法

这是我现在做任何分页时固定遵循的两套写法——把这次踩坑的教训(排序必须全局确定、深度分页用游标)固化成了可直接套用的模板,让翻页重复/遗漏的隐患再没机会混进来:

-- ============ 写法一: 普通分页(浅层, OFFSET/LIMIT)============
-- 关键: ORDER BY 末尾必须补一个唯一列(主键 id)作 tiebreaker
SELECT id, title, create_time
FROM orders
WHERE status = 'paid'
ORDER BY create_time DESC, id DESC          -- ← create_time 并列时按 id 定序
LIMIT 20 OFFSET 40;
-- 建议索引: (status, create_time, id) 复合索引, 排序与过滤都走索引

-- ============ 写法二: 深度分页 / 无限滚动(游标 keyset)============
-- 翻第一页:
SELECT id, title, create_time
FROM orders
WHERE status = 'paid'
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 记下本页最后一行的 (create_time, id) 作为游标 lastCt, lastId

-- 翻下一页: 用复合游标继续往下取(不用 OFFSET, 避免扫过大量行)
SELECT id, title, create_time
FROM orders
WHERE status = 'paid'
  AND (create_time, id) < (:lastCt, :lastId)   -- ← 复合条件, 精确接在上页之后
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 同样依赖 (status, create_time, id) 复合索引, 既稳定又快

这两套写法把我这次的教训钉死在了模板里:无论哪种分页,ORDER BY 末尾都必须带上唯一的主键 id 作决胜键,保证排序结果全局唯一确定;浅层分页用 OFFSET/LIMIT,深度分页则用游标(复合条件 (create_time, id) < (上页末值))避免大 OFFSET 的扫描开销。有了这两套模板,我再也不用每次都重新斟酌"这个排序会不会并列、翻页会不会错位"——决胜键和游标条件已经把"序列必须全局确定"这个前提焊死在了 SQL 里。把"分页必须建立在一个处处确定的序列上"这个容易被忽略的前提,变成两套拿来就用、不会出错的固定写法,这是我对这次事故最实在的交代。

写在最后

回头看,这场由"非唯一列排序"引发的"翻页重复遗漏"事故,真正教给我的,远不止"ORDER BY 后面补个 id"这一个技巧。它让我对"我们常常以为'排好序了'就意味着'每个元素都有了一个确定的位置'; 可'排序'只在'有先后之分'的地方定了序, 在那些'不分先后、并列平局'的地方, 它其实什么都没说定——而我们却默认那里也是定准的, 并把后续的逻辑建立在这个并不存在的'完全确定'之上",有了一次刻骨的体会。我栽跟头,是因为我把'部分确定(在有区分的地方有序)'误当成了'完全确定(每个位置都定死)'——我看到 ORDER BY create_time, 就以为"每一行的位置都被这个排序唯一钉死了";我没意识到, 排序只在"create_time 不同"的行之间定了先后; 对那些"create_time 相同"的并列行, 它根本没规定谁先谁后——那是一片"顺序未定义"的空白;而我后续的分页逻辑, 偏偏要求"每个位置都完全确定"; 我把它建在了那片有空白的地基上, 翻页一到空白处(并列行)就塌了。这让我领悟到一个关于"确定性的范围与盲区"的深刻认知:一个"排序/规则/约束"所提供的确定性, 往往是有范围的——它在某些维度/某些情况下把事情定死了, 但在另一些它"没有区分能力"的地方(并列、平局、相等), 留下了一片"未定义"的空白;危险在于, 我们容易把"在它能区分的地方很确定", 笼统地泛化成"它处处都确定", 从而对那片空白毫无防备, 还在它之上盖起依赖"完全确定"的逻辑;所以使用任何一种"定序/定位/区分"的机制时, 都要追问: 它真的能唯一区分所有情况吗?有没有它"分不出、定不准"的并列地带?如果有, 而我又需要完全确定, 就必须自己补上一个能打破并列的"决胜规则"。这给了我一种看待"一切'依赖某种排序/排名/定位的确定性'之事"时的清醒:每当我依赖一个"排序、排名、唯一定位"的结果时, 要追问"这个排序/规则, 能唯一地确定每一个元素的位置吗?会不会有'并列、相等、分不出高下'的情况?在那些地方, 顺序其实是未定义的吗?我的后续逻辑能容忍这片不确定吗?"——对那些"会出现并列、又要求完全确定"的场景, 主动补上一个唯一的'决胜键', 把并列的歧义彻底消除, 而不是默认排序处处都定准了;"看清确定性的边界、为并列地带补上唯一决胜键", 是写对分页、也是用对一切'排序与定位'的关键。认清 ORDER BY 不保证并列行顺序、分页要全局确定的序列、非唯一列排序要补主键 tiebreaker——这,是我用一次翻页重复遗漏的事故,换来的、关于数据库、也关于如何看待确定性边界的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写下 ORDER BY 某个时间列 做分页时,先想想"这列有重复值吗?并列的行顺序定了吗?要不要补个 id 把顺序钉死?",并顺手加上唯一的决胜键,那我对着那些"翻页时重复又遗漏"的数据折腾的大半天,就值了。

—— 别看了 · 2026