微信公眾號：云計(jì)算通俗講義

持續(xù)輸出技術(shù)干貨，歡迎關(guān)注！?

01?概述

????在MySQL中的ORDER BY有兩種排序?qū)崿F(xiàn)方式：

??? 1、利用有序索引獲取有序數(shù)據(jù)；

??? 2、文件排序。

????在使用explain分析查詢的時(shí)候，利用有序索引獲取有序數(shù)據(jù)顯示Using index。如果MySQL在排序的時(shí)候沒有使用到索引那么就會輸出using filesort，即使用文件排序。

????文件排序是通過相應(yīng)的排序算法，將取得的數(shù)據(jù)在內(nèi)存中進(jìn)行排序：MySQL需要將數(shù)據(jù)在內(nèi)存中進(jìn)行排序，所使用的內(nèi)存區(qū)域也就是我們通過sort_buffer_size系統(tǒng)變量所設(shè)置的sort buffer（排序區(qū)）。這個(gè)sort buffer是每個(gè)Thread獨(dú)享的，所以說可能在同一時(shí)刻在MySQL中可能存在多個(gè)sort buffer內(nèi)存區(qū)域。

02?原理

??? MySQL對排序有兩種實(shí)現(xiàn)：

2.1 雙路排序

? ??原理

????第一遍掃描出需要排序的字段，然后進(jìn)行排序后，根據(jù)排序結(jié)果，第二遍再掃描一下需要select的列數(shù)據(jù)。這樣會引起大量的隨機(jī)IO，效率不高，但是節(jié)約內(nèi)存。排序使用quick sort，但是如果內(nèi)存不夠則會按照block進(jìn)行排序，將排序結(jié)果寫入磁盤文件，然后再將結(jié)果合并。

? ??具體過程：

??? 1、讀取所有滿足條件的記錄。

??? 2、對于每一行，存儲一對值到緩沖區(qū)（排序列，行記錄指針），一個(gè)是排序的索引列的值，即order by用到的列值，和指向該行數(shù)據(jù)的行指針（緩沖區(qū)的大小為sort_buffer_size大?。?/p>

??? 3、當(dāng)緩沖區(qū)滿后，運(yùn)行一個(gè)快速排序（qsort）來將緩沖區(qū)中數(shù)據(jù)排序，并將排序完的數(shù)據(jù)存儲到一個(gè)臨時(shí)文件，并保存一個(gè)存儲塊的指針，當(dāng)然如果緩沖區(qū)不滿，則不會重建臨時(shí)文件了。

??? 4、重復(fù)以上步驟，直到將所有行讀完，并建立相應(yīng)的有序的臨時(shí)文件。

??? 5、對塊級進(jìn)行排序，這個(gè)類似于歸并排序算法，只通過兩個(gè)臨時(shí)文件的指針來不斷交換數(shù)據(jù)，最終達(dá)到兩個(gè)文件，都是有序的。

??? 6、重復(fù)5直到所有的數(shù)據(jù)都排序完畢。

??? 7、采取順序讀的方式，將每行數(shù)據(jù)讀入內(nèi)存，并取出數(shù)據(jù)傳到客戶端，這里讀取數(shù)據(jù)時(shí)并不是一行一行讀，讀取緩存大小由read_rnd_buffer_size來指定。

? ??特點(diǎn)

????采取的方法為：快速排序 + 歸并排序。

????但有一個(gè)問題，就是，一行數(shù)據(jù)會被讀兩次，第一次是where條件過濾時(shí)，第二個(gè)是排完序后還得用行指針去讀一次，一個(gè)優(yōu)化的方法是，直接讀入數(shù)據(jù)，排序的時(shí)候也根據(jù)這個(gè)排序，排序完成后，就直接發(fā)送到客戶端了。

2.2 單路排序

????在MySQL4.1版本之前只有第一種排序算法雙路排序，第二種算法是從MySQL4.1開始的改進(jìn)算法，主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的IO操作，將兩次變成了一次，但相應(yīng)也會耗用更多的sortbuffer空間。當(dāng)然，MySQL4.1開始的以后所有版本同時(shí)也支持第一種算法。

? ??原理

????即一遍掃描數(shù)據(jù)后將select需要的列數(shù)據(jù)以及排序的列數(shù)據(jù)都取出來，然后在sort buffer中排序，這樣就不需要進(jìn)行第二遍掃描了，當(dāng)然內(nèi)存不足時(shí)也會使用磁盤臨時(shí)文件進(jìn)行外排。

????具體過程：

????1、讀取滿足條件的記錄

????2、對于每一行，記錄排序的key和數(shù)據(jù)行指針，并且把要查詢的列也讀出來

