mysql排序區別

由淺入深詳細說說MySQL排序模式，怎麼影響MySQL選擇不同的排序模式和怎麼最佳化排序。

推薦課程：MySQL教學。

排序是資料庫中的一個基本功能，MySQL也不例外。

使用者透過Order by語句即能達到將指定的結果集排序的目的，其實不只是Order by語句，Group by語句，Distinct語句都會隱含使用排序。本文首先會簡單介紹SQL如何利用索引避免排序代價，接著會介紹MySQL實作排序的內部原理。

解決大家的以下問題：

MySQL在哪些地方會使用排序，怎麼判斷MySQL使用了排序；

MySQL有幾種排序模式，透過什麼方法讓MySQL選擇不同的排序模式；

MySQL排序跟read_rnd_buffer_size有啥關係，在哪些情況下增加read_rnd_buffer_size能優化排序;

怎麼判斷MySQL使用到了磁碟來排序，怎麼避免或優化磁碟排序；

排序時變長欄位（varchar）資料在記憶體是怎麼儲存的，5.7有哪些改進；

在情況下，排序模式有哪些改進；

sort_merge_pass到底是什麼，該狀態值過大說明了什麼問題，可以透過什麼方法解決；

 MySQL使用到了排序的話，依序可以透過什麼辦法分析和最佳化讓排序更快？

二、排序

我們透過explain查看MySQL執行計劃時，常常會看到在Extra列中顯示Using filesort。

對於無法利用索引避免排序的SQL，資料庫必須自己實作排序功能以滿足使用者需求，此時SQL的執行計劃中會出現“Using filesort”，這裡需要注意的是filesort並不意味著就是檔案排序，其實也有可能是記憶體排序，這個主要由sort_buffer_size參數與結果集大小決定。

其實這種情況就表示MySQL使用了排序。 Using filesort常出現在order by、group by、distinct、join等情況下。

MySQL內部實作排序主要有3種方式，常規排序，優化排序與優先隊列排序。

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

請看這三個排序的差異：

#a.常規排序

##(1) .從表t1中取得滿足WHERE條件的記錄

(2).對於每筆記錄，將記錄的主鍵排序鍵(id,col2)取出放入sort buffer

(3 ).如果sort buffer可以存放所有滿足條件的(id,col2)對，則進行排序；否則sort buffer滿後，進行排序並固化到臨時文件中。 (排序演算法採用的是快速排序演算法)

(4).若排序中產生了臨時文件，需要利用歸併排序演算法，保證臨時文件中記錄是有序的

#( 5).循環執行上述過程，直到所有滿足條件的記錄全部參與排序

(6).掃描排好序的(id,col2)對，並利用id去撈取SELECT需要返回的列( col1,col2,col3)

(7).將取得的結果集回傳給使用者。

從上述流程來看，是否使用檔案排序主要看sort buffer是否能容下需要排序的(id,col2)對，這個buffer的大小由sort_buffer_size參數控制。另外一次排序需要兩次IO，一次是撈(id,col2),第二次是撈(col1,col2,col3)，由於返回的結果集合是按col2排序，因此id是亂序的，通過亂序的id去撈(col1,col2,col3)時會產生大量的隨機IO。對於第二次MySQL本身一個最佳化，即在撈之前先將id排序，並放入緩衝區，這個快取區大小由參數read_rnd_buffer_size控制，然後有序去撈記錄，將隨機IO轉為順序IO。

b.優化排序

常規排序方式除了排序本身，還需要額外兩次IO。優化的排序方式相對於常規排序，減少了第二次IO。主要差異在於，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由於sort buffer中包含了查詢需要的所有字段，因此排序完成後可以直接返回，無需二次撈資料。這種方式的代價在於，同樣大小的sort buffer，能存放的(col1,col2,col3)數目要小於(id,col2)，如果sort buffer不夠大，可能導致需要寫臨時文件，造成額外的IO。當然MySQL提供了參數max_length_for_sort_data，只有當排序元組小於max_length_for_sort_data時，才能利用最佳化排序方式，否則只能用常規排序方式。

c.優先隊列排序

為了得到最終的排序結果，無論怎樣，我們都需要將所有滿足條件的記錄進行排序才能傳回。那麼相對於優化排序方式，是否還有優化空間呢？ 5.6版本針對Order by limit M，N語句，在空間層面做了最佳化，加入了一種新的排序方式--優先隊列，這種方式採用堆排序實作。堆排序演算法特徵正好可以解limit M，N 這類排序的問題，雖然仍然需要所有元素參與排序，但是只需要M N個元組的sort buffer空間即可，對於M，N很小的場景，基本上不會因為sort buffer不夠而導致需要臨時檔案進行歸併排序的問題。對於升序，採用大頂堆，最終堆中的元素組成了最小的N個元素，對於降序，採用小頂堆，最終堆中的元素組成了最大的N的元素。

以上是mysql排序區別的詳細內容。更多資訊請關注PHP中文網其他相關文章！