MySQL ORDER BY 優化深度解析：Using Index vs Using Filesort

前言

ORDER BY 是 SQL 查詢中常見的排序操作。MySQL 有兩種排序實現方式：利用有序索引直接回傳（Using index）與 檔案排序（Using filesort）。前者效能極佳，後者在大資料量時會消耗大量 CPU/記憶體甚至落盤。本文深入解析兩者的觸發條件、優化技巧與實戰案例。

兩種排序模式對比

索引排序的觸發條件（必須同時滿足）

1. ORDER BY 欄位順序與索引順序一致（或完全相反，MySQL 8.0+ 支援逆向掃描）。
2. WHERE 條件欄位為索引前綴，且不破壞索引順序。
3. SELECT 欄位僅包含索引欄位（覆蓋索引），或為主鍵（InnoDB 二級索引含主鍵）。
4. 無 JOIN、無 UNION、無 DISTINCT 等破壞順序的操作。

經典案例分析

測試表結構

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rdate` datetime NOT NULL,
  `inventid` int(11) NOT NULL,
  `customerid` int(11) NOT NULL,
  `staffid` int(11) NOT NULL,
  `data` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rdate` (`rdate`,`inventid`,`customerid`),
  KEY `idx_inventid` (`inventid`),
  KEY `idx_customerid` (`customerid`),
  KEY `idx_staffid` (`staffid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

案例 1：WHERE 使用索引前綴，ORDER BY 緊接索引順序 → Using index

EXPLAIN SELECT inventid FROM test 
WHERE rdate='2011-12-14 00:00:00' 
ORDER BY inventid, customerid;

分析：WHERE rdate=常數 固定了聯索引 uk_rdate 的第一欄，ORDER BY inventid, customerid 完全符合索引後續順序，且 SELECT inventid 為索引欄位 → Using index。

案例 2：WHERE 用索引，但 ORDER BY 欄位不在同一索引 → Filesort

EXPLAIN SELECT * FROM test 
WHERE rdate='2011-12-14 00:00:00' 
ORDER BY staffid;

分析：staffid 不在 uk_rdate 中，MySQL 必須先按 uk_rdate 篩選，再對結果集 Filesort。

案例 3：SELECT * 導致回表 → Filesort（即使 ORDER BY 符合索引）

EXPLAIN SELECT * FROM test 
WHERE rdate='2011-12-14 00:00:00' 
ORDER BY inventid, customerid;

分析：SELECT * 包含非索引欄位 data，需回表，破壞索引順序優勢 → Using filesort。

Filesort 兩種演算法

優化 Filesort 實戰技巧

1. 增加 sort_buffer_size：Session 級參數，適度調大（如 2M→8M）減少臨時檔案。
2. 只 SELECT 必要欄位：避免 SELECT *，縮減單路排序記憶體佔用。
3. 建立聯覆蓋索引：(WHERE欄位, ORDER BY欄位, SELECT欄位) 實現 Using index。
4. LIMIT 限制結果集：ORDER BY ... LIMIT 10 觸發「優先佇列排序」，只保留 Top-N，大幅降低排序成本。
5. 避免在不同方向混合排序：ORDER BY a ASC, b DESC 無法使用索引（MySQL 8.0 除外）。

MySQL 8.0 新特性：降序索引

CREATE INDEX idx_desc ON t_order (create_time DESC, id DESC);
-- 現在可支援 ORDER BY create_time DESC, id DESC 使用索引

總結

Using index 是排序效能的聖杯，關鍵在於「WHERE 固定索引前綴 + ORDER BY 遵循索引順序 + 覆蓋索引」。若無法達成，善用 LIMIT、覆蓋索引、調整 sort_buffer_size 可大幅緩解 Filesort 壓力。EXPLAIN 時務必關注 Extra 欄位，將 Filesort 視為優化切入點。