MySQL Order By 原理以及最佳化

MySQL Order By 原理以及最佳化

一 簡介
   偏向於業務的(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的元素。

三 最佳化
  通過上面的原理分析，我們知道排序的本質是通過一定的演算法(耗費cpu 運算,記憶體,臨時檔案IO)將結果集變成有序的結果集。如何最佳化呢？答案是分兩個方面利用索引的有序性(MySQL的B+ 型樹狀結構索引是預設從小到大遞增排序)減少排序,最好的方式是直接不排序。

1. create table t1(
   
2.   id int not null primary key ,
3.   key_part1 int(10) not null,
4.   key_part2 varchar(10) not null default '',
5.   key_part3
6.   key idx_kp1_kp2(key_part1,key_part2,key_part4)，
7.   key idx_kp3(id)
8. ) engine=innodb default charset=utf8

以下種類的查詢是可以利用到索引 idx_kp1_kp2的 

1. SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
   
2. SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
3. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
4. SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;
5. SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
6. SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;
7. SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2

溫馨提示 ，各位看官要辯證的看待官方給的例子，自己多動手實踐。
無法利用到索引排序的情況，其實我覺得這是本文的重點，對於廣大開發同學而言，記住那種不能利用索引排序會更簡單些。

1. 1 最常見的情況 用來尋找結果的索引(key2) 和 排序的索引(key1) 不一樣,where a=x and b=y order by id;
   
2. SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
3. 2 排序欄位在不同的索引中，無法使用索引排序
4. SELECT * FROM t1 ORDER BY key1,key2;
5. 3 排序欄位順序與索引中列順序不一致，無法使用索引排序，比如索引是 key idx_kp1_kp2(key_part1,key_part2)
6. SELECT * FROM t1 ORDER BY key_part2, key_part1;
7. 4 order by中的升降序和索引中的預設升降不一致，無法使用索引排序
8. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
9. 5 ey_part1是範圍查詢，key_part2無法使用索引排序
10. SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
11. 5 rder by和group by 欄位列不一致
12. SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 group by key_part4;
13. 6 索引��身是無序儲存的，比如hash 索引，不能利用索引的有序性。
14. 7 order by欄位只被索引了首碼 ，key idx_col(col(10))
15. select * from t1 order by col ;
16. 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() 來進行記憶體配置,導致效率降低。

本文永久更新連結地址：