【深度長文】MySQL排序内部原理探秘（1）

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"
              }