假設(shè)要查詢(xún)城市是“杭州”的所有人名字，并且按照姓名排序返回前1000個(gè)人的姓名、年齡。

假設(shè)這個(gè)表的部分定義是這樣的：

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;

查詢(xún)語(yǔ)句可以這樣寫(xiě)：

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

這個(gè)語(yǔ)句看上去邏輯很清晰，但是是否真的沒(méi)問(wèn)題，下面來(lái)看看他的執(zhí)行流程。

全字段排序

為避免全表掃描，在city字段加上索引，用explain命令來(lái)看看這個(gè)語(yǔ)句的執(zhí)行情況。

image.png

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

為了說(shuō)明這個(gè)SQL查詢(xún)語(yǔ)句的執(zhí)行過(guò)程，先來(lái)看一下city這個(gè)索引的示意圖。

image.png

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

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

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

暫且把這個(gè)排序過(guò)程，稱(chēng)為全字段排序，執(zhí)行流程的示意圖如下所示：

image.png

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

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

可以用下面介紹的方法，來(lái)確定一個(gè)排序語(yǔ)句是否使用了臨時(shí)文件。

/* 打開(kāi)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í)行語(yǔ)句 */
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;

這個(gè)方法是通過(guò)查看 OPTIMIZER_TRACE 的結(jié)果來(lái)確認(rèn)的，可以從 number_of_tmp_files中看到是否使用了臨時(shí)文件。

image.png

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

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

示例表中有4000條滿(mǎn)足city='杭州’的記錄，所以你可以看到 examined_rows=4000，表示參與排序的行數(shù)是4000行。

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

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

這里需要注意的是，為了避免對(duì)結(jié)論造成干擾，把internal_tmp_disk_storage_engine設(shè)置成MyISAM。否則，select @b-@a的結(jié)果會(huì)顯示為4001。

這是因?yàn)椴樵?xún)OPTIMIZER_TRACE這個(gè)表時(shí)，需要用到臨時(shí)表，而internal_tmp_disk_storage_engine的默認(rèn)值是InnoDB。如果使用的是InnoDB引擎的話(huà)，把數(shù)據(jù)從臨時(shí)表取出來(lái)的時(shí)候，會(huì)讓Innodb_rows_read的值加1。

rowid排序

在上面這個(gè)算法過(guò)程里面，只對(duì)原表的數(shù)據(jù)讀了一遍，剩下的操作都是在sort_buffer和臨時(shí)文件中執(zhí)行的。但這個(gè)算法有一個(gè)問(wèn)題，就是如果查詢(xún)要返回的字段很多的話(huà)，那么sort_buffer里面要放的字段數(shù)太多，這樣內(nèi)存里能夠同時(shí)放下的行數(shù)很少，要分成很多個(gè)臨時(shí)文件，排序的性能會(huì)很差。

所以如果單行很大，這個(gè)方法效率不夠好。

那么，如果MySQL認(rèn)為排序的單行長(zhǎng)度太大會(huì)怎么做呢？

接下來(lái)，修改一個(gè)參數(shù)，讓MySQL采用另外一種算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是MySQL中專(zhuān)門(mén)控制用于排序的行數(shù)據(jù)的長(zhǎng)度的一個(gè)參數(shù)。它的意思是，如果單行的長(zhǎng)度超過(guò)這個(gè)值，MySQL就認(rèn)為單行太大，要換一個(gè)算法。

city、name、age 這三個(gè)字段的定義總長(zhǎng)度是36，把max_length_for_sort_data設(shè)置為16，我們?cè)賮?lái)看看計(jì)算過(guò)程有什么改變。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主鍵id。

但這時(shí)，排序的結(jié)果就因?yàn)樯倭薱ity和age字段的值，不能直接返回了，整個(gè)執(zhí)行流程就變成如下所示的樣子：
1.初始化sort_buffer，確定放入兩個(gè)字段，即name和id；
2.從索引city找到第一個(gè)滿(mǎn)足city='杭州’條件的主鍵id，也就是圖中的ID_X；
3.到主鍵id索引取出整行，取name、id這兩個(gè)字段，存入sort_buffer中；
4.從索引city取下一個(gè)記錄的主鍵id；
5.重復(fù)步驟3、4直到不滿(mǎn)足city='杭州’條件為止，也就是圖中的ID_Y；
6.對(duì)sort_buffer中的數(shù)據(jù)按照字段name進(jìn)行排序；
7.遍歷排序結(jié)果，取前1000行，并按照id的值回到原表中取出city、name和age三個(gè)字段返回給客戶(hù)端。

