MYSQL ORDER BY Optimization

標籤：

 ORDER BY Optimization

       某些情況下，MYSQL可以使用index排序而避免額外的sorting.

       即使order by語句列不能準確的匹配index,只要沒有index中（不在order by的列）在where語句以常量形式出現。（最左首碼）

SELECT * FROM t1  ORDER BY key_part1,key_part2,... ;SELECT * FROM t1  WHERE key_part1 = constant  ORDER BY key_part2;SELECT * FROM t1  ORDER BY key_part1 DESC, key_part2 DESC;SELECT * FROM t1  WHERE key_part1 = 1  ORDER BY key_part1 DESC, key_part2 DESC;SELECT * FROM t1  WHERE key_part1 > constant  ORDER BY key_part1 ASC;SELECT * FROM t1  WHERE key_part1 < constant  ORDER BY key_part1 DESC;SELECT * FROM t1  WHERE key_part1 = constant1 AND key_part2 > constant2  ORDER BY key_part2;

       某些情況下，依舊使用Index來尋找匹配where子句的行，但MYSQL不用index來解決order by：

      1：order by子句中使用不同indexes：

SELECT * FROM t1 ORDER BY key1, key2;

      2：使用不連續的index部分（聯合key的非最左首碼）

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

      3：混合使用asc 和desc:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

      4:獲得資料行的index(where子句中)和Order by 中使用的不一樣：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

      5：order by子句中使用index的運算式：

SELECT * FROM t1 ORDER BY ABS(key);SELECT * FROM t1 ORDER BY -key;

      6：join操作時，order by子句的列不全是第一個非 const表;

      7: 不同的order by,group by運算式：

      8：只對order by子句列的首碼加index,這種情況下index不能解決sort. e.g : order by列包含一個char(20)類型，但是只對前10bytes加index；

      9：table index無序,the index of hash（memory表）；

 

    一個index是否排序可用可能受列的別名影響，表t1列 a 為索引：

可以利用index來排序：

SELECT a FROM t1 ORDER BY a;

不能：

SELECT ABS(a) AS a FROM t1 ORDER BY a;

        該語句中，order by引用的是列a, select子句的列名也是a, 但是他是別名，引用的是abs(a);

        下面的語句中，order by引用列名和select list中的列名不一樣，但是select用到a,index sort可以使用（該語句的排序結果和以abs(a)排序的完全不一樣）

SELECT ABS(a) AS b FROM t1 ORDER BY a;

        預設的，mysql對所有的組col1,col2（group by col1,col2）排序，如果一個查詢包含group by但是想避免sort的負載，可以壓制排序通過order by null.

INSERT INTO fooSELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

       依賴隱式的group by 排序在mysql5.6中被捨棄。更可取的是使用準確的order by子句。

       MYSQL有兩種filesort演算法來獲得結果。原始的方法只使用order by中的列list. 改寫過的方法不僅僅使用order by子句中的列，而是查詢中所使用到的列。

       最佳化器選擇哪個filesort演算法？正常情況下使用第二種(BLOB TEXT等大對象列外)，兩種演算法，都使用到sort_buffer_size系統變數：

       原始的filesort演算法工作：

       1：根據key值或者scan all the table(where條件)讀取所有滿足條件的行，跳過不滿足where子句的行。

       2：對於每一行，儲存（key value, row id）對在sort buffer中。

       3： 如果所有上述對能全部放在sort buffer中，臨時檔案不會被建立，否則，當sort buffer滿時，記憶體中執行quicksort並且把結果寫進臨時檔案中，儲存一個指標執行這個 sorted block.

       4:重複執行上述的過程，直到所有的行都被讀取。

       5：執行一個多路歸併排序，把第一個檔案的block轉移到另外一個臨時檔案中。重複執行，直到第一個檔案內容全部在第二個檔案中。

       6：一直merge buffer直到剩下2個block

       7：最後一次merge，唯寫入rowid到結果表

       8.根據排序結果中的rowid順序讀取資料。（手冊中還提到了一個最佳化方案，但是我不認為能起到最佳化作用）。

     

      該filesort中出現兩次讀取操作，第一次在where子句判斷，另外一次是在拍完value pairs後。然而即使第一次訪問是連續讀取（e.g. scan all the table）,但是第二次他們是隨機訪問（儘管key排過序了，但是行位置沒有~！）；

       

      第二種filesort演算法：（避開二次讀，不是記錄rowID,而是記錄查詢所使用的引用列）

     1：讀取滿足where子句的所有行

     2：對於每一行，元組記錄key value和查詢所引用到的列

     3：當buffer滿時，排序並寫入臨時檔案

     4：merge sort所有的臨時檔案，檢索有序的行資料，直接從排過序的元組中讀取需要的列而不是兩次訪問基表

   

     

      修改後的方法，列長於原來的方法。很有可能會產生大量IO，讓排序變得很慢。為了避免這個問題，最佳化器會所有讀取列的長度小於max_length_for_sort_data系統變數，才會選擇修改後的演算法。

      當filesort完成，explain輸出中extra會有using filesort，最佳化器跟蹤輸出中filesort_summary塊：

"filesort_summary": {  "rows": 100,  "examined_rows": 100,  "number_of_tmp_files": 0,  "sort_buffer_size": 25192,  "sort_mode": "<sort_key, additional_fields>"}

    其中sort mode就說了演算法：

    <sort_key,rowid>表示原始的演算法

    <sort_key,addtitional_filed>表示是修改後的演算法

    為了提高排序速度，可以檢查是否可以使用索引，如果不能使用：

       1.增加sort_buffer_size的大小

       2.增加read_rnd_buffer_size的大小

       3.通過表設計減少空間佔用

       4.修改tmpdir目錄指向專用檔案系統

       如果order by沒有使用索引，但是有limit子句，那麼最佳化器可能可以避免合并臨時檔案，直接在記憶體中排序

 

MYSQL ORDER BY Optimization