全字段排序

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

圖 1 使用 explain 命令查看語句的執行情況

Extra 這個字段中的“Using filesort”表示的就是需要排序，MySQL 會給每個線程分配一塊內存用於排序，稱為 sort_buffer。

圖 2 city 字段的索引示意圖

通常情況下，這個語句執行流程如下所示 ：

1. 初始化 sort_buffer，確定放入 name、city、age 這三個字段；
2. 從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
3. 到主鍵 id 索引取出整行，取 name、city、age 三個字段的值，存入 sort_buffer 中；
4. 從索引 city 取下一個記錄的主鍵 id；
5. 重複步驟 3、4 直到 city 的值不滿足查詢條件為止，對應的主鍵 id 也就是圖中的 ID_Y；
6. 對 sort_buffer 中的數據按照字段 name 做快速排序；
7. 按照排序結果取前 1000 行返回給客戶端

圖 3 全字段排序

圖中“按 name 排序”這個動作，可能在內存中完成，也可能需要使用外部排序，這取決於排序所需的內存和參數 sort_buffer_size。

sort_buffer_size，就是 MySQL 為排序開闢的內存（sort_buffer）的大小。如果要排序的數據量小於 sort_buffer_size，排序就在內存中完成。但如果排序數據量太大，內存放不下，則不得不利用磁盤臨時文件輔助排序

圖 4 全排序的 OPTIMIZER_TRACE 部分結果

內存放不下時，就需要使用外部排序，外部排序一般使用歸併排序算法。可以這麼簡單理解，MySQL 將需要排序的數據分成 12 份，每一份單獨排序後存在這些臨時文件中。然後把這 12 個有序文件再合併成一個有序的大文件

rowid 排序

在上面這個算法過程裡面，只對原表的數據讀了一遍，剩下的操作都是在 sort_buffer 和臨時文件中執行的。但這個算法有一個問題，就是如果查詢要返回的字段很多的話，那麼 sort_buffer 裡面要放的字段數太多，這樣內存裡能夠同時放下的行數很少，要分成很多個臨時文件，排序的性能會很差。

\tSET max_length_for_sort_data = 16;
\tmax_length_for_sort_data，

是 MySQL 中專門控制用於排序的行數據的長度的一個參數。它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個算法。

1. 初始化 sort_buffer，確定放入兩個字段，即 name 和 id；
2. 從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
3. 到主鍵 id 索引取出整行，取 name、id 這兩個字段，存入 sort_buffer 中；
4. 從索引 city 取下一個記錄的主鍵 id；
5. 重複步驟 3、4 直到不滿足 city='杭州’條件為止，也就是圖中的 ID_Y；
6. 對 sort_buffer 中的數據按照字段 name 進行排序；
7. 遍歷排序結果，取前 1000 行，並按照 id 的值回到原表中取出 city、name 和 age 三個字段返回給客戶端。

圖 5 rowid 排序

全字段排序 VS rowid 排序

如果 MySQL 實在是擔心排序內存太小，會影響排序效率，才會採用 rowid 排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取數據。

如果 MySQL 認為內存足夠大，會優先選擇全字段排序，把需要的字段都放到 sort_buffer 中，這樣排序後就會直接從內存裡面返回查詢結果了，不用再回到原表去取數據。

這也就體現了 MySQL 的一個設計思想：如果內存夠，就要多利用內存，儘量減少磁盤訪問。

對於 InnoDB 表來說，rowid 排序會要求回表多造成磁盤讀，因此不會被優先選擇。

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

圖 6 city 和 name 聯合索引示意圖

圖7 引入 (city,name) 聯合索引後，查詢語句的執行計劃

覆蓋索引是指，索引上的信息足夠滿足查詢請求，不需要再回到主鍵索引上去取數據。

alter table t add index city_user_age(city, name, age);

圖8 引入 (city,name,age) 聯合索引後，查詢語句的執行流程

圖9 引入 (city,name,age) 聯合索引後，查詢語句的執行計劃

Extra 字段裡面多了“Using index”，表示的就是使用了覆蓋索引，性能上會快很多。

這裡並不是說要為了每個查詢能用上覆蓋索引，就要把語句中涉及的字段都建上聯合索引，畢竟索引還是有維護代價的。這是一個需要權衡的決定。