索引是關聯式資料庫效能核心。本文系統整理 叢集索引、非叢集索引、覆蓋索引 三大類型的適用場景、候選欄位特徵、建立順序原則，並提供實測效能對比數據 (89 萬列住房公積金明細表)、索引維護時機與 FILLFACTOR 設定指南。
一、叢集索引：資料實體排序
特性
- 每表僅一個，資料頁實體依索引鍵排序。
- 葉層即資料頁，查找幾乎總是比非叢集快。
- 建立需 1.2 倍表大小暫存空間。
候選欄位 (優先序)
- 主鍵 (WHERE 條件常用、插入隨機)。
- 範圍查詢欄位 (
BETWEEN, >, <, GROUP BY, ORDER BY)。
- 連接欄位 (JOIN ON)。
- 低修改頻率欄位 (避免頁分裂)。
- 非單調遞增欄位 (避免熱點頁競爭)。
避免建立叢集索引的欄位
- 經常 UPDATE 的欄位 (資料列需物理移動)。
IDENTITY / 單調遞增欄位 (高併發 INSERT 熱點)。- 寬欄位 (複合鍵過大 → 非叢集索引鍵膨脹)。
二、非叢集索引：邏輯排序 + 書籤查找
特性
- 不重組資料，葉層含索引鍵值 + 書籤 (RID 或叢集鍵)。
- 每表最多 999 個 (SQL 2008+)，預設建立類型。
- 每筆 INSERT/UPDATE/DELETE 需同步維護所有非叢集索引。
適用場景
- 彙總函數欄位 (
SUM, AVG)。
- JOIN/ORDER BY/GROUP BY 常用欄位。
- 查詢回傳資料 < 20% 表大小 (書籤查找成本可接受)。
- 高選擇度欄位 (唯一值多)。
建立決策檢核
| 問題 | 是 → 行動 |
|---|
| 索引需多少空間？ | 評估磁碟成本 vs 查詢收益 |
| 欄位值是否穩定？ | 不穩定 → 慎建或設低 FILLFACTOR |
| 掃描效果是否優於索引搜尋？ | 全表掃描更快 → 不建索引 |
| 重複值是否過多？ | 選擇度低 (< 1%) → 考慮篩選索引/分區 |
三、覆蓋索引：查詢完全在索引內完成
定義
非叢集索引包含查詢所需 所有欄位 (SELECT 清單 + WHERE/JOIN/ORDER BY)，無需書籤查找回資料頁。
語法
CREATE NONCLUSTERED INDEX IX_ORDERS_COVER
ON ORDERS (CUSTID, ORDERDATE) INCLUDE (TOTALAMT, STATUS);
-- INCLUDE 欄位不計入索引鍵長度限制 (900 位元組)，不參與排序
權衡
- ✅ 查詢速度超越叢集索引 (無書籤查找)。
- ❌ 索引體積大、UPDATE 成本高 (含 INCLUDE 欄位異動時)。
- ⚠️ 僅對高頻、關鍵查詢建立，避免過度索引。
四、實測效能對比 (89 萬列、Sybase 11.0.3、C/S 環境)
| 索引策略 | 查詢 1 (日期範圍 + 餘額) | 查詢 2 (日期 + 繳費月份) |
|---|
| 無索引 | 1 分 15 秒 | 1 分 07 秒 |
| OP_DATE 非叢集 | 57 秒 | 57 秒 |
| OP_DATE 叢集 | < 1 秒 | 52 秒 |
| PAY_MONTH, OP_DATE, PRI_SURPLUS1 非叢集 | 34 秒 | < 1 秒 |
| OP_DATE, PAY_MONTH, PRI_SURPLUS1 非叢集 (前導欄位正確) | < 1 秒 | < 1 秒 |
結論：前導欄位順序決定成敗，叢集索引對範圍查詢極致有效，複合非叢集索引需依查詢模式排序欄位。
五、索引選擇技術總結
- 主鍵常用於 WHERE/JOIN → 叢集索引。
- 大量重複值 + 範圍/排序/分組 → 叢集索引候選。
- 多欄位常共同出現 → 複合索引，最高頻欄位為前導，力求覆蓋查詢。
- 全唯一值 → 明確宣告
UNIQUE，優化器可產生更佳計畫。
- 高併發 INSERT 表 → 設定
FILLFACTOR 70-90 減少頁分裂；唯讀表 → 100。
- 小資料類型 (INT < BIGINT < CHAR) → 索引頁容納更多鍵值，遍歷層數更少。
六、索引維護：重建與統計資料更新
重建時機 (ALTER INDEX ... REBUILD)
- 資料/使用模式劇烈變化。
- 大量載入完成後。
- 大量 I/O 查詢實際讀取頁數遠超預期。
- DBCC SHOWCONTIG 顯示掃描密度 < 80% 或邏輯碎片 > 30%。
⚠️ 重建叢集索引會連帶重建所有非叢集索引。
統計資料更新 (UPDATE STATISTICS)
- 大量 INSERT/DELETE/UPDATE 改變資料分布。
TRUNCATE TABLE 後重新載入資料。- 索引鍵值被修改。
- 建立自動更新統計資料 (
AUTO_UPDATE_STATISTICS ON) 並配合 AUTO_UPDATE_STATISTICS_ASYNC ON 避免查詢等待。
七、反模式：不該建索引的情況
- 查詢中從未引用的欄位。
- 基數極低欄位 (性別、布林、狀態碼 < 3 個值) — 掃描更快。
- 極寬欄位 (VARCHAR(MAX), XML) — 改用全文檢索或屬性提取。
