萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 索引管理六大鐵律:從成本認知到實戰選型

前言:索引是雙面刃,用對是良藥,用錯是毒藥

索引能加速查詢與 Join,但每個索引都有建立、維護、儲存成本。DBA 的核心任務是在「查詢加速」與「寫入/空間負擔」間找到平衡點。以下六大鐵律助你避開常見陷阱。

鐵律一:天下沒有免費午餐 — 索引成本必須量化

  • 寫入負擔:INSERT/UPDATE/DELETE 觸發 B-tree 重平衡、頁分裂,資料量越大代價越高。
  • 空間佔用:非叢集索引另存 B-tree,含鍵值+RID/叢集鍵,寬欄位索引尤為佔空間。
  • 統計資訊維護:自動/手動更新統計資訊亦消耗資源。
  • 決策原則:預估查詢加速收益 > 寫入/空間/維護成本,才建立索引。

鐵律二:低頻查詢欄位、低選擇性欄位 — 不建索引

  • 低頻查詢:如員工表 IdentityNo 唯一但從不查詢,建索引純增負擔。
  • 低選擇性:Gender (M/F)、DeptID (少數部門)、Status (幾個狀態) — 掃描比例超過 30% 時優化器傾向全表掃描,索引無效。

鐵律三:範圍查詢高頻欄位 — 必建索引,優先考慮叢集索引

  • 典型案例:OrderDate、TxnDate、LogTime。
  • 叢集索引鍵值連續儲存,範圍掃描只需定位起迄點,極度高效。
  • 搭配 TOP N 限制回傳筆數,進一步降低 I/O。
  • 例外:範圍查詢極低頻、表極小 → 成本大於收益。

鐵律四:PK/FK 欄位 — 強制建立索引

  • PK:預設建立叢集索引(可改非叢集),加速單筆定位,保證唯一性。
  • FK:Join 連接鍵,索引可避免 Hash Join/Loop Join 退化為 Table Scan,資料量大時效果指數級提升。
  • 唯一性約束:PRIMARY KEY 或 UNIQUE 均自動建立唯一索引,優化器不區分來源。

鐵律五:大型物件 (LOB) 型別 — 預設不建索引,極少數例外

  • TEXT/NTEXT/IMAGE (舊)、VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX) (新) — 長度不定、極長或空值多,索引效用極低。
  • 例外:ERP 產品規格欄位 (VARCHAR(5000)) 高頻篩選 → 僅能犧牲資源建立全文檢索或傳統索引(視版本支援)。

鐵律六:WHERE 子句高頻組合欄位 — 設計覆蓋/複合索引

  • 常見組合:CustomerID + OrderDate、ProductID + TxnDate。
  • 將高選擇性欄位置於複合索引前導欄,符合 SARG 形式,優化器可精準 Seek。
  • 覆蓋索引 (INCLUDE 非鍵欄位) 可避免 Key Lookup,實現純索引掃描。

實戰決策流程圖

查詢負載分析 (DMV/Profiler/Query Store)
    ↓
識別高頻/高耗查詢及其 WHERE/JOIN/ORDER BY 欄位
    ↓
評估欄位選擇性、資料型別、寫入頻率
    ↓
應用六大鐵律篩選候選索引
    ↓
測試環境驗證:執行計畫、邏輯讀、CPU、寫入延遲
    ↓
生產環境低峰期上線,持續監控

總結

索引管理不是一次性動作,而是持續的觀測-分析-調整循環。六大鐵律提供判斷框架,具體實施仍需結合實際負載特徵與業務優先順序。

關鍵字: SQL Server 索引、叢集索引、非叢集索引、覆蓋索引、選擇性、SARG、PK、FK、LOB、索引維護成本

copyright © 萬盛學電腦網 all rights reserved