許多開發者誤以為主鍵必然就是聚集索引,實際上這是兩個獨立的概念。主鍵是邏輯層面的約束,用於唯一標識行;聚集索引則是物理存儲層面的排序方式。一張表僅能擁有一個聚集索引,數據頁在磁盤上會按照聚集索引鍵值的順序物理排列。預設情況下,SQL Server 會將主鍵建立為聚集索引,但我們可以通過 PRIMARY KEY NONCLUSTERED 語法將主鍵定義為非聚集,並將聚集索引建立在其他更適合範圍查詢的列上(如建立日期)。
假設有三個列 c1, c2, c3,我們對比兩種建索引策略:
(c1, c2, c3),列順序固定。c1、c2、c3。| 查詢條件 | 勝出策略 | 核心原因 |
|---|---|---|
| WHERE c1 = ? | 策略二 | 單列索引 c1 直擊目標,複合索引雖可用但樹層級較深。 |
| WHERE c2 = ? | 策略二 | 複合索引首列非 c2,導致索引掃描甚至失效;單列索引 c2 精準定位。 |
| WHERE c1 = ? AND c2 = ? | 視情況 | 複合索引可精確 Seek;多單列索引需 Index Intersection(交集運算),開銷較大。 |
| WHERE c1 = ? AND c2 = ? AND c3 = ? | 策略一 | 複合索引完美覆蓋,單次 Seek 即達葉級;三單列索引需多次交集運算。 |
| WHERE c2 = ? AND c3 = ? AND c1 = ? | 策略二 | 雖列齊全但順序不符複合索引首列原則,優化器傾向放棄複合索引轉而掃描或用單列索引交集。 |
關鍵原則:複合索引遵循「最左前綴」原則,查詢必須包含索引首列才能高效利用;單列索引靈活但維護成本高、佔用空間大,且無法服務多列聯合查詢的排序與分組需求。
當查詢所需的所有列(SELECT 列表、JOIN 條件、WHERE 條件)均包含在索引鍵或包含列中時,查詢引擎無需回表查找聚集索引鍵,直接從非聚集索引葉級頁面獲取數據,這稱為「覆蓋查詢」。合理設計 INCLUDE 列可大幅減少 I/O,是查詢調優的高階手段。
每一個索引都是一棵獨立的 B+ 樹。INSERT、UPDATE、DELETE 操作不僅要修改數據頁,還必須同步維護所有相關索引。過多單列索引會導致寫入延遲飆升、鎖競爭加劇、事務日志膨脹。將多個單列索引合併為一個精心設計的複合索引,往往能在讀寫之間取得最佳平衡。權衡之道在於:以查詢負載為導向,以寫入頻率為約束,定期審視 sys.dm_db_index_usage_stats 淘汰低效索引。