假設(shè)要查詢城市是“杭州”的所有人名字，并且按照姓名排序返回前1000個(gè)人的姓名與年齡。那么SQL語句可以寫為：

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

本文主要想討論這個(gè)語句是如何執(zhí)行的，以及有什么參數(shù)會(huì)影響執(zhí)行的行為。

全字段排序

在上面的查詢語句中，為了避免全表掃描，需要在city字段加上索引。用explain命令檢查語句的執(zhí)行情況：

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

為了說明該語句的執(zhí)行過程，先看一下city索引的示意圖：

從圖中看到，滿足city='杭州'條件的行的id是從X到X+N。

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

• 初始化sort_buffer，確定放入name、city、age三個(gè)字段；

• 從city索引中找到第一個(gè)滿足city='杭州'條件的主鍵id為ID_X；

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

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

• 重復(fù)上面兩個(gè)步驟直到city值不滿足查詢條件，即找到了圖中的ID_Y；

• 對(duì)sort_buffer中的數(shù)據(jù)按照字段name做快速排序；

• 按照排序結(jié)果取前1000行返回給客戶端。

我們把這個(gè)排序過程稱為全字段排序，執(zhí)行流程示意圖如下：

其中，排序過程可能在內(nèi)存完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和參數(shù)sort_buffer_size。sort_buffer_size是MySQL為排序開辟的sort_buffer的大小。如果要排序的數(shù)據(jù)量小于這個(gè)參數(shù)，排序就在內(nèi)存中完成；如果排序數(shù)據(jù)量太大，內(nèi)存放不下，則不得不利用磁盤臨時(shí)文件輔助排序。

對(duì)于排序語句是否使用了臨時(shí)文件，可以通過下面的方法確認(rèn)：

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

