MySQL Using filesort 優化實戰：混合索引消除文件排序

MySQL Using filesort 優化實戰：混合索引消除文件排序

Using filesort 表示 MySQL 無法利用索引完成 ORDER BY，需在內存/磁盤臨時表中排序，性能顯著下降。根因通常是 WHERE 與 ORDER BY 列不在同一索引中。

問題場景

-- 原表索引
CREATE INDEX idx_user_id ON user(id);

-- 查詢
SELECT * FROM user u WHERE u.id = 100 ORDER BY u.update_time;

EXPLAIN 顯示 Using filesort，因 idx_user_id 不包含 update_time。

解決方案：建立混合索引

DROP INDEX idx_user_id ON user;
CREATE INDEX idx_user_id_update_time ON user(id, update_time);

驗證優化效果

EXPLAIN SELECT * FROM user u WHERE u.id = 100 ORDER BY u.update_time;
+----+-------------+-------+------+--------------------------+--------------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys            | key                      | key_len | ref   | rows | Extra |
+----+-------------+-------+------+--------------------------+--------------------------+---------+-------+------+-------+
| 1  | SIMPLE      | u     | ref  | idx_user_id_update_time  | idx_user_id_update_time  | 8       | const | 1    |       |
+----+-------------+-------+------+--------------------------+--------------------------+---------+-------+------+-------+

Extra 列空，Using filesort 消失。

優化器不選擇新索引時強制使用

SELECT * FROM user u FORCE INDEX(idx_user_id_update_time)
WHERE u.id = 100 ORDER BY u.update_time;

索引設計原則

1. 等值條件在前：WHERE 中 =/IN 的列放索引最左。
2. 排序/分組列在後：ORDER BY/GROUP BY 的列緊隨其後。
3. 範圍條件打斷：WHERE 有範圍查詢（>、<、>=、<=、LIKE %...），其後列無法用於排序。
4. 覆蓋索引：SELECT 列全部在索引中，避免回表。

常見誤區

• 單獨為 ORDER BY 列建索引 → WHERE 不走索引，反而全表掃描。
• 忽略字符集/排序規則差異導致索引失效。
• 過度建索引增加寫入開銷，需定期 SHOW INDEX 審計低選擇性索引。