相信很多人在面試過程中，總被問到有沒有 SQL 調優經驗，對於工作經驗年限較少的求職者，通常都是在面試之前從網上百度一些答案，提前背熟，然後面試的時候直接將提前背好的答案說出來。筆者作為一名菜鳥，在剛滿一年工作經驗的時候，出去面試，就是這麼幹的。記得去某家公司面試的時候，被面試官問到 order by 在排序的時候執行較慢，這個時候該如何優化？我當初想都沒想，就回答說給 order by 子句中的欄位加上索引（當然這答案也是我提前從網上百度來的），接著面試官問為什麼加索引就能提高 order by 的執行效率的時候，我就懵逼了，這我哪知道為什麼啊，百度也沒告訴我啊。後來面試自然也就黃了。
現在回想一下，當初是真的菜啊。不過話說回來，為什麼給 order by 子句中的欄位加索引就能加快 SQL 的執行？它一定能提高 SQL 的效率嗎？為了搞清楚這些問題，我們就得從 order by 的實現原理上說起了。
示例表為了方便說明舉例，我們先創建一個示例表，建表語句如下。
CREATE TABLE `user` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主鍵id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份證號碼',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年齡',
PRIMARY KEY ( `id` ),
INDEX ( `name` )
) ENGINE = INNODB COMMENT '用戶表';
insert into `user`(id_card,name,age) values
('429006xxxxxxxx2134','張三',22),
('429006xxxxxxxx2135','李四',26),
('129006xxxxxxxx3136','王五',28),
('129106xxxxxxxx3337','趙六',17),
('129106xxxxxxxx3349','孫XX',43),
('129106xxxxxxxx3135','馬大哈',39),
('129106xxxxxxxx3134','王一',55),
('139106xxxxxxxx2236','張三',7),
('139106xxxxxxxx2130','張三',31),
('439106xxxxxxxx2729','張三',29),
('439106xxxxxxxx2734','李明',78),
('429106xxxxxxxx1734','張三',96),
('129106xxxxxxxx1737','張三',89),
('129106xxxxxxxx1132','張三',3),
('129106xxxxxxxx1197','張三',11),
('129106xxxxxxxx1184','張三',14);我們創建了一張用戶表，表中有 4 個欄位：id 是自增的主鍵，id_card 表示用戶的身份證號碼，name 表示用戶姓名，age 表示用戶年齡，另外我們除了主鍵索引 id 外，還為 name 欄位創建了一個普通索引。最後向表中插入了幾條數據，方便後面舉例。
假設我們現在有如下需求：按照年齡從小到大，查詢前三個姓名叫做張三的用戶的身份證號碼、姓名、年齡。對應的 SQL 語句應該這麼寫：
select id_card,name,age from user where name = '張三' order by age limit 3;這條 SQL 語句邏輯比較簡單，很好理解，語句在執行時會使用到 name 索引樹，並且會進行排序，我們可以通過關鍵字 Explain 來查看一下 SQL 的執行計劃。
explain select id_card,name,age from user where name = '張三' order by age limit 3 \G上圖中 key 這一行的值為 name，這表示本次查詢會使用到 name 索引；Extra 這一行的值為 Using filesort，這表示本次查詢需要用到排序操作。接下來我們來看下，MySQL 的排序過程。
全欄位排序首先 MySQL 會為每個查詢線程分配一塊內存，叫做 sort_buffer，這塊內存的作用就是用來排序的。這塊內存有多大呢？由參數 sort_buffer_size 控制，可以通過如下命令來查看和修改：
# 查看sort_buffer的大小
show variables like 'sort_buffer_size';
# 修改sort_buffer的大小
set global sort_buffer_size = 262144;針對上面我們提到的示例，我們來看下這個排序流程：
首先 MySQL 為對應的線程分配一塊大小為 sort_buffer_size 的內存，然後確認要向內存中放入哪些欄位，由於本文示例中要查詢的是 id_card、name、age 這三個欄位，因此這塊 sort_buffer 中要存放的欄位是 id_card、name、age；通過前面的執行計劃我們已經知道了該條 SQL 語句在執行時會使用 name 索引樹，所以存儲引擎會先在 name 索引樹中找到第一個 name="張三"的葉子結點，然後返回葉子結點中存放的主鍵 id 值；根據上一步中返回的主鍵 id，進行回表，回到主鍵索引樹上查找到對應 id 的數據，並取出 id_card、name、age 這三個欄位的值，返回給 MySQL 的 server 層，放入到 sort_buffer 中；繼續在 name 索引樹中查找下一條 name="張三"的結點，重複步驟 2、3，直到在 name 索引樹上找到第一條 name 不等於張三時，停止查找。前面 4 步已經查找到了所有 name 為張三的數據，接下來就是在 sort_buffer 中，將所有數據根據 age 進行排序；整個過程的示意圖如下：
這個排序過程，因為是將查詢的所有欄位都放入到了 sort_buffer 中（id_card、name、age），因此也被稱之為全欄位排序。
看到這裡，有人肯定就會有疑問了，如果我們查詢的數據量太大，符合 name="張三"的數據有很多條，以至於 sort_buffer 這塊內存無法裝下所有數據，那這個時候我們肯定無法在 sort_buffer 內存中實現所有的排序了，那又該怎麼辦呢？答案就是藉助磁碟文件來進行排序。
藉助磁碟文件進行排序的時候，通常使用歸併排序算法。當從主鍵 id 索引樹中查詢到數據時，將數據放入到 sort_buffer 中，當 sort_buffer 中快滿時，就在 sort_buffer 中對這部分數據進行排序，然後將排好序的數據臨時存放進磁碟的一個小文件中，然後再繼續從主鍵索引樹中查詢數據，再在 sort_buffer 中進行排序，寫入磁碟的臨時文件中，循環操作，直到所有數據讀取完。最後將磁碟上的這些數據有序的小文件，合併成一個有序的大文件，這樣就完成了所有數據的排序操作。
從這個過程中，我們可以發現，在要排序的數據的大小一定額情況下，如果 sort_buffer 的大小越小，即 sort_bufer_size 越小，那麼我們在藉助磁碟排序時，需要的臨時文件越多，那麼發生 IO 的次數就越多，性能也就越差。
雖然我們知道了全欄位排序原理，也能通過查詢資料庫的配置知道 sort_buffer_size 的大小，但是我們該如何知道我們的 SQL 在執行排序時有沒有藉助磁碟文件進行排序呢？我們可以通過 MySQL 中 information_schema 庫中 optimizer_trace 表來查看 SQL 執行的優化信息，但是默認情況下 optimizer_trace 的開關是關閉的，因為它記錄的是 SQL 相關的優化信息，會額外消耗 MySQL 的資源。我們可以通過如下命令來查看和修改 optimizer_trace 的狀態
# 查看
show variables like 'optimizer_trace';
# 臨時針對當前資料庫連接修改(連接斷開後，下次再連接資料庫時，該值還是false)
set optimizer_trace = "enabled=on";
# 針對所有資料庫連接修改
set global optimizer_trace = "enabled=on";開啟了 optimizer_trace 統計信息之後，我們就可以從這張表中查看 SQL 執行的相關信息了。以文章前面示例為例，我們一次執行以下 SQL：
# 開啟統計
set optimizer_trace = "enabled=on";
# 執行查詢SQL
select id_card,name,age from user where name = '張三' order by age limit 3;
# 查詢統計信息
select * from information_schema.optimizer_trace \G最終我們可以看到如下圖所示的統計信息（我只截取了一部分）
圖中的 number_of_tmp_files 這一行表示的是排序時使用到的臨時文件的個數，如果為 0，則表示的是本次排序沒有藉助磁碟文件排序，如果大於 0，則表示藉助了磁碟文件排序。由於我電腦上安裝的 MySQL，默認 sort_buffer_size 為 256KB，例子中查詢的數據量也比較小，所以完全可以在 sort_buffer 內存中完成排序，不需要藉助磁碟文件。
如果想要演示藉助磁碟文件排序，可以先將 sort_buffer_size 設置為一個很小的值，然後再執行查詢操作，最後再看看 optimizer_trace 中統計的信息。由於本文的數據太少，而 sort_buffer_size 最小可以被設置為 32KB，不能比 32KB 還小，所以最終還是使用的是內存排序，因此就不再做示範了，你可以在工作中的測試環境中試試，驗證一下。
rowid 排序看懂了上面的全欄位排序，可能有人就會有疑問了，我們實際上只需要對 name 欄位排序，為什麼還需要把 id_card 欄位和 age 欄位也放入到 sort_buffer 中呢？而且 sort_buffer 本身是有內存大小限制的，sort_buffer 中放入的欄位越多，那它能存放的數據條數就越少，如果要對多條數據排序，那就很有可能需要用到磁碟文件排序了，顯然磁碟文件排序沒有內存排序快。
既然知道了全欄位排序的缺點，那麼我們該怎麼改進呢？這一點 MySQL 的開發人員早就已經考慮到了，因此就有了另外一種排序方式，暫且叫它 rowid 排序吧（為什麼是暫且呢？因為我在 MySQL 官方文檔中並沒有找到這種說法，這種說法是在極客時間上《MySQL 實戰 45 講》中看到的，不過具體叫什麼不重要，重要的是知道其中的原理）。
rowid 排序原理的大致思路就是，不會將 SQL 語句中 select 後面的所有欄位都放入到 sort_buffer 中，而是只會將需要被排序的欄位和主鍵 id 放入到 sort_buffer 中，對應到本文的例子中就是：將 name 欄位和主鍵 id 欄位放入到 sort_buffer 中。
在實際開發過程當中，有的表我們沒有創建主鍵索引，那這個時候 MySQL 會判斷表中有沒有唯一索引，如果有唯一索引，那就會將這個唯一索引當做主鍵；如果也沒有唯一索引，那麼 MySQL 會默認為每一行數據生成一個 rowid，這個 rowid 作用和主鍵作用一樣，那麼在排序的時候，放入到 sort_buffer 中的欄位就是被排序的欄位和 rowid 了，這也是為什麼叫它 rowid 排序的由來了。
前面我們說了全欄位排序會將不需要排序的欄位也放入到 sort_buffer 中，這些欄位會佔用內存，當這些欄位多到一定程度時，MySQL 會認為全欄位排序這種排序方式可能會需要藉助磁碟文件排序，會影響性能，因此就將排序方式改為 rowid 排序。那麼這個「一定程度」到底是個什麼程度呢？它是由參數 max_length_for_sort_data 控制的，這個參數表示的是當需要放入到 sort_buffer 中的欄位的長度之和超過這個參數值時，就會使用 rowid 排序。你可以通過如下命令查看該參數的值。
show variables like 'max_length_for_sort_data';max_length_for_sort_data 參數的默認值為 1024 個字節，對於本文中的示例，由於 id_card、name、age 這三個欄位的總長度加起來肯定是小於 1024 個字節的，沒有超過 max_length_for_sort_data 的限制，因此不會使用 rowid 排序。
為了看看 rowid 的排序流程，我先將 max_length_for_sort_data 的值設置為一個較小的值，例如 16 個字節，這樣 id_card、name、age 這三個欄位的長度之和就超過了這個限制，因此後面排序時會使用 rowid 排序算法。
# 限制設置為16個字節
set max_length_for_sort_data = 16;
# 查詢數據
select id_card,name,age from user where name = "張三" order by age limit 3以上面的查詢 SQL 為例，rowid 的排序流程如下：
首先 MySQL 為對應的線程分配一塊大小為 sort_buffer_size 的內存，然後確認要向內存中放入哪些欄位，由於示例中要查詢的是 id_card、name、age 這三個欄位，而這三個欄位的長度之和超過了 max_length_for_sort_data 的限制，所以採用 rowid 排序， 因此這塊 sort_buffer 中要存放的欄位是 age 和主鍵 id；存儲引擎先在 name 索引樹中找到第一個 name="張三"的葉子結點，然後返回葉子結點中存放的主鍵 id 值；根據上一步中返回的主鍵 id，進行回表，回到主鍵索引樹上查找到對應 id 的記錄，並取出 age 欄位的值，返回給 MySQL 的 server 層，放入到 sort_buffer 中；繼續在 name 索引樹中查找下一條 name="張三"的結點，重複步驟 2、3，直到在 name 索引樹上找到第一條 name 不等於張三時，停止查找。前面 4 步已經查找到了所有 name 為張三的數據，並將要排序的欄位 age 和主鍵 id 放入到了 sort_buffer 中，接下來就是在 sort_buffer 中，將所有數據根據 age 進行排序；從排好序的數據中，取前 3 條數據。由於我們要查詢的數據是 id_card、name、age 這三個欄位，此時 sort_buffer 中只有 id 和 age 欄位，因此此時還需要根據取到的三條數據的 id，會到主鍵索引樹上讀取 id_card、name、age 的值；最後將 id_card、name、age 欄位的數據返回。這個流程的示意圖如下：
從這個流程中，我們可以發現，相比全欄位排序而言，rowid 排序的回表次數更多。
同樣，我們也可以查看一下 rowid 排序時，optimizer_trace 中記錄的信息。執行語句如下：
select * from information_schema.optimizer_trace\G從圖中我們可以看到，sort_mode 這一行顯示的 rowid，這說明本次排序使用的是 rowid 排序，而對於全欄位排序，則顯示的不是 rowid。
order by 優化思路理解了全欄位排序和 rowid 排序的原理，現在我們可以思考一下該如何優化排序的 SQL。
1. 調整 sort_buffer_size 大小首先，無論是全欄位排序還是 rowid 排序，它們都會受到 sort_buffer 內存大小的影響，如果數據過多，就到導致藉助磁碟文件排序。藉助磁碟文件排序，很產生磁碟 IO，性能差，顯然這不是我們所期望的，我們應該儘量避免。如果參數 sort_buffer_size 太小，而 MySQL 伺服器的配置又較高，我們可以嘗試將 sort_buffer_size 設置得大一點。
2. max_length_for_sort_data當查詢欄位的長度超過 max_length_for_sort_data 的限制後，MySQL 就會採用 rowid 排序，但是 rowid 排序會產生更多的回表次數，這可能會造成磁碟讀，也會降低查詢性能，所以為了避免 MySQL 使用 rowid 排序，我們可以將 max_length_for_sort_data 參數的值適當調大一點。
max_length_for_sort_data 參數的值，MySQL 默認是 1024，即 1KB。我個人覺得這個值已經很大了，1024 個字節已經可以包含很多欄位了，按照平均每個欄位 8 個字節來算（變長的 varchar 類型除外），差不多可以容納 256 個欄位了。如果你的查詢 SQL 中要查詢的欄位長度超過了 1024 個字節，這極有可能是 SQL 寫得有問題了，我們可以嘗試去優化 SQL，而不是去調整 MySQL 的系統參數。例如通過減少查詢欄位，分多次查詢，或者通過中間表來優化 SQL（這個地方也再次印證了儘量不要使用 select * 這類 SQL 語句的說法）。總之我個人覺得，max_length_for_sort_data 參數的值儘量不要調整。
說到這裡，《高性能 MySQL》一書中，在第八章開頭，作者曾提到過，MySQL 雖然為我們提供了很多可以配置的系統參數，但是這些參數大部分我們都可以直接採用默認值，只有極少數需要我們根據實際場景去調整。如果我們過多的去調整參數，並且還沒有經過實際生產驗證，極有可能起到反作用。
3. 使用聯合索引因為數據是亂序的，所以我們要對數據進行排序，那如果假設數據本身就是有序的，那麼我們就不用再對數據進行排序操作了，避免了後面 sort_buffer 內存大小、磁碟文件排序等一系列問題了。我們都知道，MySQL 中索引的數據結構使用的是 B+Tree，該數據結構的一大特點就是索引值是有序存放的，那麼我們可以利用有序性這一特點，來避免排序工作。
對於本文示例中的 SQL，如果 age 欄位在索引樹上本身就是有序的，那麼我們就不用再額外在 sort_buffer 中排序了，因此我們可以考慮建立一個 name 和 age 的聯合索引：index(name,age)。
再繼續思考，因為我們需要查詢 id_card、name、age 這三個欄位的信息，而 index(name,age)這個聯合索引上只有 name 和 age 的欄位值，這意味著雖然我們可以通過這個聯合索引避免掉排序操作，但是我們還需要回到主鍵索引樹上取 id_card 欄位的值，也就是需要回表，回表可能又造成磁碟讀，所以我們還有優化空間。
如果看過我這篇文章的朋友《MySQL 索引的工作原理》，這個時候可能會想到，避免回表操作最常用的手段就是使用覆蓋索引技術，所以這個時候我們可以創建 name、age、id_card 這三個欄位的聯合索引：index(name,age,id_card)，這樣聯合索引樹上存的數據，已經全部滿足了我們要查詢的數據，所以不需要再進行回表操作了。SQL 語句如下：
# 我們先刪除前面為name欄位創建的索引
alter table user drop index `name`;
# 創建name、age、id_card的聯合索引
alter table user add index(`name`,`age`,`id_card`);
# 使用explain關鍵字，查看一下SQL的執行計劃
EXPLAIN select id_card,name,age from user where name = "張三" order by age limit 3\G;SQL 的執行計劃如下圖：
從圖中，我們可以發現，Extra 這一行變成了 Using index，沒有了 Using filesort。Using index 表示使用到了覆蓋索引，沒有 Using filesort 表示本次 SQL 的執行不需要用到 sort_buffer 進行排序操作。
需要額外說明的是，本文示例 SQL 中，只查詢了 name="張三"的數據，所以我們能保證在聯合索引 index(name,age,id_card)上，age 欄位的值是有序的。如果我們的查詢條件為 name in ("張三","王五")，那麼就無法保證 age 欄位是有序的了，因為聯合索引中，是先保證第一列有序，再依次保證後面的列有序，所以這個時候還是得排序。如果還想利用這個特性，這個時候我們可以分兩次查詢，然後在應用程式的內存中進行數據的排序，如：
# 分兩次查詢
select id_card,name,age from user where name = '張三' order by age limit 3;
select id_card,name,age from user where name = '李四' order by age limit 3;
# 然後在應用程式中自己排序
解答開篇在文章開篇處我提到了一個問題：給 order by 子句中的欄位添加索引，就一定能加快 SQL 的執行效率嗎？現在我們來做個實驗，示例表和數據還是前面的，不同的是我們將除了主鍵以外的索引都刪除，然後再為 age 欄位創建一個索引。最終 user 表中，id 為主鍵索引，age 列有一個普通索引。然後我們用 Explain 關鍵字查看一下如下 SQL 的執行計劃:
explain select id_card,name,age from user order by age limit 3 \G執行計劃如下圖所示。
從圖中我們可以發現，key 那一行的值為 null，這說明沒有使用到 age 索引；type 這一行的值為 ALL，這說明進行了全表掃描；Extra 這一行的值為 Using filesort，這說明在 sort_buffer 進行了排序。
看到這裡是不是有點毀三觀啊，我們為 age 列創建了索引，怎麼就沒有使用呢？
這是因為我們要查詢 id_card、name、age 這三個欄位，而 age 索引樹上沒有存放這些信息，所以最終還是回表到主鍵索引樹上查詢這些信息。這個時候 MySQL 認為，雖然 age 索引樹上 age 欄位值是有序的，可以避免排序操作，但是它需要回表到主鍵索引樹去取其他欄位的信息，MySQL 認為這個回表操作所消耗的性能大於避免排序操作所節省的性能，所以乾脆就直接掃描主鍵索引樹了，而不使用 age 索引樹了。
繼續實驗，我們的 user 表中的數據太少了，一共只有 16 條，現在我們增加一點數據，我寫了個簡單的存儲過程，向資料庫中插入了 10 萬條數據（為了簡單，每條數據的 name 和 id_card 的值都是瞎編的）。
delimiter ;;
create procedure fakeData()
BEGIN
DECLARE
i INT;
SET i = 1;
WHILE
( i <= 100000 ) DO
INSERT INTO user(id_card,name,age)
VALUES
( '429006xxxxxxxx2135', CONCAT('AA',i), i%100 ); # 身份證號碼都是一樣的（實際情況顯然不是這樣），姓名為AA+i，年齡為對i除以100取模
SET i = i + 1;
END WHILE;
END
delimiter ;;
# 執行存儲過程
call fakeData();現在表裡面大概有 10 萬行數據了，我們在用 Explain 查看一下上面的查詢過程：
explain select id_card,name,age from user order by age limit 3 \G查詢的執行計劃如下圖所示：
從圖中我們可以看到，type 為 index，key 為 age，這說明使用了 age 這個索引，並且 Extra 這一行顯示為 null，說明也不需要額外排序。同樣的 SQL 語句，因為表中的數據量不一樣，看到的卻是不同的執行計劃，這是為什麼呢？
這是因為表中的數據量比較多，id 主鍵索引樹這個時候有 10 萬行數據了，如果對 id 索引樹進行全表掃描的話，MySQL 會認為這個過程會比較費時。而通過走 age 索引樹，取排序前三的 age 所對應的 id，然後回表到主鍵索引樹取數據，這個過程相比較直接對 id 索引樹進行全表掃描執行得快，所以就決定走 age 索引樹了，也就是我們在執行計劃中看到的。
現在我們再看一下下面這個 SQL 語句的執行計劃：
explain select id_card,name,age from user order by age limit 1000 \G這條 SQL 語句和前面的也是類似，唯一的區別就是 limit 取了前 1000 條記錄，結果我們看到執行計劃的截圖當中，type 為 ALL，key 為 null，這些說明這條 SQL 語句沒有使用 age 索引，並且進行了全表掃描，Extra 這一行為 Using filesort，說明需要在內存中進行排序。
這又是為什麼呢？這是因為如果使用 age 索引樹的話，就得回表，回到主鍵索引樹中取數據。而 limit 為 1000，表示要取 1000 條數據，這就要回 1000 次表，MySQL 任務這個過程回表次數太多，消耗太大，還不如直接對主鍵索引樹進行全表掃描，所以沒有選擇 age 索引。
看了這三個例子，同樣的 SQL 邏輯，不同的是表中的數據量和要返回的數據條數，居然看到的執行計劃不一樣，有的使用了索引，有的沒有使用索引，產生這些現象的原因在於 MySQL 優化器是如何選擇的。因此在實際開發過程中，一條 SQL 語句究竟有沒有使用索引，我們需要先通過 Explain 查看了執行計劃才能確定，所以以後不要談到 SQL 優化，上來不管三七二十一就說創建索引了。
總結本文主要講解了 order by 排序的兩種方法，分別為全欄位排序和 rowid 排序，排序過程受系統參數 sort_buffer_size 和 max_length_for_sort_data 的影響。當查詢的數據量過大時，超過 sort_buffer_size 的大小，那麼就會藉助磁碟文件進行排序。如果查詢的欄位過多，每一行記錄的查詢欄位長度之和超過 max_length_for_sort_data 後，MySQL 會認為數據量過大，可能會超過 sort_buffer_size，因此會選擇使用 rowid 排序。
開發人員如何知道一條 SQL 語句的排序使用的是全欄位排序還是 rowid 排序呢？排序過程中有沒有藉助磁碟文件排序呢？可以通過查看 optimizer_trace 來查看，number_of_tmp_files 表示藉助臨時磁碟文件的個數，從 sort_mode 這一行中，可以知道是那種排序，如果顯示的是 rowid，那就是 rowid 排序，否則是全欄位排序。默認情況下，optimizer_trace 的開關是關閉的，因為統計這些信息，需要額外消耗 MySQL 伺服器的資源。
接著我們根據掌握的 order by 的排序原理，提供了幾種優化 order by 語句的思路，可以通過調整 MySQL 的系統參數 sort_buffer_size 和 max_length_for_sort_data，或者創建聯合索引來提高 SQL 的執行效率。
最後，我通過幾個例子，證明了 即使為 order by 子句中的欄位創建了索引，在執行時就一定會選擇索引，MySQL 的優化器會根據實際情況來抉擇是否使用索引。而我們在實際開發過程中，如果要對 SQL 語句優化，也應該是如此，結合實際場景，藉助 Explain 等工具，經過分析後再決定如何優化 SQL。
參考資料