聊聊 Order By 是怎么實現的？
首先排序功能由 ORDER BY 實現，具體排列順序取決于優化器的選擇。若優化器認為索引排序更有效率，則使用索引排序；反之，則使用 filesort（執行計劃中額外信息提示：使用 filesort）。然而，索引排序的適用情況有限，且不確定性較高，通常還是會采用 filesort。
在 filesort 排序中，如果排序的數據較少，可在內存中的 sort_buffer 上完成；否則，需借助臨時文件進行排序。實際排序過程中，如果字段長度較短，可直接在 sort_buffer 中進行全字段排序并返回結果集。若字段長度較長，可能出于空間考量，采用基于 row_id 的排序，此時會進行二次回表后返回結果集。
索引排序
眾所周知，索引具備天然的排序屬性，因此在使用 ORDER BY 時，若能充分利用索引，則效率必然最佳。
MySQL 確實可以基于索引執行 ORDER BY 查詢，然而這一過程是否必然使用索引完全由優化器決定。
CREATE TABLE `t2` (
`id` INT(11),
`a` varchar(64) NOT NULL,
`b` varchar(64) NOT NULL,
`c` varchar(64) NOT NULL,
`d` varchar(64) NOT NULL,
`f` varchar(64) DEFAULT NULL,
PRIMARY KEY(id),
UNIQUE KEY `f` (`f`),
KEY `idx_abc` (`a`,`b`,`c`)
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1假設存在如上所述的表格，在排序過程中可能會出現以下情況（由于優化器的干預，以下結果并不一定可以 100%復現。根據我的實驗，在 MySQL 5.7 環境中是可以的）。
select * from t2 order by a;
-- 不走索引，使用filesort（后面介紹啥是filesort）
select * from t2 order by a limit 100;
-- 走索引
select a,b from t2 order by a limit 100;
-- 走索引
select a,b,c from t2 order by a;
-- 走索引
select a,b,c,d from t2 order by a;
-- 不走索引，使用filesort
select a,b,c,d from t2 where a = "Paidaxing" order by b;
-- 走索引
select a,b,c,d from t2 where b = "Paidaxing" order by a;
-- 不走索引，使用filesort在使用索引字段進行排序時，優化器會根據成本評估來選擇是否通過索引進行排序。經過我的多次驗證，以下情況中，索引排序的概率較高：
- 查詢的字段和 ORDER BY 的字段組成了一個聯合索引，并且查詢條件符合最左前綴匹配，使得查詢可以使用索引覆蓋。例如：SELECT a, b, c FROM t2 ORDER BY a;
- 查詢條件中包含了 LIMIT，并且 LIMIT 的數量不是很大。（在我測試的表中，數據量為 80 萬，當 LIMIT 超過 2 萬多時就不再使用索引排序），例如：ORDER BY a LIMIT 100
- 雖然未遵循最左前綴匹配，但是前導列通過常量進行了查詢，例如：WHERE a = "Paidaxing" ORDER BY b
filesort 排序
如果無法使用或優化器認為索引排序效率不高，MySQL 將執行 filesort 操作，以讀取表中的行并對它們進行排序。
在排序過程中，MySQL 為每個線程分配一塊內存用于排序，稱為sort_buffer，其大小由 sort_buffer_size控制。
根據 sort_buffer_size 的大小不同，排序操作會發生在不同的位置：
- 如果排序數據量小于 sort_buffer_size，則排序在內存中完成。
- 如果排序數據量大于 sort_buffer_size，則需要利用磁盤臨時文件輔助排序。
臨時文件排序采用歸并排序算法，首先將需要排序的數據分配到多個臨時文件中，同時進行排序操作，然后將多個排序完成的文件合并成一個結果集返回給客戶端。相對于在內存中的 sort_buffer 排序，磁盤上的臨時文件排序速度較慢。
除了sort_buffer_size參數之外，影響排序算法的另一個關鍵參數是 max_length_for_sort_data。
max_length_for_sort_data 是 MySQL 中控制用于排序的行數據的長度的一個參數，默認值為 1024 字節。如果單行長度超過該值，MySQL 就會認為單行太大，因此會采用 rowid 排序；否則，會進行全字段排序。
全字段排序
所謂全字段排序，即將要查詢的所有字段都放入 sort_buffer 中，然后根據排序字段進行排序，排序完成后直接將結果集返回給客戶端。
假設我們有以下查詢 SQL：
select a,d,f from t2 where a = "Paidaxing" order by d;
--因為此處涉及的字段為 a、d、f 三個，因此將滿足 WHERE 條件的所有數據的 a、d、f 字段都放入 sort_buffer 中，然后根據 d 字段在 sort_buffer 中進行排序，排序完成后返回給客戶端的大致過程如下：
- 從索引 a 中取出滿足條件 a = "Paidaxing" 的第一條記錄的主鍵 ID。
- 根據主鍵 ID 回表，提取 a、d、f 三個字段的值，并將它們存入 sort_buffer。
- 繼續查詢下一個符合 a = "Paidaxing" 條件的記錄，重復執行第 1 和第 2 步驟。
- 在 sort_buffer 中根據 d 字段進行排序。
- 將排序后的結果集返回給客戶端。
圖片
以上過程中，如果數據在 sort_buffer 中無法全部存放，則會使用臨時文件，并對臨時文件進行歸并排序。
全字段排序的優點在于只需要對原表進行一次回表查詢（每條記錄只需回表一次），排序完成后可以直接返回所需字段，因此效率較高。但其缺點在于，如果要查詢的字段較多，會占用大量 sort_buffer 空間，導致可存儲的數據量減少。當需要排序的數據量增大時，可能會使用臨時文件，從而導致整體性能下降。
為避免這個問題，可以采用 row_id 排序的方式。
row_id 排序
這個也很好理解，即在構建 sort_buffer 時，不需要將所有要查詢的字段都放進去，只需要將排序字段和主鍵放入即可。
select a,d,f from t2 where a = "Paidaxing" order by d;比如這個 SQL，只需要將 d 和 id 放入 sort_buffer 中，首先按照 d 進行排序。排序完成后，根據 id 將對應的 a、d、f 幾個字段查詢出來，然后返回給客戶端的大致過程如下：
- 從索引 a 中取出符合條件 a = "Paidaxing" 的第一條記錄的主鍵 ID。
- 根據主鍵 ID 回表，提取 d 字段的值，并將其存入 sort_buffer。
- 繼續查詢下一個符合條件 a = "Paidaxing" 的記錄，重復執行第 1 和第 2 步驟。
- 在 sort_buffer 中根據 d 進行排序。
- 根據排序后的 id，回表查詢出對應的 a、d、f 幾個字段。
- 將結果集返回給客戶端。
圖片
以上的第五步，與全字段排序算法相比確實多了一次回表操作。因此，這種方案的效率肯定會稍慢一些。
如何選擇
如何選擇排序方式？
實際上，row_id 是 MySQL 的一種優化算法，它首先考慮使用全字段排序。只有在認為字段長度過長可能影響效率時，才會采用 row_id 排序方式。此外，如果能夠利用 sort_buffer 完成排序，MySQL 就不會使用臨時文件。
綜上所述，MySQL 在選擇排序方式時會優先考慮速度、內存和減少回表次數等因素。
