這天風(fēng)和日麗，小a正在工位上苦練摸魚技術(shù)，

突然接到產(chǎn)品的??，又來需求？      

只聽到產(chǎn)品又開始口若黃河：我需要要查詢到city是“上?！钡乃腥说膎ame，并且還要按name排序返回前1000人的name、age。

小a急忙正襟危坐，從一堆庫(kù)表中翻出需要的表，抽出其建表語(yǔ)句： 

看看表結(jié)構(gòu)，再看看產(chǎn)品的需求 

感覺很容易，隨手SQL這么一寫：  

誒，這語(yǔ)句看著簡(jiǎn)單而樸實(shí)，一個(gè)需求好像就完美解決了。但為了顯示自己強(qiáng)大的性能優(yōu)化水平，考慮到要避免全表掃描，于是又給 city 字段加索引。建完索引，自然還需要使用explain驗(yàn)證一下：

explain select city, name, age from citizen where city = '上海' order by name limit 1000;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+|  1 | SIMPLE      | citizen | NULL       | ALL  | city          | NULL | NULL    | NULL |   32 |   100.00 | Using where; Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)

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

這時(shí)魔鬼產(chǎn)品突然湊過來問：給我看看你代碼咋寫的，你這么寫你真的懂MySQL 底層怎么執(zhí)行order by的嗎？小a突然驚醒，還真沒想過這些。

產(chǎn)品經(jīng)理冷笑道：你知道你的 city 索引長(zhǎng)啥樣嗎？我自己建立的，我咋可能不知道！隨手直接畫出

• city字段的索引示意圖 

產(chǎn)品，你可好好看了，這里 id_x ~ id_(x+n) 的數(shù)據(jù)都滿足city='上海’。

產(chǎn)品：那你倒是說說這條SQL的執(zhí)行流程？不知道了吧，我來告訴你吧：

1. 初始化sort_buffer，確定放入name、city、age三字段

2. 從索引city找到第一個(gè)滿足city='上?！?/span>條件的主鍵id， 即id_x；

3. 到id主鍵索引取出整行，取name、city、age三個(gè)字段的值，存入sort_buffer

4. 從索引city取下一個(gè)記錄的主鍵id

5. 重復(fù)3、4，直到city的值不滿足查詢條件，即主鍵id_y

6. 對(duì)sort_buffer中數(shù)據(jù)按name做快排

7. 取排序后結(jié)果的前1000行返回給客戶端

這就是全字段排序，執(zhí)行流程如下： 

按name排序 這一操作可能在內(nèi)存中完成，也可能需要外部排序，而這就取決于

• 排序所需內(nèi)存

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

產(chǎn)品又開始炫技了，又問到：你知道 一條排序語(yǔ)句何時(shí)才會(huì)使用臨時(shí)文件 嗎？這？這還真又觸及到我的知識(shí)盲區(qū)了！ 

mysql> SET optimizer_trace='enabled=on';Query OK, 0 rows affected (0.00 sec)
/* 使用 @a 保存 Innodb_rows_read 的初始值 */mysql> select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';Query OK, 1 row affected (0.00 sec)
mysql> select city, name,age from citizen where city='上海' order by name limit 1000;+--------+------+-----+| city   | name | age |+--------+------+-----+| 上海   | java |  22 |...
/* 查看 OPTIMIZER_TRACE 輸出 */SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* 使用 @b 保存 Innodb_rows_read 的當(dāng)前值 */mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';Query OK, 1 row affected (0.00 sec)
/* 計(jì)算 Innodb_rows_read 的差值 */mysql> select @b-@a;

查看 OPTIMIZER_TRACE 結(jié)果中的 number_of_tmp_files 字段確認(rèn)是否使用臨時(shí)文件。

"filesort_execution": [],"filesort_summary": {  "rows": 4000  "examined_rows": 4000,  "number_of_tmp_files": 12,  "sort_buffer_size": 32664 ,  "sort_mode": "<sort_key, packed_additional_fields>"

• number_of_tmp_files 排序過程中使用的臨時(shí)文件數(shù)。為啥需要12個(gè)文件？?jī)?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 參與排序的行數(shù)。測(cè)試表有4000條滿足city='上?！挠涗?，所以該參數(shù)為4000。

• sort_mode 的packed_additional_fields 排序過程對(duì)字符串做了“緊湊”處理。即使name字段的定義是varchar(16)，在排序過程中還是要按實(shí)際長(zhǎng)度分配空間。

select @b-@a 的結(jié)果4000，即整個(gè)執(zhí)行過程只掃描了4000行。

