參考回答

MySQL 的數(shù)據(jù)排序主要通過兩種方式實現(xiàn)：索引排序和文件排序（filesort）。

首先是索引排序，這是最高效的方式。當(dāng)我們的 ORDER BY 子句中使用的字段恰好有索引，并且索引的順序與排序要求一致時，MySQL 會直接利用索引的有序性返回結(jié)果，完全不需要額外的排序操作。這種方式在執(zhí)行0計劃中不會出現(xiàn) "Using filesort" 標(biāo)識。

其次是文件排序，當(dāng)無法利用索引時，MySQL 會啟用 filesort 機(jī)制。這個過程會根據(jù)待排序數(shù)據(jù)量的大小采用不同策略：

1. 內(nèi)存排序階段：如果數(shù)據(jù)量較小，能夠放入 sort_buffer（由參數(shù) sort_buffer_size 控制）中，就在內(nèi)存中完成排序。內(nèi)存排序又分為兩種模式：

• 單路排序(全字段排序) ：直接把查詢需要的所有字段都讀到 sort_buffer 中進(jìn)行排序，排完直接返回，避免回表
• **雙路排序(rowid 排序)**：當(dāng)單行數(shù)據(jù)過大時（超過 max_length_for_sort_data），只讀取排序字段和主鍵 ID 到 sort_buffer，排序完成后再回表查詢其他字段

2. 磁盤排序階段：如果數(shù)據(jù)量超過 sort_buffer 容量，MySQL 會使用歸并排序算法，將數(shù)據(jù)分批次在內(nèi)存中排序后寫入臨時文件，最后再將多個有序文件合并，這個過程會涉及大量磁盤 IO，性能較差。

判斷使用哪種排序方式的關(guān)鍵因素包括：是否有合適的索引、數(shù)據(jù)量大小、sort_buffer_size 參數(shù)、max_length_for_sort_data 參數(shù)等。所以，在實際優(yōu)化中，我們應(yīng)該優(yōu)先考慮建立合適的索引來避免 filesort，如果必須使用 filesort，則需要合理調(diào)整相關(guān)參數(shù)以盡量在內(nèi)存中完成排序。

圖片

一、從一個場景說起

假設(shè)你正在開發(fā)一個電商平臺的訂單系統(tǒng)，產(chǎn)品經(jīng)理要求：用戶進(jìn)入"我的訂單"頁面時，需要按下單時間倒序展示最近的 20 條訂單。你很快寫出了這樣的查詢語句：

SELECT order_id, user_id, order_time, total_amount 
FROM orders 
WHERE user_id = 10086 
ORDER BY order_time DESC 
LIMIT 20;

這個看似簡單的查詢，背后卻隱藏著 MySQL 復(fù)雜的排序機(jī)制。當(dāng)你用 EXPLAIN 分析這條 SQL 時，可能會看到兩種截然不同的結(jié)果：一種是干凈利落，直接走索引；另一種則出現(xiàn)了 "Using filesort" ，使用了文件排序。

這兩種情況的性能差異可能達(dá)到幾十倍甚至上百倍。為什么會有這樣的差異？MySQL 內(nèi)部到底是如何處理排序的？下面，我們從最底層的原理開始剖析。

二、索引排序

2.1 索引天然有序

很多開發(fā)者都知道索引能加速查詢，遇事不決加索引，因為索引有一個重要特性：索引本身就是有序的。

在 InnoDB 存儲引擎中，索引采用 B+ 樹結(jié)構(gòu)。這種樹的葉子節(jié)點(diǎn)按照索引鍵值從左到右串聯(lián)成一個有序鏈表。當(dāng)你在 order_time 字段上建立索引后，MySQL 實際上已經(jīng)維護(hù)了一個按時間排序的"目錄"。

想象一下圖書館的書籍編目系統(tǒng)：如果圖書已經(jīng)按照出版時間在書架上從左到右排列好了，當(dāng)讀者要求"給我最近出版的 20 本書"時，管理員只需要從最右邊取 20 本即可，完全不需要把所有書搬出來重新排序。

2.2 觸發(fā)索引排序的條件

但索引排序并非萬能鑰匙，它的觸發(fā)需要滿足嚴(yán)苛的條件：

1）索引列順序必須與 ORDER BY 順序完全匹配

