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排序操作。
</div>
