MySQL 利用索引優化 ORDER BY 排序：避免 filesort 的實戰指南
兩種排序方式
- Index Scan 排序：直接按索引順序讀取，零成本，
Extra 顯示 Using index。
- Filesort 排序：內存/磁盤快速排序，
Extra 顯示 Using filesort，性能開銷大。
使用索引排序的必要條件
- 索引列順序與
ORDER BY 列順序 完全一致。
- 所有排序列 方向一致 (全
ASC 或全 DESC)。
- 若為多表 JOIN，
ORDER BY 列必須 全部來自驅動表 (第一張表)。
實戰驗證 (actor 表)
CREATE TABLE actor(
actor_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(16) NOT NULL DEFAULT '',
password VARCHAR(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
KEY (name)
) ENGINE=InnoDB;
| SQL | EXPLAIN type | key | Extra | 說明 |
|---|
ORDER BY actor_id | index | PRIMARY | Using index | 主鍵有序，直接索引掃描 |
ORDER BY password | ALL | NULL | Using filesort | 無索引，全表掃描 + filesort |
ORDER BY name | index | name | Using index | 二級索引有序，覆蓋索引掃描 |
常見優化場景
1. 單表 ORDER BY 單列
SELECT col1, col2 FROM t ORDER BY sort_col;
-- 方案：INDEX (sort_col)
2. WHERE + ORDER BY
SELECT * FROM t WHERE colX = val ORDER BY sort_col;
-- 方案：組合索引 (colX, sort_col) -- colX 為等值條件，sort_col 排序
-- 注意：WHERE colX IN (v1,v2) 為範圍條件，無法利用索引排序
3. WHERE + 多列 ORDER BY
SELECT * FROM t WHERE uid=1 ORDER BY x, y LIMIT 10;
-- 最佳索引：(uid, x, y) -- 等值列在前，排序列在後
-- 避免：(x, y, uid) -- 排序列在前，等值列在後，無法利用索引排序
無法使用索引排序的典型場景
| 場景 | 示例 | 原因 |
|---|
| 不同索引鍵混合排序 | ORDER BY key1, key2 (分別建單列索引) | 無法合併兩棵 B+Tree |
| 非連續索引鍵部分排序 | 索引 (k1,k2)，WHERE k2=const ORDER BY k2 | 缺少最左列 k1 |
| 混合 ASC/DESC | ORDER BY k1 DESC, k2 ASC | 方向不一致 (8.0+ 支持降序索引) |
| WHERE 條件列與 ORDER BY 列不同 | WHERE k2=const ORDER BY k1 | 索引無法同時滿足過濾與排序 |
| ORDER BY 列使用函數/表達式 | ORDER BY YEAR(logindate) | 索引存儲原始值，無法匹配計算後值 |
Filesort 兩種算法與優化
- 兩遍掃描 (Two-pass)：MySQL 4.1 前。讀取排序鍵 + 行指針 → 內存排序 → 再按指針回表讀取列。隨機 I/O 多，內存佔用小。
- 單遍掃描 (Single-pass)：MySQL 4.1+ 默認。一次性讀取所有需求列 → 內存排序 → 直接輸出。I/O 少，但內存佔用大。受
max_length_for_sort_data 控制：若總列長度超過此值，退回兩遍算法。
優化建議：SELECT 只取必要列，避免 SELECT * 觸發兩遍算法；適當增大 sort_buffer_size。
JOIN + ORDER BY + LIMIT 陷阱
ORDER BY 僅引用驅動表列：先 Filesort 驅動表結果，再 JOIN，Extra 顯示 Using filesort。ORDER BY 引用非驅動表列：必須生成臨時表 JOIN 完成後再排序，Extra 顯示 Using temporary; Using filesort (性能極差)。
