索引架構決策：聚集 vs 非聚集
| 維度 | 聚集索引 | 非聚集索引 |
|---|
| 物理存儲 | 葉級即數據頁，按鍵值有序 | 葉級為行定位器 (RID/聚集鍵)，邏輯有序 |
| 每表數量 | 1 | ≤999 (2008+) |
| 理想鍵特徵 | 窄、唯一、遞增、靜態 | 高選擇性、覆蓋查詢列、外鍵列 |
| 範圍查詢 | 極優 (順序掃描) | 需書簽查找或覆蓋索引 |
| 插入熱點 | 末尾追加無爭用 (遞增鍵) | 隨機修改 B+ 樹 |
實踐鐵律：主鍵默認聚集 (可改)；外鍵必建非聚集；聚集鍵寬大時非聚集索引體積膨脹 (含聚集鍵值)；堆表 (無聚集) 適合純插入/掃描場景。
填充因子與頁分裂機制
- Fill Factor (0/100=默認填滿) 預留頁面空間，緩解插入/更新導致的頁分裂
- 頁分裂產生：邏輯碎片 (頁邏輯順序≠物理順序) + 物理碎片 (頁填充度↓)
- 填充因子靜態綁定於索引創建/重建時刻，不可動態調整
- 高頻插入表 (如日誌/明細) 建議 Fill Factor 80-90%；唯讀/少改表 0/100 最大化密度
碎片治理：REORGANIZE vs REBUILD
| 操作 | 語法 | 模式 | 碎片閾值 | 統計更新 | 填充因子生效 | 在線能力 |
|---|
| 重組 | ALTER INDEX ... REORGANIZE | 葉級頁重排、壓縮 | 10-30% | 否 | 否 | 完全在線 (短鎖) |
| 重建 | ALTER INDEX ... REBUILD [WITH (ONLINE=ON, FILLFACTOR=80)] | 全新構建 B+ 樹 | >30% | 是 (FULLSCAN) | 是 | 企業版在線 / 標準版離線 |
舊命令映射：DBCC INDEXDEFRAG → REORGANIZE；DBCC DBREINDEX → REBUILD。
統計信息：優化器的決策基石
- 自動創建/更新 (默認 ON)，大表異步更新 (AUTO_UPDATE_STATISTICS_ASYNC)
- 觸發閾值：修改行數 > 500 + 20% 表行數
- 手動：
UPDATE STATISTICS tbl [idx] WITH FULLSCAN / sp_updatestats
- 檢視：
DBCC SHOW_STATISTICS / sys.dm_db_stats_properties (直方圖/密度/步數)
- 過期統計 → 誤估行數 → 錯誤計劃 (掃描代替查找、錯誤連接順序/類型)
自動化維護體系
- 社區金標準：Ola Hallengren
IndexOptimize 腳本 — 智能閾值判斷、動態選擇 REORG/REBUILD、可配置填充因子、支援可用性群組、詳細日誌
- 維護計劃 (SSMS)：圖形化向導，適合簡單場景，靈活性受限
- Agent 作業排程：低峰期執行，REBUILD 間隔視碎片增長速度 (周/月)，REORG 可日頻
- 監控儀表盤：
sys.dm_db_index_physical_stats 碎片趨勢、sys.dm_db_index_usage_stats 使用率、sys.dm_db_missing_index_* 缺失建議
反模式清單
- ❌ 低基數列 (性別/狀態) 單列索引
- ❌ 過度索引 (寫入放大、優化器迷路)
- ❌ 忽略 Included Columns 覆蓋查詢
- ❌ 聚集鍵選用 GUID/寬字符/複合鍵導致非聚集索引膨脹
- ✅ 定期審查未用索引 (usages_stats: user_seeks+scans+lookups=0)
- ✅ 監控缺失索引 DMV 轉化為優化動作
- 上一篇文章： SQL Server 2000 SELECT 語句優化三板斧：STATISTICS IO、TIME 與 SHOWPLAN 實戰指南
- 下一篇文章： SQL Server 指定文件位置優化 I/O 性能實戰
Copyright ©
程式碼之家 All Rights Reserved
