一 簡介 偏向于業務的(MySQL)DBA或者業務的開發者來說，order by 排序是一個常見的業務功能，將結果根據指定的字段排序，滿足前端展示的需求。然而排序操作也是經常出現慢查詢排行榜的座上賓。本文將從原理和實際案例優化，order by 使用限制等幾個方面來逐步了解order by 排序。 二 原理 在了解order by 排序的原理之前，強烈安利兩篇關于排序算法的文章 《歸并排序的實現》 《經典排序算法》。MySQL 支持兩種排序算法，常規排序和優化，并且在MySQL 5.6版本中 針對order by limit M,N 做了特別的優化，這里列為第三種。 2.1 常規排序 a 從表t1中獲取滿足WHERE條件的記錄 b 對于每條記錄，將記錄的主鍵+排序鍵(id,col2)取出放入sort buffer c 如果sort buffer可以存放所有滿足條件的(id,col2)對，則進行排序；否則sort buffer滿后，進行排序并固化到臨時文件中。(排序算法采用的是快速排序算法) d 若排序中產生了臨時文件，需要利用歸并排序算法，保證臨時文件中記錄是有序的 e 循環執行上述過程，直到所有滿足條件的記錄全部參與排序 f 掃描排好序的(id,col2)對，并利用id去撈取SELECT需要返回的列(col1,col2,col3) g 將獲取的結果集返回給用戶。 從上述流程來看，是否使用文件排序主要看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。 2.2 優化排序 常規排序方式除了排序本身，還需要額外兩次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時，才能利用優化排序方式，否則只能用常規排序方式。 2.3 優先隊列排序 為了得到最終的排序結果，無論怎樣，我們都需要將所有滿足條件的記錄進行排序才能返回。那么相對于優化排序方式，是否還有優化空間呢？5.6版本針對Order by limit M，N語句，在空間層面做了優化，加入了一種新的排序方式:優先隊列，這種方式采用堆排序實現。堆排序算法特征正好可以解limit M，N 這類排序的問題，雖然仍然需要所有元素參與排序，但是只需要M+N個元組的sort buffer空間即可，對于M，N很小的場景，基本不會因為sort buffer不夠而導致需要臨時文件進行歸并排序的問題。對于升序，采用大頂堆，最終堆中的元素組成了最小的N個元素，對于降序，采用小頂堆，最終堆中的元素組成了最大的N的元素。
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
5 ey_part1是范圍查詢，key_part2無法使用索引排序
SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
5 rder by和group by 字段列不一致
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 group by key_part4;
6 索引本身是無序存儲的，比如hash 索引，不能利用索引的有序性。
7 order by字段只被索引了前綴 ，key idx_col(col(10))
select * from t1 order by col ;
8 對于還有join的關聯查詢，排序字段并非全部來自于第一個表，使用explain 查看執行計劃第一個表 type 值不是const 。
當無法避免排序操作時,又該如何來優化呢？很顯然,優先選擇using index的排序方式，在無法滿足利用索引排序的情況下，盡可能讓 MySQL 選擇使用第二種單路算法來進行排序。這樣可以減少大量的隨機IO操作,很大幅度地提高排序的效率。 1 加大 max_length_for_sort_data 參數的設置 在 MySQL 中,決定使用老式排序算法還是改進版排序算法是通過參數max_length_for_sort_data來決定的。當所有返回字段的最大長度小于這個參數值時,MySQL 就會選擇改進后的排序算法,反之,則選擇老式的算法。所以,如果有充足的內存讓MySQL 存放須要返回的非排序字段,就可以加大這個參數的值來讓 MySQL 選擇使用改進版的排序算法。 2 去掉不必要的返回字段 當內存不是很充裕時,不能簡單地通過強行加大上面的參數來強迫 MySQL 去使用改進版的排序算法,否則可能會造成 MySQL 不得不將數據分成很多段,然后進行排序,這樣可能會得不償失。此時就須要去掉不必要的返回字段,讓返回結果長度適應 max_length_for_sort_data 參數的限制。
同時也要規范MySQL開發規范，盡量避免大字段。當有select 查詢列含有大字段blob或者text 的時候,MySQL 會選擇常規排序。 "The optimizer selects which filesort algorithm to use. It normally uses the modified algorithm except when BLOB or TEXT columns are involved, in which case it uses the original algorithm." 3 增大 sort_buffer_size 參數設置 這個值如果過小的話,再加上你一次返回的條數過多,那么很可能就會分很多次進行排序,然后最后將每次的排序結果再串聯起來,這樣就會更慢,增大 sort_buffer_size 并不是為了讓 MySQL選擇改進版的排序算法,而是為了讓MySQL盡量減少在排序過程中對須要排序的數據進行分段,因為分段會造成 MySQL 不得不使用臨時表來進行交換排序。但是這個值不是越大越好： 1 sort_buffer_size 是一個connection級參數,在每個connection第一次需要使用這個buffer的時候,一次性分配設置的內存。 2 sort_buffer_size 并不是越大越好,由于是connection級的參數,過大的設置+高并發可能會耗盡系統內存資源。 3 據說sort_buffer_size 超過2M的時候,就會使用mmap() 而不是 malloc() 來進行內存分配,導致效率降低。
四 參考文章 [1] MySQL order by 調優官方文檔 [2] MySQL排序原理與案例分析 [3] 淘寶MySQL 月報 本文的原理分析部分 采取偷懶策略，直接從我的前同事 雁閑 的博客[2]摘抄了，強烈安利 雁閑的 博客 ，MySQL 源碼研究者，精通MySQL 業務和底層運維。
