MySQL中filesort算法有幾種

這篇文章主要介紹了MySQL中filesort算法有幾種，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

一.filesort算法介紹
MySQL有兩種filesort算法:two-pass和single-pass。
(1).two-pass
列長度之和超過max_length_for_sort_data字節(jié)時(shí)就使用這個(gè)算法，其原理是:先按照where篩選條件讀取數(shù)據(jù)行，并存儲(chǔ)每行的排序字段和行指針到排序緩沖區(qū)(sort buffer)。如果排序緩沖大小不夠，就在內(nèi)存中運(yùn)行一個(gè)快速排序操作，把排序結(jié)果存儲(chǔ)到一個(gè)臨時(shí)文件里，
用一個(gè)指針指向這個(gè)已經(jīng)排序好了的塊，然后繼續(xù)讀取數(shù)據(jù)，直到所有行都讀取完畢為止。這是第一次讀取記錄。以上第一次讀取記錄時(shí)，可以按照索引排序或表掃描，可以做到順序讀取。但第二次讀取記錄時(shí)，雖然排序字段是有序的，行緩存里存儲(chǔ)的行指針是有序的，
但所指向的物理記錄需要隨機(jī)讀，所以這個(gè)算法可能會(huì)帶來很多隨機(jī)讀，從而導(dǎo)致效率很低。
優(yōu)點(diǎn)是：排序的數(shù)據(jù)量小，一般在內(nèi)存里即可完成。
缺點(diǎn)是：需要讀取記錄兩次，第二次讀取時(shí)候可能會(huì)產(chǎn)生許多隨機(jī)IO，成本可能會(huì)比較高。


(2).single-pass
MySQL一般使用這種算法。其原理是：按照篩選條件把SQL中涉及的字段全部讀入排序緩沖區(qū)(sort buffer)里，然后依據(jù)排序字段進(jìn)行排序，
如果排序緩沖不夠，會(huì)將臨時(shí)排序結(jié)果寫入到一個(gè)臨時(shí)文件里，最后合并臨時(shí)排序文件，直接返回已經(jīng)排序好的結(jié)果集.
優(yōu)點(diǎn)是：不需要讀取記錄兩次，相對(duì)于two-pass，可以減少I/O開銷。
缺點(diǎn)是：由于要讀入所有字段，排序緩沖可能不夠，需要額外的臨時(shí)文件協(xié)助進(jìn)行排序，導(dǎo)致增加額外的I/O成本。


二.排序相關(guān)的參數(shù)設(shè)置和優(yōu)化
相關(guān)參數(shù)如下:
max_length_for_sort_data:如果各列長度之和(包括選擇列、排序列)超過了max_length_for_sort_data字節(jié)，那么就使用two-pass算法。
如果排序BLOB、Text字段，使用的也是two-pass算法，那么這個(gè)值設(shè)置的太高會(huì)導(dǎo)致系統(tǒng)I/O上升和CPU下降，所以建議不要將max_length_for_sort_data
的值設(shè)置的太高。
max_sort_length:如果排序BLOB、TEXT字段，則僅排序前max_sort_length個(gè)字節(jié)。
可以考慮的優(yōu)化方向如下：
(1).增大sort_buffer_size
一般情況下使用默認(rèn)的single-pass算法即可。可以考慮增大sort_buffer_size以減少磁盤I/O。
需要留意的是字段長度之和不要超過max_length_for_sort_data,只查詢所需要的列，注意列的長度和類型。 MySQL目前讀取和計(jì)算列的長度是按照定義的最大的度進(jìn)行的，所以在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候，不要將varchar類型的字段設(shè)置得過大，雖然對(duì)于varchar類型來說，在物理磁盤中實(shí)際
存儲(chǔ)可以做到緊湊，但在排序的時(shí)候是會(huì)分配最大定義長度的，有時(shí)候排序階段所產(chǎn)生的臨時(shí)文件甚至比原始表還要大。MySQL 5.7版本在這方面做了一些優(yōu)化。
(2).對(duì)于two-pass算法，可以考慮增大read_rnd_buffer_size，但由于這個(gè)全局變量是對(duì)所有連接都生效，因此建議只在會(huì)話級(jí)進(jìn)行設(shè)置，以加速一些特殊的大操作。
(3).在操作系統(tǒng)層面，優(yōu)化臨時(shí)文件的讀寫。

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