這篇文章給大家介紹如何在MySQL中優(yōu)化order by語句，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

海珠網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營維護(hù)。創(chuàng)新互聯(lián)從2013年創(chuàng)立到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)。

MySQL中的兩種排序方式

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

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

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

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

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

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

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

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

首先要注意：

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

ORDER BY優(yōu)化

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

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

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

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

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

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

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

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

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

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.排序字段在多個索引中,無法利用索引排序

排序字段在多個索引（不在同一個索引）中,無法利用索引排序：

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

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

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

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

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

注意字段store_id,email在一個組合索引中

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

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

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

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

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子句不要求必須索引中第一列,沒有仍然可以利用索引排序。但是有個前提條件，只有在等值過濾時才可以，范圍查詢時不可以：

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

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

畫外音：

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

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

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

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的字段都是升序或者降序。否則肯定需要額外的排序操作，就會出現(xiàn)Filesort。

Filesort優(yōu)化

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

Filesort的兩種排序算法：

1.兩次掃描算法

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

2.一次掃描算法

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

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

關(guān)于如何在Mysql中優(yōu)化order by語句就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。