和面试官聊聊MySQL排序的坑（含：order by limit 分页出现重复数据问题）

这是准备考我实操经验呀！巧了前一段时间遇到过类似的坑。针对多字段进行组合排序时，首先order by 后可加多个字段时，字段之间用英文逗号隔开；其次优先级按照字段先后顺序排序；最后需要针对每个字段分别进行自定义排序；如果不指定，order by 默认按照升序（ASC）排序。

表数据如下：

SQL1：

select * from user ORDER BY age, userName desc limit 8;

这里我们想实现的效果是先按照age倒序排序，再按照userName倒序排序。实际效果是先按照age正序排序，再按照userName倒序排序。

修改SQL如下，即可实现全部倒序的效果：

select * from user ORDER BY age desc, userName desc limit 8;

在MySQL中我们通常使用limit关键字进行分页查询，比如limit(0,10)表示列出第一页的10条数据，limit(10,10)表示列出第二页。但是，当limit遇到order by的时候，可能会出现翻到第二页的时候，竟然又出现了第一页的记录。有时很难复现，

SQL如下：

select *  from user where gender = 1 ORDER BY age desc limit 5;
select * from user where gender = 1 ORDER BY age desc limit 5,5;

解决方案：
1、索引排序字段，即如果在字段上添加索引，就直接按照索引的有序性进行读取并分页，从而可以规避遇到的这个问题。

2、如果排序字段都没有索引，则业务上一般选择在order by的最后加上id asc，也就是在原有排序的基础上排序结果按照主键ID正序排序。因为MYSQL默认按照主键ID正序排序，所以对原结果不会有影响。相应SQL如下：

select *  from user where gender = 1 ORDER BY age desc, id asc limit 5;
select * from user where gender = 1 ORDER BY age desc, id asc limit 5,5;

3、建议使用order by limit进行分页查询时，order by 的最后jiashang id asc。

原因分析：

在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用priority queue做堆排序；以达到 在不能使用索引有序性的时候，如果要排序，并且使用了limit n，可以在排序的过程中在保留n条记录 的效果；进而减少sort buffer内存的使用。也就是说MYSQL5.x低版本没这个问题。

但是因为堆排序是一个不稳定的排序方法，即相同的值可能排序出来的结果和读出来的数据顺序不一致。

从SQL的执行顺序来看，在进行order by 排序时，所有的记录都是以堆排序的方式来排列的，若排序字段不具备索引有序性，则在第二页数据中，MYSQL见到哪条拿哪条。

另外在功能上，排序是数据库提供的基础功能，而分页是衍生出来的应用需求。MYSQL和Oracle提供的limit n和rownum < n的方法，都没有明确定义分页的概念，不同场景对数据分页都没有非常高的准确性要求。

有两种方式，分别是：常规排序、优化排序，MySQL5.6以上采用优先队列排序（堆排序算法）。

1> 从 保存满足where条件的全部记录的临时表中获取全部记录；
2> 将要排序的字段值和row ID 组成键值对（例如：(id, age)），存放到sort buffer中；
3> 如果sort buffer可以存放所有满足条件的(id,age)对，则直接采用快速排序算法进行排序；否则sort buffer满后，进行排序并持久化到临时文件中；
4> 若排序中产生了临时文件，则利用归并排序算法，保证临时文件中记录是有序的；
5> 循环执行上述过程，直到所有满足条件的记录全部参与排序；
6> 扫描排好序的(id,age)键值对，并利用id回表去捞取SELECT需要返回的列（比如(age,name)）；
7> 将获取的结果集返回给用户。

常规排序除了排序本身，还需要额外两次IO：回表、排序存到临时文件。优化排序相对于常规排序，减少了回表的IO损耗。主要区别在于放入sort buffer的键值对不再由要排序的字段和row ID组成(比如：(id,age))；而是查询的字段和要排序的字段（比如：(age,name)）。只是这种方式是有代价的，同样大小的sort buffer，能存放的(age,name)数目要小于(id,age)，如果sort buffer不够大，可能导致原本不需要写临时文件的排序需要写临时文件，造成额外的IO。

不过这种方式是有开启条件的： 参数max_length_for_sort_data，只有当排序元组小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式。