MySQL ORDER BY 深度解析：語法、索引優化與 filesort 避坑指南

ORDER BY 看似簡單，實則是效能殺手重災區。本文系統梳理語法、索引利用條件、無法用索引的 6 大場景、優化實戰。

基礎語法

SELECT column_list FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC] ...;

• ASC：升序 (預設)，1→9, a→z
• DESC：降序，9→1, z→a
• 多欄位排序：先按第一欄，相等才按第二欄

索引優化核心原則

ORDER BY 能用上索引的充要條件：

1. 排序欄位構成索引的最左前綴
2. 排序方向與索引定義方向一致 (全 ASC 或全 DESC)
3. WHERE 條件欄位與 ORDER BY 欄位能組成聯合索引

能用上索引的場景 (Good Cases)

SQL 模式	建議索引	說明
ORDER BY col1	INDEX (col1)	單欄索引
WHERE a=1 ORDER BY b	INDEX (a, b)	WHERE 欄位在前，排序欄位在後
WHERE a=1 ORDER BY b, c	INDEX (a, b, c)	多欄排序符合最左前綴
WHERE a=1 ORDER BY b DESC, c DESC	INDEX (a, b DESC, c DESC)	8.0+ 支援降序索引
WHERE a IN (1,2) ORDER BY b	INDEX (a, b)	IN 常數列表視為等值，可用索引

無法用索引的 6 大死穴 (Bad Cases → filesort)

場景	SQL 範例	原因	解決方案
不同索引欄位排序	ORDER BY key1, key2 (分別建單列索引)	非同一索引	建聯合索引 (key1, key2)
非連續索引部分排序	WHERE key2=1 ORDER BY key_part2 (索引 (key_part1, key_part2))	跳過 key_part1	WHERE 加 key_part1=常數 或改索引順序
混合 ASC/DESC	ORDER BY a ASC, b DESC	方向不一致	8.0+ 建降序索引；舊版應用層排序
WHERE 與 ORDER BY 欄位不同	WHERE key2=1 ORDER BY key1	無聯合索引覆蓋	建 (key2, key1) 或覆蓋索引
排序欄位用函數/表達式	ORDER BY YEAR(date_col)	非直接欄位值	冗餘欄位存年份/虛擬欄位/應用層排序
JOIN 時排序非驅動表欄位	SELECT * FROM t1 JOIN t2 ... ORDER BY t2.col	非驅動表排序需臨時表	調整驅動表、冗餘排序欄位到驅動表

EXPLAIN 判斷是否 filesort

EXPLAIN SELECT * FROM t ORDER BY col;
-- Extra 欄位出現 "Using filesort" = 觸發文件排序 (慢)
-- Extra 欄位無 filesort = 使用索引排序 (快)

實戰優化案例

案例 1：分頁查詢優化

-- 原始：大偏移量極慢
SELECT * FROM articles WHERE status=1 ORDER BY created_at DESC LIMIT 100000, 20;

-- 優化 1：延遲關聯 (先查 ID 再回表)
SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles WHERE status=1 ORDER BY created_at DESC LIMIT 100000, 20
) b ON a.id = b.id;

-- 優化 2：游標分頁 (需唯一遞增鍵)
SELECT * FROM articles 
WHERE status=1 AND id < 最后一页最大ID 
ORDER BY created_at DESC LIMIT 20;

案例 2：覆蓋索引避免回表

-- 查詢只需 id, title, created_at
CREATE INDEX idx_status_created ON articles(status, created_at DESC, id);
-- 包含所有查詢欄位 → 覆蓋索引，無需回表

案例 3：強制索引 (謹慎使用)

SELECT * FROM t FORCE INDEX (idx_a_b) WHERE a=1 ORDER BY b;

配置參數影響排序效能

參數	作用	調優建議
sort_buffer_size	排序緩衝區大小 (每線程)	256K~2M，大排序可適當調大
max_length_for_sort_data	觸發雙路排序的行長度閾值	預設 1024，大欄位查詢可調大
sort_mode	排序算法選擇	通常自動選擇，無需干預

總結：ORDER BY 優化檢查清單

索引設計要同時服務 WHERE 過濾與 ORDER BY 排序，才是真正的高效查詢。