ORDER BY 觸發 Using filesort 是大表查詢性能殺手。MySQL 能利用索引順序直接返回有序結果，但需滿足嚴格的「索引列序、排序方向、WHERE 常量」三重約束。本文提煉 5 條鐵律，複盤真實優化案例，從 9 萬行全表掃描降至 3 千行索引範圍。

索引消除 Filesort 的 5 條鐵律

1. WHERE 與 ORDER BY 使用同一索引：複合索引列序 = WHERE 條件列 + ORDER BY 列
2. WHERE 條件列必須為常量：WHERE col = const，變量/子查詢失效
3. ORDER BY 方向統一：全部 ASC 或全部 DESC，混合方向失效
4. ORDER BY 列必須在索引中連續：不能跳過索引中間列
5. 查詢列覆蓋索引或主鍵回表：SELECT * 需回表，但不影響排序優化

可用索引 vs 不可用索引對照表

SQL 模式	索引	結果	原因
ORDER BY key1, key2	INDEX(key1, key2)	✅	順序匹配
WHERE key1=1 ORDER BY key1 DESC, key2 DESC	INDEX(key1, key2)	✅	WHERE 常量 + 方向統一
ORDER BY key1 DESC, key2 ASC	INDEX(key1, key2)	❌	混合 ASC/DESC
WHERE key2=1 ORDER BY key1	INDEX(key1, key2)	❌	WHERE列非索引最左
WHERE key1=1 ORDER BY key1, key3	INDEX(key1, key2, key3)	❌	跳過 key2，不連續
WHERE key1=1 ORDER BY key2	INDEX(key1, key2)	✅	WHERE最左常量 + ORDER BY次左

EXPLAIN 秒懂判斷法

EXPLAIN SELECT ... ORDER BY create_date DESC LIMIT 10;

• Extra 包含 Using filesort → ❌ 文件排序，未用索引
• Extra 無 filesort，type=ref/range，key=索引名 → ✅ 索引排序

實戰案例複盤：從 9 萬行全表掃描到 3 千行索引範圍

原始慢查詢

SELECT ActionID, UserID, ... 
FROM wikiuseractionlog 
WHERE CreateDate > '0001-01-01 00:00:00' 
  AND ActionType IN (10,9,19,20) 
ORDER BY CreateDate DESC 
LIMIT 93060, 20;

EXPLAIN 结果：
type=ALL, key=NULL, rows=91859, Extra="Using where; Using filesort"

優化步驟與原理

1. 修正異常日期常量：'0001-01-01' 導致選擇率極高（>20%），優化器放棄索引。改為 '2014-01-01'，選擇率降至 4%，索引生效。

   
   WHERE CreateDate > '2014-01-01 00:00:00'
   -- rows: 91859 → 3737
   

2. 建立複合索引 (WHERE列, ORDER BY列)：

   
   CREATE INDEX idx_date_type ON wikiuseractionlog(CreateDate, ActionType);
   -- 或 idx_type_date(ActionType, CreateDate) 視選擇率而定
   

3. ORDER BY 列必須出現在 WHERE 中：

   
   -- 失效：ORDER BY CreateDate 但 WHERE 無 CreateDate
   WHERE ActionType IN (...) ORDER BY CreateDate
   
   -- 生效：WHERE 包含 CreateDate 常量範圍
   WHERE CreateDate > '2014-01-01' ORDER BY CreateDate
   

   原理：WHERE 範圍掃描時按索引順序讀取，天然有序，無需額外排序。

4. 避免深分頁大 Offset：

   
   LIMIT 93060, 20  -- 掃描 9 萬行丟棄
   -- 改用延遲關聯或游標分頁
   WHERE CreateDate > '2014-01-01' AND CreateDate < 'last_seen_date' ORDER BY CreateDate LIMIT 20
   

5. IN 列表優化為 UNION ALL：

   
   -- IN 列表導致範圍查詢退化
   WHERE ActionType IN (10,9,19,20)
   
   -- 改為 UNION ALL 多個等值查詢，各自走索引
   (SELECT ... WHERE ActionType=10 ORDER BY CreateDate LIMIT 20)
   UNION ALL
   (SELECT ... WHERE ActionType=9 ORDER BY CreateDate LIMIT 20)
   ...
   ORDER BY CreateDate DESC LIMIT 20;
   

優化後 EXPLAIN

type=range, key=idx_date_type, key_len=8, rows=3737, Extra="Using where"
-- 無 Using filesort，性能提升 20 倍+

SELECT * vs SELECT 列 的索引排序差異

-- 表 a(id 普通列有索引)
SELECT * FROM a ORDER BY id;      -- ❌ Using filesort
SELECT id FROM a ORDER BY id;     -- ✅ Using index
SELECT * FROM a WHERE id=10 ORDER BY id;  -- ✅ WHERE 引用 id

規律：ORDER BY 列必須出現在 SELECT 列表或 WHERE 條件中，才能觸發索引排序。

配置參數輔助優化

• sort_buffer_size：排序緩衝區，避免臨時文件（會話級，建議 2-4MB）
• read_rnd_buffer_size：隨機讀緩衝，優化排序後回表
• tmpdir：指向高速存儲（NVMe/內存盤），緩解 filesort 磁碟壓力

ORDER BY 優化不是玄學，是索引列序設計 + WHERE 常量約束 + 方向統一的工程規律。用 EXPLAIN 驗證每一次變更，讓「Using filesort」成為歷史。