MySQL中Order By索引的優化

本篇內容介紹了“MySQL中Order By索引的優化”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

在一些情況下，MySQL可以直接使用索引來滿足一個ORDER BY 或GROUP BY 子句而無需做額外的排序。盡管ORDER BY 不是和索引的順序準確匹配，索引還是可以被用到，只要不用的索引部分和所有的額外的ORDER BY 字段在WHERE 子句中都被包括了。

使用索引的MySQL Order By

下列的幾個查詢都會使用索引來解決ORDER BY 或GROUP BY 部分：

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

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

SELECT * FROM t1 WHERE key_part1=constant GROUP 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

在另一些情況下，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 中的字段都不全是來自第一個非常數的表中（也就是說，在EXPLAIN 分析的結果中的第一個表的連接類型不是const）。

* 使用了不同的ORDER BY 和GROUP BY 表達式。

* 表索引中的記錄不是按序存儲。例如，HASH 和HEAP 表就是這樣。

通過執行EXPLAIN SELECT ... ORDER BY，就知道MySQL是否在查詢中使用了索引。如果Extra 字段的值是Using filesort，則說明MySQL無法使用索引。詳情請看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。當必須對結果進行排序時，MySQL 4.1以前 它使用了以下filesort 算法：

1. 根據索引鍵讀取記錄，或者掃描數據表。那些無法匹配WHERE 分句的記錄都會被略過。

2. 在緩沖中每條記錄都用一個‘對’存儲了2個值（索引鍵及記錄指針）。緩沖的大小依據系統變量sort_buffer_size 的值而定。

3. 當緩沖慢了時，就運行qsort（快速排序）并將結果存儲在臨時文件中。將存儲的塊指針保存起來（如果所有的‘對’值都能保存在緩沖中，就無需創建臨時文件了）。

4. 執行上面的操作，直到所有的記錄都讀取出來了。

5. 做一次多重合并，將多達MERGEBUFF（7）個區域的塊保存在另一個臨時文件中。重復這個操作，直到所有在第一個文件的塊都放到第二個文件了。

6. 重復以上操作，直到剩余的塊數量小于MERGEBUFF2 (15)。

7. 在最后一次多重合并時，只有記錄的指針（排序索引鍵的最后部分）寫到結果文件中去。

8. 通過讀取結果文件中的記錄指針來按序讀取記錄。想要優化這個操作，MySQL將記錄指針讀取放到一個大的塊里，并且使用它來按序讀取記錄，將記錄放到緩沖中。緩沖的大小由系統變量read_rnd_buffer_size 的值而定。這個步驟的代碼在源文件`sql/records.cc' 中。

這個逼近算法的一個問題是，讀取了2次記錄：一次是估算WHERE 分句時，第二次是排序時。盡管第一次都成功讀取記錄了（例如，做了一次全表掃描），第二次是隨機的讀取（索引鍵已經排好序了，但是記錄并沒有）。在MySQL 4.1 及更新版本中，filesort 優化算法用于記錄中不只包括索引鍵值和記錄的位置，還包括查詢中要求的字段。這么做避免了需要2次讀取記錄。改進的filesort 算法做法大致如下：

1. 跟以前一樣，讀取匹配WHERE 分句的記錄。

2. 相對于每個記錄，都記錄了一個對應的；‘元組’信息信息，包括索引鍵值、記錄位置、以及查詢中所需要的所有字段。

3. 根據索引鍵對‘元組’信息進行排序。

4. 按序讀取記錄，不過是從已經排序過的‘元組’列表中讀取記錄，而非從數據表中再讀取一次。

使用改進后的filesort 算法相比原來的，‘元組’比‘對’需要占用更長的空間，它們很少正好適合放在排序緩沖中（緩沖的大小是由sort_buffer_size 的值決定的）。因此，這就可能需要有更多的I/O操作，導致改進的算法更慢。為了避免使之變慢，這種優化方法只用于排序‘元組’中額外的字段的大小總和超過系統變量max_length_for_sort_data 的情況（這個變量的值設置太高的一個表象就是高磁盤負載低CPU負載）。想要提高ORDER BY 的速度，首先要看MySQL能否使用索引而非額外的排序過程。如果不能使用索引，可以試著遵循以下策略：

* 增加sort_buffer_size 的值。

* 增加read_rnd_buffer_size 的值。

* 修改tmpdir，讓它指向一個有很多剩余空間的專用文件系統。

如果使用MySQL 4.1或更新，這個選項允許有多個路徑用循環的格式。各個路徑之間在Unix 上用冒號（':'）分隔開來，在Windows，NetWare以及OS/2 上用分號（';'）。可以利用這個特性將負載平均分攤給幾個目錄。注意：這些路徑必須是分布在不同物理磁盤上的目錄，而非在同一個物理磁盤上的不同目錄

“MySQL中Order By索引的優化”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！