MySQL排序優(yōu)化

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

1.通過(guò)排序操作；

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

一、使用索引生成排序

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

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

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

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

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

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

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

--排序方向與索引列不一致
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

--排序不滿(mǎn)足最左法則
WHERE rental_date='1999-05-05'
ORDER BY customer_id 

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

#inventory_id 使用了in多個(gè)等值條件查詢(xún)，in對(duì)于排序認(rèn)為是一種范圍查詢(xú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í)際無(wú)法使用索引排序。

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

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

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

二、文件排序

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

在關(guān)聯(lián)查詢(xún)的時(shí)候如果需要排序，MySQL會(huì)分兩種情況處理這樣的文件排序。如果ORDER BY子句的所有列都來(lái)自關(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子句的所有列都來(lái)自關(guān)聯(lián)的第一張表。

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

最新評(píng)論