參考回答

MySQL 的數據排序主要通過兩種方式實現：索引排序和文件排序（filesort）。

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

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

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

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

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

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

圖片

一、從一個場景說起

假設你正在開發一個電商平臺的訂單系統，產品經理要求：用戶進入"我的訂單"頁面時，需要按下單時間倒序展示最近的 20 條訂單。你很快寫出了這樣的查詢語句：

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

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

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

二、索引排序

2.1 索引天然有序

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

在 InnoDB 存儲引擎中，索引采用 B+ 樹結構。這種樹的葉子節點按照索引鍵值從左到右串聯成一個有序鏈表。當你在 order_time 字段上建立索引后，MySQL 實際上已經維護了一個按時間排序的"目錄"。

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

2.2 觸發索引排序的條件

但索引排序并非萬能鑰匙，它的觸發需要滿足嚴苛的條件：

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

假設你建立了一個聯合索引 INDEX idx_user_time(user_id, order_time)。這個索引的存儲結構是先按 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 之后的版本對索引排序能力進行了重要優化，支持利用索引處理方向不一致的排序（即對聯合索引中不同字段使用 ASC 和 DESC 混合排序），無需額外的文件排序（filesort）。

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

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

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

注意事項：

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

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

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

2.3 優化器的權衡

即使滿足了上述所有條件，MySQL 優化器仍然可能選擇不用索引排序。這是因為優化器會計算"成本"。

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

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

三、文件排序

3.1 filesort 的觸發時機

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

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

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

3.2 sort_buffer：排序的臨時工作區

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

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

sort_buffer 的工作流程像這樣：

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

關鍵問題來了：如果數據量太大，sort_buffer 裝不下怎么辦？

3.3 單路排序

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

假設你的查詢是：

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

單路排序的執行過程：

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

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

4. 內存快排：在 sort_buffer 中對這 5000 條數據按 order_time 進行快速排序
5. 取出結果：排序完成后，取前 20 條返回給客戶端

這種方式的優點是一次性完成，不需要回表，缺點是占用內存較大。如果單行數據很寬（比如包含大字段），很容易超過 sort_buffer 限制。

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

當單行數據太大時，MySQL 會切換到雙路排序模式。判斷標準是參數 max_length_for_sort_data，默認值是 4096 字節。

如果參與排序的單行數據長度超過這個閾值，就會觸發雙路排序。

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

還是剛才的例子，執行過程變成：

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

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

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

這種方式的優點是占用內存小，更容易在內存中完成排序，缺點是需要額外的回表操作。

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