合理的索引設計能讓查詢速度提升數十倍;不當的索引卻會拖垮寫入效能。本文系統剖析叢集/非叢集/覆蓋索引特性、選擇原則、維護策略與實測案例。
BETWEEN、>、<、GROUP BY、ORDER BY 受益最大——命中首筆後續資料物理毗連。IDENTITY 列,插入熱點集中於尾頁,引發鎖衝突。order_date BETWEEN ...)GROUP BY/ORDER BY 常用列非叢集索引在 B+ 樹葉層存「鍵值 + 行定位器」(RID 或叢集鍵),多一層間接查找。無叢集索引時最多 249 個非叢集索引;有叢集索引時最多 999 個。
| 考量因素 | 評估標準 |
|---|---|
| 空間成本 | 索引大小 = 鍵長 × 行數 × 1.2(填充因子) |
| 鍵值穩定性 | 頻繁更新的列不適合做索引鍵 |
| 選擇性 | 唯一值比例高(> 90%)才值得建索引 |
| 重複值分佈 | 低基數列(如性別)單列索引無效 |
SUM、AVG)JOIN、ORDER BY、GROUP BY 常用列高頻 UPDATE/DELETE 表上,每個非叢集索引都需同步維護:行移動觸發所有非叢集索引的指標更新,甚至引發索引頁分裂。DELETE 還需整理頁面空間。慎重建立,定期評估移除低效索引。
索引鍵 + 包含列 涵蓋查詢所需所有欄位,優化器直接從索引葉層返回結果,無需回表(Key Lookup)。
CREATE INDEX IX_Orders_Cover ON Orders (CustomerID) INCLUDE (OrderDate, TotalAmount, Status);
權衡: 索引體積增大、UPDATE 觸發包含列修改時開銷大。僅對高頻關鍵查詢使用。
| 索引策略 | 查詢 1(日期範圍+餘額) | 查詢 2(日期+繳費月份聚合) |
|---|---|---|
| 無索引 | 1分15秒 | 1分07秒 |
op_date 非叢集 | 57秒 | 57秒 |
op_date 叢集 | <1秒 | 52秒 |
pay_month, op_date, surplus 複合非叢集 | 34秒 | <1秒 |
op_date, pay_month, surplus 複合非叢集 | <1秒 | <1秒 |
結論: 索引欄位順序決定成敗——WHERE 等值條件列優先,範圍列次之,包含列最後。
UNIQUE,優化器可生成更優計畫。FILLFACTOR(如 80%)減少頁分裂;唯讀表設 100%。INT 優於 BIGINT、CHAR),整數鍵值存取最快。DBCC CHECKDB 報索引錯誤注意: 重建叢集索引會級聯重建所有非叢集索引。
優化器依賴統計資訊(分布直方圖+密度向量)判斷索引效用。統計資訊不自動即時更新,以下情況必須手動 UPDATE STATISTICS:
TRUNCATE TABLE 後重新載入VARCHAR(MAX)、XML)直接做鍵值將表置於一個檔案群組,非叢集索引置於另一群組,且兩群組映射到不同物理磁碟/陣列,可並行化 I/O,顯著改善讀寫吞吐。