MySQL 文件排序 vs 索引排序：原理、性能對比與優化實戰
核心概念
| 排序方式 | 原理 | 觸發條件 | 性能特徵 |
|---|
| 索引排序 | 利用索引有序性，按索引順序讀取，無需額外排序 | ORDER BY 列為索引最左前綴、排序方向一致、無篩選條件破壞順序 | 零排序開銷、極快、EXPLAIN Extra 無 Using filesort |
| 文件排序 | 在內存/磁盤臨時表中對結果集排序 | 無合適索引、ORDER BY 非索引列、混合 ASC/DESC、JOIN 後排序 | CPU 密集、可能落盤、EXPLAIN Extra 顯示 Using filesort |
實驗驗證
表結構
CREATE TABLE artist (
artist_id INT UNSIGNED PRIMARY KEY,
type ENUM('Band','Person','Unknown','Combination') NOT NULL,
name VARCHAR(255) NOT NULL,
gender ENUM('Male','Female') DEFAULT NULL,
founded YEAR DEFAULT NULL,
country_id SMALLINT UNSIGNED DEFAULT NULL,
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
場景一：文件排序
EXPLAIN SELECT name, founded FROM artist WHERE name LIKE 'AUSTRALIA%' ORDER BY founded;
-- Extra: Using index condition; Using filesort
執行後查看會話狀態：
SHOW SESSION STATUS LIKE '%sort%';
-- Sort_range: 1, Sort_rows: 22 → 發生了排序操作
場景二：索引排序
EXPLAIN SELECT name, founded FROM artist WHERE name LIKE 'AUSTRALIA%' ORDER BY name;
-- Extra: Using index condition (無 filesort)
SHOW SESSION STATUS LIKE '%sort%';
-- 所有排序計數器均為 0 → 無排序開銷
排序狀態變量解讀
| 變量 | 含義 | 調優參考 |
|---|
| Sort_merge_passes | 歸併排序段數 | ＞0 表示 sort_buffer_size 不足，需增大 |
| Sort_range | 範圍掃描排序次數 | 索引範圍查詢後排序 |
| Sort_rows | 排序總行數 | 評估排序規模 |
| Sort_scan | 全表掃描排序次數 | 無索引全表排序 |
優化策略
- 建立合適索引：
INDEX idx_name_founded (name, founded) 支援 WHERE name + ORDER BY founded
- 避免 SELECT *：只查詢所需列，減少排序行寬度，降低 sort_buffer 壓力
- 調大 sort_buffer_size：會話級，
SET sort_buffer_size = 8M，但慎用（每連接分配）
- 使用 LIMIT：
ORDER BY founded LIMIT 10 可觸發 Priority Queue 排序（MySQL 8.0+），僅維護 Top-N
- 強制索引：
ORDER BY name ASC, founded DESC 混合方向無法用索引，嘗試統一方向或重建索引
進階：MySQL 8.0 排序優化
- 即時排序：早期版本雙路排序（兩次 IO），5.6+ 單路排序（一次 IO，將所有列打包）
- 優先隊列排序：
ORDER BY ... LIMIT N 使用小頂堆，內存佔用 O(N) 而非 O(行數)
- 排序緩衝區複用：並行查詢排序緩衝區可複用
排查檢查單
-- 1. 確認是否 filesort
EXPLAIN SELECT ... ORDER BY col;
-- 2. 查看排序規模
SHOW SESSION STATUS LIKE 'Sort_rows';
-- 3. 檢查索引是否可用
SHOW INDEX FROM table;
-- 4. 分析 sort_buffer 是否足夠
SHOW SESSION STATUS LIKE 'Sort_merge_passes';
