在日常的業(yè)務(wù)開發(fā)中，order by 排序是少不了的。但要寫出高效的排序SQL，需要先花點(diǎn)精力和時(shí)間來(lái)了解排序的底層原理，這樣才能找到優(yōu)化排序的好策略。
排序的方式
index（索引排序，性能最佳）
盡可能使用索引字段來(lái)排序
filesort（文件排序）
2.1 雙路排序
MySQL4.1 之前的版本，通過(guò)兩次掃描磁盤，最終得到數(shù)據(jù)。先從磁盤中讀取行指針和 order by 列，并對(duì)它們進(jìn)行排序，然后掃描已經(jīng)排好序的列表，按照列表中的值重新從列表中讀出（再一次從磁盤中讀），要對(duì)磁盤進(jìn)行兩次掃描，IO是很耗時(shí)的。
2.2 單路排序
MySQL4.1 之后，增加的更優(yōu)排序算法，從磁盤讀取查詢需要的所有列，按照order by列在buffer（緩沖區(qū)）對(duì)它們進(jìn)行序，然后掃描排序后的列表進(jìn)行輸出，它的效率要更快一些，避免了第二次讀取數(shù)據(jù)（從磁盤讀）并且把隨機(jī)IO變成了順序IO，但是它會(huì)使用過(guò)多空間，因?yàn)樗衙恳恍卸急４嬖趦?nèi)存中了。
不足：
在sort_buffer中，單路算法比雙路算法要多占用很多空間，因?yàn)閱温匪惴ㄊ前阉凶侄味既〕觯杂锌赡苋〕龅臄?shù)據(jù)總大小超出了，sort_buffer（MySQL會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序） 的容量，導(dǎo)致每次只能取 sort_buffer 容量大小的數(shù)據(jù)，進(jìn)行排序（創(chuàng)建tmp文件，多路合并），排完再取出。sort_buffer容量太小，再排......從而多次IO操作，本想著省一次IO操作，反而導(dǎo)致了大量的IO操作，反而得不償失。
使用單路排序滿足的條件：
1. 查詢語(yǔ)句所取出的字段類型大小總和要小于max_length_for_sort_data2. 排序字段中不包含text和blob類型
優(yōu)化策略
3.1 只query需要的字段
1. 當(dāng)query的字段大小總和小于max_length_for_sort_data，而且排序字段不是TEXT｜BLOB類型，會(huì)使用單路排序算法，否則使用多路排序算法。2. 兩種算法的數(shù)據(jù)都有可能超出sort_buffer的容量，超出之后，創(chuàng)建tmp文件進(jìn)行合并排序，導(dǎo)致多次的IO，但是使用單路排序的風(fēng)險(xiǎn)更大，所以要提高sort_buffer_size。
3.2 嘗試提高sortbuffersize
不管使用哪種算法，提高這個(gè)參數(shù)都會(huì)提高效率，要根據(jù)系統(tǒng)的自身能力去提高，因?yàn)檫@個(gè)參數(shù)是針對(duì)每個(gè)進(jìn)程的。
3.3 嘗試提高maxlengthforsortdata
提高這個(gè)參數(shù)，會(huì)增加用改進(jìn)算法的概率。但如果設(shè)置得太高，數(shù)據(jù)總?cè)萘砍鰏ort_buffer_size的概率會(huì)增大，明顯癥狀是高的磁盤IO活動(dòng)和低的處理器使用率。
實(shí)例
數(shù)據(jù)表
*************************** *************************** Table: userCreate 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;
如果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;
不能使用索引進(jìn)行排序
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;//跳過(guò)了name字段，違反最左前綴法則select * from user where name = 'zhangsan' order by city;//跳過(guò)了age字段，違反最左前綴法則select * from user where name = 'zhangsan' order by age,addr;//含有非索引字段
