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

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

索引排序

眾所周知，索引具備天然的排序?qū)傩裕虼嗽谑褂?ORDER BY 時(shí)，若能充分利用索引，則效率必然最佳。

MySQL 確實(shí)可以基于索引執(zhí)行 ORDER BY 查詢，然而這一過(guò)程是否必然使用索引完全由優(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è)存在如上所述的表格，在排序過(guò)程中可能會(huì)出現(xiàn)以下情況（由于優(yōu)化器的干預(yù)，以下結(jié)果并不一定可以 100%復(fù)現(xiàn)。根據(jù)我的實(shí)驗(yàn)，在 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

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

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

filesort 排序

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

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

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

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

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

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

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

全字段排序

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

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

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

--

因?yàn)榇颂幧婕暗淖侄螢?a、d、f 三個(gè)，因此將滿足 WHERE 條件的所有數(shù)據(jù)的 a、d、f 字段都放入 sort_buffer 中，然后根據(jù) d 字段在 sort_buffer 中進(jìn)行排序，排序完成后返回給客戶端的大致過(guò)程如下：

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

圖片

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

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

為避免這個(gè)問(wèn)題，可以采用 row_id 排序的方式。

row_id 排序

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

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

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

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

圖片

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

如何選擇

如何選擇排序方式？

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

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