MySQL分页排序中的坑

今天项目提测,被测出了一个奇怪的bug,第一页最后一条数据和第二页的第一条数据重复了,并且神奇的丢失了一个条数据,下面开始来分析一下这个场景。

第一页
第二页

场景为对一些作业进行按时间排序,但是有的作业时间是相同的。

对代码进行查看发现,代码并没有什么问题,只是简单的查询全集并按时间排序分页返回给前端。

所以去看一看mysql对limit的解释,从官方文档中发现了这样一句话

如果多个行在列中具有相同的值ORDER BY,则服务器可以按任意顺序自由返回这些行,并且根据总体执行计划可能会有所不同。换句话说,这些行的排序顺序对于无序列是非确定性的。

根据这句话我们可以发现,在order by的值相同时,返回的顺序不一定一致。

下面我们来思考一下造成这个坑的原因是什么呢?

在MySQL 5.6版本,优化器在遇到order by xxx limit x,x 时,会做一个优化,使用优先队列,来进行排序,这样的好处在于在排序过程中,仅保留需要的n条数据即可。

如果你已经熟悉了各大排序的特点,那么可以知道堆排序是不稳定排序,所以导致了今天的这个问题。

下面分享一下各个排序的稳定性:

  1. 冒泡排序:稳定
  2. 选择排序:不稳定,每次选择最小的与之前交换,可能会将相同元素的顺序打乱
  3. 插入排序:稳定
  4. 快速排序:不稳定,不稳定发生在key与相遇点进行交换时。
  5. 归并排序:稳定
  6. 基数排序:稳定
  7. 希尔排序:不稳定,插入顺序不同,相同元素可能在不同的插入顺序中分别排序
  8. 堆排序:不稳定,可能子节点与父节点进行交换时,将两个相同元素顺序打乱,5(left) 6(root) 5(right) -> 5 5 6,此时right位于5的left

下面说一下问题的解决方法:

  1. order by后加一个唯一字段(id)进行排序
  2. 给order by字段加上索引

世上没有解决不了的bug,也许只是方向不对