沃趣科技  羅小波

---

一、我們要解決什麼問題
二、排序，排序，排序
三、索引優化排序
四、排序模式
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.2MySQL外部排序
5.2.1MySQL外部排序演算法
5.2.2sort_merge_passes
六、trace 結果解釋

6.1     是否存在磁碟外部排序

6.2     是否存在優先佇列優化排序

七、MySQL其他相關排序引數
7.1max_sort_length
7.2innodb_disable_sort_file_cache
7.3innodb_sort_buffer_size
八、MySQL排序優化總結
九、參考文獻

警示：不好意思，我們太想把排序的前前後後說清楚了，導致這是一篇長文！

一、我們要解決什麼問題

MySQL排序其實是一個老生長談的問題了，但是我們這次想由淺入深詳細的說說MySQL排序模式，怎麼影響MySQL選擇不同的排序模式和怎麼優化排序。

同時也希望通過這篇文章解決大家的以下疑問：

1. MySQL在哪些地方會使用排序，怎麼判斷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. 最後MySQL使用到了排序的話，依次可以通過什麼辦法分析和優化讓排序更快

二、排序，排序，排序

我們通過explain檢視MySQL執行計劃的時候，經常會看到在Extra列中顯示Using filesort。
其實這種情況就說明MySQL就使用了排序。
Using filesort經常出現在order by、group by、distinct、join等情況下。

三、索引優化排序

看到排序，我們的DBA首先想到的肯定是，是否可以利用索引來優化。
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"
              }

4.2 排序模式概覽

我們這裡主要關心MySQL到底是怎麼排序的，採用了什麼排序演算法。

請關注這裡

"sort_mode": "<sort_key, packed_additional_fields>"

MySQL的sort_mode有三種。
摘錄5.7.13中sql/filesort.cc原始碼如下：

  Opt_trace_object(trace, "filesort_summary")
    .add("rows", num_rows)
    .add("examined_rows", param.examined_rows)
    .add("number_of_tmp_files", num_chunks)
    .add("sort_buffer_size", table_sort.sort_buffer_size())
    .add_alnum("sort_mode",
               param.using_packed_addons() ?
               "<sort_key, packed_additional_fields>" :
               param.using_addon_fields() ?
               "<sort_key, additional_fields>" : "<sort_key, rowid>");

“< sort_key, rowid >”和“< sort_key, additional_fields >” 看過其他介紹介紹MySQL排序文章的同學應該比較清楚，“< sort_key, packed_additional_fields >” 相對較新。

• < sort_key, rowid >對應的是MySQL 4.1之前的“原始排序模式”

• < sort_key, additional_fields >對應的是MySQL 4.1以後引入的“修改後排序模式”

• < sort_key, packed_additional_fields >是MySQL 5.7.3以後引入的進一步優化的"打包資料排序模式”

下面我們來一一介紹這三個模式：

4.2.1  回表排序模式

• 根據索引或者全表掃描，按照過濾條件獲得需要查詢的排序欄位值和row ID；

• 將要排序欄位值和row ID組成鍵值對，存入sort buffer中；

• 如果sort buffer記憶體大於這些鍵值對的記憶體，就不需要建立臨時檔案了。否則，每次sort buffer填滿以後，需要直接用qsort(快速排序演算法)在記憶體中排好序，並寫到臨時檔案中；

• 重複上述步驟，直到所有的行資料都正常讀取了完成；

• 用到了臨時檔案的，需要利用磁碟外部排序，將row id寫入到結果檔案中；

• 根據結果檔案中的row ID按序讀取使用者需要返回的資料。由於row ID不是順序的，導致回表時是隨機IO，為了進一步優化效能（變成順序IO），MySQL會讀一批row ID，並將讀到的資料按排序欄位順序插入快取區中(記憶體大小read_rnd_buffer_size)。

4.2.2 不回表排序模式

• 根據索引或者全表掃描，按照過濾條件獲得需要查詢的資料；

• 將要排序的列值和 使用者需要返回的欄位 組成鍵值對，存入sort buffer中；

• 如果sort buffer記憶體大於這些鍵值對的記憶體，就不需要建立臨時檔案了。否則，每次sort buffer填滿以後，需要直接用qsort(快速排序演算法)在記憶體中排好序，並寫到臨時檔案中；

• 重複上述步驟，直到所有的行資料都正常讀取了完成；

• 用到了臨時檔案的，需要利用磁碟外部排序，將排序後的資料寫入到結果檔案中；

• 直接從結果檔案中返回使用者需要的欄位資料，而不是根據row ID再次回表查詢。

