Orderby 排序優化

zhangdeTalk發表於2019-12-30

在日常的業務開發中，order by 排序是少不了的。但要寫出高效的排序SQL，需要先花點精力和時間來了解排序的底層原理，這樣才能找到優化排序的好策略。

排序的方式

index（索引排序，效能最佳）

儘可能使用索引欄位來排序

filesort（檔案排序）

2.1 雙路排序

MySQL4.1 之前的版本，通過兩次掃描磁碟，最終得到資料
先從磁碟中讀取行指標和 order by 列，並對它們進行排序，然後掃描已經排好序的列表，按照列表中的值重新從列表中讀出（再一次從磁碟中讀），要對磁碟進行兩次掃描，I\O是很耗時的。

2.2 單路排序

MySQL4.1 之後，增加的更優排序演算法
從磁碟讀取查詢需要的所有列，按照order by列在buffer（緩衝區）對它們進行排序，然後掃描排序後的列表進行輸出
它的效率要更快一些，避免了第二次讀取資料（從磁碟讀）並且把隨機I\O變成了順序I\O
但是它會使用過多空間，因為它把每一行都儲存在記憶體中了

不足：
在sort_buffer中，單路演算法比雙路演算法要多佔用很多空間
因為單路演算法是把所有欄位都取出，所以有可能取出的資料總大小超出了 sort_buffer（MySQL會給每個執行緒分配一塊記憶體用於排序） 的容量，導致每次只能取 sort_buffer 容量大小的資料，進行排序（建立tmp檔案，多路合併），排完再取出
sort_buffer容量太小，再排......從而多次I\O操作
本想著省一次I\O操作，反而導致了大量的I\O操作，反而得不償失。

使用單路排序滿足的條件：
1. 查詢語句所取出的欄位型別大小總和要小於 max_length_for_sort_data
2. 排序欄位中不包含text和blob型別

優化策略

order by時，只query需要的欄位

1. 當query的欄位大小總和小於max_length_for_sort_data，而且排序欄位不是TEXT｜BLOB型別，會使用單路排序演算法，否則使用多路排序演算法
2. 兩種演算法的資料都有可能超出sort_buffer的容量，超出之後，建立tmp檔案進行合併排序，導致多次的I\O，但是使用單路排序的風險更大，所以要提高sort_buffer_size

嘗試提高sort_buffer_size

不管使用哪種演算法，提高這個引數都會提高效率
要根據系統的自身能力去提高，因為這個引數是針對每個程式的

嘗試提高max_length_for_sort_data

提高這個引數，會增加用改進演算法的概率。
但如果設定得太高，資料總容量超出sort_buffer_size的概率會增大，明顯症狀是高的磁碟I\O活動和低的處理器使用率

例項

資料表

*************************** ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(10) NOT NULL DEFAULT '0',
`city` varchar(20) NOT NULL,
`addr` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_age_city` (`name`,`age`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

order by能使用索引最左字首

* select id,name,age,city from user order by name;
* select id,name,age,city from user order by name,age,city;
* explain select id,name,age,city from user order by name desc,age desc,city desc;

Orderby 排序優化

如果where使用索引的最左字首定義為常量，則order by 能使用索引

* select * from user where name = 'zhangsan' order by age,city;
* select * from user where name = 'zhangsan' and age = 20 order by city;
* select * from user where name = 'zhangsan' and age > 20 order by age,city;

Orderby 排序優化

不能使用索引進行排序

select * from user order by name,age,city;//query*欄位
select * from user order by addr;//非索引欄位排序
select * from user order by name,addr;//含有非索引欄位
select * from user where age = 20 order by city;//跳過了name欄位，違反最左字首法則
select * from user where name = 'zhangsan' order by city;//跳過了age欄位，違反最左字首法則
select * from user where name = 'zhangsan' order by age,addr;//含有非索引欄位

Orderby 排序優化