MySQL EXPLAIN 出現 Using filesort 的原因分析與優化策略

前言

在 MySQL 效能調校中，EXPLAIN 是分析 SQL 執行計劃的核心工具。當 Extra 欄位出現 Using filesort 時，代表 MySQL 需要額外進行排序操作，這通常意味著查詢效能有優化空間。本文深入剖析 Using filesort 產生的機制，並透過實戰案例示範如何透過索引設計消除檔案排序。

什麼是 Using filesort？

根據 MySQL 官方文件定義：「MySQL 必須額外掃描一次以找出如何按排序順序檢索行。排序是通過根據聯接類型瀏覽所有行，並為所有符合 WHERE 條件的行保存排序鍵值和行指標來完成。」

簡單來說，當查詢需要排序但無法直接利用索引順序返回資料時，MySQL 會在記憶體（或磁碟）中建立臨時表進行排序，這就是 Using filesort。這屬於外部排序，效能較差，應盡量避免。

核心原理：多欄索引的最左前綴原則

多欄索引（複合索引）採用 B+ 樹結構，排序規則為：先按第一欄排序，第一欄相同再按第二欄排序，以此類推。因此索引 (A, B, C) 可支援 ORDER BY A、ORDER BY A, B、ORDER BY A, B, C，但無法支援 ORDER BY B 或 ORDER BY C。

實戰案例分析

問題 SQL

SELECT * FROM DB.TB 
WHERE ID = 2224 AND FID IN (11,8,13,123,382,40) 
ORDER BY INVERSE_DATE LIMIT 0, 4

原索引設計

IDX (ID, FID, INVERSE_DATE)

EXPLAIN 結果

Using where; Using filesort

原因剖析

索引順序為 ID → FID → INVERSE_DATE。查詢條件使用 ID = 常數 和 FID IN (...)，屬於範圍查詢。根據最左前綴原則，當遇到範圍查詢（IN、>、<、>=、<=）時，後續欄位 無法利用索引排序。因此 ORDER BY INVERSE_DATE 只能透過 filesort 完成。

優化方案

建立新索引 IDX2 (ID, INVERSE_DATE)，將排序欄位前移。此時查詢可直接利用索引順序返回資料，消除 Using filesort。

驗證實驗：單表測試

建表與資料準備

CREATE TABLE `testing` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `room_number` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (`id`),
  KEY `room_number` (`room_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 插入 10 萬筆測試資料
DELIMITER $$
CREATE PROCEDURE `insert_data` ()
BEGIN
  DECLARE v INT DEFAULT 1;
  WHILE v < 100000 DO
    INSERT INTO testing VALUES(v, v);
    SET v = v + 1;
  END WHILE;
END $$
DELIMITER ;
CALL insert_data();

優化前：出現 Using filesort

EXPLAIN SELECT id FROM testing WHERE room_number = 1000 ORDER BY id;
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows | Extra                       |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | testing | ref  | room_number   | room_number | 4       | const |    1 | Using where; Using filesort |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+

索引 room_number 用於 WHERE 條件，但 ORDER BY id 無法利用索引排序，產生 filesort。

優化後：建立複合索引消除 filesort

ALTER TABLE testing ADD INDEX room_number_id (room_number, id);

EXPLAIN SELECT id FROM testing WHERE room_number = 1000 ORDER BY id;
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys              | key            | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | testing | ref  | room_number,room_number_id | room_number_id | 4       | const |    1 | Using where |
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+-------------+

複合索引 (room_number, id) 同時滿足 WHERE 篩選與 ORDER BY 排序，Using filesort 消失。

總結與最佳實踐

1. ORDER BY 欄位務必建立索引，否則必然出現 Using filesort。
2. 複合索引遵循最左前綴原則：WHERE 條件中的等值查詢欄位放前面，範圍查詢欄位放後面，排序欄位放最後。
3. 避免「WHERE 範圍查詢 + ORDER BY 不同欄位」 的模式，這會導致索引失效於排序階段。
4. 並非所有 Using filesort 都需優化：若結果集極小（如 LIMIT 10）或查詢頻率低，優化收益可能不抵索引維護成本。
5. 善用 EXPLAIN 觀察 Extra 欄位，結合 Handler_read_* 狀態變數量化排序成本。

延伸閱讀

• MySQL 官方文件：EXPLAIN Output Format
• 《高效能 MySQL》第 3 章：索引優化與排序優化