0、导读
在MySQL里，什么情况下会发生排序。MySQL内部的排序怎么完成的，怎么合理分配内存，怎么避免发生额外的磁盘I/O，这里都有解，看过来！
本文由沃趣科技数据库工程师罗小波撰写，二次排版时，个别文字老叶略有调增。
友情提醒：建议阅读时间20分钟，请先找好蹲坑。
一、本文想解决什么问题
二、如何识别需要排序
三、如何利用索引优化排序
四、排序的几种模式
4.1、实际trace结果
4.2、排序模式概览
4.2.1、回表排序模式
4.2.2、不回表排序模式
4.2.3、打包数据排序模式
4.2.4、三种模式比较
五、外部排序
5.1、普通外部排序
5.1.1、两路外部排序
5.1.2、多路外部排序
5.2、MySQL外部排序
5.2.1、MySQL外部排序算法
5.2.2、关于sort_merge_passes
六、optimizer trace解读
6.1、是否存在磁盘外部排序
6.2、是否存在优先队列优化排序
七、MySQL其他相关排序参数
7.1、max_sort_length
7.2、innodb_disable_sort_file_cache
7.3、innodb_sort_buffer_size
八、MySQL排序优化总结
九、参考文献
一、主要内容简介
MySQL排序是个老生长谈的话题，这次我们想由浅入深详细说说MySQL的几种排序模式，怎么选择不同排序模式，以及如何优化排序。
同时也希望通过本文能解决大家的几个疑问：
- MySQL什么时候做排序，怎么判断需要进行排序；
- MySQL有几种排序模式，有什么方法让MySQL选择不同的排序模式；
- MySQL排序跟 read_rnd_buffer_size 有啥关系，在哪些情况下增加 read_rnd_buffer_size 能优化排序效率；
- 怎么判断MySQL使用了磁盘排序，怎么避免或者优化磁盘排序；
- 排序时变长字段（varchar）数据在内存是怎么存储的，5.7有哪些改进；
- 加了LIMIT后，排序模式有哪些改进；
- sort_merge_pass到底是什么鬼，该状态值过大说明了什么问题，可以通过什么方法解决；
- 迫不得已要进行排序的话，有什么优化手段让排序更快；
二、如何识别需要排序？
利用EXPLAIN查看执行计划候时，如果在Extra列中显示Using filesort，其实这种情况就说明MySQL需要进行排序。
Using filesort经常出现在order by、group by、distinct、join等情况下。
三、利用索引优化排序
需要排序时，首先想到的一般是：能否利用索引来优化。
InnoDB默认采用的是B+tree索引，B+tree索引本身就是有序的，以下面的查询为例：
select * from film where actor_name='苍老师' order by prod_time;
只需创建多列索引(actor_name, prod_time)，就能利用B+tree的特性来避免额外排序。
如下图所示：
通过B+tree查找到actor_name=’苍老师’的数据后，只需要按序往右继续扫描即可，无需额外排序操作。
下面都是其他可以利用索引优化排序的情形：
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC; SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC; SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
从以上例子里面我们也可以看到，如果要让MySQL使用索引优化排序应该怎么建组合索引。
四、排序模式
4.1 实际trace结果
但是还是有非常多的SQL没法使用索引进行排序，例如
select * from film where Producer like '东京热%' and prod_time>'2015-12-01' order by actor_age;
我们想查询’东京热’出品的，从去年12月1号以来，并且按照演员的年龄排序的电影信息。
（好吧，假设我这里有一个每一位男DBA都想维护的数据库:）
这种情况下，使用索引已经无法避免排序了，那MySQL排序到底会怎么做列。
笼统的来说，它会按照：
- 依据“Producer like ‘东京热%’ and prod_time>’2015-12-01’ ”过滤数据，查找需要的数据；
- 对查找到的数据按照“order by actor_age”进行排序,并按照“select *”将必要的数据按照actor_age依序返回给客户端。
空口无凭，我们可以利用MySQL的optimize trace来查看是否如上所述。
如果通过optimize trace看到更详细的MySQL优化器trace信息，可以查看阿里印风的博客初识5.6的optimizer trace
trace结果如下：
- 依据“Producer like ‘东京热%’ and prod_time>’2015-12-01’ ”过滤数据，查找需要的数据
"attaching_conditions_to_tables": { "original_condition": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`film`", "attached": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))" } ] }
- 对查找到的数据按照“order by actor_age”进行排序,并 按照“select *”将必要的数据按照actor_age依序返回给客户端
"join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`film`", "field": "actor_age" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "rows": 1, "examined_rows": 5, "number_of_tmp_files": 0, "sort_buffer_size": 261872, "sort_mode": "<sort_key, packed_additional_fields>" } } ] }
这里，我们可以明显看到，MySQL在执行这个select的时候执行了针对film表.actor_age字段的asc排序操作。
"filesort_information": [ { "direction": "asc", "table": "`film`", "field": "actor_age" }
