MySQL 索引優化 ORDER BY 排序完全指南

MySQL 索引優化 ORDER BY 排序完全指南

在資料庫開發中，合理建立索引是提升查詢效能的核心手段之一。特別是針對 ORDER BY 和 GROUP BY 操作，適當的索引設計能避免檔案排序，大幅降低 CPU 和 I/O 開銷。本文系統整理 MySQL 利用索引優化排序的原則、適用場景與常見陷阱，助你寫出高效能 SQL。

核心原則：MySQL 單查詢僅能使用一個索引

MySQL 在單次查詢中只能選擇一個索引。若需對多欄位聯合排序或過濾，必須建立複合索引，且欄位順序需嚴格遵循「最左前綴」原則。

索引優化 ORDER BY 的三大經典場景

1. 純排序場景

SELECT col1, col2 FROM tbl ORDER BY sort_col;

在 sort_col 上建立單列索引即可利用索引完成排序，避免 filesort。

2. WHERE + ORDER BY 等值查詢

SELECT col1, col2 FROM tbl WHERE filter_col = 'val' ORDER BY sort_col;

建立複合索引 (filter_col, sort_col)。MySQL 先用索引定位 filter_col，再直接按索引順序讀取 sort_col，實現「where 過濾 + order by 排序」雙重優化。

3. WHERE + 多欄位 ORDER BY

SELECT * FROM tbl WHERE uid = 1 ORDER BY x, y LIMIT 10;

最佳索引為 (uid, x, y)。注意：索引順序必須與 ORDER BY 欄位順序完全一致，且方向（ASC/DESC）一致。若建立 (x, y, uid) 則無法優化排序。

無法使用索引優化排序的 6 大反模式

資料型別選擇對索引效能的影響

• 越小越好：INT 優於 BIGINT，DATE 優於 DATETIME。較小型別減少磁碟、記憶體、CPU 快取佔用。
• 簡單優於複雜：整數比較遠快於字串比較。IP 存為 INT UNSIGNED，時間用原生 DATETIME/TIMESTAMP。
• 避免 NULL：NULL 會增加索引統計複雜度，建議用 0、空串或特殊值替代，並設 NOT NULL。

實戰建議

1. 用 EXPLAIN 觀察 Extra 欄是否出現 Using filesort，若有則需優化索引。
2. 複合索引欄位順序 = WHERE 等值條件 → WHERE 範圍條件 → ORDER BY 欄位 → SELECT 欄位（覆蓋索引）。
3. 大量資料分頁時，優先用「延遲關聯」或「書籤式分頁」替代 LIMIT offset, N。

掌握上述原則，配合 EXPLAIN 持續調優，即可讓 MySQL 排序查詢跑在索引上，實現毫秒級響應。