SQL Server 索引效能考量：建立策略、複合索引順序、填充因子與統計資料

為什麼不給每個欄位建索引？

索引是雙面刃：

• ✅ 加速查詢（Seek、Range Scan、Covering）
• ❌ 拖慢寫入（INSERT/UPDATE/DELETE 需同步維護所有索引）
• ❌ 佔用空間（非叢集索引含基表鍵值）
• ❌ 頁分裂產生碎片（隨機鍵值插入）

原則：查詢效能收益 > 維護成本 時才建索引。

優先考慮建索引的欄位

1. 主鍵：實體識別、叢集索引預設
2. 外鍵：JOIN 條件、RI 檢查加速
3. 高頻 WHERE/JOIN/ORDER BY/GROUP BY 欄位

不建議建索引的欄位

1. 極少查詢引用
2. 低基數（< 3 種值，如性別、布林） → 全表掃描更快
3. 小表（< 100 頁） → 掃描成本極低
4. 頻繁大幅更新的欄位

統計資料與基數估算

SQL Server 自動建立/更新統計資料（直方圖 + 密度向量）。優化器依統計資料估算列數：

• 估算回傳 > 30% 表大小 → 傾向 Clustered Index Scan
• 估算回傳 < 1% → 傾向 Nonclustered Index Seek + Key Lookup

-- 查看統計資料
DBCC SHOW_STATISTICS ('TableName', 'IndexName')

-- 手動更新
UPDATE STATISTICS TableName IndexName WITH FULLSCAN

複合索引設計鐵律

1. 最左前綴原則

CREATE INDEX IX_Orders_CustDate ON Orders (CustomerID, OrderDate)

-- 可用：WHERE CustomerID = 1
-- 可用：WHERE CustomerID = 1 AND OrderDate > '2024-01-01'
-- 不用：WHERE OrderDate > '2024-01-01'  -- 缺少前導欄位

2. 選擇性高的欄位前置

基數：A 欄 30% 重複、B 欄 10% 重複、C 欄 25% 重複 → 順序 B, C, A。
例外：查詢模式固定為 WHERE A = ? AND B = ? → 依查詢順序調整。

3. 索引鍵長度 ≤ 900 位元組 (16 欄位上限)

• INT 4 位元組、BIGINT 8、DATETIME 8、CHAR(n) n、VARCHAR(n) n+2
• 過長鍵值 → 考慮雜湊欄位、計算欄位、或只索引前綴

4. INCLUDE 非鍵欄位實現覆蓋索引

CREATE INDEX IX_Orders_Cover ON Orders (CustomerID, OrderDate) 
INCLUDE (Amount, Status)
-- 查詢 SELECT Amount, Status FROM Orders WHERE CustomerID=1 AND OrderDate>...
-- 純索引掃描，無 Key Lookup

填充因子：留空間減少頁分裂

CREATE INDEX IX_Orders_Random ON Orders (RandomGUID) WITH (FILLFACTOR = 80)
ALTER INDEX IX_Orders_Random ON Orders REBUILD WITH (FILLFACTOR = 80)

索引維護自動化

-- Ola Hallengren 索引優化腳本核心邏輯
-- 頁面碎片 > 30% → REBUILD (ONLINE=ON)
-- 頁面碎片 5-30% → REORGANIZE
-- 頁面計數 < 1000 → 跳過

-- 統計資料更新：修改列數 > 20% + 500 → UPDATE STATISTICS WITH FULLSCAN

監控未使用/重複索引

-- 未使用索引 (自上次重啟)
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE o.type = 'U' AND i.type_desc = 'NONCLUSTERED'
  AND (s.object_id IS NULL OR (s.user_seeks=0 AND s.user_scans=0 AND s.user_lookups=0))

-- 重複索引 (相同鍵欄位、相同包含欄位)
-- 需人工審視 sys.index_columns 對比

實務建議清單

1. 工作負載分析先行：sys.dm_exec_query_stats、Query Store、Extended Events
2. 避免過度索引：每表 5-10 個非叢集索引為宜
3. 叢集索引慎選：窄、唯一、遞增、靜態（Identity 最佳）
4. 定期重建/重組 + 更新統計資料
5. 監控 Page Splits/sec、Index Fragmentation、Missing Index DMVs