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的几种排序模式，怎么选择不同排序模式，以及如何优化排序。

同时也希望通过本文能解决大家的几个疑问：

1. MySQL什么时候做排序，怎么判断需要进行排序；
   
2. MySQL有几种排序模式，有什么方法让MySQL选择不同的排序模式；
   
3. MySQL排序跟 read_rnd_buffer_size 有啥关系，在哪些情况下增加 read_rnd_buffer_size 能优化排序效率；
   
4. 怎么判断MySQL使用了磁盘排序，怎么避免或者优化磁盘排序；
   
5. 排序时变长字段（varchar）数据在内存是怎么存储的，5.7有哪些改进；
   
6. 加了LIMIT后，排序模式有哪些改进；
   
7. sort_merge_pass到底是什么鬼，该状态值过大说明了什么问题，可以通过什么方法解决；
   
8. 迫不得已要进行排序的话，有什么优化手段让排序更快；
   

二、如何识别需要排序？

利用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排序到底会怎么做列。
笼统的来说，它会按照：

1. 依据“Producer like ‘东京热%’  and prod_time>’2015-12-01’  ”过滤数据，查找需要的数据；
   
2. 对查找到的数据按照“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"
              }