叢集索引 vs 非叢集索引
| 特性 | 叢集索引 | 非叢集索引 |
|---|
| 資料儲存順序 | 資料列依索引鍵值物理排序 | 資料列順序不變，索引另存 B+ 樹 |
| 每張表數量 | 最多 1 個 | 最多 999 個 (SQL 2008+) |
| 查詢效能 | 範圍查詢極快（掃描連續頁） | 點查詢快，範圍查需書籤查找 |
| 寫入影響 | INSERT/UPDATE/DELETE 需維持物理順序，頁分裂成本高 | 僅更新索引樹，成本較低 |
| 適合欄位 | 窄、唯一、遞增、常用範圍查詢（如 Identity、日期） | 常用 WHERE/JOIN/ORDER BY 欄位、覆蓋索引 |
實測案例：62 萬列 record 表，不同索引策略下查詢耗時
-- 查詢 1：日期範圍 + 金額過濾
SELECT COUNT(*) FROM record
WHERE date > '19991201' AND date < '19991214' AND amount > 2000;
-- 查詢 2：日期分組彙總
SELECT date, SUM(amount) FROM record GROUP BY date;
-- 查詢 3：日期範圍 + 地點 IN 清單
SELECT COUNT(*) FROM record
WHERE date > '19990901' AND place IN ('BJ','SH');
| 索引策略 | 查詢 1 | 查詢 2 | 查詢 3 | 分析 |
|----------|--------|--------|--------|------|
| date 非叢集 | 25秒 | 55秒 | 27秒 | 非叢集索引下重複值分散，範圍查仍需掃描大量資料頁 |
| date 叢集 | 14秒 | 28秒 | 14秒 | 物理順序匹配範圍，僅掃描相關頁，分組彙總亦受益 |
| (place, date, amount) 組合 | 26秒 | 27秒 | <1秒 | **前導欄 place 未用於查詢 1/2，索引失效**；查詢 3 形成覆蓋索引極快 |
| (date, place, amount) 組合 | <1秒 | 11秒 | <1秒 | **前導欄 date 滿足所有查詢**，查詢 1/3 形成覆蓋索引，查詢 2 避免排序 |
組合索引設計鐵律
- 前導欄優先：WHERE/JOIN/ORDER BY 最常用的高選擇性欄位放最前。
- 覆蓋索引：查詢所需所有欄位皆包含在索引中（Key 或 INCLUDE），避免書籤查找。
- 等值在前，範圍在後：
WHERE a=1 AND b>2 AND c=3 → 索引 (a, c, b) 或 (a, b) INCLUDE(c)。
- INCLUDE 非鍵欄位：
CREATE INDEX IX_Order_CustDate ON Orders(CustomerID, OrderDate) INCLUDE (TotalAmount, Status); 不計入索引鍵長度限制（900 bytes），支援覆蓋查詢。
常見反模式與修正
| 反模式 | 症狀 | 修正 |
|---|
| 過度索引 | 寫入極慢、磁碟佔用暴增、計畫選擇錯誤 | 定期用 sys.dm_db_index_usage_stats 找出未使用索引並刪除 |
| 隱式轉換導致索引失效 | WHERE varchar_col = 123（數值隱式轉字串） | 參數型別與欄位一致，或顯式 CAST |
| 函數包裹欄位 | WHERE YEAR(OrderDate) = 2024 | 改用範圍：OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01' |
| 叢集索引用 GUID | 隨機插入導致嚴重頁分裂、碎片率高 | 用 NEWSEQUENTIALID() 或 Identity/日期作叢集鍵，GUID 只建非叢集 |
維護與監控
-- 碎片檢查
SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName,
avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 30 AND page_count > 1000;
-- 重建/重組
ALTER INDEX ALL ON dbo.BigTable REBUILD; -- 碎片 > 30%
ALTER INDEX IX_Name ON dbo.BigTable REORGANIZE; -- 碎片 5~30%
-- 更新統計資料
UPDATE STATISTICS dbo.BigTable WITH FULLSCAN;
