MySQL ORDER BY 排序全解析：升降序、NULL處理與分頁優化

基本語法

SELECT ... FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
[LIMIT offset, count];

• 預設 ASC 升序，DESC 降序。
• 多欄位排序：先按第一欄，相同再按第二欄，以此類推。

NULL 值排序規則

可用 ORDER BY ISNULL(col), col ASC 強制 NULL 最後，或 ORDER BY col IS NULL, col（MySQL 8.0+ 支援 NULLS FIRST/LAST）。

實戰範例

-- 查詢 jtsr 表，按 jtsr 降序，取前 3 筆
SELECT * FROM jtsr
WHERE id IS NOT NULL
ORDER BY jtsr DESC
LIMIT 3;
+----+------+------+-----+
| id | user | jtsr | sex |
+----+------+------+-----+
|  5 | ggg  | 5000 | 男  |
|  4 | fff  | 4000 | 男  |
|  3 | ddd  | 3000 | 女  |
+----+------+------+-----+

效能優化關鍵點

1. 索引消除 Filesort

• ORDER BY 欄位若有索引且符合最左前綴，可直接掃描索引輸出有序結果，避免 Using filesort。
• 複合索引順序需與 ORDER BY 欄位順序、方向一致（或完全相反）。

2. LIMIT 配合索引

-- 優化：索引 (jtsr DESC) 直接取前 3，無需全表排序
EXPLAIN SELECT * FROM jtsr ORDER BY jtsr DESC LIMIT 3;

3. 避免「深分頁」效能陷阱

LIMIT 100000, 10 需掃描 10 萬行再丟棄。優化方案：

• 延遲關聯：SELECT * FROM t JOIN (SELECT id FROM t ORDER BY col LIMIT 100000, 10) tmp USING (id)
• 游標分頁：記住上一頁最大 id，下頁查 WHERE id > last_id ORDER BY id LIMIT 10。

4. 排序緩衝區設定

• sort_buffer_size：每個執行緒專用，預設 256KB，大排序可適當調大（如 2MB），但過大會導致記憶體耗盡。
• max_length_for_sort_data：控制是否使用「額外欄位排序」演算法。