面試就面試,問我原理干嘛,order by

假設(shè)有這么一張用戶表 user：

• id int(11)：主鍵
• username varchar(16)：用戶名
• age int(11)：年齡
• city varchar(16)：城市

假設(shè)有這么一個(gè)需求：查詢出城市是 “南京” 的所有用戶名，并且按照用戶名進(jìn)行排序，返回前 1000 個(gè)人的姓名、年齡。

眾所周知，排序使用的關(guān)鍵字是 order by，不難寫出這樣的 SQL 語句：

select?city,?username,?age?from?user?where?city?=?'南京'?order?by?username?limit?1000;

這篇文章，我們就來解釋下，涉及 order by 的語句具體是怎么執(zhí)行的，以及有什么參數(shù)會(huì)影響執(zhí)行的行為

老規(guī)矩，背誦版在文末。點(diǎn)擊閱讀原文可以直達(dá)我收錄整理的各大廠面試真題

全字段排序

為避免全表掃描，我們?cè)诓樵儣l件的 city 字段上面建立索引。然后用 explain 命令來看看這個(gè)語句的執(zhí)行情況：

偷個(gè)懶，因?yàn)槲移鋵?shí)一條數(shù)據(jù)也沒插入（狗頭保命），所以大伙兒在上圖中看見的 explain 分析出來的這條 SQL 的影響行數(shù) rows 是 1

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

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

1）初始化 sort_buffer，放入 city、username、age 這三個(gè)字段；

2）從索引 city 找到第一個(gè)滿足 city='南京' 條件的主鍵 id

3）到主鍵 id 的索引樹上查找到對(duì)應(yīng)的整行數(shù)據(jù)（回表查詢），然后取出 city、username、age 三個(gè)字段的值，存入 sort_buffer 中

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

5）重復(fù)步驟 3、4 直到 city 的值不滿足查詢條件為止

6）對(duì) sort_buffer 中的數(shù)據(jù)按照字段 username 做快速排序

按照字段 username 做快速排序這個(gè)動(dòng)作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和 sort_buffer 的大小，由參數(shù) sort_buffer_size 決定。

如果要排序的數(shù)據(jù)量小于 sort_buffer_size，排序就在內(nèi)存中完成。但如果排序數(shù)據(jù)量太大，內(nèi)存放不下，則就需要利用磁盤臨時(shí)文件來輔助排序。

解釋下這里使用磁盤臨時(shí)文件來進(jìn)行輔助排序的含義，外部排序常用的排序算法是多路歸并排序算法，具體步驟如下：

• 到主鍵 id 索引樹上查找到對(duì)應(yīng)的整行數(shù)據(jù)后，取 city、username、age 三個(gè)字段的值，存入 sort_buffer 中，能存多少是多少，當(dāng) sort_buffer 快要滿時(shí)，就對(duì) sort_buffer 中的數(shù)據(jù)進(jìn)行排序，排完后，把數(shù)據(jù)臨時(shí)放到磁盤的一個(gè)小文件中，然后清空 sort_buffer（這樣的話，一個(gè)很大的數(shù)據(jù)，就會(huì)被分成若干個(gè)臨時(shí)磁盤文件）
• 繼續(xù)回到主鍵 id 索引樹取數(shù)據(jù)，重復(fù)上一步，直到取出所有滿足條件的數(shù)據(jù)
• 最后，歸并已經(jīng)有序的若干個(gè)臨時(shí)磁盤文件，形成一個(gè)完整的有序大文件

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

可以看出，整個(gè)排序過程，我們要查詢的 city、username、age 全都參與了，所以，暫且把這個(gè)排序過程，稱為全字段排序

整條語句的執(zhí)行流程的示意圖如下所示：

針對(duì)上面利用磁盤臨時(shí)文件進(jìn)行輔助排序的過程，不知道大家會(huì)不會(huì)有個(gè)很自然的想法：sort_buffer 內(nèi)存放不下，需要用到臨時(shí)磁盤文件，磁盤文件越多，排序效率顯然就會(huì)越低下。那為什么還要把排序不相關(guān)的字段 city、username 放到 sort_buffer 中呢？只存放排序相關(guān)的 age 字段，這樣劃分的磁盤文件不就相對(duì)變少了嘛~

這就是 rowid 排序 ??

rowid 排序

rowid 排序，聽名字大概就能理解，就是，只把需要用于排序的字段和對(duì)應(yīng)的主鍵 id，放到 sort_buffer 中。

那怎么確定走的是全字段排序還是 rowid 排序呢？

