能用上索引的 ORDER BY 模式
-- 1. 索引順序完全匹配
ORDER BY key_part1, key_part2...
-- 2. WHERE 固定前綴，ORDER BY 後綴
WHERE key_part1 = const ORDER BY key_part2
-- 3. GROUP BY 同理
WHERE key_part1 = const GROUP BY key_part2
-- 4. 全部 DESC（MySQL 8.0+ 降序索引）
ORDER BY key_part1 DESC, key_part2 DESC
-- 5. WHERE 固定值 + 全 DESC
WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC
無法用索引的模式（觸發 Filesort）
| 模式 | 原因 |
|---|
ORDER BY key1, key2 (不同索引) | 索引鍵不連續 |
WHERE key2=const ORDER BY key_part2 | 非最左前綴 |
ORDER BY key_part1 DESC, key_part2 ASC | 混合 ASC/DESC |
WHERE key2=const ORDER BY key1 | WHERE/ORDER 索引不同 |
| 多表 JOIN，ORDER BY 非首表列 | 非驅動表排序 |
ORDER BY 與 GROUP BY 表達式不同 | 無法複用排序 |
| HASH/HEAP 表 | 數據無序存儲 |
Filesort 算法演進
| 版本 | 算法 | 特點 |
|---|
| ≤ 4.0 | 雙值排序 | 只存 (sort_key, row_ptr)，需二次回表讀數據 |
| 4.1+ | 元組排序 | 存 (sort_key, row_ptr, select_columns)，單次讀取，佔用更大 sort_buffer |
優化參數調優
[mysqld]
# 排序緩存（每線程）
sort_buffer_size = 4M # 適當調大，避免磁盤臨時文件
max_length_for_sort_data = 4096 # 超過此長度退回雙值算法
# 隨機讀緩存（Filesort 合併階段）
read_rnd_buffer_size = 1M
# 臨時文件目錄（分布不同物理盤）
tmpdir = /tmp1:/tmp2:/tmp3
EXPLAIN 判斷技巧
EXPLAIN SELECT * FROM t ORDER BY col;
-- Extra 欄位：
-- Using filesort → 觸發 Filesort，需優化
-- Using index → 覆蓋索引，無排序開銷
-- 無額外信息 → 索引順序直接返回
實戰優化清單
- 為
WHERE + ORDER BY 組合建立**聯合索引** (where_col, order_col)
- 避免
SELECT *，只查所需列 → 觸發元組排序優化
LIMIT N 結合索引可大幅減少排序行數- 大表分頁深分頁 → 子查詢主鍵回表 / 游標 / 延遲關聯
- 監控
Created_tmp_disk_tables / Sort_merge_passes 判斷磁盤排序頻率
