索引是加速查詢的利器，也是寫入效能的負擔。本文提煉六大索引管理鐵律，涵蓋成本認知、欄位選擇、範圍查詢、主外鍵、特殊型別、WHERE 子句融合，並提供實務檢核表與維護腳本。
鐵律一：天下沒有免費午餐 — 索引有成本
- 寫入開銷： 每次 INSERT/UPDATE/DELETE 需同步維護所有相關索引 B+ 樹，寫入放大效應明顯。
- 儲存空間： 非叢集索引獨立儲存鍵值 + RID/叢集鍵，大表索引常超過資料本身。
- 維護負擔： 重建/重組索引、更新統計資訊佔用 CPU、I/O、鎖定資源。
- 優化器負擔： 索引過多導致計畫搜尋空間爆炸，編譯時間延長。
原則： 每個索引必須有明確「付費查詢」支撐，定期清理未被使用的索引 (sys.dm_db_index_usage_stats)。
鐵律二：低選擇性、低頻查詢欄位 — 不建索引
| 欄位類型 | 範例 | 理由 |
|---|
| 極低基數 | 性別 (M/F)、狀態碼 (0/1/2)、布林旗標 | 掃描比例超過 30% 時優化器傾向全表掃描，索引無效 |
| 極高重複值 | 公司代碼 (單一租戶)、預設分類 | 同低基數，區分度不足 |
| 極少用於 WHERE/JOIN/ORDER BY | 備註、描述、建立時間 (僅顯示不篩選) | 維護成本 > 查詢收益 |
例外： 覆蓋索引 INCLUDE 欄位可包含低選擇性欄位，避免 Key Lookup。
鐵律三：高頻範圍查詢欄位 — 必建索引
- 日期區間：
WHERE OrderDate BETWEEN @Start AND @End
- 數值區間：
WHERE Price BETWEEN 100 AND 500
- 字首比對：
WHERE Name LIKE 'Zhang%' (非前置萬用字元)
索引天然有序，範圍掃描只需定位起點、順序讀取葉節點，極度高效。搭配 TOP N 可進一步縮短回傳路徑。
鐵律四：主鍵與外鍵 — 必須建立索引
- 主鍵： 自動建立唯一叢集/非叢集索引，保證實體唯一與邏輯定位。
- 外鍵： SQL Server 不自動建立 FK 索引！無索引時，父表刪除/更新需掃描整張子表驗證参照完整性，產生嚴重鎖爭用與阻塞。
-- 必須手動為 FK 欄位建立索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Sales.Orders(CustomerID);
-- 若常用複合條件
CREATE NONCLUSTERED INDEX IX_Orders_Cust_Date ON Sales.Orders(CustomerID, OrderDate);
鐵律五：大型物件型別 (LOB) — 謹慎建立索引
| 型別 | 特性 | 建議 |
|---|
| TEXT / NTEXT / IMAGE | 已棄用，行外儲存，長度不定 | 不可建索引，遷移至 VARCHAR(MAX)/VARBINARY(MAX) |
| VARCHAR(MAX) / NVARCHAR(MAX) / VARBINARY(MAX) | 行外儲存，可能極大 | 一般不建索引；極少數全文搜尋需求用全文索引 |
| XML | 結構化大物件 | 使用 XML 索引 (主 XML 索引 + 次要索引) |
若業務極度依賴大欄位精確比對 (如合約全文比對)，考慮計算欄位存雜湊值 (CHECKSUM/HASHBYTES) 並建立索引。
鐵律六：索引與 WHERE 子句融為一體 — SARGable 設計
索引鍵順序應匹配查詢模式：
-- 查詢模式：WHERE CustomerID = @Cust AND OrderDate BETWEEN @S AND @E
-- 最佳索引：(CustomerID, OrderDate) — 等值在前，範圍在後
CREATE INDEX IX_Orders_Cust_Date ON Sales.Orders(CustomerID, OrderDate) INCLUDE (TotalAmount);
- 等值條件欄位置於索引鍵前段
- 範圍條件欄位置於等值之後
- SELECT/ORDER BY/GROUP BY 欄位納入
INCLUDE 實現覆蓋索引
- 避免在索引欄位上做運算/函數：
WHERE YEAR(OrderDate)=2024 → 改為範圍查詢
索引維護實務腳本
-- 1. 偵測未使用索引 (保留 30 天以上無 user_seeks/scans/lookups)
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.type_desc,
s.user_seeks, s.user_scans, s.user_lookups,
s.last_user_seek, s.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id=s.object_id AND i.index_id=s.index_id
WHERE i.type > 0 -- 排除堆疊
AND (s.user_seeks+s.user_scans+s.user_lookups) = 0
AND i.name IS NOT NULL;
-- 2. 碎片整理 (線上/離線視版本與版本)
-- 碎片 5-30% → REORGANIZE；>30% → REBUILD
-- 建議使用 Ola Hallengren 維護方案或 Maintenance Plan
索引設計檢核表
- 是否有查詢使用此欄位組合？(查詢計畫驗證)
- 欄位選擇性是否足夠？(密度 < 1% 為佳)
- 是否為 FK 欄位？(必建)
- 是否為 LOB 型別？(一般不建)
- 索引鍵順序是否匹配 WHERE 等值→範圍順序？
- 是否需 INCLUDE 覆蓋查詢欄位？
- 叢集索引是否已佔用最佳欄位？(避免 PK 自動佔用)
- 是否規劃定期重建/統計更新？
索引管理是持續迭代過程：監控 → 分析 → 調整 → 驗證。將六大鐵律內化為審查標準，能讓資料庫長期保持最佳效能狀態。
- 上一篇文章： SQL Server 進階查詢技巧：多表連接、子查詢、集合運算與 TOP 分頁完全指南
- 下一篇文章： SQL Server 內部架構深度解析：從 TDS 封包到儲存引擎的完整追蹤
Copyright ©
程式碼之家 All Rights Reserved
