MySQL 在進行 Order By 操作排序時，通常有兩種排序方式：
- 全欄位排序
- Row_id 排序
MySQL 中每個執行緒在執行排序時，都會被分配一塊區域 - sort buffer，它的大小通過 sort_buffer_size 控制。
全欄位排序指的是，將要查詢的欄位，全都存入 sort buffer 中，然後對 sort buffer 進行排序，然後將結果返回給客戶端。
Row_id 排序：將被排序的欄位和對應主鍵索引的 ID 放入，sort buffer 中，然後對 sort buffer 進行排序，最後額外進行一次回表操作查詢額外的資訊，然後將結果返回給客戶端。
全欄位排序和 Row_id 排序的主要區別在：
- sort buffer 存入的內容不同
- 回表查詢的次數不一致。
對於 InnoDB 表來說，在記憶體足夠的情況下，會優先選擇全欄位排序的方式。在記憶體不足的情況下，可能會借用外部檔案進行排序。
但如果單行內容較大時，會導致拆分的外部檔案過多，進行歸併排序時，效率變低。此時會採用 Row_id 的排序方式。
對於 Memory 表來說，會優先選擇 Row_id 的排序方式。
接下來會對全欄位排序和 Row_id 排序進行驗證，最後並給出一些調優的技巧。
環境準備
假設存在如下表結構，表裡有 5萬的資料行, 其中 type 為二級索引。
# MySQL5.7.28, RR
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`type` varchar(16) NOT NULL,
`phone` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=InnoDB;
向表中插入資料：
import random
import MySQLdb
def prepare_data():
result = []
type = ["a", "b", "c", "d", "e"]
for i in range(50000):
index = random.randint(0, 4)
result.append((str(i), str(type[index]), str(i + 10), str(i)))
return result
def insert_data():
db = MySQLdb.connect(host='10.124.207.xxx',
user='xxxx',
passwd='xxxxx',
db='usecase',
charset='utf8')
sql = 'INSERT INTO test_table ( name, type, phone, addr) VALUES ( %s, %s, %s, %s);'
cur = db.cursor()
cur.executemany(sql, prepare_data())
db.commit()
db.close()
if __name__ == '__main__':
try:
insert_data()
except Exception as e:
下面會進行查詢操作：
``select name,type,phone from test_table where type='a' order by name limit 1000 ;`
並對排序的過程進行分析。
全欄位排序
這裡對該查詢語句進行了 EXPLAIN 操作，可以看到在 Extra 列 ：
- 用到了索引
- 進行了排序操作 - filesort （無法利用索引預設有序的情況）
針對本次 SELECT 來說，經歷如下的過程：
- 使用 type = a 的二級索引，找到滿足的第一個值。
- 根據該值，找到主鍵 ID。回表去找 name 和 phone 的值。
- 然後將
type,name, phone存入 sort_buffer。 - 然後重複 1 - 4 過程，查詢所有 type =a 的內容，然後將 name, phone, type 存入 sort_buffer。
- 然後對分配記憶體裡的資訊進行排序。
- 然後選擇前 1000 條，返回給客戶端。
開啟 optimizer_trace ，檢視執行的流程：
可以看到，對應 "sort_mode": 中的內容為 sort_key 和其他欄位，這就表示用的是全欄位排序。
chosen: true 表示使用的是優先佇列的排序演算法。
但在實際情況下，往往會出現待排序的內容大於分配用於排序的空間，此時就會用到外部的檔案排序，而這種外部排序一般都使用歸併排序。
將 sort_buffer 調小，重新執行：
將預設大小臨時改小：
# 將 size 調小, 並重新登入
set sort_buffer_size= 24 * 1024;
/* 開啟optimizer_trace，只對本執行緒有效 */
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';
/* 執行語句 */
select type,name,phone from test_table where type='a' order by name limit 1000 ;
/* 檢視 OPTIMIZER_TRACE 輸出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b儲存Innodb_rows_read的當前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 計算Innodb_rows_read差值 */
select @b-@a;
在 OPTIMIZER_TRACE 中檢視：
number_of_tmp_files 表示使用的臨時檔案數為 20，上面的 chosen 表示由於空間不足，無法使用堆排序。
可以發現：
-
當
sort_buffer_size大於要待排序的內容，則使用記憶體排序。 -
如果小於，則使用外部臨時檔案輔助排序。
但還有一種情況，就是待排序的內容資料量太大或者單行查詢的欄位太多（如 SELECT *）這種情況，會導致生成的臨時檔案數量太多，效率不高。所以就出現了另一種 Row_id 的排序方式。
Row_id 排序
在單行查詢欄位很多時，在 sort_buffer 中僅僅儲存必要的欄位，最後額外再進行一次統一回表的操作，查詢必要的資訊。
這裡可以將 SET max_length_for_sort_data = 16; 改小，模擬這種情況。
這裡 sort_mode: sort_key, rowid 指的就是用的 Row_id 這種處理方式。
對應經歷的過程就會變成：
- 使用 type = a 的二級索引，找到滿足的第一個值。
- 根據該值，找到主鍵 ID。回表去找 row_id 的值。（發生變化）
- 然後將
type, row_id存入 sort_buffer。（發生變化） - 然後重複 1 - 4 過程，查詢所有 type = a 的內容，然後將 type, row_id 存入 sort_buffer。（發生變化）
- 然後對分配記憶體裡的資訊進行排序。
- 然後選擇前 1000 條，並取到需要的 row_id 集合。
- 根據 row_id，回表查詢所需要的資訊，然後返回給客戶端。（發生變化）
這裡 2, 3, 4, 7 和之前全欄位排序相比，發生了變化。
但需要注意的是，在記憶體足夠的情況下，InooDB 會優先選擇全排序的方式。但對於 Memory 方式的表結構，則會有不同的選擇。
記憶體臨時表的排序選擇
在如使用 Union 或者 Group By 等查詢的情況下，會建立臨時表，採用 Memory 作為儲存的引擎。
而對於記憶體臨時表，會優先採用 row_id 排序。
因為記憶體臨時表，本身會在原表基礎上，新建一張臨時表儲存需要的資訊。因為臨時表本身就在記憶體中，所以最後一次回表的操作，不會進行額外的磁碟 IO。所以 MySQL 會優先選擇 row_id 的排序方式。
優化方法
場景1：利用索引有序，讓 Order by 不排序
之前僅僅有 type 型別的索引，可以將其改成 type, name 的聯合索引：
alter table test_table add index type_name(type, name);
在建立聯合索引時，因為本身有序。在查詢 typem,name,phone 時，會將 sort buffer 中的排序過程省略，也就是全排序過程中的第五步。
可以看到，在 Extra 中 filesort 的過程，已經被省略了。
場景2：利用覆蓋索引，簡化回表流程
alter table test_table add index type_name(type, name, phone);
這裡由於想要查詢的欄位，都已經在二級索引上了。所以不需要進行回表，而且本身也是有序的。
並且可以發現，Extra 變成了 Using index. 表示直接使用了索引。
場景3：IN 導致 Order By 需要排序
假設存在 type_name(type, name); 聯合索引。
將之前的 type = 換成了 IN，由於是對 a 和 b 兩個型別同時排序，所以就需要 filesort 操作。
如果不想讓 MySQL 進行排序的操作，可以將 IN 拆分成多個 = 執行，然後在呼叫端，自己進行合併。
總結
在 MySQL 中，使用 Order BY 時，通常有全欄位排序和 Row_id 兩種排序方式。
對於 InooDB 來說，在記憶體足夠的情況下，會優先選擇全欄位排序。在記憶體不足，並所需排序內容不多時，會採用外部歸併排序的方式。
但如果所在單行內容太大，導致拆分檔案過多的情況下，會選擇 Row_id 的排序方式。
對於 Memory 表來說，由於本身就在記憶體中，所以會優先選擇 Row_id 的排序方式。
使用 Order By 操作時，不一定真的意味著真的去做排序，可以利用索引本身有序，或者覆蓋索引，拆分 SQL 的方式，減少 MySQL 的排序過程。
參考
題外話：最近在系統的學習 MySQL，推薦一個比較好的學習材料就是<<丁奇老師的 MySQL 45 講>>，連結已經附在文章末尾。
文章中很多知識點就是從中學來，加入自己的理解並整理的。
大家在購買後，強烈推薦讀一讀評論區的內容，價值非常高，不少同學問出了自己在思考時的一些困惑。
