索引是資料庫性能的靈魂。MSSQL 提供 **聚集索引** 與 **非聚集索引** 兩種結構,理解其底層原理與適用場景,是寫出高性能查詢的前提。
| 場景 | 聚集索引 | 非聚集索引 |
|---|---|---|
| 列經常 GROUP BY / ORDER BY | ✅ 適合 | ✅ 適合 |
| 範圍查詢 (BETWEEN, >, <, >=, <=) | ✅ 極佳 | ❌ 不適合 (大量書籤查找) |
| 極少唯一值 (如性別、狀態) | ❌ 違反選擇性原則 | ❌ 同理 |
| 少量不同值 (如地區、部門) | ✅ 適合 | ❌ 不適合 |
| 大量不同值 (如 ID、流水號) | ❌ 導致頁分裂/碎片 | ✅ 適合 |
| 頻繁 UPDATE 的列 | ❌ 導致行移動/頁分裂 | ✅ 適合 (僅更新索引葉子) |
| 外鍵列 | ✅ 適合 (連接排序) | ✅ 適合 |
| 主鍵列 | ⚠️ 默認但非最佳 | ✅ 適合 (常作非聚集主鍵) |
**錯!** 自增 ID 做聚集索引雖無頁分裂,但**業務查幾乎不按 ID 範圍查**。真實系統核心查詢多按「日期」、「用戶」篩選。將聚集索引建在高選擇性、範圍查詢頻繁的業務列 (如 CreateTime),性能提升數量級。
實測數據 (1000萬行,近 3 月 25萬行):
| 方案 | 查詢語句 | 耗時 |
|---|---|---|
| 僅主鍵聚集,全表掃描 | SELECT ... FROM t | 128 秒 |
| 主鍵聚集 + 日期非聚集 | WHERE CreateTime > DATEADD(day,-90,GETDATE()) | 54 秒 |
| 日期建聚集索引 | WHERE CreateTime > DATEADD(day,-90,GETDATE()) | 2.4 秒 |
同一列建非聚集 vs 聚集,速度差 22 倍 (上表方案 2 vs 3)、**索引類型選擇比是否建索引更關鍵**。
複合聚集索引 **順序極其重要**:最左前綴原則。若聚集鍵為 (CreateTime, UserName):
WHERE CreateTime > ... → ✅ 用到索引 (2.5 秒)WHERE CreateTime > ... AND UserName = '辦公室' → ✅ 用到索引 (2.5 秒)WHERE UserName = '辦公室' → ❌ **全表掃描** (無最左列)若頻繁單獨按 UserName 查,需額外建 UserName 非聚集索引。
CreateTime、OrderDate、TenantId (多租戶))。PRIMARY KEY NONCLUSTERED (Id)。INCLUDE 常查詢列,避免書籤查找。ALTER INDEX ... REORGANIZE/REBUILD、UPDATE STATISTICS。sys.dm_db_missing_index_* DMV。 索引設計是權衡讀寫、存儲、維護成本的藝術,**以核心業務查詢模式為導向**,而非教條遵循默認設置。