/* @a保存Innodb_rows_read的初始值 */
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`\G

/* @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;

該方法通過查看information_schema數(shù)據(jù)庫下的OPTIMIZER_TRACE表查看，用number_of_tmp_files字段查看：

圖中結(jié)果表示的就是使用了12個(gè)臨時(shí)文件。外部排序一般使用歸并排序算法，12個(gè)臨時(shí)文件可以理解為：MySQL將需要排序的數(shù)據(jù)分成12份，每一份單獨(dú)排序后放在這些臨時(shí)文件中，然后把這12個(gè)有序文件再合并成一個(gè)有序的大文件。

而如果sort_buffer_size超過了需要排序的數(shù)據(jù)量的大小，number_of_tmp_files就會(huì)是0。sort_buffer_size越小，number_of_tmp_files的值會(huì)越大。

再解釋下上面結(jié)果中的其他一些字段：

• examined_rows=4000，表示參與排序的行數(shù)是4000行；

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

同時(shí)，查詢語句select @b-@a的返回結(jié)果是4000，表示整個(gè)執(zhí)行過程只掃描了4000 行。

rowid排序

在全字段排序過程中，只對(duì)原表的數(shù)據(jù)讀了一遍，剩下的操作都是在sort_buffer和臨時(shí)文件中執(zhí)行的。如果查詢要返回的字段很多，那么sort_buffer里能存的行數(shù)會(huì)變得很少，可能會(huì)需要很多臨時(shí)文件，排序的性能變得很差。

這里介紹一個(gè)參數(shù)：

set max_length_for_sort_data = 16;

這是MySQL中專門控制用于排序的行數(shù)據(jù)的長(zhǎng)度的一個(gè)參數(shù)。當(dāng)單行的長(zhǎng)度超過這個(gè)值，MySQL會(huì)認(rèn)為排序的單行長(zhǎng)度太大，需要換一個(gè)算法。

假設(shè)在t表中，city字段和name為varchar(16)，主鍵id和age字段為int(11)。那么city、name、age三個(gè)字段的定義總長(zhǎng)度為36，大于了設(shè)置的參數(shù)值16，此時(shí)計(jì)算過程會(huì)發(fā)生改變：

新算法放入sort_buffer的字段只有要排序的列name和主鍵id，由于排序結(jié)果缺少部分字段，不能直接返回，整個(gè)執(zhí)行流程變?yōu)椋?/p>

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

• 從city索引找到第一個(gè)滿足city='杭州'條件的主鍵id為ID_X；

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

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

• 重復(fù)上面兩個(gè)步驟直到city值不滿足查詢條件，即找到了圖中的ID_Y；

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

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

我們把這個(gè)排序過程稱為rowid排序，執(zhí)行流程示意圖如下：

可以發(fā)現(xiàn)，rowid排序多訪問了一次表的主鍵索引。

另外，圖里的“結(jié)果集”是一個(gè)邏輯概念，實(shí)際上MySQL服務(wù)端獲得結(jié)果后是直接返回給客戶端的，而不是還在服務(wù)端耗費(fèi)內(nèi)存存儲(chǔ)結(jié)果。

如果對(duì)上述過程查看OPTIMIZER_TRACE表，得到的結(jié)果如下：

其中：

• examined_rows=4000，表示用于排序的數(shù)據(jù)是4000行；

• number_of_tmp_files=10，是因?yàn)槊恳恍卸甲冃×耍枰判虻目倲?shù)據(jù)量就變小，需要的臨時(shí)文件也減少了；

• sort_mode里變?yōu)閞owid，表示參與排序的只有name和id兩個(gè)字段。

此時(shí)，查詢語句select @b-@a的返回結(jié)果是5000，因?yàn)樵诟鶕?jù)id去原表取值的過程需要多掃描1000行。

全字段排序 VS rowid排序

從上面兩種排序方法，可以看出，如果MySQL認(rèn)為內(nèi)存足夠大，會(huì)優(yōu)先選擇全字段排序；如果MySQL認(rèn)為內(nèi)存太小，會(huì)采用rowid排序。這體現(xiàn)了MySQL的一個(gè)設(shè)計(jì)思想：如果內(nèi)存夠，就多利用內(nèi)存，盡量減少磁盤訪問。

對(duì)于InnoDB表，rowid排序回表會(huì)增加磁盤讀，因此不會(huì)被優(yōu)先選擇。

那么是不是所有的order by都需要排序操作呢？不是的，就像在本文的例子中，如果從city索引上取出的行天熱按照name遞增排序，就可以不用再排序。所以可以對(duì)city和name創(chuàng)建聯(lián)合索引，對(duì)應(yīng)的示意圖為：

整個(gè)查詢流程變?yōu)椋?/p>

• 從索引(city,name)找到第一個(gè)滿足city='杭州'的主鍵id；

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

• 從索引(city,name)取下一個(gè)滿足條件的主鍵id；

• 重復(fù)以上兩步，直到查到第1000條記錄或不滿足條件city='杭州'。

該過程不需要臨時(shí)表，也不需要排序，用explain進(jìn)行驗(yàn)證：

可以看到，Extra里沒有Using filesort了。

那么這個(gè)語句能否進(jìn)一步簡(jiǎn)化呢？是能的，由于最后要返回三個(gè)字段，可以考慮覆蓋索引，對(duì)三個(gè)字段建立聯(lián)合索引。此時(shí)整個(gè)查詢流程變?yōu)椋?/p>

• 從索引(city,name,age)找到第一個(gè)滿足city='杭州'的記錄，取name、city、age字段的值作為結(jié)果集的一部分直接返回；

• 從索引(city,name,age)取下一個(gè)記錄，同樣取出三個(gè)字段并返回；

• 重復(fù)上面一步，直到查到第1000條記錄或不滿足條件city='杭州'。

其流程和驗(yàn)證如下：

不過并不是說為了查詢索引能用上覆蓋索引就需要把涉及的字段都建立聯(lián)合索引，索引有一定代價(jià)，這需要權(quán)衡。