本篇文章我們將了解ORDER BY語(yǔ)句的優(yōu)化，在此之前，你需要對(duì)索引有基本的了解，不了解的老少爺們可以先看一下我之前寫(xiě)過(guò)的索引相關(guān)文章?，F(xiàn)在讓我們開(kāi)始吧。

MySQL中的兩種排序方式

1.通過(guò)有序索引順序掃描直接返回有序數(shù)據(jù)

因?yàn)樗饕慕Y(jié)構(gòu)是B+樹(shù)，索引中的數(shù)據(jù)是按照一定順序進(jìn)行排列的，所以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時(shí)，Extra顯示為Using index。

2.Filesort排序，對(duì)返回的數(shù)據(jù)進(jìn)行排序

所有不是通過(guò)索引直接返回排序結(jié)果的操作都是Filesort排序，也就是說(shuō)進(jìn)行了額外的排序操作。EXPLAIN分析查詢時(shí)，Extra顯示為Using filesort。

ORDER BY優(yōu)化的核心原則

盡量減少額外的排序，通過(guò)索引直接返回有序數(shù)據(jù)。

ORDER BY優(yōu)化實(shí)戰(zhàn)

用于實(shí)驗(yàn)的customer表的索引情況：

首先要注意：

MySQL一次查詢只能使用一個(gè)索引，如果要對(duì)多個(gè)字段使用索引，建立復(fù)合索引。

ORDER BY優(yōu)化

1.查詢的字段，應(yīng)該只包含此次查詢使用的索引字段和主鍵，其余的非索引字段和索引字段作為查詢字段則不會(huì)使用索引。

只查詢用于排序的索引字段，可以利用索引排序：

explain select store_id,email from customer order by store_id,email;

但是要注意，排序字段在多個(gè)索引中,無(wú)法使用索引排序,查詢一次只能使用一個(gè)索引：

explain select store_id,email,last_name from customer order by store_id,email,last_name;

只查詢用于排序的索引字段和主鍵，可以利用索引排序：

畫(huà)外音：MySQL默認(rèn)的InnoDB引擎在物理上采用聚集索引這種方式，按主鍵進(jìn)行搜索，所以InnoDB引擎要求表必須有主鍵，即使沒(méi)有顯式指定主鍵，InnoDB引擎也會(huì)生成唯一的隱式主鍵，也就是說(shuō)索引中必定有主鍵。

explain select customer_id,store_id,email from customer order by store_id,email;

查詢用于排序的索引字段和主鍵之外的字段，不會(huì)利用索引排序：

explain select store_id,email,last_name from customer order by store_id,email;

explain select * from customer order by store_id,email;

WHERE + ORDER BY 優(yōu)化

1.排序字段在多個(gè)索引中,無(wú)法利用索引排序

排序字段在多個(gè)索引（不在同一個(gè)索引）中,無(wú)法利用索引排序：

explain select * from customer where last_name='swj' order by last_name,store_id;

畫(huà)外音：當(dāng)排序字段不在同一個(gè)索引時(shí)，無(wú)法滿足在一顆B+樹(shù)中完成排序，必須再進(jìn)行一次額外的排序

排序字段在一個(gè)索引中,并且WHERE條件和ORDER BY使用相同的索引,可以利用索引排序：

explain select * from customer where last_name='swj' order by last_name;

當(dāng)然組合索引也可以利用索引排序：

注意字段store_id,email在一個(gè)組合索引中

explain select * from customer where store_id = 5 order by store_id,email;

2.排序字段順序與索引列順序不一致,無(wú)法利用索引排序

畫(huà)外音：這條是針對(duì)組合索引而言的，我們都知道使用組合索引必要要遵循最左原則，WHERE子句必須有索引中第一列，雖然ORDER BY子句沒(méi)有這個(gè)要求，但是也要求排序字段順序和組合索引列順序匹配。我們平常在使用組合索引的時(shí)候，一定要養(yǎng)成按照組合索引列順序書(shū)寫(xiě)的好習(xí)慣。

排序字段順序與索引列順序不一致,無(wú)法利用索引排序：

explain select * from customer where store_id > 5 order by email,store_id;

應(yīng)該確保排序字段順序與索引列順序一致,這樣可以利用索引排序：

explain select * from customer where store_id > 5 order by store_id,email;

ORDER BY子句不要求必須索引中第一列,沒(méi)有仍然可以利用索引排序。但是有個(gè)前提條件，只有在等值過(guò)濾時(shí)才可以，范圍查詢時(shí)不可以：

explain select * from customer where store_id = 5 order by email;

explain select * from customer where store_id > 5 order by email;

畫(huà)外音：

其原因其實(shí)也很簡(jiǎn)單，范圍查詢時(shí)，第一列a肯定是排序好的（默認(rèn)是升序），而第二個(gè)字段b其實(shí)就不是排序的了。但是如果a字段有相同的值時(shí)，那么b字段就是排序的了。所以如果是范圍查詢，就只能對(duì)b做一次額外的排序。

3.升降序不一致,無(wú)法利用索引排序

ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否則無(wú)法利用索引排序。

explain select * from customer where store_id > 5 order by store_id,email;

explain select * from customer where store_id > 5 order by store_id desc,email desc;

explain select * from customer where store_id > 5 order by store_id desc,email asc;

總結(jié)：

上面的優(yōu)化其實(shí)可以匯總為：WHERE條件和ORDER BY使用相同的索引，并且ORDER BY的順序和索引順序相同，并且ORDER BY的字段都是升序或者降序。否則肯定需要額外的排序操作，就會(huì)出現(xiàn)Filesort。

Filesort優(yōu)化

通過(guò)創(chuàng)建合適的索引能夠減少Filesort的出現(xiàn)，但是在某些情況下，無(wú)法完全讓Filesort消失，此時(shí)只能想辦法加快Filesort的操作。

Filesort的兩種排序算法：

1.兩次掃描算法

首先根據(jù)條件取出排序字段和行指針信息，之后在排序區(qū)sort buffer中排序。這種排序算法需要訪問(wèn)兩次數(shù)據(jù)，第一次獲取排序字段和行指針信息，第二次根據(jù)行指針獲取記錄，第二次讀取操作可能會(huì)導(dǎo)致大量隨即I/O操作。優(yōu)點(diǎn)是排序的時(shí)候內(nèi)存開(kāi)銷(xiāo)較小。

2.一次掃描算法

一次性取出滿足條件的行的所有字段，然后在排序區(qū)sort buffer中排序后直接輸出結(jié)果集。排序的時(shí)候內(nèi)存開(kāi)銷(xiāo)比較大，但是排序效率比兩次掃描算法要高。

根據(jù)兩種排序算法的特性，適當(dāng)加大系統(tǒng)變量max_length_for_sort_data的值，能夠讓MySQL選擇更優(yōu)化的Filesort排序算法。并且在書(shū)寫(xiě)SQL語(yǔ)句時(shí)，只使用需要的字段，而不是SELECT * 所有的字段，這樣可以減少排序區(qū)的使用，提高SQL性能。

總結(jié)

以上所述是小編給大家介紹的Mysql優(yōu)化order by語(yǔ)句的方法詳解，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

最新評(píng)論