MySQL排序優(yōu)化

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

1.通過排序操作；

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

一、使用索引生成排序

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

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

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

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

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

即使ORDER BY子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因?yàn)樗饕牡谝涣斜恢付橐粋€(gè)常數(shù)。這個(gè)查詢?cè)诓煌姹镜腗ySQL中可能會(huì)有不同的表現(xiàn)。因?yàn)閟elect列中的staff_id不在排序索引中，而且不是主鍵，沒有實(shí)現(xiàn)索引覆蓋，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序全表掃描慢，有些版本的MySQL可能會(huì)通過計(jì)算成本選擇文件排序 (filesort)。如果只有rental_id列就沒問題了，前面說過主鍵有額外的“紅利”。

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

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

--排序字段包含一個(gè)不在索引中的字段
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多個(gè)等值條件查詢，in對(duì)于排序認(rèn)為是一種范圍查詢
WHERE rental_date='1999-05-05' AND inventory_id in (xx,xx)
ORDER BY customer_id 

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

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

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

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

二、文件排序

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

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

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

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

最新評(píng)論