前言有个 bug 特别诡异一条ORDER BY ... LIMIT的分页 SQL测试环境好好的上线后偶尔出现第 2 页的数据第 1 页也出现过或者翻页时有数据莫名跳过。更让人抓狂的是把LIMIT去掉ORDER BY的结果看着又是对的。于是很多人得出结论“是LIMIT把顺序搞乱了。”这个锅LIMIT不背。真正的原因是你的排序字段有重复值而重复值之间的顺序MySQL 从不保证。LIMIT只是把这个一直存在的隐患暴露了出来。这篇文章讲清楚为什么排序结果会不稳定LIMIT在其中扮演了什么角色以及怎么写才不会翻车。环境说明本文基于 MySQL 8.0存储引擎 InnoDB。继续复用前几篇的orders表100 万行。一、先复现同一条 SQL两次结果不一样orders表的status字段只有 0/1/2/3 四种值大量重复。我们按status排序分页-- 第 1 页SELECTid,statusFROMordersORDERBYstatusLIMIT0,5;---------------- | id | status | ---------------- | 337012 | 0 | | 15 | 0 | | 998233 | 0 | | 41 | 0 | | 672319 | 0 | ----------------看起来没问题status都是 0有序。但问题在于这 5 行是从几十万个status 0的行里随便挑出来的 5 个。它们的id毫无规律。换个时间再执行同样的 SQL或者数据发生增删后返回的可能就是另外 5 行。更麻烦的是分页-- 第 2 页SELECTid,statusFROMordersORDERBYstatusLIMIT5,5;如果第 1 页和第 2 页两次查询之间MySQL 选择的排序方式不同就可能出现第 1 页的某行第 2 页又冒出来或者有些行被跳过。这就是分页错乱的现场。二、根因ORDER BY只保证排序字段有序问题的核心是一句话ORDER BY status只保证status这一列有序对于status相同的行它们之间谁前谁后MySQL 不做任何承诺。status 0的行有几十万条ORDER BY status只要求这几十万条排在status 1的前面即可——至于这几十万条内部怎么排SQL 标准和 MySQL 都没有规定。这在数据库里叫不稳定排序unstable sort相等的元素排序后的相对顺序不确定。这不是 MySQL 的bug而是官方明确定义的行为。MySQL 官方文档在 LIMIT 优化那一节里写得清清楚楚If multiple rows have identical values in theORDER BYcolumns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.翻译过来就是如果多行在ORDER BY的列上值相同服务器可以按任意顺序返回这些行而且会因整体执行计划的不同而不同。换句话说这些行相对于未参与排序的列来说顺序是不确定的。官方都把话说到这份上了——所以别指望相等行的顺序会稳定它从设计上就不稳定。那这个不确定的顺序实际由什么决定取决于 MySQL 当时选择的执行方式如果走了某个索引顺序可能跟着索引走如果做全表扫描 filesort顺序可能跟数据的物理存储、扫描顺序有关数据量、LIMIT大小不同优化器可能选不同的排序算法顺序又会变这些因素一变相等行的相对顺序就变了。所以不是结果乱了而是它本来就没被定义过。三、LIMIT为什么会放大这个问题既然顺序不确定为什么不加LIMIT时看着又正常LIMIT到底做了什么关键在于 MySQL 对ORDER BY ... LIMIT有一个特殊优化优先队列排序priority queue基于堆。不带LIMIT的ORDER BY要把所有满足条件的行全部排好序再返回。虽然相等行的顺序仍不保证但同一份数据、同样的执行方式下多跑几次看起来往往是稳定的不容易察觉问题。带LIMIT n的ORDER BYMySQL 发现我只要前 n 行何必全排于是可能改用堆排序只维护 Top n。这条优化路径下相等元素被取到堆里的顺序和全量排序很可能不一样。结果就是加不加LIMIT、LIMIT取多少可能触发不同的排序算法导致相等行的顺序在不同查询间不一致。这正是翻页时数据重复或跳过的直接原因——第 1 页和第 2 页恰好走了会产生不同相对顺序的路径。所以LIMIT不是元凶它只是触发了不同的排序策略把相等行顺序未定义这个一直存在的隐患引爆了。小结根因是排序字段有重复 顺序未定义LIMIT是放大器。两者叠加才有了诡异的分页错乱。四、正解给排序加一个唯一的兜底字段解决办法出奇地简单让ORDER BY的整体是唯一确定的就不会有相等行。做法是在排序末尾加一个唯一字段通常是主键id作为最后的 tie-breaker打破平局-- 有坑status 有大量重复相等行顺序不确定SELECTid,statusFROMordersORDERBYstatusLIMIT5,5;-- 正解加主键 id 兜底排序结果全局唯一确定SELECTid,statusFROMordersORDERBYstatus,idLIMIT5,5;加上, id之后status相同的行再按id排——而id是主键、绝对唯一所以任意两行都有明确的先后排序结果全局唯一。无论查多少次、怎么翻页、数据怎么变同样的偏移永远返回同样的行。这个原则可以推广只要用了ORDER BY ... LIMIT做分页就要保证ORDER BY的字段组合能唯一确定每一行的位置。如果业务排序字段本身可能重复时间、金额、状态、排序权重……就在最后补一个唯一字段。配合索引更好。如果经常按created_at倒序翻页可以建(created_at, id)联合索引ORDER BY created_at DESC, id DESC既稳定又能走索引避免 filesort这一点在《深分页优化》里详细讲过。五、常见误区与面试高频问答Q不加LIMIT的ORDER BY就一定稳定吗也不是。相等行的顺序同样没有保证只是不带LIMIT时不容易触发不同的排序算法表现得看起来稳定。别依赖这种表象只要排序字段可能重复就该加唯一兜底字段。Q加了id兜底性能会变差吗几乎不会反而可能更好。如果有(排序字段, id)的联合索引排序能直接走索引、免掉 filesort。即便没有多比较一个主键的成本也微乎其微远比分页错乱的 bug 划算。Q主键是 UUID无序也能当兜底字段吗能。兜底字段要的是唯一性不是有序性——只要能唯一确定两行的先后即可。UUID 唯一所以能保证排序稳定。只是无序主键在其他方面如页分裂有别的问题那是另一个话题。Q为什么测试环境不出问题上线才出测试环境数据量小、并发低优化器往往稳定地选同一条执行路径相等行顺序碰巧一致。上线后数据量、并发、LIMIT大小都变了优化器可能切换排序策略隐患才暴露。这类 bug 的典型特征就是本地复现不了。总结“加了LIMIT顺序还乱”真相是ORDER BY 字段只保证该字段有序对于该字段值相等的行相互顺序从不保证不稳定排序。LIMIT会触发优先队列/堆排序等不同的排序策略让相等行的顺序在不同查询间不一致从而放大这个隐患表现为分页重复或跳过。根因不是LIMIT是排序字段有重复 顺序未定义。正解一句话ORDER BY分页时末尾补一个唯一字段如主键id兜底让排序结果全局唯一确定翻页永不错乱。一句话记忆排序字段可能重复就一定要加主键兜底——ORDER BY status有坑ORDER BY status, id才踏实。