MySQL中filesort算法有幾種

這篇文章主要介紹了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).在操作系統層面，優化臨時文件的讀寫。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL中filesort算法有幾種”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!