MySQL ORDER BY 索引優化實戰：覆蓋索引、組合索引消除 filesort 的 5 大模式

前言

ORDER BY 若無索引支援會觸發 Using filesort，大數據量下極慢。本文總結 5 種常見 ORDER BY 模式與對應的索引設計策略，並列舉建立索引的 5 大鐵律。

建立索引 5 大鐵律

1. 合理索引加速讀取，不合理索引拖慢寫入。
2. 索引越多，UPDATE/INSERT/DELETE 越慢。
3. MyISAM (B-Tree) 較適合大量讀取場景；InnoDB 支援交易但索引維護成本較高。
4. SQL/結構優化到極限仍有瓶頸 → 引入 Redis/Memcached 分散式快取。
5. 習慣用 EXPLAIN 分析每條 SQL。

常見錯誤：在 SELECT 欄位建索引

-- ❌ 無效：SELECT 欄位不需索引
SELECT id,title,content,cat_id FROM article WHERE cat_id=1;
-- 正確：WHERE/ORDER BY 欄位建索引
CREATE INDEX idx_cat_id ON article(cat_id);

5 大 ORDER BY 優化模式

模式 1：ORDER BY + LIMIT

SELECT col1,col2 FROM tbl ORDER BY sort_col LIMIT offset, N;
-- 索引：INDEX idx_sort (sort_col)

直接走索引掃描取前 N 筆，無 filesort。

模式 2：WHERE + ORDER BY + LIMIT (等值條件)

SELECT * FROM tbl WHERE colX = val ORDER BY sort_col LIMIT offset, N;
-- 索引：INDEX idx_x_sort (colX, sort_col)  -- 組合索引，等值在前、排序在後

單欄索引 sort_col 雖可用但需 filesort；組合索引可直接按順序取資料。

模式 3：WHERE + IN + ORDER BY + LIMIT

SELECT * FROM tbl WHERE colX IN (v1,v2,...) ORDER BY sort_col LIMIT offset, N;

難點：IN 視為多個等值，組合索引 (colX, sort_col) 可能退化為 Using where; Using filesort。
實務建議：UNION ALL 拆分多條 colX=vi 查詢各走組合索引，或強制 USE INDEX 配合 ORDER BY 讓優化器選擇排序索引 (視版本/資料分布測試)。

模式 4：ORDER BY 含函數/運算式

-- ❌ 失效
SELECT * FROM tbl ORDER BY YEAR(date_col) LIMIT 30;
-- ✅ 改寫：若需按年排序，建立虛擬欄位/生成欄位索引 (MySQL 5.7+)
ALTER TABLE tbl ADD year_col INT GENERATED ALWAYS AS (YEAR(date_col)) STORED, ADD INDEX idx_year (year_col);

模式 5：WHERE + 多欄 ORDER BY + LIMIT

SELECT * FROM tbl WHERE uid=1 ORDER BY x,y LIMIT 10;
-- 直覺索引 (x,y,uid) ❌
-- 最佳索引 (uid,x,y) ✅  -- WHERE 條件欄位在最左，後接排序欄位

MySQL 排序機制要求：WHERE 等值欄位最左，ORDER BY 欄位依序緊接。

驗證清單

• 加索引後必跑 EXPLAIN 確認 Extra 無 Using filesort、Using temporary。
• key_len 確認組合索引用了幾個欄位。

延伸閱讀

• MySQL 用戶權限取消與刪除
• MySQL 索引比較分析
• MySQL 索引的核心作用與效能量化
• MySQL 索引缺點與注意事項
• MySQL 連接字串說明