索引類型選擇指南
| 索引類型 | 適用場景 | 限制 |
|---|
| 叢集索引 | 欄位值高度相關、頻繁範圍查詢 (BETWEEN, >, <, >=, <=)、ORDER BY、GROUP BY 欄位 | 每表僅一個；資料頁物理順序與索引順序一致 |
| 非叢集索引 | 欄位值分散度高、精確查詢 (WHERE col = @val)、JOIN 條件、外來鍵欄位 | 每表最多 249 個 (SQL 2000) / 999 個 (SQL 2005+) |
- 主鍵預設建立唯一叢集索引；若主鍵非叢集鍵，需顯式指定
NONCLUSTERED。
- 外來鍵欄位建立非叢集索引可大幅加速連接與刪除父資料列時的檢查。
- 堆積表 (無叢集索引) 適合大量插入、極少查詢的暫存/階段表。
資料頁與填補因子
- 索引建立時依
FILLFACTOR (0-100) 保留每頁免費空間，減少頁分裂。
- 隨時間推移，插入/更新/刪除導致碎片：邏輯碎片 (頁順序錯亂) 與密度碎片 (頁填滿度低)。
FILLFACTOR 非動態維護，需定期重建索引重新套用。
碎片整理兩大指令
| 指令 | 模式 | 封鎖 | 適用時機 |
|---|
| DBCC INDEXDEFRAG | 線上 (Online) | 不封鎖查詢/更新 | 碎片輕微 (<30%)、維護視窗短 |
| DBCC DBREINDEX / ALTER INDEX REBUILD | 離線 (預設) / 線上 (Enterprise) | 離線模式佔用 Sch-M 鎖 | 碎片嚴重、需重設 FILLFACTOR、統計資料過期 |
SQL 2005+ 建議使用 ALTER INDEX ... REBUILD/REORGANIZE 替代 DBCC，支援 ONLINE = ON、MAXDOP、DATA_COMPRESSION 等進階選項。
統計資料維護
- 查詢優化器依賴索引統計資料估算基數。資料變更超過 20% + 500 列即標記過期。
- 啟用
AUTO_UPDATE_STATISTICS (預設開啟)，大型資料表可搭配 AUTO_UPDATE_STATISTICS_ASYNC 避免查詢等待。
- 定期手動
UPDATE STATISTICS ... WITH FULLSCAN 確保關鍵查詢計畫穩定。
- 使用
DBCC SHOWCONTIG (SQL 2000) 或 sys.dm_db_index_physical_stats (SQL 2005+) 監控碎片與統計狀態。
實務檢查清單
- 識別高耗用查詢 (DMV/Profiler/Query Store)。
- 分析執行計畫：是否出現 Table Scan、Key Lookup、Missing Index 提示。
- 建立覆蓋索引消除 Key Lookup；調整現有索引欄位順序符合查詢謂詞。
- 設定合理
FILLFACTOR (OLTP 通常 80-90，唯讀/數倉 100)。
- 排程每週/每月索引重建/重組 + 統計更新。
- 監控
page_split/sec、avg_fragmentation_in_percent 指標。
