目錄

order by 優(yōu)化

概述

測試

優(yōu)化原則

group by 優(yōu)化

測試

優(yōu)化原則

---

order by 優(yōu)化

概述

MySQL的排序，有兩種方式：

• Using filesort : 通過表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū)sortbuffer中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。
• Using index : 通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高。

對于以上的兩種排序方式，Using index的性能高，而Using filesort的性能低，我們在優(yōu)化排序
操作時，盡量要優(yōu)化為 Using index。

測試

假設現(xiàn)在在tb_user表中根據(jù)年齡或電話號碼來排序：?（age和phone均無索引）

explain select id,age,phone from tb_user order by age ;

explain select id,age,phone from tb_user order by age, phone ;

?由于 age, phone 都沒有索引，所以此時再排序時，出現(xiàn)Using filesort， 排序性能較低。
?

創(chuàng)建索引

-- 創(chuàng)建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

創(chuàng)建索引后，根據(jù)age和phone進行升序排序：

explain select id,age,phone from tb_user order by age,phone;

?
建立索引之后，再次進行排序查詢，就由原來的Using filesort，變?yōu)榱?Using index，性能就是比較高的了。

再根據(jù)age和phone進行降序排序：

explain select id,age,phone from tb_user order by age desc ,phone desc;

也出現(xiàn) Using index， 但是此時Extra中出現(xiàn)了 Backward index scan，這個代表反向掃描索引，因為在MySQL中我們創(chuàng)建的索引，默認索引的葉子節(jié)點是從小到大排序的，而此時我們查詢排序時，是從大到小，所以，在降序排序掃描時，就是反向掃描，就會出現(xiàn) Backward index scan。

在MySQL8版本中，支持降序索引，我們也可以創(chuàng)建降序索引。

根據(jù)phone，age進行升序排序，phone在前，age在后：

explain select id,age,phone from tb_user order by phone , age;

排序時,也需要滿足最左前綴法則,否則也會出現(xiàn) filesort。

因為在創(chuàng)建索引的時候， age是第一個字段，phone是第二個字段，所以排序時也該按照這個順序來，否則就會出現(xiàn) Using filesort。

根據(jù)age, phone進行降序一個升序，一個降序：

explain select id,age,phone from tb_user order by age asc , phone desc ;

因為創(chuàng)建索引時，如果未指定順序，默認都是按照升序排序的，而查詢時，一個升序，一個降序，此時就會出現(xiàn)Using filesort。

為了解決上述的問題，我們可以創(chuàng)建一個索引，這個聯(lián)合索引中 age 升序排序，phone 倒序排序。

創(chuàng)建聯(lián)合索引(age 升序排序，phone 倒序排序) ：

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

這時執(zhí)行SQL語句就達到我們的預期了：

---

優(yōu)化原則

由上述的測試,我們得出order by優(yōu)化原則:

1. 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則。
2. 盡量使用覆蓋索引。
3. 多字段排序, 一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC）。
4. 如果不可避免的出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當增大排序緩沖區(qū)大小sort_buffer_size(默認256k)。?

group by 優(yōu)化

分組操作，我們主要來看看索引對于分組操作的影響。

測試

在沒有索引的情況下，執(zhí)行如下SQL，查詢執(zhí)行計劃：

explain select profession , count(*) from tb_user group by profession ; 

與order by優(yōu)化類似，Using? temporary也是效率比較低的，我們要利用索引將其變?yōu)閁sing index。

我們針對于 profession ， age， status 創(chuàng)建一個聯(lián)合索引：

create index idx_user_pro_age_sta on tb_user(profession , age , status);

然后再執(zhí)行前面相同的SQL查看執(zhí)行計劃：

explain select profession , count(*) from tb_user group by profession ; 

同樣，如果僅僅根據(jù)age分組，就會出現(xiàn) Using temporary ；

而如果是根據(jù)profession,age兩個字段同時分組，則不會出現(xiàn) Using temporary。

原因是對于分組操作，在聯(lián)合索引中，也是符合最左前綴法則的。

優(yōu)化原則

所以，在分組操作中，我們需要通過以下兩點進行優(yōu)化，以提升性能：

1. 在分組操作時，可以通過索引來提高效率。
2. 分組操作時，索引的使用也是滿足最左前綴法則的。

---

END

---

學習自：黑馬程序員——MySQL數(shù)據(jù)庫課程