實(shí)際上有個(gè)參數(shù)控制的。這個(gè)參數(shù)就是 max_length_for_sort_data，是 MySQL 中專門控制用于排序的行數(shù)據(jù)的長(zhǎng)度的一個(gè)參數(shù)。它的意思是，如果單行的長(zhǎng)度超過這個(gè)值，MySQL 就認(rèn)為單行太大（那么數(shù)據(jù)量肯定就越大，sort_buffer 可能不夠用），不能再像之前那樣把所有 select 的字段都存進(jìn) sort_buffer 了，要換一個(gè)算法，只存排序相關(guān)的字段

show?variables?like?'max_length_for_sort_data';

可以看到，max_length_for_sort_data 的默認(rèn)值是 1024

可以通過下面這行命令進(jìn)行修改

SET?max_length_for_sort_data?=?16;

表中我們定義的這三個(gè)字段 city、username、age 的總長(zhǎng)度是 36，我把 max_length_for_sort_data 設(shè)置為 16，顯然，單行的長(zhǎng)度已經(jīng)超過這個(gè)值了，排序算法應(yīng)該由全字段排序轉(zhuǎn)成了 rowid 排序。

整個(gè)執(zhí)行流程就變成如下所示的樣子：

1）初始化 sort_buffer，放入兩個(gè)字段，即 username 和主鍵 id

2）從 city 索引中找到第一個(gè)滿足 city='南京' 條件的主鍵 id

3）到主鍵 id 的索引樹上查找到對(duì)應(yīng)的整行數(shù)據(jù)（回表查詢），取出 username 和 id 這兩個(gè)字段，存入 sort_buffer 中

4）從 city 索引中取下一個(gè)記錄的主鍵 id；重復(fù)步驟 3、4 直到不滿足 city='南京' 的條件為止

5）對(duì) sort_buffer 中的數(shù)據(jù)按照字段 username 進(jìn)行排序

6）遍歷排序結(jié)果，取前 1000 行，并按照 id 的值回到主鍵 id 的索引樹中取出 city、username 和 age 三個(gè)字段返回給客戶端

可以看到，新的 rowid 算法放入 sort_buffer 的字段，只有要排序的列（即 username 字段）和主鍵 id。但有利有弊，存放在 sort_buffer 中的數(shù)據(jù)因?yàn)樯倭?city 和 age 字段的值，所以不能直接返回給客戶端了，需要再進(jìn)行一次回表查詢。

這個(gè)執(zhí)行流程的示意圖如下：

從上面我們可以看出來，事實(shí)上，如果內(nèi)存足夠大的話，MySQL 優(yōu)先選擇的仍然是全字段排序，把需要的字段都放到 sort_buffer 中，這樣排序后就會(huì)直接從內(nèi)存里面返回查詢結(jié)果了，不用再回表查詢，減少磁盤訪問。

回表的話應(yīng)該首先去緩沖池 Buffer Pool 中找到對(duì)應(yīng)版本的數(shù)據(jù)，若找不到，則需要進(jìn)行磁盤讀（索引文件是磁盤文件），理論上不會(huì)觸發(fā)磁盤讀，因?yàn)槿?id 的時(shí)候已經(jīng)從磁盤讀取了一次放到了緩沖池 Buffer Pool 中了，但不排除，第一次取完數(shù)據(jù)放到 sort buffer 后緩存中的數(shù)據(jù)頁被淘汰了，可能會(huì)觸發(fā)磁盤讀

order by 優(yōu)化

很顯然，如果不排序就能得到正確的結(jié)果，那對(duì)系統(tǒng)的消耗會(huì)小很多，語句的執(zhí)行時(shí)間也會(huì)變得更短。

那么，是不是所有的 order by 都需要排序操作呢？

并不是！

從上面分析的執(zhí)行過程我們可以看到，MySQL 之所以需要 sort_buffer，并且在 sort_buffer 上做排序操作，其原因是原來的數(shù)據(jù)都是無序的。

回顧下我們的需求：查詢出 city 是 “南京” 的所有 username，并且按照 username 進(jìn)行排序，返回前 1000 個(gè)人的姓名、年齡。

那，如果能夠保證從 city 這個(gè)索引上取出來的數(shù)據(jù)行，已經(jīng)天然就是按照 username 進(jìn)行遞增排序的話，不就不用再排序了嗎

所以，我們可以在這張表上創(chuàng)建一個(gè) city 和 username 的聯(lián)合索引：

alter?table?user?add?index?idx_city_username(city,?username);

在這個(gè)聯(lián)合索引上，我們依然可以用樹搜索的方式定位到第一個(gè)滿足 city='南京' 的記錄，并且額外確保了，接下來按順序取 “下一條記錄” 的遍歷過程中，只要 city 的值是南京，username 的值就一定是有序的（不清楚的小伙伴可以回看下聯(lián)合索引相關(guān)的知識(shí)）。

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

