首先排序功能由 ORDER BY 實現(xiàn)，具體排列順序取決于優(yōu)化器的選擇。若優(yōu)化器認為索引排序更有效率，則使用索引排序；反之，則使用 filesort（執(zhí)行計劃中額外信息提示：使用 filesort）。然而，索引排序的適用情況有限，且不確定性較高，通常還是會采用 filesort。

在 filesort 排序中，如果排序的數(shù)據(jù)較少，可在內(nèi)存中的 sort_buffer 上完成；否則，需借助臨時文件進行排序。實際排序過程中，如果字段長度較短，可直接在 sort_buffer 中進行全字段排序并返回結(jié)果集。若字段長度較長，可能出于空間考量，采用基于 row_id 的排序，此時會進行二次回表后返回結(jié)果集。

索引排序

眾所周知，索引具備天然的排序?qū)傩?，因此在使?ORDER BY 時，若能充分利用索引，則效率必然最佳。

MySQL 確實可以基于索引執(zhí)行 ORDER BY 查詢，然而這一過程是否必然使用索引完全由優(yōu)化器決定。

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

假設(shè)存在如上所述的表格，在排序過程中可能會出現(xiàn)以下情況（由于優(yōu)化器的干預(yù)，以下結(jié)果并不一定可以 100%復(fù)現(xiàn)。根據(jù)我的實驗，在 MySQL 5.7 環(huán)境中是可以的）。

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

在使用索引字段進行排序時，優(yōu)化器會根據(jù)成本評估來選擇是否通過索引進行排序。經(jīng)過我的多次驗證，以下情況中，索引排序的概率較高：

• 查詢的字段和 ORDER BY 的字段組成了一個聯(lián)合索引，并且查詢條件符合最左前綴匹配，使得查詢可以使用索引覆蓋。例如：SELECT a, b, c FROM t2 ORDER BY a;
• 查詢條件中包含了 LIMIT，并且 LIMIT 的數(shù)量不是很大。（在我測試的表中，數(shù)據(jù)量為 80 萬，當 LIMIT 超過 2 萬多時就不再使用索引排序），例如：ORDER BY a LIMIT 100
• 雖然未遵循最左前綴匹配，但是前導(dǎo)列通過常量進行了查詢，例如：WHERE a = "Paidaxing" ORDER BY b

filesort 排序

如果無法使用或優(yōu)化器認為索引排序效率不高，MySQL 將執(zhí)行 filesort 操作，以讀取表中的行并對它們進行排序。

在排序過程中，MySQL 為每個線程分配一塊內(nèi)存用于排序，稱為sort_buffer，其大小由 sort_buffer_size控制。

根據(jù) sort_buffer_size 的大小不同，排序操作會發(fā)生在不同的位置：

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

臨時文件排序采用歸并排序算法，首先將需要排序的數(shù)據(jù)分配到多個臨時文件中，同時進行排序操作，然后將多個排序完成的文件合并成一個結(jié)果集返回給客戶端。相對于在內(nèi)存中的 sort_buffer 排序，磁盤上的臨時文件排序速度較慢。

除了sort_buffer_size參數(shù)之外，影響排序算法的另一個關(guān)鍵參數(shù)是 max_length_for_sort_data。

max_length_for_sort_data 是 MySQL 中控制用于排序的行數(shù)據(jù)的長度的一個參數(shù)，默認值為 1024 字節(jié)。如果單行長度超過該值，MySQL 就會認為單行太大，因此會采用 rowid 排序；否則，會進行全字段排序。

全字段排序

所謂全字段排序，即將要查詢的所有字段都放入 sort_buffer 中，然后根據(jù)排序字段進行排序，排序完成后直接將結(jié)果集返回給客戶端。

假設(shè)我們有以下查詢 SQL：

select a,d,f from t2 where a = "Paidaxing" order by d;

--

因為此處涉及的字段為 a、d、f 三個，因此將滿足 WHERE 條件的所有數(shù)據(jù)的 a、d、f 字段都放入 sort_buffer 中，然后根據(jù) d 字段在 sort_buffer 中進行排序，排序完成后返回給客戶端的大致過程如下：

1. 從索引 a 中取出滿足條件 a = "Paidaxing" 的第一條記錄的主鍵 ID。
2. 根據(jù)主鍵 ID 回表，提取 a、d、f 三個字段的值，并將它們存入 sort_buffer。
3. 繼續(xù)查詢下一個符合 a = "Paidaxing" 條件的記錄，重復(fù)執(zhí)行第 1 和第 2 步驟。
4. 在 sort_buffer 中根據(jù) d 字段進行排序。
5. 將排序后的結(jié)果集返回給客戶端。

圖片

以上過程中，如果數(shù)據(jù)在 sort_buffer 中無法全部存放，則會使用臨時文件，并對臨時文件進行歸并排序。

全字段排序的優(yōu)點在于只需要對原表進行一次回表查詢（每條記錄只需回表一次），排序完成后可以直接返回所需字段，因此效率較高。但其缺點在于，如果要查詢的字段較多，會占用大量 sort_buffer 空間，導(dǎo)致可存儲的數(shù)據(jù)量減少。當需要排序的數(shù)據(jù)量增大時，可能會使用臨時文件，從而導(dǎo)致整體性能下降。

為避免這個問題，可以采用 row_id 排序的方式。

row_id 排序

這個也很好理解，即在構(gòu)建 sort_buffer 時，不需要將所有要查詢的字段都放進去，只需要將排序字段和主鍵放入即可。

select a,d,f from t2 where a = "Paidaxing" order by d;

比如這個 SQL，只需要將 d 和 id 放入 sort_buffer 中，首先按照 d 進行排序。排序完成后，根據(jù) id 將對應(yīng)的 a、d、f 幾個字段查詢出來，然后返回給客戶端的大致過程如下：

1. 從索引 a 中取出符合條件 a = "Paidaxing" 的第一條記錄的主鍵 ID。
2. 根據(jù)主鍵 ID 回表，提取 d 字段的值，并將其存入 sort_buffer。
3. 繼續(xù)查詢下一個符合條件 a = "Paidaxing" 的記錄，重復(fù)執(zhí)行第 1 和第 2 步驟。
4. 在 sort_buffer 中根據(jù) d 進行排序。
5. 根據(jù)排序后的 id，回表查詢出對應(yīng)的 a、d、f 幾個字段。
6. 將結(jié)果集返回給客戶端。

圖片

以上的第五步，與全字段排序算法相比確實多了一次回表操作。因此，這種方案的效率肯定會稍慢一些。

如何選擇

如何選擇排序方式？

實際上，row_id 是 MySQL 的一種優(yōu)化算法，它首先考慮使用全字段排序。只有在認為字段長度過長可能影響效率時，才會采用 row_id 排序方式。此外，如果能夠利用 sort_buffer 完成排序，MySQL 就不會使用臨時文件。

綜上所述，MySQL 在選擇排序方式時會優(yōu)先考慮速度、內(nèi)存和減少回表次數(shù)等因素。