假設(shè)你建立了一個聯(lián)合索引 INDEX idx_user_time(user_id, order_time)。這個索引的存儲結(jié)構(gòu)是先按 user_id 排序，user_id 相同時再按 order_time 排序。

此時如果你的查詢是：

-- ? 能用索引排序
WHERE user_id = 10086 ORDER BY order_time

-- ? 不能用索引排序
ORDER BY order_time, user_id  -- 順序顛倒

2） 排序方向必須一致

-- ? 都是升序或都是降序
ORDER BY order_time DESC, status DESC

-- ? 方向不一致
ORDER BY order_time DESC, status ASC

MySQL 8.0 之前的版本無法利用索引處理方向不一致的排序，因為索引只能單向掃描。就像電梯要么向上要么向下，不能一邊上一邊下。在MySQL 8.0 之后的版本對索引排序能力進(jìn)行了重要優(yōu)化，支持利用索引處理方向不一致的排序（即對聯(lián)合索引中不同字段使用 ASC 和 DESC 混合排序），無需額外的文件排序（filesort）。

8.0 之前的限制：聯(lián)合索引的物理存儲是 “單向有序” 的（例如 (a ASC, b ASC)），只能按索引定義的方向掃描。如果查詢中排序方向與索引定義不一致（如 ORDER BY a ASC, b DESC），索引無法直接滿足排序需求，會觸發(fā)文件排序。

8.0 及之后的優(yōu)化：引入了 “降序索引”（descending index）支持，允許在創(chuàng)建聯(lián)合索引時為每個字段指定排序方向（ASC 或 DESC），且優(yōu)化器能利用這類索引處理混合方向的排序。

• 例如，若創(chuàng)建索引 INDEX idx_mixed(a ASC, b DESC)，則查詢 ORDER BY a ASC, b DESC 可直接通過索引掃描返回有序結(jié)果。
• 即使索引定義為全 ASC（如 (a ASC, b ASC)），8.0 優(yōu)化器也能反向掃描索引（從后向前讀），來滿足 ORDER BY a DESC, b DESC 這類同方向倒序的需求，無需文件排序。

注意事項：

• 降序索引僅支持 InnoDB 存儲引擎。
• 混合排序的字段順序仍需遵循聯(lián)合索引的 “最左前綴原則”（如索引 (a, b) 可支持 ORDER BY a ASC, b DESC，但不支持 ORDER BY b ASC, a DESC）。

3）WHERE 條件與 ORDER BY 字段的配合

當(dāng)查詢既有 WHERE 過濾又有 ORDER BY 排序時，索引必須同時滿足兩者的需求。最優(yōu)情況是建立覆蓋索引，把 WHERE、ORDER BY、SELECT 涉及的字段都包含進(jìn)去。

2.3 優(yōu)化器的權(quán)衡

即使?jié)M足了上述所有條件，MySQL 優(yōu)化器仍然可能選擇不用索引排序。這是因為優(yōu)化器會計算"成本"。

假設(shè)你的查詢需要返回 100 萬條數(shù)據(jù)，雖然有索引可以保證有序，但每條數(shù)據(jù)都需要回表查詢（因為索引中只有排序字段，其他字段在主鍵索引上）。優(yōu)化器一算：這得回表 100 萬次！還不如直接全表掃描，把所有數(shù)據(jù)讀到內(nèi)存里排序一次。

這就是為什么你會看到一些"明明有索引卻不用"的詭異現(xiàn)象。優(yōu)化器并非不智能，而是在做綜合權(quán)衡。

三、文件排序

3.1 filesort 的觸發(fā)時機(jī)

當(dāng)下面任何一個條件成立時，MySQL 就會放棄索引排序，啟動 filesort 機(jī)制：

1. 排序字段沒有索引
2. 索引無法覆蓋所有查詢字段（需要大量回表）
3. ORDER BY 使用了表達(dá)式或函數(shù)（如 ORDER BY YEAR(order_time)）
4. 多表關(guān)聯(lián)查詢的復(fù)雜排序
5. 優(yōu)化器評估索引排序成本過高

在執(zhí)行計劃的 Extra 列中出現(xiàn) "Using filesort"，就是 MySQL 在告訴你："我得自己排序了"。

3.2 sort_buffer：排序的臨時工作區(qū)