4.2.3打包資料排序模式

第三種排序模式的改進僅僅在於將char和varchar欄位存到sort buffer中時，更加緊縮。

在之前的兩種模式中，儲存了”yes”3個字元的定義為VARCHAR(255)的列會在記憶體中申請255個字元記憶體空間，但是5.7.3改進後，只需要儲存2個位元組的欄位長度和3個字元記憶體空間（用於儲存”yes”這三個字元）就夠了，記憶體空間整整壓縮了50多倍,可以讓更多的鍵值對儲存在sort buffer中。

4.2.4三種模式比較

第二種模式是第一種模式的改進，避免了二次回表，採用的是用空間換時間的方法。

但是由於sort buffer就那麼大，如果使用者要查詢的資料非常大的話，很多時間浪費在多次磁碟外部排序，導致更多的IO操作，效率可能還不如第一種方式。

所以，MySQL給使用者提供了一個max_length_for_sort_data的引數。當“排序的鍵值對大小” > max_length_for_sort_data時，MySQL認為磁碟外部排序的IO效率不如回表的效率，會選擇第一種排序模式；反之，會選擇第二種不回表的模式。

第三種模式主要是解決變長字元資料儲存空間浪費的問題，對於實際資料不多，欄位定義較長的改進效果會更加明顯。

能看到這裡的同學絕逼是真愛，但是還沒完，後面的東西可能會更燒腦…
      建議大家喝杯咖啡再繼續看。

很多文章寫到這裡可能就差不多了，但是大家忘記關注一個問題了：“如果排序的資料不能完全放在sort buffer記憶體裡面，是怎麼通過外部排序完成整個排序過程的呢？”
要解決這個問題，我們首先需要簡單檢視一下外部排序到底是怎麼做的。

五、外部排序

5.1 普通外部排序

5.1.1 兩路外部排序

我們先來看一下最簡單，最普遍的兩路外部排序演算法。

假設記憶體只有100M，但是排序的資料有900M，那麼對應的外部排序演算法如下：

1. 從要排序的900M資料中讀取100MB資料到記憶體中，並按照傳統的內部排序演算法（快速排序）進行排序；

2. 將排序好的資料寫入磁碟；

3. 重複1，2兩步，直到每個100MB chunk大小排序好的資料都被寫入磁碟；

4. 每次讀取排序好的chunk中前10MB（= 100MB / (9 chunks + 1)）資料，一共9個chunk需要90MB，剩下的10MB作為輸出快取；

5. 對這些資料進行一個“9路歸併”，並將結果寫入輸出快取。如果輸出快取滿了，則直接寫入最終排序結果檔案並清空輸出快取；如果9個10MB的輸入快取空了，從對應的檔案再讀10MB的資料，直到讀完整個檔案。最終輸出的排序結果檔案就是900MB排好序的資料了。

5.1.2 多路外部排序

上述排序演算法是一個兩路排序演算法（先排序，後歸併）。但是這種演算法有一個問題，假設要排序的資料是50GB而記憶體只有100MB，那麼每次從500個排序好的分片中取200KB（100MB / 501 約等於200KB）就是很多個隨機IO。效率非常慢，對應可以這樣來改進：

1. 從要排序的50GB資料中讀取100MB資料到記憶體中，並按照傳統的內部排序演算法（快速排序）進行排序；

2. 將排序好的資料寫入磁碟；

3. 重複1，2兩步，直到每個100MB chunk大小排序好的資料都被寫入磁碟；

4. 每次取25個分片進行歸併排序，這樣就形成了20個（500/25=20）更大的2.5GB有序的檔案；

5. 對這20個2.5GB的有序檔案進行歸併排序，形成最終排序結果檔案。

對應的資料量更大的情況可以進行更多次歸併。

1. 

5.2 MySQL外部排序

5.2.1 MySQL外部排序演算法

那MySQL使用的外部排序是怎麼樣的列，我們以回表排序模式為例：

1. 根據索引或者全表掃描，按照過濾條件獲得需要查詢的資料；

2. 將要排序的列值和row ID組成鍵值對，存入sort buffer中；

3. 如果sort buffer記憶體大於這些鍵值對的記憶體，就不需要建立臨時檔案了。否則，每次sort buffer填滿以後，需要直接用qsort(快速排序模式)在記憶體中排好序，作為一個block寫到臨時檔案中。跟正常的外部排序寫到多個檔案中不一樣，MySQL只會寫到一個臨時檔案中，並通過儲存檔案偏移量的方式來模擬多個檔案歸併排序；

