本篇文章我們將了解ORDER BY語句的優(yōu)化，在此之前，你需要對索引有基本的了解，不了解的老少爺們可以先看一下我之前寫過的索引相關(guān)文章。現(xià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性能。

總結(jié)

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

最新評論