這個(gè)執(zhí)行流程的示意圖如下，稱(chēng)為rowid排序。

image.png

對(duì)比之前的全字段排序流程圖你會(huì)發(fā)現(xiàn)，rowid排序多訪問(wèn)了一次表t的主鍵索引，就是步驟7。

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

根據(jù)這個(gè)說(shuō)明過(guò)程和圖示，可以想一下，這個(gè)時(shí)候執(zhí)行select @b-@a，結(jié)果會(huì)是多少呢？

現(xiàn)在，來(lái)看看結(jié)果有什么不同。

首先，圖中的examined_rows的值還是4000，表示用于排序的數(shù)據(jù)是4000行。但是select @b-@a這個(gè)語(yǔ)句的值變成5000了。

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

image.png

從OPTIMIZER_TRACE的結(jié)果中，你還能看到另外兩個(gè)信息也變了。

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

全字段排序 VS rowid排序

如果MySQL實(shí)在是擔(dān)心排序內(nèi)存太小，會(huì)影響排序效率，才會(huì)采用rowid排序算法，這樣排序過(guò)程中一次可以排序更多行，但是需要再回到原表去取數(shù)據(jù)。

如果MySQL認(rèn)為內(nèi)存足夠大，會(huì)優(yōu)先選擇全字段排序，把需要的字段都放到sort_buffer中，這樣排序后就會(huì)直接從內(nèi)存里面返回查詢(xún)結(jié)果了，不用再回到原表去取數(shù)據(jù)。

這也就體現(xiàn)了MySQL的一個(gè)設(shè)計(jì)思想：如果內(nèi)存夠，就要多利用內(nèi)存，盡量減少磁盤(pán)訪問(wèn)。

對(duì)于InnoDB表來(lái)說(shuō)，rowid排序會(huì)要求回表多造成磁盤(pán)讀，因此不會(huì)被優(yōu)先選擇。

MySQL做排序是一個(gè)成本比較高的操作。是不是所有的order by都需要排序操作呢？如果不排序就能得到正確的結(jié)果，那對(duì)系統(tǒng)的消耗會(huì)小很多，語(yǔ)句的執(zhí)行時(shí)間也會(huì)變得更短。

其實(shí)，并不是所有的order by語(yǔ)句，都需要排序操作的。從上面分析的執(zhí)行過(guò)程，可以看到，MySQL之所以需要生成臨時(shí)表，并且在臨時(shí)表上做排序操作，其原因是原來(lái)的數(shù)據(jù)都是無(wú)序的。

可以設(shè)想下，如果能夠保證從city這個(gè)索引上取出來(lái)的行，天然就是按照name遞增排序的話(huà)，是不是就可以不用再排序了呢？
確實(shí)是這樣的。

所以，可以在這個(gè)市民表上創(chuàng)建一個(gè)city和name的聯(lián)合索引，對(duì)應(yīng)的SQL語(yǔ)句是：

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

作為與city索引的對(duì)比，看看這個(gè)索引的示意圖。

image.png

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

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

image.png

可以看到，這個(gè)查詢(xún)過(guò)程不需要臨時(shí)表，也不需要排序。接下來(lái)，用explain的結(jié)果來(lái)印證一下。

image.png

按照覆蓋索引的概念，可以再優(yōu)化一下這個(gè)查詢(xún)語(yǔ)句的執(zhí)行流程。

針對(duì)這個(gè)查詢(xún)，可以創(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字段的值相同的行來(lái)說(shuō)，還是按照name字段的值遞增排序的，此時(shí)的查詢(xún)語(yǔ)句也就不再需要排序了。這樣整個(gè)查詢(xún)語(yǔ)句的執(zhí)行流程就變成了：
1.從索引(city,name,age)找到第一個(gè)滿(mǎn)足city='杭州’條件的記錄，取出其中的city、name和age這三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
2.從索引(city,name,age)取下一個(gè)記錄，同樣取出這三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
3.重復(fù)執(zhí)行步驟2，直到查到第1000條記錄，或者是不滿(mǎn)足city='杭州’條件時(shí)循環(huán)結(jié)束。

image.png

再來(lái)看看explain的結(jié)果。

image.png

當(dāng)然，這里并不是說(shuō)要為了每個(gè)查詢(xún)能用上覆蓋索引，就要把語(yǔ)句中涉及的字段都建上聯(lián)合索引，畢竟索引還是有維護(hù)代價(jià)的。這是一個(gè)需要權(quán)衡的決定。

——學(xué)自極客時(shí)間