order by關鍵字優化

1. ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序

建表SQL

CREATE TABLE tblA(#id int primary key not null auto_increment,age INT,birth TIMESTAMP NOT NULL );INSERT INTO tblA(age,birth) VALUES(22,NOW()); INSERT INTO tblA(age,birth) VALUES(23,NOW()); INSERT INTO tblA(age,birth) VALUES(24,NOW());CREATE INDEX idx_A_ageBirth ON tblA(age,birth);SELECT * FROM tblA;

Case

MySQL支持二種方式的排序，FileSort和Index，Index效率高.
它指MySQL掃描索引本身完成排序。FileSort方式效率較低。

ORDER BY滿足兩情況，會使用Index方式排序:

ORDER BY 語句使用索引最左前列

使用Where子句與Order BY子句條件列組合滿足索引最左前列

2. 盡可能在索引列上完成排序操作，遵照索引建的最佳左前綴

3. 如果不在索引列上，filesort有兩種算法：mysql就要啟動雙路排序和單路排序

雙路排序

MySQL 4.1之前是使用雙路排序,字面意思就是兩次掃描磁盤，最終得到數據，
讀取行指針和orderby列，對他們進行排序，然后掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的數據輸出

從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段。

取一批數據，要對磁盤進行了兩次掃描，眾所周知，I\O是很耗時的，所以在mysql4.1之后，出現了第二種改進的算法，就是單路排序。

單路排序

從磁盤讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然后掃描排序后的列表進行輸出，它的效率更快一些，避免了第二次讀取數據。并且把隨機IO變成了順序IO,但是它會使用更多的空間，因為它把每一行都保存在內存中了

結論及引申出的問題

由于單路是后出的，總體而言好過雙路

但是用單路有問題

在sort_buffer中，方法B比方法A要多占用很多空間，因為方法B是把所有字段都取出, 所以有可能取出的數據的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的數據，進行排序（創建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……從而多次I/O。

本來想省一次I/O操作，反而導致了大量的I/O操作，反而得不償失。

優化策略

增大max_length_for_sort_data參數的設置

增大sort_buffer_size參數的設置

Why

提高Order By的速度
?
1. Order by時select * 是一個大忌只Query需要的字段， 這點非常重要。在這里的影響是：
? 1.1 當Query的字段大小總和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 類型時，會用改進后的算法——單路排序， 否則用老算法——多路排序。
? 1.2 兩種算法的數據都有可能超出sort_buffer的容量，超出之后，會創建tmp文件進行合并排序，導致多次I/O，但是用單路排序算法的風險會更大一些,所以要提高sort_buffer_size。
?
2. 嘗試提高 sort_buffer_size
不管用哪種算法，提高這個參數都會提高效率，當然，要根據系統的能力去提高，因為這個參數是針對每個進程的
?
3. 嘗試提高 max_length_for_sort_data
提高這個參數， 會增加用改進算法的概率。但是如果設的太高，數據總容量超出sort_buffer_size的概率就增大，明顯癥狀是高的磁盤I/O活動和低的處理器使用率.?
?

?

總結

以上是生活随笔為你收集整理的为排序使用索引OrderBy优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。