在開發(fā)應(yīng)用的時(shí)候拘哨，一定會(huì)經(jīng)常碰到需要根據(jù)指定的字段排序來顯示結(jié)果的需求。以市民表為例信峻，假設(shè)你要查詢城市是“杭州”的所有人名字倦青，并且按照姓名排序返回前 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;
sql查詢語句如下：
select city,name,age from t where city='杭州' order by name limit 1000 ;
這個(gè)語句看上去邏輯很清晰产镐，但是你了解它的執(zhí)行流程嗎？本文踢步，我們一起來看看這個(gè)語句是怎么執(zhí)行的癣亚，以及有什么參數(shù)會(huì)影響執(zhí)行的行為。
全字段排序
- 為避免全表掃描获印，我們需要在 city 字段加上索引述雾。在 city 字段上創(chuàng)建索引之后，我們用 explain 命令來看看這個(gè)語句的執(zhí)行情況。
explain結(jié)果圖
- Extra 這個(gè)字段中的“Using filesort”表示的就是需要排序玻孟，MySQL 會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序唆缴，稱為 sort_buffer。為了說明這個(gè) SQL 查詢語句的執(zhí)行過程取募，我們先來看一下 city 這個(gè)索引的示意圖琐谤。
city索引字段示意圖
- 從圖中可以看到蚪腐，滿足 city='杭州’條件的行惰蜜，是從 ID_X 到 ID_(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ù)步驟 3、4 直到 city 的值不滿足查詢條件為止爽航，對(duì)應(yīng)的主鍵 id 也就是圖中的 ID_Y蚓让；
- 對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 做快速排序；
- 按照排序結(jié)果取前 1000 行返回給客戶端讥珍。
- 我們暫且把這個(gè)排序過程历极，稱為全字段排序，執(zhí)行流程的示意圖如下所示：
全字段排序
- 圖中“按 name 排序”這個(gè)動(dòng)作衷佃，可能在內(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萝风，只對(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;
- 這個(gè)方法是通過查看 OPTIMIZER_TRACE 的結(jié)果來確認(rèn)的，你可以從 number_of_tmp_files 中看到是否使用了臨時(shí)文件紫岩。
OPTIMIZER_TRACE部分結(jié)果圖
- number_of_tmp_files 表示的是规惰，排序過程中使用的臨時(shí)文件數(shù)。你一定奇怪泉蝌，為什么需要 12 個(gè)文件歇万？?jī)?nèi)存放不下時(shí)，就需要使用外部排序勋陪，外部排序一般使用歸并排序算法贪磺。可以這么簡(jiǎn)單理解诅愚，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 的值就越大撒踪。
- 我們的示例表中有 4000 條滿足 city='杭州’的記錄，所以你可以看到 examined_rows=4000制妄，表示參與排序的行數(shù)是 4000 行。
- sort_mode 里面的 packed_additional_fields 的意思是耕捞，排序過程對(duì)字符串做了“緊湊”處理。即使 name 字段的定義是 varchar(16)俺抽，在排序過程中還是要按照實(shí)際長(zhǎng)度來分配空間的敞映。
- 同時(shí)振愿，最后一個(gè)查詢語句 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)椴樵?OPTIMIZER_TRACE 這個(gè)表時(shí)，需要用到臨時(shí)表藻肄，而 internal_tmp_disk_storage_engine 的默認(rèn)值是 InnoDB蔑舞。如果使用的是 InnoDB 引擎的話，把數(shù)據(jù)從臨時(shí)表取出來的時(shí)候仅炊，會(huì)讓 Innodb_rows_read 的值加 1斗幼。
rowid 排序
- 在上面這個(gè)算法過程里面，只對(duì)原表的數(shù)據(jù)讀了一遍抚垄，剩下的操作都是在 sort_buffer 和臨時(shí)文件中執(zhí)行的蜕窿。但這個(gè)算法有一個(gè)問題，就是如果查詢要返回的字段很多的話呆馁，那么 sort_buffer 里面要放的字段數(shù)太多桐经，這樣內(nèi)存里能夠同時(shí)放下的行數(shù)很少，要分成很多個(gè)臨時(shí)文件浙滤，排序的性能會(huì)很差阴挣。
- 所以如果單行很大，這個(gè)方法效率不夠好纺腊。那么畔咧，如果 MySQL 認(rèn)為排序的單行長(zhǎng)度太大會(huì)怎么做呢？接下來揖膜，我來修改一個(gè)參數(shù)誓沸，讓 MySQL 采用另外一種算法。
SET max_length_for_sort_data = 16;
- max_length_for_sort_data壹粟，是 MySQL 中專門控制用于排序的行數(shù)據(jù)的長(zhǎng)度的一個(gè)參數(shù)拜隧。它的意思是，如果單行的長(zhǎng)度超過這個(gè)值趁仙，MySQL 就認(rèn)為單行太大洪添，要換一個(gè)算法。
- city雀费、name、age 這三個(gè)字段的定義總長(zhǎng)度是 36盏袄，我把 max_length_for_sort_data 設(shè)置為 16，我們?cè)賮砜纯从?jì)算過程有什么改變炭菌。
新的算法放入 sort_buffer 的字段黑低，只有要排序的列（即 name 字段）和主鍵 id酌毡。
但這時(shí)，排序的結(jié)果就因?yàn)樯倭?city 和 age 字段的值菩暗，不能直接返回了停团，整個(gè)執(zhí)行流程就變成如下所示的樣子：- 初始化 sort_buffer掏熬，確定放入兩個(gè)字段，即 name 和 id舌胶；
- 從索引 city 找到第一個(gè)滿足 city='杭州’條件的主鍵 id疮丛，也就是圖中的 ID_X；
- 到主鍵 id 索引取出整行履恩，取 name似袁、id 這兩個(gè)字段咐刨，存入 sort_buffer 中；
從索引 city 取下一個(gè)記錄的主鍵 id而涉； - 重復(fù)步驟 3联予、4 直到不滿足 city='杭州’條件為止，也就是圖中的 ID_Y季眷；
對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 進(jìn)行排序； - 遍歷排序結(jié)果威酒，取前 1000 行挺峡，并按照 id 的值回到原表中取出 city、name 和 age 三個(gè)字段返回給客戶端尤仍。
rowid排序示意圖
- 對(duì)比前面圖的全字段排序流程圖你會(huì)發(fā)現(xiàn)宰啦，rowid 排序多訪問了一次表 t 的主鍵索引绑莺，就是步驟 7惕耕。
- 需要說明的是，最后的“結(jié)果集”是一個(gè)邏輯概念欺缘，實(shí)際上 MySQL 服務(wù)端從排序后的 sort_buffer 中依次取出 id挤安，然后到原表查到 city、name 和 age 這三個(gè)字段的結(jié)果嫩絮，不需要在服務(wù)端再耗費(fèi)內(nèi)存存儲(chǔ)結(jié)果剿干，是直接返回給客戶端的穆刻。
根據(jù)這個(gè)說明過程和圖示氢伟，你可以想一下幽歼，這個(gè)時(shí)候執(zhí)行 select @b-@a甸私，結(jié)果會(huì)是多少呢设褐？ - 現(xiàn)在，我們就來看看結(jié)果有什么不同。
- 首先外冀，圖中的 examined_rows 的值還是 4000掀泳，表示用于排序的數(shù)據(jù)是 4000 行。但是 select @b-@a 這個(gè)語句的值變成 5000 了脑沿。
- 因?yàn)檫@時(shí)候除了排序過程外马僻，在排序完成后韭邓，還要根據(jù) id 去原表取值。由于語句是 limit 1000瞭郑，因此會(huì)多讀 1000 行鸭你。
OPTIMIZER_TRACE結(jié)果輸出圖
- 從 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 排序算法，這樣排序過程中一次可以排序更多行健民，但是需要再回到原表去取數(shù)據(jù)贫贝。
- 如果 MySQL 認(rèn)為內(nèi)存足夠大，會(huì)優(yōu)先選擇全字段排序凤优，把需要的字段都放到 sort_buffer 中蜈彼，這樣排序后就會(huì)直接從內(nèi)存里面返回查詢結(jié)果了，不用再回到原表去取數(shù)據(jù)棍辕。
- 這也就體現(xiàn)了 MySQL 的一個(gè)設(shè)計(jì)思想：如果內(nèi)存夠楚昭，就要多利用內(nèi)存拍顷，盡量減少磁盤訪問。
- 對(duì)于 InnoDB 表來說尿贫，rowid 排序會(huì)要求回表多造成磁盤讀，因此不會(huì)被優(yōu)先選擇匾乓。
- 看到這里拼缝，你就了解了彰亥，MySQL 做排序是一個(gè)成本比較高的操作。那么你會(huì)問猪叙，是不是所有的 order by 都需要排序操作呢仁卷？如果不排序就能得到正確的結(jié)果锦积，那對(duì)系統(tǒng)的消耗會(huì)小很多歉嗓，語句的執(zhí)行時(shí)間也會(huì)變得更短。
- 其實(shí)哮幢，并不是所有的 order by 語句志珍，都需要排序操作的伦糯。從上面分析的執(zhí)行過程，我們可以看到喂击，MySQL 之所以需要生成臨時(shí)表淤翔，并且在臨時(shí)表上做排序操作，其原因是原來的數(shù)據(jù)都是無序的监嗜。
- 你可以設(shè)想下，如果能夠保證從 city 這個(gè)索引上取出來的行稚补，天然就是按照 name 遞增排序的話课幕，是不是就可以不用再排序了呢五垮？
- 確實(shí)是這樣的。所以润绎，我們可以在這個(gè)市民表上創(chuàng)建一個(gè) city 和 name 的聯(lián)合索引诞挨，對(duì)應(yīng)的 SQL 語句是：
alter table t add index city_user(city, name);
city和name聯(lián)合索引示意圖
- 在這個(gè)索引里面惶傻，我們依然可以用樹搜索的方式定位到第一個(gè)滿足 city='杭州’的記錄银室，并且額外確保了，接下來按順序取“下一條記錄”的遍歷過程中辜荠，只要 city 的值是杭州抓狭，name 的值就一定是有序的。
- 這樣整個(gè)查詢過程的流程就變成了：
- 從索引 (city,name) 找到第一個(gè)滿足 city='杭州’條件的主鍵 id狱从；
- 到主鍵 id 索引取出整行叠纹，取 name、city与涡、age 三個(gè)字段的值，作為結(jié)果集的一部分直接返回氨肌；
- 從索引 (city,name) 取下一個(gè)記錄主鍵 id酌畜；
- 重復(fù)步驟 2、3恳守，直到查到第 1000 條記錄催烘，或者是不滿足 city='杭州’條件時(shí)循環(huán)結(jié)束缎罢。
引入聯(lián)合索引排序示意圖
explain結(jié)果圖
- 從圖中可以看到策精，Extra 字段中沒有 Using filesort 了咽袜，也就是不需要排序了。而且由于 (city,name) 這個(gè)聯(lián)合索引本身有序，所以這個(gè)查詢也不用把 4000 行全都讀一遍抽莱，只要找到滿足條件的前 1000 條記錄就可以退出了。也就是說匕垫，在我們這個(gè)例子里虐呻，只需要掃描 1000 次斟叼。
- 覆蓋索引是指，索引上的信息足夠滿足查詢請(qǐng)求忽孽，不需要再回到主鍵索引上去取數(shù)據(jù)。按照覆蓋索引的概念兄一，我們可以再優(yōu)化一下這個(gè)查詢語句的執(zhí)行流程出革。
針對(duì)這個(gè)查詢，我們可以創(chuàng)建一個(gè) city耳璧、name 和 age 的聯(lián)合索引栖雾，對(duì)應(yīng)的 SQL 語句就是：
alter table t add index city_user_age(city, name, age);
- 這時(shí)，對(duì)于 city 字段的值相同的行來說召廷，還是按照 name 字段的值遞增排序的竞慢，此時(shí)的查詢語句也就不再需要排序了治泥。這樣整個(gè)查詢語句的執(zhí)行流程就變成了：
- 從索引 (city,name,age) 找到第一個(gè)滿足 city='杭州’條件的記錄，取出其中的 city败潦、name 和 age 這三個(gè)字段的值劫扒，作為結(jié)果集的一部分直接返回狸膏；
- 從索引 (city,name,age) 取下一個(gè)記錄，同樣取出這三個(gè)字段的值贤旷，作為結(jié)果集的一部分直接返回砾脑；
- 重復(fù)執(zhí)行步驟 2，直到查到第 1000 條記錄县遣，或者是不滿足 city='杭州’條件時(shí)循環(huán)結(jié)束。
聯(lián)合索引其兴，查詢語句示意圖
explain示意圖
- 可以看到元旬，Extra 字段里面多了“Using index”守问，表示的就是使用了覆蓋索引耗帕，性能上會(huì)快很多。
- 當(dāng)然体啰，這里并不是說要為了每個(gè)查詢能用上覆蓋索引，就要把語句中涉及的字段都建上聯(lián)合索引荒勇，畢竟索引還是有維護(hù)代價(jià)的沽翔。這是一個(gè)需要權(quán)衡的決定窿凤。
小結(jié)
- 本文介紹了 MySQL 里面 order by 語句的幾種算法流程。
- 在開發(fā)系統(tǒng)的時(shí)候哨颂，你總是不可避免地會(huì)使用到 order by 語句相种。你心里要清楚每個(gè)語句的排序邏輯是怎么實(shí)現(xiàn)的寝并，還要能夠分析出在最壞情況下衬潦，每個(gè)語句的執(zhí)行對(duì)系統(tǒng)資源的消耗植酥，這樣才能做到下筆如有神弦牡，不犯低級(jí)錯(cuò)誤驾锰。
