top
Loading...
7.2.12.MySQL如何優化ORDERBY
7.2.12. MySQL如何優化ORDER BY

在某些情況中,MySQL可以使用一個索引來滿足ORDER BY子句,而不需要額外的排序。

即使ORDER BY不確切匹配索引,只要WHERE子句中的所有未使用的索引部分和所有額外的ORDER BY 列為常數,就可以使用索引。下面的查詢使用索引來解決ORDER BY部分:

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;

在某些情況下,MySQL不能使用索引來解決ORDER BY,盡管它仍然使用索引來找到匹配WHERE子句的行。這些情況包括:

·         對不同的關鍵字使用ORDER BY

·                SELECT * FROM t1 ORDER BY key1, key2

·         對關鍵字的非連續元素使用ORDER BY

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

·         混合ASCDESC

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

·         用于查詢行的關鍵字與ORDER BY中所使用的不相同:

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

·         你正聯接許多表,并且ORDER BY中的列并不是全部來自第1個用于搜索行的非常量表。(這是EXPLAIN輸出中的沒有const聯接類型的第1個表)

·         有不同的ORDER BYGROUP BY表達式。

·         使用的表索引的類型不能按順序保存行。例如,對于HEAP表的HASH索引情況即如此。

通過EXPLAIN SELECT ...ORDER BY,可以檢查MySQL是否可以使用索引來解決查詢。如果Extra列內有Using filesort,則不能解決查詢。參見7.2.1節,“EXPLAIN語法(獲取關于SELECT的信息)”。

文件排序優化不僅用于記錄排序關鍵字和行的位置,并且還記錄查詢需要的列。這樣可以避免兩次讀取行。文件排序算法的工作象這樣:

1.    讀行匹配WHERE子句的行,如前面所示。

2.    對于每個行,記錄構成排序關鍵字和行位置的一系列值,并且記錄查詢需要的列。

3.    根據排序關鍵字排序元組

4.    按排序的順序檢索行,但直接從排序的元組讀取需要的列,而不是再一次訪問表。

該算法比以前版本的Mysql有很大的改進。

為了避免速度變慢,該優化只用于排序元組中的extra列的總大小不超過max_length_for_sort_data系統變量值的時候。(將該變量設置得太高的的跡象是將看到硬盤活動太頻繁而CPU活動較低)

如果想要增加ORDER BY的速度,首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能,可以嘗試下面的策略:

·         增加sort_buffer_size變量的大小。

·         增加read_rnd_buffer_size變量的大小。

·         更改tmpdir指向具有大量空閑空間的專用文件系統。該選項接受幾個使用round-robin(循環)模式的路徑。在Unix中路徑應用冒號(:)區間開,在WindowsNetWareOS/2中用分號()。可以使用該特性將負載均分到幾個目錄中。注釋:路徑應為位于不同物理硬盤上的文件系統的目錄,而不是同一硬盤的不同的分區。

默認情況下,MySQL排序所有GROUP BY col1col2...查詢的方法如同在查詢中指定ORDER BY col1col2...。如果顯式包括一個包含相同的列的ORDER BY子句,MySQL可以毫不減速地對它進行優化,盡管仍然進行排序。如果查詢包括GROUP BY但你想要避免排序結果的消耗,你可以指定ORDER BY NULL禁止排序。例如:

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

作者:mysql.com
來源:http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
北斗有巢氏 有巢氏北斗