MySQL EXPLAIN 深度解析：Using filesort 成因與優化

EXPLAIN 基礎

EXPLAIN SELECT * FROM tbl WHERE ...;
-- 等同於 DESCRIBE tbl 或 SHOW COLUMNS FROM tbl

關鍵欄位：type (存取類型)、key (實際用索引)、rows (預估掃描行)、Extra (額外資訊)。

Using filesort 核心成因

只有 ORDER BY 才會觸發 filesort。若 ORDER BY 欄位無索引，無論值是否重複，必現 Using filesort。

實戰案例分析

問題 SQL

SELECT * FROM DB.TB 
WHERE ID = 2222 
  AND FID IN (9,8,3,13,38,40) 
ORDER BY INVERSE_DATE 
LIMIT 0, 5;

現有索引

IDX (ID, FID, INVERSE_DATE)  -- 三欄複合索引

EXPLAIN 結果

Using where; Using filesort

根因剖析

複合索引 B+Tree 結構：先按 ID 排序 → 同 ID 下按 FID 排序 → 同 FID 下按 INVERSE_DATE 排序。

查詢條件 ID = 2222 精確匹配第一列，FID IN (...) 為多值匹配 (範圍查詢)，導致 INVERSE_DATE 無法保持全局有序，MySQL 必須額外排序 → Using filesort。

優化方案

-- 新增支援 ORDER BY 的索引
ALTER TABLE DB.TB ADD INDEX idx_id_invdate (ID, INVERSE_DATE);

新索引結構：ID 排序 → 同 ID 下 INVERSE_DATE 排序。查詢時先用 ID=2222 定位，再按索引順序掃描 INVERSE_DATE，直接滿足 ORDER BY，消除 filesort。

進階優化模式

關鍵原則總結

1. 最左前綴原則： 索引列順序 = WHERE 等值條件 → ORDER BY 列 → 範圍條件
2. 範圍列截斷： 遇到 >, <, IN, LIKE '%...' 等範圍條件，後續索引列失效
3. 覆蓋索引： SELECT id, name FROM t WHERE id=1 ORDER BY name 若索引含 (id, name) 可避免回表
4. LIMIT 配合： ORDER BY indexed_col LIMIT N 可極大減少排序成本

驗證優化效果

-- 優化前
EXPLAIN SELECT ... ORDER BY INVERSE_DATE LIMIT 5;
-- type=ref, Extra=Using where; Using filesort

-- 優化後
EXPLAIN SELECT ... ORDER BY INVERSE_DATE LIMIT 5;
-- type=ref, Extra=Using where (無 filesort)

現代 MySQL 8.0+ 引入 降序索引 (INDEX (a ASC, b DESC)) 與 隱藏索引 功能，可更靈活應對混合排序場景。