MySQL排序優(yōu)化

MySQL有兩種方式生成有序的結(jié)果：

1.通過排序操作；

2.按索引順序掃描。如果EXPLAIN出來的type列的值為“index”，則說明使用了索引掃描來做排序。（但如果不為“index”，也不能說明沒有使用索引掃描來做排序。）

一、使用索引生成排序

MySQL可以使用同一個索引既滿足排序(ORDER BY)，又用于查詢(WHERE)操作的。因此如果可以，設(shè)計索引的時候盡可能考慮同時滿足兩種任務(wù)。

索引掃描本身是很快的，但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就回表查詢一次對應(yīng)的行。這基本都是隨機I/O，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序全表掃描慢。索引覆蓋還有個額外的紅利，就是主鍵。即使索引列中不包含主鍵，但因為二級索引的葉子節(jié)點包含了主鍵的值，所以也能用于對主鍵做覆蓋查詢。

只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣，MySQL才能使用索引對結(jié)果進行排序。如果查詢關(guān)聯(lián)多張表，則只有當(dāng)ORDER BY子句引用的字段全部為第一張表時，才能使用索引做排序。

ORDER BY子句使用索引的限制和where查詢是一樣的，都需要滿足左前綴要求。但有一種情況ORDER BY子句可以不滿足左前綴要求。如果where子句或者join子句中對相關(guān)列指定了常量，就可以彌補索引的不足。

例如，有一張租賃表rental在列 (rental_data, inventory_id, customer_id) 上有索引?？梢允褂迷撍饕秊橐韵虏樵冏雠判?。EXPLAIN中可以看到?jīng)]有出現(xiàn)文件排序 (filesort)。

即使ORDER BY子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因為索引的第一列被指定為一個常數(shù)。這個查詢在不同版本的MySQL中可能會有不同的表現(xiàn)。因為select列中的staff_id不在排序索引中，而且不是主鍵，沒有實現(xiàn)索引覆蓋，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序全表掃描慢，有些版本的MySQL可能會通過計算成本選擇文件排序 (filesort)。如果只有rental_id列就沒問題了，前面說過主鍵有額外的“紅利”。

下面是一些不能使用索引做排序的例子：

--排序方向與索引列不一致
WHERE rental_date='1999-05-05'
ORDER BY inventory_id DESC,customer_id ASC

--排序字段包含一個不在索引中的字段
WHERE rental_date='1999-05-05'
ORDER BY inventory_id ,staff_id

--排序不滿足最左法則
WHERE rental_date='1999-05-05'
ORDER BY customer_id 

--rental_date使用了范圍查詢，后續(xù)索引列失效
WHERE rental_date>'1999-05-05'
ORDER BY inventory_id,customer_id 

#inventory_id 使用了in多個等值條件查詢，in對于排序認為是一種范圍查詢
WHERE rental_date='1999-05-05' AND inventory_id in (xx,xx)
ORDER BY customer_id 

下面的例子理論上是可以使用索引進行關(guān)聯(lián)排序的，但由于優(yōu)化器在優(yōu)化時將film_actor表當(dāng)作關(guān)聯(lián)的第二張表，所以實際無法使用索引排序。

應(yīng)該盡可能使用更多的索引列。索引只能使用索引的最左前綴，當(dāng)遇到第一個范圍查詢，就會停止使用后面的索引列。MYSQL無法再使用范圍列后面的其他字段進行排序了，但對于“多個等值條件查詢”則沒有限制，所以可以考慮把范圍查詢語句改寫成IN列表的形式。但是IN在where條件中不算范圍查詢，但對于order by不適用，對于排序認為是一種范圍查詢。在前面舉的不能使用索引做排序的例子的最后一個就說明了這個問題。

如果排序查詢中有LIMIT，那么LIMIT也會在排序之后應(yīng)用，所以即使需要返回較少的數(shù)據(jù)，需要排序的數(shù)據(jù)量仍然非常大（雖然MySQL后續(xù)版本做了優(yōu)化，根據(jù)實際情況拋棄不滿足條件的結(jié)果，然后再排序）。所以此時如果能使用索引排序是最好的選擇。

如果服務(wù)器能夠按需要順序讀取數(shù)據(jù)，那么就不再需要額外的排序操作，并且group by查詢也無需再做排序和將行按組進行聚合計算了。

二、文件排序

當(dāng)不能使用索引生成排序結(jié)果時，需要進行文件排序（filesort），如果數(shù)據(jù)量小則在內(nèi)存中進行，如果數(shù)據(jù)量大則需要使用磁盤，這兩種情況都叫文件排序（filesort）。如果需要排序的數(shù)據(jù)量小于“排序緩沖區(qū)”，就使用內(nèi)存“快速排序”。如果內(nèi)存不夠，就先將數(shù)據(jù)分塊，對每個獨立的塊使用“快速排序”，并將各個塊的排序結(jié)果存放正在磁盤上，然后將各個塊進行合并（mergge），最后返回排序結(jié)果。

在關(guān)聯(lián)查詢的時候如果需要排序，MySQL會分兩種情況處理這樣的文件排序。如果ORDER BY子句的所有列都來自關(guān)聯(lián)的第一張表，那么MySQL在關(guān)聯(lián)處理第一個表的時候就進行文件排序。如果是這樣，那么在MySQL的EXPLAIN結(jié)果中可以看到Extra字段會有“Using filesort”。除此以外的所有情況，MySQL都會先將關(guān)聯(lián)的結(jié)果存放到一個臨時表中，然后在所有關(guān)聯(lián)都結(jié)束后，再進行文件排序。這種情況下，在MySQL的EXPLAIN結(jié)果中的Extra字段可以看到“Using temporary; Using filesort”。

所以盡量使ORDER BY子句的所有列都來自關(guān)聯(lián)的第一張表。

到此這篇關(guān)于MySQL排序優(yōu)化詳細解析的文章就介紹到這了,更多相關(guān)MySQL排序優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論