mysql排序不稳定问题

现象

当order by中的列具有相同的值时，每次查询到的顺序存在不一致的现象，如下：


类似的，在分页排序中，有的数据会在页面连续出现多次，有的数据则在数据中一次也不能出现。

为什么会出现此现象

PageHelper首先将前端传递的参数保存到page这个对象中，接着将page的副本存放入ThreadLoacl中，这样可以保证分页的时候，参数互不影响，接着利用了mybatis提供的拦截器，取得ThreadLocal的值，重新拼装分页SQL，完成分页。
其中mysql通过采用limit分页。

public String getPageSql(String sql, Page page, RowBounds rowBounds, CacheKey pageKey) {
    StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
    sqlBuilder.append(sql);
    sqlBuilder.append(" limit ?,?");
    return sqlBuilder.toString();
}

通过explain，查看sql的执行计划，在extra中显示如图

Using temporary表示由于排序没有走索引，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表。
Using filesort表示没有使用索引排序，它并不意味着在硬盘上排序，filesort与文件无关。消除Using filesort的方法就是让查询sql的排序走索引。

外部排序模式

mysql使用的排序模式有四种：

• 第一种，回表排序模式
  对需要排序的记录生成<sort_key,rowid>的元数据进行排序，该元数据仅包含排序字段和rowid。排序完成后只有按字段排序的rowid，因此还需要通过rowid进行回表操作获取所需要的列的值，可能会导致大量的随机IO读消耗；
• 第二种，不回表排序模式
  不回表排序是回表排序模式的改进，对需要排序的记录生成<sort_key,additional_fields>的元数据，该元数据包含排序字段和需要返回的所有列。排序完后不需要回表，但是元数据要比第一种方法长得多，采用的是用空间换时间的方法，需要更多的空间用于排序。
  但是由于sort buffer就那么大，如果用户要查询的数据非常大的话，很多时间浪费在多次磁盘外部排序，导致更多的IO操作，效率可能还不如第一种方式。
  所以，MySQL给用户提供了一个max_length_for_sort_data的参数。当“排序的键值对大小” > max_length_for_sort_data时，MySQL认为磁盘外部排序的IO效率不如回表的效率，会选择第一种排序模式；反之，会选择第二种不回表的模式。
• 第三种，打包数据排序模式
  与第二种区别，仅仅在于将char和varchar字段存到sort buffer中时，更加紧缩。
  在之前的两种模式中，存储了“yes”3个字符的定义为VARCHAR(255)的列会在内存中申请255个字符内存空间，但是5.7.3改进后，只需要存储2个字节的字段长度和3个字符内存空间（用于保存”yes”这三个字符）就够了，内存空间整整压缩了50多倍,可以让更多的键值对保存在sort buffer中。
• 第四种，优先队列排序
  在前三种方式中，都需要将满足条件的记录先排序再返回结果。而在5.6版本中针对Order by limit M，N语句，在空间层面做了优化，使用一种新的排序方式–优先队列(priority queue) 。
  优先队列排序采用堆排序实现，堆排序算法特征正好可以解决limit M，N 这类排序的问题。虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。
  其中对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。

排序算法

前三种排序方式使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块，即使用到了快速排序及归并排序。而优先队列则使用堆排序。

其中快速排序，堆排序是不稳定的。
排序算法的稳定性指的是能保证排序前2个相等的数其在序列的前后位置顺序和排序后它们两个的前后位置顺序相同。即如果Ai = Aj，Ai原来在位置前，排序后Ai还是要在Aj位置前。

快速排序的不稳定性发生在key与相遇点进行交换时。
堆排序的不稳定性发生在子节点与父节点进行交换时，将两个相同元素顺序打乱，5(left) 6(root) 5(right) -> 5 5 6，此时5(right)位于5(left)的左侧。

如何解决此问题

• 使用索引， 因为索引本身也是有序的，如果在需要排序的字段上面建立了合适的索引，那么就可以跳过排序的过程，提高SQL的查询速度。（强制走索引：force index）
  
  B+ tree其中一个特点，即所有叶子节点增加了一个链指针，以此保证了索引的有序性。

• 如果实在不能走索引，可以加一个唯一字段(例如id)进行排序

题外话

• pagehelper进入doProcessPage方法后，通过反射机制，会先查询出数据总数量，然后再进行分页SQL的拼装，MappedStatement的getBoundSql。
  查询出的总数量是为了返回分页信息用，如总页数等。

• 当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢。此事可以将上一页的最大值当成参数作为查询条件或者采用子查询优化法。

• 查询缓存，可以看作SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html