注意，為了避免對(duì)結(jié)論造成干擾，我把internal_tmp_disk_storage_engine設(shè)置成MyISAM。否則，select @b-@a的結(jié)果會(huì)顯示為4001。因?yàn)椴樵?strong>OPTIMIZER_TRACE表時(shí)，需要用到臨時(shí)表，而internal_tmp_disk_storage_engine的默認(rèn)值是InnoDB。若使用InnoDB，把數(shù)據(jù)從臨時(shí)表取出時(shí)，會(huì)讓Innodb_rows_read的值加1。

我驚奇地望著產(chǎn)品，像瞻仰偉人一般，不如你繼承我的代碼吧，讓我來做產(chǎn)品？  

rowid排序 上面的算法，只是對(duì)原表數(shù)據(jù)讀了一遍，剩下的操作都是在sort_buffer和臨時(shí)文件中執(zhí)行。但這就存在問題：若查詢要返回的字段很多，那么sort_buffer要放的字段數(shù)就會(huì)很多，內(nèi)存里能夠同時(shí)放下的行數(shù)就會(huì)變少，就要分成很多臨時(shí)文件，排序性能就會(huì)很差。 所以若單行很大，該方法的效率可不夠行哦。

產(chǎn)品大大又開始發(fā)難，那么你知道若MySQL認(rèn)為排序的單行長(zhǎng)度太大，它又會(huì)干啥嗎？

現(xiàn)在修改個(gè)參數(shù)，讓MySQL采用另外一種算法。

SET max_length_for_sort_data = 16;

• max_length_for_sort_data MySQL用于控制用于排序的行數(shù)據(jù)的長(zhǎng)度。若單行的長(zhǎng)度超過該值，MySQL就認(rèn)為單行太大，要換個(gè)算法。

city、name、age 三字段的定義總長(zhǎng)度36，那你看我把max_length_for_sort_data設(shè)為16會(huì)咋樣。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主鍵id。但這時(shí)，排序的結(jié)果就因少了city和age字段值，不能直接返回了，整個(gè)執(zhí)行流程變成如下：

1. 初始化sort_buffer，確定放入兩個(gè)字段，即name和id

2. 從city找到第一個(gè)滿足city='上?！瘲l件的主鍵id，也就是圖中的id_x

3. 到id取出整行，取name、id這兩個(gè)字段，存入sort_buffer

4. 從city取下一個(gè)記錄的主鍵id

5. 重復(fù)步驟3、4直到不滿足city='上海’，也就是圖中的id_y

6. 對(duì)sort_buffer中的數(shù)據(jù)按照字段name進(jìn)行排序

7. 遍歷排序結(jié)果，取前1000行，并按照id的值回到原表中取出city、name和age三個(gè)字段返回給客戶端。

聽到這里，感覺明白了一些：產(chǎn)品你別急，你看我畫下這個(gè)rowid排序執(zhí)行過程的示意圖，看看對(duì)不對(duì)？ 

 你看這個(gè)和你之前畫的全字段排序示意圖，其實(shí)就是多訪問了一次表citizen的主鍵索引，即step7。

注意了，最后的resultSet是一個(gè)邏輯概念，實(shí)際上MySQL服務(wù)端從排序后的sort_buffer中依次取出id，然后到原表查到city、name和age這三字段的結(jié)果，不需要在服務(wù)端再耗費(fèi)內(nèi)存存儲(chǔ)結(jié)果，而是直接返回給客戶端。

這時(shí)查看rowid排序的OPTIMIZER_TRACE結(jié)果，看看和之前的不同之處在哪里

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

• select @b-@a結(jié)果變成5000 因?yàn)檫@時(shí)除了排序過程，在排序完成后，還要根據(jù)id去原表取值。由于語(yǔ)句是limit 1000，因此會(huì)多讀1000行。

• sort_mode 變成了 <sort_key, rowid> 表示參與排序的只有name和id字段

• number_of_tmp_files 變成10 因?yàn)檫@時(shí)參與排序的行數(shù)雖然還是4000，但每行都變小了，因此需排序的總數(shù)據(jù)量就小了，需要的臨時(shí)文件也就少咯。

產(chǎn)品最后總結(jié)到：

• 若MySQL認(rèn)為排序內(nèi)存太小，會(huì)影響排序效率，就會(huì)采用rowid排序 這樣排序過程中一次可以排序更多行，但最后需要回表取數(shù)據(jù)