4. 重複上述步驟，直到所有的行資料都正常讀取了完成；

5. 每MERGEBUFF (7) 個block抽取一批資料進行排序，歸併排序到另外一個臨時檔案中，直到所有的資料都排序好到新的臨時檔案中；

6. 重複以上歸併排序過程，直到剩下不到MERGEBUFF2 (15)個block。
   通俗一點解釋：
   第一次迴圈中，一個block對應一個sort buffer（大小為sort_buffer_size）排序好的資料；每7個做一個歸併。
   第二次迴圈中，一個block對應MERGEBUFF (7) 個sort buffer的資料，每7個做一個歸併。
   …
   直到所有的block數量小於MERGEBUFF2 (15)。

7. 最後一輪迴圈，僅將row ID寫入到結果檔案中；

8. 根據結果檔案中的row ID按序讀取使用者需要返回的資料。為了進一步優化效能，MySQL會讀一批row ID，並將讀到的資料按排序欄位要求插入快取區中(記憶體大小read_rnd_buffer_size)。

這裡我們需要注意的是：

1. MySQL把外部排序好的分片寫入同一個檔案中，通過儲存檔案偏移量的方式來區別各個分片位置；

2. MySQL每MERGEBUFF (7)個分片做一個歸併，最終分片數達到MERGEBUFF2 (15)時，做最後一次歸併。這兩個值都寫死在程式碼中了…

5.2.2 sort_merge_passes

MySQL手冊中對Sort_merge_passes的描述只有一句話

 Sort_merge_passes
The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

這段話並沒有把sort_merge_passes到底是什麼，該值比較大時說明了什麼，通過什麼方式可以緩解這個問題。
我們把上面MySQL的外部排序演算法搞清楚了，這個問題就清楚了。

其實sort_merge_passes對應的就是MySQL做歸併排序的次數，也就是說，如果sort_merge_passes值比較大，說明sort_buffer和要排序的資料差距越大，我們可以通過增大sort_buffer_size或者讓填入sort_buffer_size的鍵值對更小來緩解sort_merge_passes歸併排序的次數。

對應的，我們可以在原始碼中看到證據。
上述MySQL外部排序的演算法中第5到第7步，是通過sql/filesort.cc檔案中merge_many_buff()函式來實現，第5步單次歸併使用merge_buffers()實現，原始碼摘錄如下：

int merge_many_buff(Sort_param *param, Sort_buffer sort_buffer,
                    Merge_chunk_array chunk_array,
                    size_t *p_num_chunks, IO_CACHE *t_file)
{
...

    for (i=0 ; i < num_chunks - MERGEBUFF * 3 / 2 ; i+= MERGEBUFF)
    {
      if (merge_buffers(param,                  // param
                        from_file,              // from_file
                        to_file,                // to_file
                        sort_buffer,            // sort_buffer
                        last_chunk++,           // last_chunk [out]
                        Merge_chunk_array(&chunk_array[i], MERGEBUFF),
                        0))                     // flag
      goto cleanup;
    }
    if (merge_buffers(param,
                      from_file,
                      to_file,
                      sort_buffer,
                      last_chunk++,
                      Merge_chunk_array(&chunk_array[i], num_chunks - i),
                      0))
      break;                                    /* purecov: inspected */
...
}

擷取部分merge_buffers()的程式碼如下，

int merge_buffers(Sort_param *param, IO_CACHE *from_file,
                  IO_CACHE *to_file, Sort_buffer sort_buffer,
                  Merge_chunk *last_chunk,
                  Merge_chunk_array chunk_array,
                  int flag)
{
...
  current_thd->inc_status_sort_merge_passes();
...
}

可以看到：每個merge_buffers()都會增加sort_merge_passes，也就是說每一次對MERGEBUFF (7) 個block歸併排序都會讓sort_merge_passes加一，sort_merge_passes越多表示排序的資料太多，需要多次merge pass。解決的方案無非就是縮減要排序資料的大小或者增加sort_buffer_size。

打個小廣告，在我們的qmonitor中就有sort_merge_pass的效能指標和引數值過大的報警設定。

六、trace 結果解釋

說明白了三種排序模式和外部排序的方法，我們回過頭來看一下trace的結果。

6.1 是否存在磁碟外部排序

"number_of_tmp_files": 0,

number_of_tmp_files表示有多少個分片，如果number_of_tmp_files不等於0，表示一個sort_buffer_size大小的記憶體無法儲存所有的鍵值對，也就是說，MySQL在排序中使用到了磁碟來排序。

6.2 是否存在優先佇列優化排序

