MySQL的兩種filesort演算法介紹以及相關引數的設定和優化

一.filesort演算法介紹
MySQL有兩種filesort演算法:two-pass和single-pass。
(1).two-pass
列長度之和超過max_length_for_sort_data位元組時就使用這個演算法，其原理是:先按照where篩選條件讀取資料行，並儲存每行的排序欄位和行指標到排序緩衝區(sort buffer)。如果排序緩衝大小不夠，就在記憶體中執行一個快速排序操作，把排序結果儲存到一個臨時檔案裡，
用一個指標指向這個已經排序好了的塊，然後繼續讀取資料，直到所有行都讀取完畢為止。這是第一次讀取記錄。以上第一次讀取記錄時，可以按照索引排序或表掃描，可以做到順序讀取。但第二次讀取記錄時，雖然排序欄位是有序的，行快取裡儲存的行指標是有序的，
但所指向的物理記錄需要隨機讀，所以這個演算法可能會帶來很多隨機讀，從而導致效率很低。
優點是：排序的資料量小，一般在記憶體裡即可完成。
缺點是：需要讀取記錄兩次，第二次讀取時候可能會產生許多隨機IO，成本可能會比較高。


(2).single-pass
MySQL一般使用這種演算法。其原理是：按照篩選條件把SQL中涉及的欄位全部讀入排序緩衝區(sort buffer)裡，然後依據排序欄位進行排序，
如果排序緩衝不夠，會將臨時排序結果寫入到一個臨時檔案裡，最後合併臨時排序檔案，直接返回已經排序好的結果集.
優點是：不需要讀取記錄兩次，相對於two-pass，可以減少I/O開銷。
缺點是：由於要讀入所有欄位，排序緩衝可能不夠，需要額外的臨時檔案協助進行排序，導致增加額外的I/O成本。


二.排序相關的引數設定和優化
相關引數如下:
max_length_for_sort_data:如果各列長度之和(包括選擇列、排序列)超過了max_length_for_sort_data位元組，那麼就使用two-pass演算法。
如果排序BLOB、Text欄位，使用的也是two-pass演算法，那麼這個值設定的太高會導致系統I/O上升和CPU下降，所以建議不要將max_length_for_sort_data
的值設定的太高。
max_sort_length:如果排序BLOB、TEXT欄位，則僅排序前max_sort_length個位元組。
可以考慮的優化方向如下：
(1).增大sort_buffer_size
一般情況下使用預設的single-pass演算法即可。可以考慮增大sort_buffer_size以減少磁碟I/O。
需要留意的是欄位長度之和不要超過max_length_for_sort_data,只查詢所需要的列，注意列的長度和型別。 MySQL目前讀取和計算列的長度是按照定義的最大的度進行的，所以在設計表結構的時候，不要將varchar型別的欄位設定得過大，雖然對於varchar型別來說，在物理磁碟中實際
儲存可以做到緊湊，但在排序的時候是會分配最大定義長度的，有時候排序階段所產生的臨時檔案甚至比原始表還要大。MySQL 5.7版本在這方面做了一些優化。
(2).對於two-pass演算法，可以考慮增大read_rnd_buffer_size，但由於這個全域性變數是對所有連線都生效，因此建議只在會話級進行設定，以加速一些特殊的大操作。
(3).在作業系統層面，優化臨時檔案的讀寫。

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