• 若MySQL認(rèn)為內(nèi)存夠大，會(huì)優(yōu)先選擇全字段排序 把需要字段都放到sort_buffer，這樣排序后就直接從內(nèi)存返回查詢結(jié)果，不用回表。

所以MySQL就是：若內(nèi)存夠，就多利用內(nèi)存，盡量減少磁盤訪問。

對(duì)InnoDB，rowid排序會(huì)要求回表，多造成了磁盤讀，因此不會(huì)被優(yōu)先選擇。所以MySQL排序是個(gè)高成本操作。

• 是不是所有order by都需排序呢？若不排序就能得到正確的結(jié)果，那對(duì)系統(tǒng)的消耗會(huì)小很多，語(yǔ)句的執(zhí)行時(shí)間也會(huì)變得更短。

并非所有order by都需排序操作。MySQL之所以需要生成臨時(shí)表，并且在臨時(shí)表上做排序，是因?yàn)樵瓉淼臄?shù)據(jù)都是無(wú)序的。

• 如果能保證從city索引上取出來的行，天生就是按name遞增排序，是不是就可以不用再排序了？是的。

所以可以創(chuàng)建一個(gè)city,name聯(lián)合索引：

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

• 該索引的示意圖  依然可以用樹搜索定位到第一個(gè)滿足city='上?！挠涗?，并且能確保接下來按順序取“下一條記錄”的遍歷過程，只要city是上海，name值一定有序。這樣整個(gè)查詢過程的流程就變成：

1. 從索引(city,name)找到第一個(gè)滿足city='上?！瘲l件的主鍵id

2. 到主鍵id索引取出整行，取name、city、age三個(gè)字段的值，作為結(jié)果集的一部分直接返回

3. 從索引(city,name)取下一個(gè)記錄主鍵id

4. 重復(fù)步驟2、3，直到查到第1000條記錄，或者是不滿足city='上海’條件時(shí)循環(huán)結(jié)束

• 引入(city,name)聯(lián)合索引后，查詢語(yǔ)句的執(zhí)行計(jì)劃 

可見，該查詢過程無(wú)需臨時(shí)表，也無(wú)需排序。

• 使用 explain 查看(city,name)聯(lián)合索引，查詢語(yǔ)句的執(zhí)行計(jì)劃

 explain select city, name, age from citizen where city = '上海' order by name limit 1000;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+|  1 | SIMPLE      | citizen | NULL       | ref  | city,name       | name | 51    | const |   4000 |   100.00 | Using index condition |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)

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

該語(yǔ)句的執(zhí)行流程有沒有可能進(jìn)一步簡(jiǎn)化呢？

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

按覆蓋索引，可以再優(yōu)化一下這個(gè)查詢語(yǔ)句的執(zhí)行流程。針對(duì)這個(gè)查詢，我們可以創(chuàng)建一個(gè)city、name和age的聯(lián)合索引，對(duì)應(yīng)的SQL語(yǔ)句就是：

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

這時(shí)，對(duì)于city字段的值相同的行來說，還是按照name字段的值遞增排序的，此時(shí)的查詢語(yǔ)句也就不再需要排序了。這樣整個(gè)查詢語(yǔ)句的執(zhí)行流程就變成了：

1. 從索引(city,name,age)找到第一個(gè)滿足city='上?！瘲l件的記錄，取出其中的city、name和age這三個(gè)字段的值，作為結(jié)果集的一部分直接返回

2. 從索引(city,name,age)取下一個(gè)記錄，同樣取出這三個(gè)字段的值，作為結(jié)果集的一部分直接返回

3. 重復(fù)2，直到查到第1000條記錄或不滿足city='上海'

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

• explain查看(city,name,age)聯(lián)合索引查詢語(yǔ)句的執(zhí)行計(jì)劃

explain select city, name, age from citizen where city = '上海' order by name limit 1000;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+|  1 | SIMPLE      | citizen | NULL       | ref  | city,name,age        | age | 51    | const |   4000 |   100.00 | Using where; Using index |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)

Extra字段里面多了“Using index”，說明使用了覆蓋索引，性能上會(huì)快很多。但這并非說要為了每個(gè)查詢能用上覆蓋索引，就要把語(yǔ)句中涉及的字段都建上聯(lián)合索引，畢竟索引也很占空間，而且修改新增都會(huì)導(dǎo)致索引改變，還是具體業(yè)務(wù)場(chǎng)景具體分析。

參考

• “order by”是怎么工作的？

• https://blog.csdn.net/Linuxhus/article/details/112672434?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242

• 天天寫order by，你知道Mysql底層執(zhí)行原理嗎？