資料庫索引優化進階：ORDER BY 與 WHERE 合用索引策略

資料庫索引優化進階：ORDER BY 與 WHERE 合用索引策略

建立索引不僅要考慮 WHERE 條件欄位，ORDER BY 排序欄位同樣重要。本文透過 PostgreSQL 執行計畫說明如何建立複合索引避免額外排序操作，提升查詢效能。

情境：WHERE + ORDER BY 查詢

SELECT * FROM mytable
WHERE category_id = 1 AND user_id = 2
ORDER BY adddate DESC;

錯誤做法：分別建立單欄索引

若只在 category_id、user_id、adddate 分別建立單欄索引，資料庫可能只使用 WHERE 條件索引，執行完仍需對結果集進行 Sort（排序） 操作，造成效能損耗。

正確做法：建立複合索引

CREATE INDEX mytable_categoryid_userid_adddate
ON mytable (category_id, user_id, adddate);

索引欄位順序：WHERE 條件的等值比較欄位在前，範圍/排序欄位在後。

執行計畫比較

未優化前（需額外 Sort）

EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;

-- QUERY PLAN:
-- Sort (cost=2.03..2.03 rows=1 width=16)
--   -> Index Scan using mytable_categoryid_userid_addda
--      on mytable (cost=0.00..2.02 rows=1 width=16)

出現 Sort 節點表示資料庫需額外排序。

優化後（Index Scan Backward 直接由索引取得已排序資料）

EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY category_id DESC, user_id DESC, adddate DESC;

-- QUERY PLAN:
-- Index Scan Backward using mytable_categoryid_userid_addda
-- on mytable (cost=0.00..2.02 rows=1 width=16)

關鍵技巧：在 ORDER BY 中加入 WHERE 條件欄位（category_id DESC, user_id DESC），雖然這兩欄位實際上不會改變排序結果，但能讓查詢規劃器確認索引順序完全符合，直接採用 Index Scan Backward 從索引尾端反向掃描，完全避免排序。

核心原則總結

1. 複合索引欄位順序：等值比較（=）欄位 > 範圍比較（>, <, LIKE）欄位 > 排序（ORDER BY）欄位。
2. ORDER BY 欄位方向一致：索引建立為 ASC（預設），查詢若為 DESC，資料庫可採用 Backward Scan；若混合 ASC/DESC 可能無法使用索引排序。
3. 覆蓋索引：若 SELECT 欄位皆包含在索引中，可達成 Index Only Scan，完全不需回表。
4. 避免過度索引：每個索引增加寫入負擔，僅為高頻查詢建立。

MySQL / PostgreSQL 共通適用

上述原理在 MySQL (InnoDB)、PostgreSQL、SQL Server 等主流關聯式資料庫皆適用。實務上請搭配 EXPLAIN (MySQL) 或 EXPLAIN ANALYZE (PostgreSQL) 驗證執行計畫。

延伸：多表 JOIN 索引策略

當 WHERE 條件涉及多個表格欄位時，建議：

• 優先縮小驅動表（驅動表為小結果集的表）。
• 被驅動表的 JOIN 欄位建立索引。
• 考慮將常用查詢改寫為子查詢或使用衍生表，減少 JOIN 複雜度。

索引設計是資料庫效能調校的核心技能，建議開發者養成「寫查詢前先想索引、寫完後必看執行計畫」的習慣。