由於我們的這個SQL裡面沒有對資料進行分頁限制，所以filesort_priority_queue_optimization並沒有啟用

"filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            },

而正常情況下，使用了Limit會啟用優先佇列的優化。優先佇列類似於FIFO先進先出佇列。

演算法稍微有點改變，以回表排序模式為例。

• sort_buffer_size足夠大

如果Limit 限制返回N條資料，並且N條資料比sort_buffer_size小，那麼MySQL會把sort buffer作為priority queue，在第二步插入priority queue時會按序插入佇列；在第三步，佇列滿了以後，並不會寫入外部磁碟檔案，而是直接淘汰最尾端的一條資料，直到所有的資料都正常讀取完成。

演算法如下：

1. 根據索引或者全表掃描，按照過濾條件獲得需要查詢的資料

2. 將要排序的列值和row ID組成鍵值對，按序存入中priority queue中

3. 如果priority queue滿了，直接淘汰最尾端記錄。

4. 重複上述步驟，直到所有的行資料都正常讀取了完成

5. 最後一輪迴圈，僅將row ID寫入到結果檔案中

6. 根據結果檔案中的row ID按序讀取使用者需要返回的資料。為了進一步優化效能，MySQL會讀一批row ID，並將讀到的資料按排序欄位要求插入快取區中(記憶體大小read_rnd_buffer_size)。

• sort_buffer_size不夠大

否則，N條資料比sort_buffer_size大的情況下，MySQL無法直接利用sort buffer作為priority queue，正常的檔案外部排序還是一樣的，只是在最後返回結果時，只根據N個row ID將資料返回出來。具體的演算法我們就不列舉了。

這裡MySQL到底是否選擇priority queue是在sql/filesort.cc的check_if_pq_applicable()函式中確定的，具體的程式碼細節這裡就不展開了。

另外，我們也沒有討論limit m,n的情況，如果是Limit m,n， 上面對應的“N個row ID”就是“M+N個row ID”了，MySQL的limit m,n 其實是取m+n行資料，最後把M條資料丟掉。

從上面我們也可以看到sort_buffer_size足夠大對limit資料比較小的情況，優化效果是很明顯的。

七、MySQL其他相關排序引數

7.1 max_sort_length

這裡需要區別max_sort_length 和max_length_for_sort_data。

max_length_for_sort_data是為了讓MySQL選擇”< sort_key, rowid >”還是”< sort_key, additional_fields >”的模式。

而max_sort_length是鍵值對的大小無法確定時（比如使用者要查詢的資料包含了 SUBSTRING_INDEX(col1, ‘.’,2)）MySQL會對每個鍵值對分配max_sort_length個位元組的記憶體，這樣導致記憶體空間浪費，磁碟外部排序次數過多。

7.2 innodb_disable_sort_file_cache

innodb_disable_sort_file_cache設定為ON的話，表示在排序中生成的臨時檔案不會用到檔案系統的快取，類似於O_DIRECT開啟檔案。

7.3 innodb_sort_buffer_size

這個引數其實跟我們這裡討論的SQL排序沒有什麼關係。innodb_sort_buffer_size設定的是在建立InnoDB 索引時，使用到的sort buffer的大小。
以前寫死為1M，現在開放出來，允許使用者自定義設定這個引數了。

八、MySQL排序優化總結

最後整理一下優化MySQL排序的手段

1. 排序和查詢的欄位儘量少。只查詢你用到的欄位，不要使用select * ；使用limit查詢必要的行資料；

2. 要排序或者查詢的欄位，儘量不要用不確定字元函式，避免MySQL直接分配max_sort_length，導致sort buffer空間不足；

3. 使用索引來優化或者避免排序；

4. 增加sort_buffer_size大小，避免磁碟排序；

5. 不得不使用original 排序演算法時，增加read_rnd_buffer_size；

6. 欄位長度定義合適就好（避免過長）；

7. tmpdir建議獨立存放，放在高速儲存裝置上。

寫到這裡，大家可以回顧一下文章開頭的那八個問題，如果回答不了這些問題，說明其實你沒有真正的理解透MySQL的排序，或者說我們的這篇文章寫的太爛了==

是不是感覺累覺不愛了，我們的文章也終於要結束了，引用一個圖片作為文章的結尾，向Christophe致敬。當下次其他同學問你，MySQL的排序到底是怎麼做的時候，你可以告訴他：

This is

九、參考文獻

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
http://coding-geek.com/how-databases-work/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2125740/，如需轉載，請註明出處，否則將追究法律責任。