MySQL Order By索引優化方法

2024-07-24 12:49:09

字體：大中小

來源：轉載

供稿：網友

盡管 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' 中。

上一篇：MySQL Order By用法分享

下一篇：mysql中為用戶設置密碼的多種方法

發表評論

共有條評論