和面试官聊聊MySQL排序的坑（含order by limit 分页出现重复数据问题、结合索引 order by什么情况不会进行内存排序操作(Using filesort)）

1. 面试官：MYSQL按多个字段排序需要注意哪些问题？

这是准备考我实操经验呀！巧了前一段时间遇到过类似的坑。针对多字段进行组合排序时，首先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;

2. 面试官：有没有遇到过order by limit分页出现数据重复的问题？怎么解决的？

在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的方法，都没有明确定义分页的概念，不同场景对数据分页都没有非常高的准确性要求。

3. 面试官：既然你提到了MySQL5.x低版本不会有排序分页数据重复的问题，聊一下MySQL5.x低版本的排序是怎做的？

有两种方式，分别是：常规排序、优化排序，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时，才能利用优化排序方式，否则只能用常规排序方式。

面试官OS： 可以呀，又答上来了。既然上面你提到了 根据索引排序字段 可以解决order by limit分页出现数据重复的问题。

4.面试官：结合索引 order by什么情况不会进行内存排序操作？

我丢，问这么细；是不是要压我工资啊，嘿嘿，我会。

我之前做过一个试验，从order by的字段在不在where条件中、在不在select中？select内容是否索引覆盖、回表？验证了 结合索引 order by不会进行内存排序操作的情况。

表结构和索引如下：

• 

• user表只有一个非主键索引的索引 – 组合索引：idx_name_age(age, gender)

具体分了四种场景，如下：

1> where和order by的字段都在索引中，但order by的字段不在where条件中，会在内存中进行排序操作。

explain select id, age, gender from user where age > 1 order by gender desc;

从执行计划来看，Extra信息中有Using filesort，也就是进行了内存排序操作；另外type = index表示查询只走了组合索引idx_name_age，即索引覆盖。

2> where和order by的字段都在索引中，并且order by的字段也在where条件中，不会在内存中进行排序操作。

explain select id, age, gender from user where age > 1 and gender = 1 order by gender desc;

从执行计划来看，Extra信息中没有Using filesort，也就是没进行内存排序操作；另外type = index表示查询只走了组合索引idx_name_age，即索引覆盖。

3> 在2> where和order by的字段都在索引中，并且order by的字段也在where条件中 的基础上，order by的字段不出现在select条件中，不会在内存中进行排序操作。

explain select id, age from user where age > 1 and gender = 1 order by gender desc;

结果和2> 的结果一样；从执行计划来看：Extra信息中没有Using filesort，也就是没进行内存排序操作；另外type = index表示查询只走了组合索引idx_name_age，即索引覆盖。

4> 在2> where和order by的字段都在索引中，并且order by的字段也在where条件中 的基础上，select内容没有索引覆盖，要回表的情况下，不会在内存中进行排序操作

explain select * from user where age > 1 and gender = 1 order by gender desc;

从执行计划来看：Extra信息中没有Using filesort，也就是没进行内存排序操作；另外type = ALL，possible_keys = idx_name_age，表示查询走了组合索引idx_name_age，但由于select中的内容没有索引覆盖，所以回表做了全表扫描。

结论

无论select中的内容是什么、进不进行回表，只有当order by 的字段全部出现在 where条件中，并且where条件中的字段全部在一个索引中，才会利用索引的有序性进行排序，而不会在MYSQL中进行内存排序操作。

另外：这个结论不仅对order by有效，对其他需要排序的操作也一样有效；比如group by 、union 、distinct等。

面试官再追问：where条件的字段不在索引中，只有order by的字段在索引中，会用到索引吗？会利用索引排序吗？

从上一个问题我们其实已经分析清楚了。答：where条件的字段不在索引中，只有order by的字段在索引中，无论select的内容，都不会走索引，也不会利用索引的有序性，并且会在内存中进行排序操作。

验证如下：

表结构和索引如下：

• 

• user2表只有一个非主键索引的索引 – 普通索引：idx_type(type)

SQL如下：

explain select id, type from user2 where age > 1 and gender >=0 order by type desc;
explain select * from user2 where age > 1 and gender >=0 order by type desc;

执行计划如下：

两个SQL的执行计划内容是一样的：

• type = ALL, possible_keys = null ；表示查询没有利用到索引。
• Extra 中有 Using filesort；表示没有利用索引的有序性，直接在内容中进行了排序操作。