MySQL ORDER BY 優化兩大思路：索引消除排序與 EXPLAIN 驗證

ORDER BY 觸發文件排序（filesort）會嚴重拖慢查詢。本文提供兩大優化思路：索引覆蓋排序、EXPLAIN 驗證排序模式。

思路一：索引滿足 ORDER BY 消除排序

條件：

1. WHERE 條件列為常數（非變量）
2. ORDER BY 列與索引順序一致
3. 組合索引遵循最左前綴原則

優化示例

-- 表結構：is_active(常數查詢) + value(排序)
SELECT * FROM ad_user 
WHERE is_active = 'Y' 
ORDER BY value;

建立組合索引 INDEX idx_active_value (is_active, value)，查詢直接按索引順序返回，零排序開銷。

無法使用索引的場景

• ORDER BY 列混用 ASC/DESC
• WHERE 條件使用變量（參數化查詢）
• WHERE 與 ORDER BY 列不匹配
• 非連續索引列排序
• GROUP BY 與 ORDER BY 表達式不一致
• 存儲引擎不支持有序存儲（如 HEAP）

思路二：調大排序緩存緩解 Filesort

當索引無法覆蓋排序時，調整會話/全局緩存：

• sort_buffer_size：排序緩存，超過則用臨時文件
• read_buffer_size：順序讀緩存
• tmpdir：指向高性能存儲（SSD/內存盤），可多目錄分流

EXPLAIN 驗證排序模式

EXPLAIN SELECT * FROM ad_user WHERE is_active='Y' ORDER BY value;

• Extra 欄出現 Using filesort → 觸發文件排序，需優化
• 無 Using filesort → 走索引順序，零排序開銷

Filesort 內部流程（優化版）

1. 讀取滿足 WHERE 的行
2. 每行構造：排序鍵 + 行指針 + 查詢需返回的列
3. 內存/臨時表排序元組
4. 按序讀取元組直接返回列值，避免二次回表

關鍵優化：元組包含 SELECT 列，省去排序後再按行指針回表讀取。

實戰決策樹

ORDER BY 慢？
├─ 能加索引覆蓋？ → 加組合索引（WHERE常數 + ORDER BY列）
├─ WHERE用變量？ → 業務評估：固定SQL換性能 vs 動態參數換靈活
├─ 大表必排？ → 調大 sort_buffer_size、tmpdir指向SSD
└─ 允許近似？ → 應用層抽樣/預排序/緩存