索引效能核心原理
查詢成本多為磁碟 I/O。索引避免全表掃描，僅需讀取少數資料頁。但索引增加 INSERT/UPDATE/DELETE 開銷。合理索引 = 正確分析查詢模式 + 適當建立/移除。
一、叢集索引選擇與使用
特性
- 資料實體依索引鍵排序，葉層即資料頁
- 每表僅一個，需 ≥ 120% 表大小暫存空間
- 範圍查詢 (
BETWEEN, >, <)、GROUP BY/ORDER BY 極佳
選擇原則
- 大多數表應有叢集索引，降低尾頁競爭 (高交易環境)
- 頻繁 INSERT 表 勿建於單調遞增欄位 (IDENTITY)，易造成熱點封鎖
- 避免包含頻繁修改欄位 (值變更導致資料行物理移動)
- 基於 WHERE、JOIN、GROUP BY、ORDER BY 欄位選擇
候選欄位
- 主鍵 (WHERE 條件、隨機插入)
- 範圍存取欄位
- GROUP BY/ORDER BY 欄位
- 不常修改欄位
- JOIN 欄位
二、非叢集索引選擇與使用
特性
- 不重組資料，葉層含索引鍵值 + 書籤 (RID/叢集鍵)
- 無叢集索引表最多 250 個非叢集索引
- 每個提供不同排序存取路徑
權衡考量
- 空間佔用
- 欄位穩定性
- 選擇性 (掃描效果)
- 重複值分布
適用場景
- 常用於彙總函數 (
SUM 等)
- 常用於 JOIN/ORDER BY/GROUP BY
- 查詢回傳 ≤ 20% 表資料量
三、覆蓋索引
- 非叢集索引葉層包含查詢所需所有欄位，無需回表 (Key Lookup)
- 查詢速度可超越叢集索引
- 代價：索引體積大、UPDATE 導致索引鍵值變更開銷大
- 僅對高頻關鍵查詢建立，否則得不償失
四、索引鍵選擇技巧
- 優先選擇小資料型別欄位 (單一索引頁容納更多鍵值)
- 盡可能用整數 做鍵值 (存取速度最快)
- 複合索引：最高選擇性/最常用欄位置於最左 (前導欄位)
- 全唯一鍵值 → 定義為 UNIQUE 索引，優化器可產生更佳計畫
五、填充因子
- 頻繁 INSERT 表 → 設定
FILLFACTOR < 100 (如 80) 減少分頁、提高並發、降低死鎖
- 唯讀表 →
FILLFACTOR = 100 最大化密度
六、索引維護
重建索引 (重組 B-Tree)
觸發條件：資料/使用模式劇變、排序順序變更、大量匯入前後、大量 I/O 查詢讀取頁數異常、空間使用超估、DBCC 檢測異常。重建叢集索引會連帶重建所有非叢集索引。
更新統計資訊
觸發條件：資料分布變更、TRUNCATE TABLE 後新增資料、索引鍵值修改。執行 UPDATE STATISTICS 確保優化器擁有最新統計。
七、不當索引反模式
- 從未在查詢中參考的欄位
- 極低基數欄位 (是/否、男/女、狀態碼 < 3 個值)
進階：檔案群組分離
表置於一檔案群組，非叢集索引置於另一群組，對應不同實體磁碟，平衡 I/O 負載。
