1. 全字段排序
2. rowid排序
3. 全字段排序和rowid排序
   3.1 聯(lián)合索引優(yōu)化
   3.2 覆蓋索引優(yōu)化
4. 優(yōu)先隊(duì)列算法
5. 優(yōu)化建議
   5.1 修改系統(tǒng)參數(shù)
   5.2 優(yōu)化sql

1. 全字段排序

CREATE TABLE `t` ( 
 `id` int(11) NOT NULL,
 `city` varchar(16) NOT NULL, 
 `name` varchar(16) NOT NULL, 
 `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL, 
  PRIMARY KEY (`id`), KEY `city` (`city`)) ENGINE=InnoDB;

如果要查詢city是杭州的所有人名字,并且按姓名排序返回前1000個(gè)人的姓名和年齡,sql可以這么寫:

mysql> explain select city,name,age from t where city='杭州' order by name limit 1000 ;
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | city          | city | 50      | const |    4000 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Extra 這個(gè)字段中的“Using filesort”表示的就是需要排序，MySQL 會給每個(gè)線程分配一塊內(nèi)存用于排序，稱為 sort_buffer。

為了說明這個(gè) SQL 查詢語句的執(zhí)行過程，我們先來看一下 city 這個(gè)索引的示意圖。

city 字段的索引示意圖.png

從圖中可以看到，滿足 city='杭州’條件的行，是從 ID_X 到 ID_(X+N) 的這些記錄。

通常情況下，這個(gè)語句執(zhí)行流程如下所示 ：

執(zhí)行流程.png

1. 初始化 sort_buffer，確定放入 name、city、age 這三個(gè)字段；
2. 從索引 city 找到第一個(gè)滿足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
3. 到主鍵 id 索引取出整行，取 name、city、age 三個(gè)字段的值，存入 sort_buffer 中；
4. 從索引 city 取下一個(gè)記錄的主鍵 id；
5. 重復(fù)步驟 3、4 直到 city 的值不滿足查詢條件為止，對應(yīng)的主鍵 id 也就是圖中的 ID_Y；
6. 對 sort_buffer 中的數(shù)據(jù)按照字段 name 做快速排序；
7. 按照排序結(jié)果取前 1000 行返回給客戶端。

圖中“按name排序 ”這個(gè)動作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和參數(shù) sort_buffer_size;

sort_buffer_size：

MySQL為排序開辟的內(nèi)存（sort_buffer）的大小。如果要排序的數(shù)據(jù)量小于sort_buffer_size，排序就在內(nèi)存中完成。若排序數(shù)據(jù)量太大，內(nèi)存放不下，則得利用磁盤臨時(shí)文件輔助排序。

如何確定一個(gè)排序語句是否使用臨時(shí)文件？

/* 打開optimizer_trace，只對本線程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值，若是mysql5.7之下，則使用 from INFORMATION_SCHEMA.SESSION_STATUS */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 執(zhí)行語句 */
select city,name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 輸出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;

/* @b保存Innodb_rows_read的當(dāng)前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 計(jì)算Innodb_rows_read差值 */
select @b-@a;

該方法是通過查看 OPTIMIZER_TRACE 的結(jié)果來確認(rèn)的，你可以從 number_of_tmp_files查看是否使用了臨時(shí)文件。

image.png

number_of_tmp_files：排序過程中使用的臨時(shí)文件數(shù)。為啥需要12個(gè)文件？內(nèi)存放不下時(shí)，就需要使用外部排序，外部排序一般使用歸并排序。
MySQL將需要排序的數(shù)據(jù)分成12份，每一份單獨(dú)排序后存在這些臨時(shí)文件中。然后把這12個(gè)有序文件再合并成一個(gè)有序的大文件。

如果 sort_buffer_size 超過了需要排序的數(shù)據(jù)量的大小，number_of_tmp_files就是0，表示排序可以直接在內(nèi)存中完成。

否則就需要放在臨時(shí)文件中排序。sort_buffer_size越小，需要分成的份數(shù)越多，number_of_tmp_files的值就越大。

• examined_rows：測試表中有4000條滿足city='上?！挠涗洠?examined_rows=4000：表示參與排序的行數(shù)是4000。
• sort_mode：排序過程對字符串做了“緊湊”處理。即使name字段的定義是varchar(16)，在排序過程中還是要按實(shí)際長度分配空間。

注意：為了避免對結(jié)論造成干擾，要把internal_tmp_disk_storage_engine設(shè)置成MyISAM。否則，select @b-@a的結(jié)果會顯示為4001。因?yàn)椴樵僌PTIMIZER_TRACE這個(gè)表時(shí)，需要用到臨時(shí)表。如果使用的是InnoDB引擎的話，把數(shù)據(jù)從臨時(shí)表取出來的時(shí)候，會讓Innodb_rows_read的值加1。

2. rowid排序

在上面的過程里，只對原表的數(shù)據(jù)讀了一遍，剩下的操作都是在sort_buffer和臨時(shí)文件中執(zhí)行的。

但這個(gè)算法有一個(gè)問題，如果查詢要返回的字段很多的話，那么sort_buffer里面要放的字段數(shù)太多，這樣內(nèi)存里能夠同時(shí)放下的行數(shù)很少，要分成很多個(gè)臨時(shí)文件，排序的性能會很差。所以如果單行很大，這個(gè)方法效率不夠好。

如果修改一個(gè)參數(shù)，讓MySQL采用另外一種算法。

SET max_length_for_sort_data = 16;

• max_length_for_sort_data：是控制用于排序的行數(shù)據(jù)的長度的一個(gè)參數(shù)。如果單行的長度超過這個(gè)值，MySQL就認(rèn)為單行太大，要換一個(gè)算法—rowid排序。

city、name、age 這三個(gè)字段的定義總長度是36，把max_length_for_sort_data設(shè)置為16時(shí)，新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主鍵id。

整個(gè)流程將會變成：

1. 初始化sort_buffer，確定放入兩個(gè)字段，即name和id；
2. 從索引city找到第一個(gè)滿足city='杭州’條件的主鍵id（ID_X）；
3. 到主鍵id索引取出整行，取name、id這兩個(gè)字段，存入sort_buffer中；
4. 從索引city取下一個(gè)記錄的主鍵id；
5. 重復(fù)步驟3、4直到不滿足city='杭州’條件為止（ID_Y）；
6. 對sort_buffer中的數(shù)據(jù)按照字段name進(jìn)行排序；
7. 遍歷排序結(jié)果，取前1000行，并按照id的值回到原表中取出city、name和age三個(gè)字段返回給客戶端。

rowId排序.png

對比全字段排序流程，rowid排序多訪問了一次表t的主鍵索引（步驟7）。

"filesort_execution": [
],
"filesort_summary": {
  "rows": 4000,
  "examined_rows": 4000,
  "number_of_tmp_files": 10,
  "sort_buffer_size": 32728,
  "sort_mode": "<sort_key,rowid>"
}

rowid排序OPTIMIZER_TRACE部分輸出中：examined_rows的值還是4000，表示用于排序的數(shù)據(jù)是4000行。但是select @b-@a這個(gè)語句的值變成5000了。

因?yàn)檫@時(shí)候除了排序過程外，在排序完成后，還要根據(jù)id去原表取值,語句是limit 1000，因此會多讀1000行。

其他的變化：

• sort_mode變成了<sort_key, rowid>，表示參與排序的只有name和id這兩個(gè)字段。
• number_of_tmp_files變成10，是因?yàn)檫@時(shí)候參與排序的行數(shù)雖然仍然是4000行，但是每一行都變小了，因此需要排序的總數(shù)據(jù)量就變小了，需要的臨時(shí)文件也相應(yīng)地變少了。

3. 全字段排序和rowid排序

• 如果MySQL擔(dān)心排序內(nèi)存太小，會影響排序效率，才會采用rowid排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取數(shù)據(jù)。
• 如果MySQL認(rèn)為內(nèi)存足夠大，會優(yōu)先選擇全字段排序，把需要的字段都放到sort_buffer中，這樣排序后就會直接從內(nèi)存里面返回查詢結(jié)果了，不用再回到原表去取數(shù)據(jù)。

也體現(xiàn)了MySQL的一個(gè)設(shè)計(jì)思想：如果內(nèi)存夠，就要多利用內(nèi)存，盡量減少磁盤訪問。對于InnoDB表來說，rowid排序會要求回表多造成磁盤讀，因此不會被優(yōu)先選擇。

實(shí)際上，并不是所有的order by語句都需要排序操作的。MySQL之所以需要生成臨時(shí)表，并且在臨時(shí)表上做排序操作，其原因是原來的數(shù)據(jù)都是無序的。如果能夠保證從city這個(gè)索引上取出來的行，都是按照name遞增排序的話，就可以不用再排序了。

3.1 聯(lián)合索引優(yōu)化

可以在這個(gè)市民表上創(chuàng)建一個(gè)city和name的聯(lián)合索引：

alter table t add index city_user(city, name);

city和name的聯(lián)合索引.png

在這個(gè)索引里面，依然可以用樹搜索的方式定位到第一個(gè)滿足city='杭州’的記錄，并且額外確保了接下來按順序取“下一條記錄”的遍歷過程中，只要city的值是杭州，name的值就一定是有序的。

這樣整個(gè)查詢過程的流程就變成：

1. 從索引(city,name)找到第一個(gè)滿足city='杭州’條件的主鍵id；
2. 到主鍵id索引取出整行，取name、city、age三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
3. 從索引(city,name)取下一個(gè)記錄主鍵id；
4. 重復(fù)步驟2、3，直到查到第1000條記錄，或者是不滿足city='杭州’條件時(shí)循環(huán)結(jié)束。

image.png

查看執(zhí)行計(jì)劃：

mysql> explain select city,name,age from test.t where city='杭州' order by name limit 1000;
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys  | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | city,city_user | city_user | 50      | const |    4000 | Using where |
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Extra字段中沒有Using filesort，需要排序。而且由于(city,name)這個(gè)聯(lián)合索引本身有序，所以這個(gè)查詢也不用把4000行全都讀一遍，只要找到滿足條件的前1000條記錄就可以退出了。

3.2 覆蓋索引優(yōu)化

• 覆蓋索引：索引上的信息足夠滿足查詢請求，不需要再回到主鍵索引上去取數(shù)據(jù)（無需回表查詢）。

針對這個(gè)查詢，可以創(chuàng)建一個(gè)city、name和age的聯(lián)合索引：

alter table t add index city_user_age(city, name, age);

執(zhí)行流程：

1. 從索引(city,name,age)找到第一個(gè)滿足city='杭州’條件的記錄，取出其中的city、name和age這三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
2. 從索引(city,name,age)取下一個(gè)記錄，同樣取出這三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
3. 重復(fù)執(zhí)行步驟2，直到查到第1000條記錄，或者是不滿足city='杭州’條件時(shí)循環(huán)結(jié)束。

image.png

引入(city,name,age)聯(lián)合索引后，查詢語句的執(zhí)行流程

mysql> explain select city,name,age from test.t where city='杭州' order by name limit 1000;
+----+-------------+-------+------+------------------------------+---------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys                | key           | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+------------------------------+---------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | city,city_user,city_user_age | city_user_age | 50      | const |    4000 | Using where; Using index |
+----+-------------+-------+------+------------------------------+---------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Extra字段里面多了“Using index”，表示的就是使用了覆蓋索引，性能上會快很多。

4. 優(yōu)先隊(duì)列算法

優(yōu)先隊(duì)列排序也成為堆排序，主要是針對order by ... limit M,N的優(yōu)化。雖然該排序算法需要掃描所有的記錄，但是對于sort_buffer_size來講該排序算法下僅僅需要M+N個(gè)元組的空間即可進(jìn)行排序，避免了sort buffer不夠而導(dǎo)致需要臨時(shí)文件進(jìn)行歸并排序的問題。對于升序，采用大頂堆，最終堆中的元素組成了最小的N個(gè)元素，對于降序，采用小頂堆，最終堆中的元素組成了最大的N的元素。

select city,name,age from test.t where city='杭州' order by name limit 3;

MySQL 5.6 版本引入的一個(gè)新的排序算法，即：優(yōu)先隊(duì)列排序算法。接下來，我們就看看為什么沒有使用臨時(shí)文件的算法，也就是歸并排序算法，而是采用了優(yōu)先隊(duì)列排序算法。

其實(shí)，我們現(xiàn)在的 SQL 語句，只需要取 name 值最小的 3 條數(shù)據(jù)。但是，如果使用歸并排序算法的話，雖然最終也能得到前 3 個(gè)值，但是這個(gè)算法結(jié)束后，已經(jīng)將 10000 行數(shù)據(jù)都排好序了。

那么問題來了，我們要3行，為啥給我們排10000行呢？也就是說有沒有更優(yōu)的處理方法：即優(yōu)先隊(duì)列排序算法。

只需要在內(nèi)存中維護(hù)大小為3的大頂堆，就可以實(shí)現(xiàn)。

5. 優(yōu)化建議

5.1 修改系統(tǒng)參數(shù)

• sort_buffer_size

對于多路并歸的排序方式，理論上只要我們的sort_buffer_size足夠大，就可以避免使用到磁盤臨時(shí)表，但是若該參數(shù)是基于會話級別的，若設(shè)置不合理極有可能占用過多內(nèi)存，導(dǎo)致OOM。

• max_length_for_sort_data

max_length_for_sort_data：是控制用于排序的行數(shù)據(jù)的長度的一個(gè)參數(shù)。如果單行的長度超過這個(gè)值，MySQL就認(rèn)為單行太大，要換一個(gè)算法—rowid排序。

但是該字段設(shè)置的過大，就可能會提高磁盤的IO操作。

不推薦修改默認(rèn)值.png

5.2 優(yōu)化sql

借助覆蓋索引和聯(lián)合索引查詢出有序的數(shù)據(jù)記錄。

MySql性能（7）—MySql索引掃描與order by排序優(yōu)化

假設(shè)表里面有city_name(city, name)聯(lián)合索引，要查杭州和蘇州兩個(gè)城市中所有的市民的姓名，并且按名字排序，顯示前100條記錄。如果SQL查詢語句是這么寫的 ：

select * from t where city in ('杭州',"蘇州") order by name limit 100;

雖然有(city,name)聯(lián)合索引，對于單個(gè)city內(nèi)部，name是遞增的。但是由于這條SQL語句不是要單獨(dú)地查一個(gè)city的值，而是同時(shí)查了"杭州"和" 蘇州 "兩個(gè)城市，因此所有滿足條件的name就不是遞增的了。也就是說，這條SQL語句需要排序。

設(shè)計(jì)出在數(shù)據(jù)庫端不需要排序的方案：

要用到(city,name)聯(lián)合索引的特性，把這一條語句拆成兩條語句，執(zhí)行流程如下：

• 執(zhí)行select * from t where city=“杭州” order by name limit 100;，這個(gè)語句不需要排序，客戶端用一個(gè)長度為100的內(nèi)存數(shù)組A保存結(jié)果。
• 執(zhí)行select * from t where city=“蘇州” order by name limit 100;用相同的方法，結(jié)果被存進(jìn)了內(nèi)存數(shù)組B。
  現(xiàn)在A和B是兩個(gè)有序數(shù)組，可以用歸并排序的思想，得到name最小的前100值。

如果有分頁需求，要顯示第101頁，也就是說語句最后要改成 “l(fā)imit 10000,100”，如何實(shí)現(xiàn)？

select * from t where city="杭州" order by name limit 10100; 

和

select * from t where city="蘇州" order by name limit 10100; 

這時(shí)候數(shù)據(jù)量較大，可以同時(shí)起兩個(gè)連接一行行讀結(jié)果，用歸并排序算法拿到這兩個(gè)結(jié)果集里，按順序取第10001~10100的name值。

這個(gè)方案有一個(gè)明顯的損失，就是從數(shù)據(jù)庫返回給客戶端的數(shù)據(jù)量變大了。如果數(shù)據(jù)的單行比較大的話，可以考慮把這兩條SQL語句改成下面這種寫法：

select id,name from t where city="杭州" order by name limit 10100;

和

select id,name from t where city="蘇州" order by name limit 10100;

然后，再用歸并排序的方法取得按name順序第10001~10100的name、id的值，然后拿著這100個(gè)id到數(shù)據(jù)庫中去查出所有記錄。

文章來源

MySQL中orderBy的工作原理

16 | “order by”是怎么工作的？

MySQL中的sort_buffer_size參數(shù)大小的設(shè)置問題