索引是雙面刃:用得好是「加速器」,用不好是「累贅」。本文提煉六大鐵律,助你在「查詢加速」與「維護成本」間找到平衡點,避開索引濫建與缺失的雙重陷阱。
鐵律一:天下沒有免費午餐——索引有明確成本
- 寫入代價:每次 `INSERT/UPDATE/DELETE` 皆需同步維護所有相關索引 (B-Tree 分頁分裂、合併、統計更新),寫入量大時顯著拖慢交易。
- 空間佔用:非叢集索引 = 索引鍵值 + 書籤 (RID 或叢集鍵) + 結構開銷。寬索引、含 `INCLUDE` 欄位更佔空間。
- 維護開銷:重組/重建索引、更新統計資訊佔用 CPU、I/O、鎖資源、記錄檔空間。
- 決策準則:預估「查詢加速收益」>「寫入/空間/維護成本」才建立。定期用 `sys.dm_db_index_usage_stats` 掃描 `user_seeks + user_scans + user_lookups = 0` 且 `user_updates > 0` 的廢棄索引並刪除。
鐵律二:低選擇性/低頻查詢欄位 —— 不建索引
- 低選擇性:重複值極多 (如 `Gender` M/F、`Status` 0/1/2、`IsDeleted` 0/1)。優化器發現掃描索引 + 書籤查找成本 > 全表掃描,會直接忽略索引走 Clustered Index Scan/Table Scan。
- 低頻查詢:極少出現在 `WHERE`/`JOIN`/`ORDER BY`/`GROUP BY` 中的欄位 (如 `IDNumber` 只供人工查核)。建立索引純屬浪費。
- 例外:極少數「覆蓋索引」場景——查詢僅需該低選擇性欄位 + 叢集鍵,且極少回表,可考慮 `INCLUDE` 但極少見。
鐵律三:高頻範圍查詢欄位 —— 優先建索引
- 範圍查詢 (`BETWEEN`、`>`、`<`、`>=`、`<=`、`LIKE 'prefix%'`)、排序 (`ORDER BY`)、分組 (`GROUP BY`)、連接 (`JOIN ON`)。
- 索引天然有序,範圍掃描僅需定位起點 + 向前/向後掃描葉層,避免全表掃描 + 排序 (Sort 運算子極耗記憶體/TempDB)。
- 典型欄位:`CreateDate`、`OrderDate`、`LogTime`、`Price`、`Score`。
- 加分技巧:搭配 `TOP (N)` 限制回傳行數,優化器可啟用 `TOP` 運算子提前終止掃描,大幅降低 I/O。
- 例外:資料量極小 (如 < 1000 行) 或範圍查詢極罕見,成本不回本。
鐵律四:主鍵與外鍵 —— 必須建索引 (自動/手動)
- 主鍵 (PK):SQL Server **自動建立唯一叢集索引** (除非指定 `NONCLUSTERED`)。PK 同時保證實體唯一性與邏輯識別,加速單行定位 (`Seek`)。
- 外鍵 (FK):**不自動建索引**!但極強烈建議手動建立非叢集索引。
- 原因 1:`JOIN` 連接加速 (FK 欄位常為 `ON` 條件)。
- 原因 2:父表 `DELETE/UPDATE` 時,子表需快速定位受影響行驗證參考完整性,無索引會鎖定全表掃描,造成嚴重阻塞。
- 唯一性實現:`PRIMARY KEY` (隱含 `NOT NULL` + `UNIQUE` + 索引) vs `UNIQUE CONSTRAINT` / `UNIQUE INDEX` (允許單一 NULL,語義上區分「識別鍵」與「業務唯一屬性」)。優化器對兩者處理無差異。
鐵律五:大型物件型別 (LOB) —— 謹慎建索引,優先考慮全文檢索/檔案流
- 型別:`TEXT`/`NTEXT`/`IMAGE` (舊)、`VARCHAR(MAX)`/`NVARCHAR(MAX)`/`VARBINARY(MAX)`、`XML`、`FILESTREAM`。
- 特點:長度不可控、行外儲存 (LOB 分配單元)、無法直接建立標準 B-Tree 索引。
- 標準索引無效:無法對 `VARCHAR(MAX)` 建 `INDEX` (除非 `CHECKSUM` 計算列或全文索引)。
- 替代方案:
- 全文檢索:`CREATE FULLTEXT INDEX` 針對文字內容搜尋。
- 計算列 + 索引:`ALTER TABLE T ADD Chk AS CHECKSUM(LongCol) PERSISTED; CREATE INDEX IX_Chk ON T(Chk);` 精確匹配用 (有碰撞風險)。
- 檔案表/FILESTREAM:超大二進位物件移至檔案系統,DB 只存指標。
- 業務妥協:若必須精確比對長文本 (如產品規格 5000 字),評估「犧牲寫入效能換查詢速度」是否值得,極少數情況下建立全文索引或計算列索引。
鐵律六:索引與 WHERE 子句融合——覆蓋索引與索引鍵順序設計
- 分析高頻 WHERE 組合:識別核心查詢模式 (如 `WHERE CustomerID = @Cust AND OrderDate BETWEEN @Start AND @End`)。
- 複合索引鍵順序原則:等值比較欄位在前 (CustomerID),範圍/排序欄位在後 (OrderDate)。優化器可對等值欄位 `Seek` 定位,再對範圍欄位有序掃描。
- 覆蓋索引:`INCLUDE` 查詢所需的非鍵欄位 (`SELECT Col1, Col2 ...`),避免「書籤查找」回叢集索引/堆表,實現純索引掃描 (`Index Only Scan`),效能提升數量級。
- 避免過度索引:單表索引建議 ≤ 5-8 個 (視寫入頻率)。每個新索引都要經過「讀寫比測試」。
附錄:索引維護自動化建議
-- 定期 (每週/每日) 執行索引優化 (Ola Hallengren 腳本或 Maintenance Plan)
-- 1. 統計資訊更新 (高頻,低成本)
EXEC sp_updatestats; -- 或針對特定表 UPDATE STATISTICS ... WITH FULLSCAN
-- 2. 索引碎片整理 (低頻,高成本)
-- avg_fragmentation_in_percent > 30% → REBUILD (ONLINE=ON)
-- 5% ~ 30% → REORGANIZE
-- < 5% → 無操作
結語
索引設計非一勞永逸,需隨業務查詢模式演進而動態調整。遵循「六大鐵律」建立基線,配合 `Query Store`、`sys.dm_db_index_usage_stats`、執行計畫持續監控,定期清理廢棄索引、補齊遺漏索引、調整鍵順序與 `INCLUDE` 欄位,方能長期維持資料庫高效能運轉。