在某些情況中,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;
· 混合ASC和DESC:
· 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 BY和GROUP 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中路徑應用冒號(‘:’)區間開,在Windows、NetWare和OS/2中用分號(‘;’)。可以使用該特性將負載均分到幾個目錄中。注釋:路徑應為位于不同物理硬盤上的文件系統的目錄,而不是同一硬盤的不同的分區。
默認情況下,MySQL排序所有GROUP BY col1,col2,...查詢的方法如同在查詢中指定ORDER BY col1,col2,...。如果顯式包括一個包含相同的列的ORDER BY子句,MySQL可以毫不減速地對它進行優化,盡管仍然進行排序。如果查詢包括GROUP BY但你想要避免排序結果的消耗,你可以指定ORDER BY NULL禁止排序。例如:
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;