【MySQL】order by 原理以及優化

orclwujian發表於2016-11-15
MySql優化
一 簡介
   偏向於業務的(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() 來進行記憶體分配,導致效率降低。

四  參考文章
[1] MySQL order by 調優官方文件 
[2] MySQL排序原理與案例分析   
[3] 淘寶MySQL 月報 
本文的原理分析部分 採取偷懶策略，直接從我的前同事 雁閒 的部落格[2]摘抄了，強烈安利 雁閒的 部落格 ，MySQL 原始碼研究者，精通MySQL 業務和底層運維。

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

相關文章