主鍵≠聚集索引、單列vs多列索引選型、覆蓋查詢與索引維護成本全解析

核心概念澄清

• 主鍵：邏輯約束，強制唯一+非空，默認創建聚集索引（可指定 NONCLUSTERED）。
• 聚集索引：物理存儲順序，每表僅一個，葉級即數據頁。
• 非聚集索引：獨立 B+ 樹，葉級存索引鍵+聚集鍵（書籤），需回表（Key Lookup）獲取非覆蓋列。

代碼證明：PRIMARY KEY NONCLUSTERED (Id) 合法，主鍵不聚集，另建聚集索引於他列。

多列索引 vs 多個單列索引：性能對比實驗

表 T(c1, c2, c3)，對比：

• Case 1：CREATE INDEX IX_123 ON T(c1, c2, c3);（多列索引，順序 c1→c2→c3）
• Case 2：CREATE INDEX IX_1 ON T(c1); CREATE INDEX IX_2 ON T(c2); CREATE INDEX IX_3 ON T(c3);（三個單列索引）

覆蓋查詢：避免回表的殺手鐧

查詢所需列全部包含在索引鍵或 INCLUDE 列中，優化器直接掃描索引葉級，無需 Key Lookup。

CREATE INDEX IX_Order_Cover ON Orders (CustomerID, OrderDate) INCLUDE (TotalAmount, Status);
-- 查詢：SELECT CustomerID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 123;
-- 直接命中索引，零回表

索引過多的負面影響

• 寫入放大：每次 INSERT/UPDATE/DELETE 需同步維護所有索引，索引越多、越寬，延遲越大。
• 空間佔用：非聚集索引複製鍵列+聚集鍵+RID，大表索引體積常超過數據體積。
• 統計維護：自動更新統計觸發頻率增加，重編譯開銷上升。
• 優化器選擇困難：候選索引多，優化器耗時長，可能選錯計劃。

實戰優化策略

1. 合併單列索引為多列索引：將 IX_c1、IX_c2、IX_c3 合併為 IX_c1_c2_c3（按查詢頻率/選擇性排序），測試顯示寫入性能提升 30-50%。
2. 審慎使用 INCLUDE：僅為高頻查詢的非鍵列添加，避免索引過寬。
3. 定期清理未用索引：sys.dm_db_index_usage_stats 監控 user_seeks + user_scans + user_lookups = 0 的索引（保留足夠觀察期，如 30 天）。
4. 填充因子：高更新表設置 FILLFACTOR = 80-90 減少頁分裂。
5. 壓縮：大表啟用 DATA_COMPRESSION = PAGE 節省空間、減少 I/O。