????3、根據(jù)索引key排序

????4、讀取排序完成的文件，并直接根據(jù)數(shù)據(jù)位置讀取數(shù)據(jù)返回客戶端，而不是去訪問表

????特點(diǎn)

????單路排序一次性將結(jié)果讀取出來，然后在sort buffer中排序，避免了雙路排序的兩次讀的隨機(jī)IO。

????這也有一個(gè)問題：當(dāng)獲取的列很多的時(shí)候，排序起來就很占空間，因此，max_length_for_sort_data變量就決定了是否能使用這個(gè)排序算法。

????MySQL根據(jù)sort_buffer_size來判斷是否使用磁盤臨時(shí)文件，如果需要排序的數(shù)據(jù)能放入sort_buffer_size則無需使用磁盤臨時(shí)文件，此時(shí)explain只會輸出using filesort否則需要使用磁盤臨時(shí)文件explain會輸出using temporary;using filesort。

03?選擇

????MySQL主要通過比較我們所設(shè)定的系統(tǒng)參數(shù)max_length_for_sort_data的大小和Query語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。

????如果需要的列數(shù)據(jù)一行可以放入max_length_for_sort_data則使用一遍掃描否則使用兩遍掃描（如果max_length_for_sort_data更大，則使用第二種優(yōu)化后的算法，反之使用第一種算法）。所以如果希望ORDER BY操作的效率盡可能的高，一定要注意max_length_for_sort_data參數(shù)的設(shè)置。

????如果數(shù)據(jù)庫出現(xiàn)大量的排序等待，造成系統(tǒng)負(fù)載很高，而且響應(yīng)時(shí)間變得很長，可以考慮是否為MySQL 使用了傳統(tǒng)的第一種排序算法而導(dǎo)致，在加大了max_length_for_sort_data參數(shù)值之后，系統(tǒng)負(fù)載是否馬上得到了大的緩解，響應(yīng)是否快很多。

04?優(yōu)化

????對于文件排序的優(yōu)化，應(yīng)該讓MySQL避免使用第一種雙路排序，盡量選擇使用第二種單路算法來進(jìn)行排序。這樣可以減少大量的隨機(jī)IO操作，很大幅度地提高排序工作的效率。

????1、加大max_length_for_sort_data參數(shù)的設(shè)置

????在MySQL中，決定使用老的雙路排序算法還是改進(jìn)版單路排序算法是通過參數(shù)max_length_for_ sort_data來決定的。當(dāng)所有返回字段的最大長度小于這個(gè)參數(shù)值時(shí)，MySQL就會選擇改進(jìn)后的單路排序算法，反之，則選擇老式的雙路排序算法。所以，如果有充足的內(nèi)存讓MySQL存放需要返回的非排序字段，就可以加大這個(gè)參數(shù)的值來讓MySQL選擇使用改進(jìn)版的排序算法。

????2、去掉不必要的返回字段或列長度盡量小一些

????對于內(nèi)存不是非常充裕的情況，不能強(qiáng)行增大配置項(xiàng)max_length_for_sort_data，否則可能會造成MySQL不得不將數(shù)據(jù)分成很多段，然后進(jìn)行排序，這樣可能會得不償失。此時(shí)可以選擇去掉不必要的返回字段或者將列長度盡可能設(shè)置小一些，讓返回結(jié)果長度適應(yīng)max_length_for_sort_data參數(shù)的限制。

????3、增大sort_buffer_size參數(shù)設(shè)置

????增大sort_buffer_size并不是為了讓MySQL選擇改進(jìn)版的單路排序算法，而是為了讓MySQL盡量減少在排序過程中對需要排序的數(shù)據(jù)進(jìn)行分段，因?yàn)榉侄螘斐蒑ySQL不得不使用臨時(shí)表來進(jìn)行交換排序。

????4、增加read_rnd_buffer_size大小，可以一次性多讀到內(nèi)存中

????該變量可以被任何存儲引擎使用，當(dāng)從一個(gè)已經(jīng)排序的鍵值表中讀取行時(shí)，會先從該緩沖區(qū)中獲取而不再從磁盤上獲取。默認(rèn)為256K。

??? 5、改變tmpdir，使其指向多個(gè)物理盤（不是分區(qū)）的目錄。

05?總結(jié)

????當(dāng)看到MySQL的explain輸出using filesort時(shí)，說明排序時(shí)沒有使用索引。如果輸出using temporary;using filesort則說明使用文件排序和磁盤臨時(shí)表，這種情況需要引起注意，效率會比較低。

????總結(jié)來說，盡量避免出現(xiàn)文件排序，如果出現(xiàn)using filesort需要考慮優(yōu)化。