1）從聯(lián)合索引 (city, username) 上找到第一個(gè)滿足 city='南京' 條件的主鍵 id

2）到主鍵 id 的索引樹上查找到對(duì)應(yīng)的整行數(shù)據(jù)（回表查詢），取出 username、city 和 age 這三個(gè)字段的值，作為結(jié)果集的一部分直接返回

3）從聯(lián)合索引 (city, username) 上取下一個(gè)記錄主鍵 id；

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

可以看到，這個(gè)查詢過程不需要 sort_buffer，也不需要排序，整個(gè)流程被大大縮短了。

再用 explain 分析下這條語句：

從圖中可以看到，Extra 字段中沒有 Using filesort 了，也就是不需要排序了。

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

說到這里，不知道有沒有小伙伴能夠察覺點(diǎn)什么

回表查詢！

是的，說了這么多，回表查詢這個(gè)東西一直都在啊，完全可以用上 覆蓋索引 來去掉回表過程啊~

不就是要回表取出 username、city 和 age 這三個(gè)字段的值嗎，咱就直接創(chuàng)建一個(gè) city、name 和 age 的聯(lián)合索引，對(duì)應(yīng)的 SQL 語句就是：

alter?table?user?add?index?idx_city_username_age(city,?username,?age);

這樣，整個(gè)流程就被進(jìn)一步簡(jiǎn)化：

1）從聯(lián)合索引 (city, username, age) 樹上找到第一個(gè)滿足 city='南京' 條件的記錄，把這條記錄作為結(jié)果集的一部分直接返回；

2）從聯(lián)合索引 (city, username, age) 樹上取下一個(gè)記錄，同樣將這條記錄作為結(jié)果集的一部分直接返回

3）重復(fù)執(zhí)行步驟 2，直到查到第 1000 條記錄，或者是不滿足 city='南京' 條件時(shí)循環(huán)結(jié)束

如下圖所示：

當(dāng)然了，使用覆蓋索引性能上會(huì)快很多，但是索引的維護(hù)也是需要代價(jià)的，這里需要自己做一個(gè)權(quán)衡取舍~

最后放上這道題的背誦版：

?? 面試官：SQL 優(yōu)化了解過嗎？

?? 小牛肉：我來說一下 order by 語句的優(yōu)化。

1. order by 的基本原理其實(shí)就是 MySQL 會(huì)給每個(gè)線程分配一塊內(nèi)存也就是 sort_buffer 用于排序，sort_buffer 中存儲(chǔ)的是 select 涉及到的所有的字段，可以稱為全字段排序吧。排序這個(gè)動(dòng)作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和 sort_buffer 的大小，由參數(shù) sort_buffer_size 決定。如果要排序的數(shù)據(jù)量小于 sort_buffer_size，排序就在內(nèi)存中完成。但如果排序數(shù)據(jù)量太大，內(nèi)存放不下，就需要利用磁盤臨時(shí)文件來輔助排序。
2. 這里其實(shí)可以優(yōu)化下，只存放排序相關(guān)的字段，而不是 select 涉及的所有字段，這樣 sort_buffer 中存放的東西就多一點(diǎn)，就盡可能避免使用磁盤進(jìn)行外部排序，或者說使得劃分的磁盤文件相對(duì)變少，減少磁盤訪問。這種排序稱為 rowid 排序。如果表中單行的長(zhǎng)度超過 max_length_for_sort_data 定義的值，那 MySQL 就認(rèn)為單行太大（那么數(shù)據(jù)量肯定就越大，sort_buffer 可能不夠用），由全字段排序改為 rowid 排序。

以上是我們說的關(guān)于 order by 的兩個(gè)參數(shù)優(yōu)化，還可以根據(jù)索引進(jìn)行一些優(yōu)化

1. 以 select a, b, c from table where a = xxxx order by b 為例，我們?yōu)椴樵儣l件 a 和排序條件 b 建立聯(lián)合索引，聯(lián)合索引就是 a 是從小到大絕對(duì)有序的，如果 a 相同，再按 b 從小到大排序，這樣就不需要排序了，直接避免了排序這個(gè)操作。
2. 還可以進(jìn)一步優(yōu)化，由于聯(lián)合索引 (a, b) 中沒有 c 的值，所以從聯(lián)合索引樹上獲取符合條件的對(duì)應(yīng)主鍵 id 后，還需要回表查詢?nèi)〕?a b c 的值，這個(gè)回表查詢的過程可以通過建立 (a,b,c) 覆蓋索引來避免。

心之所向，素履以往，我是小牛肉，小伙伴們下篇文章再見 ??