MySQL 會為每個需要排序的查詢分配一塊內(nèi)存區(qū)域，叫做 sort_buffer（排序緩沖區(qū)）。這塊內(nèi)存的大小由參數(shù) sort_buffer_size 控制，默認(rèn)值通常是 256KB。

這塊內(nèi)存是會話級別的，意味著每個客戶端連接都有自己獨(dú)立的 sort_buffer。如果你的系統(tǒng)有 1000 個并發(fā)連接，每個連接的 sort_buffer 設(shè)置為 4MB，理論上就需要 4GB 內(nèi)存來支撐排序操作。

sort_buffer 的工作流程像這樣：

• MySQL 根據(jù) WHERE 條件篩選出需要排序的記錄
• 將這些記錄的相關(guān)字段讀入 sort_buffer
• 在 sort_buffer 中使用快速排序算法進(jìn)行排序
• 返回排序后的結(jié)果

關(guān)鍵問題來了：如果數(shù)據(jù)量太大，sort_buffer 裝不下怎么辦？

3.3 單路排序

這是 MySQL 默認(rèn)采用的排序方式。它的核心思想是：把查詢需要的所有字段都讀到 sort_buffer 中，排序完成后直接返回，不需要再回表。

假設(shè)你的查詢是：

SELECT order_id, user_id, order_time, total_amount, status 
FROM orders 
WHERE user_id = 10086 
ORDER BY order_time DESC 
LIMIT 20;

單路排序的執(zhí)行過程：

1. 掃描定位：根據(jù) user_id = 10086 的條件，找到所有符合的記錄（假設(shè)有 5000 條）
2. 字段提取：對每條記錄，提取 order_id、user_id、order_time、total_amount、status 這五個字段的值
3. 裝載緩沖區(qū)：將這 5000 條記錄的五個字段全部裝入 sort_buffer

• 如果 5000 條數(shù)據(jù)占用空間小于 sort_buffer_size（比如 256KB），全部裝入內(nèi)存
• 如果超過了，就需要使用外部排序

4. 內(nèi)存快排：在 sort_buffer 中對這 5000 條數(shù)據(jù)按 order_time 進(jìn)行快速排序
5. 取出結(jié)果：排序完成后，取前 20 條返回給客戶端

這種方式的優(yōu)點(diǎn)是一次性完成，不需要回表，缺點(diǎn)是占用內(nèi)存較大。如果單行數(shù)據(jù)很寬（比如包含大字段），很容易超過 sort_buffer 限制。

3.4 雙路排序(rowid 排序)：空間換時間

當(dāng)單行數(shù)據(jù)太大時，MySQL 會切換到雙路排序模式。判斷標(biāo)準(zhǔn)是參數(shù) max_length_for_sort_data，默認(rèn)值是 4096 字節(jié)。

如果參與排序的單行數(shù)據(jù)長度超過這個閾值，就會觸發(fā)雙路排序。

雙路排序的思路是：只把排序字段和主鍵 ID 讀到 sort_buffer，排序完成后再回表查詢其他字段。

還是剛才的例子，執(zhí)行過程變成：

• 掃描定位：找到 user_id = 10086 的 5000 條記錄
• 精簡提取：對每條記錄，只提取 order_time（排序字段）和 order_id（主鍵）兩個字段
• 裝載緩沖區(qū)：將 5000 條記錄的兩個字段裝入 sort_buffer

因為只有兩個字段，占用空間大大減少，更容易在內(nèi)存中完成

• 內(nèi)存快排：按 order_time 排序這 5000 條數(shù)據(jù)
• 取出前 20：排序后選出前 20 條的 order_id
• 回表查詢：根據(jù)這 20 個 order_id，回到主鍵索引上查詢完整的記錄（包括 user_id、total_amount、status）
• 返回結(jié)果：將查詢到的 20 條完整記錄返回客戶端

這種方式的優(yōu)點(diǎn)是占用內(nèi)存小，更容易在內(nèi)存中完成排序，缺點(diǎn)是需要額外的回表操作。

不過仔細(xì)想想，回表只針對最終返回的 20 條數(shù)據(jù)，而不是全部 5000 條，所以這個代價是可以接受的。如果沒有 LIMIT 限制，需要返回全部 5000 條，那回表代價就很高了。