目錄

order by 優(yōu)化

概述

測(cè)試

優(yōu)化原則

group by 優(yōu)化

測(cè)試

優(yōu)化原則

---

order by 優(yōu)化

概述

MySQL的排序，有兩種方式：

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

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

測(cè)試

假設(shè)現(xiàn)在在tb_user表中根據(jù)年齡或電話(huà)號(hào)碼來(lái)排序：?（age和phone均無(wú)索引）

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 都沒(méi)有索引，所以此時(shí)再排序時(shí)，出現(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進(jìn)行升序排序：

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

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

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

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

也出現(xiàn) Using index， 但是此時(shí)Extra中出現(xiàn)了 Backward index scan，這個(gè)代表反向掃描索引，因?yàn)樵贛ySQL中我們創(chuàng)建的索引，默認(rèn)索引的葉子節(jié)點(diǎn)是從小到大排序的，而此時(shí)我們查詢(xún)排序時(shí)，是從大到小，所以，在降序排序掃描時(shí)，就是反向掃描，就會(huì)出現(xiàn) Backward index scan。

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

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

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

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

因?yàn)樵趧?chuàng)建索引的時(shí)候， age是第一個(gè)字段，phone是第二個(gè)字段，所以排序時(shí)也該按照這個(gè)順序來(lái)，否則就會(huì)出現(xiàn) Using filesort。

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

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

因?yàn)閯?chuàng)建索引時(shí)，如果未指定順序，默認(rèn)都是按照升序排序的，而查詢(xún)時(shí)，一個(gè)升序，一個(gè)降序，此時(shí)就會(huì)出現(xiàn)Using filesort。

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

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

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

這時(shí)執(zhí)行SQL語(yǔ)句就達(dá)到我們的預(yù)期了：

---

優(yōu)化原則

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

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

group by 優(yōu)化

分組操作，我們主要來(lái)看看索引對(duì)于分組操作的影響。

測(cè)試

在沒(méi)有索引的情況下，執(zhí)行如下SQL，查詢(xún)執(zhí)行計(jì)劃：

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

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

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

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

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

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

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

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

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

優(yōu)化原則

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

1. 在分組操作時(shí)，可以通過(guò)索引來(lái)提高效率。
2. 分組操作時(shí)，索引的使用也是滿(mǎn)足最左前綴法則的。

---

END

---

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