漢語字典正文按拼音排序,查「安」自然翻前頁,查「張」翻後頁。**正文本身就是目錄**,找到即到達資料,無需二次查找。
→ 每張表只能有一個聚集索引,因為物理排序只能有一種順序。
不認發音時,查部首 → 檢字表得頁碼 → 翻頁。
檢字表順序 (馳→張→弩) ≠ 正文順序,**目錄純粹是目錄,需二次定位**。
→ 非聚集索引儲存 (鍵值 + 書籤/叢集鍵),查詢需 Key Lookup 回表。
| 動作描述 | 聚集索引 | 非聚集索引 |
|---|---|---|
| 欄位常被 GROUP BY/ORDER BY | ✅ 應 | ✅ 應 |
| 回傳範圍資料 | ✅ 應 | ❌ 不應 |
| 極少不同值 (如性別) | ❌ 不應 | ❌ 不應 |
| 少數不同值 (如部門) | ✅ 應 | ❌ 不應 |
| 大量不同值 (如身分證號) | ❌ 不應 | ✅ 應 |
| 頻繁更新的欄位 | ❌ 不應 | ✅ 應 |
| 外鍵欄位 | ✅ 應 | ✅ 應 |
| 主鍵欄位 | ✅ 應 | ✅ 應 |
| 頻繁修改索引欄位 | ❌ 不應 | ✅ 應 |
IDENTITY 自增 ID 唯一但業務上從不查詢,佔用唯一聚集索引位置。
辦公自動化系統核心查詢永遠圍繞 日期 與 使用者。
同一欄位 fariqi (發文日期),非聚集 vs 聚集 速度差 22 倍:
| 場景 | 索引配置 | 查詢語句 | 耗時 |
|---|---|---|---|
| 1 | PK 聚集,無其他索引 | 全表掃描 | 128,470 ms (128 秒) |
| 2 | PK 聚集 + fariqi 非聚集 | WHERE fariqi > DATEADD(day,-90,GETDATE()) | 53,763 ms (54 秒) |
| 3 | fariqi 聚集 | WHERE fariqi > DATEADD(day,-90,GETDATE()) | 2,423 ms (2 秒) |
結果集皆 25 萬筆。聚集索引將掃描範圍從 1000 萬縮減至 25 萬,**物理順序 = 邏輯順序** 才是王道。
複合聚集索引 (fariqi, neibuyonghu),日期為前導欄:
| 查詢條件 | 耗時 | 說明 |
|---|---|---|
| WHERE fariqi > '2004-5-5' | 2,513 ms | 前導欄 Seek,極快 |
| WHERE fariqi > '2004-5-5' AND neibuyonghu = '辦公室' | 2,516 ms | 同樣快,第二欄為剩餘範圍過濾 |
| WHERE neibuyonghu = '辦公室' | 60,280 ms | 非前導欄 → 索引失效,全表掃描 |
結論:**複合索引前導欄必須出現在 WHERE/JOIN/ORDER BY 中**,否則索引廢棄。
DECLARE @d DATETIME; SET @d = GETDATE();
-- 待測查詢
SELECT gid, fariqi, neibuyonghu, title FROM Tgongwen WHERE fariqi > '2004-5-5';
SELECT [執行耗時(ms)] = DATEDIFF(ms, @d, GETDATE());
聚集索引選擇直接決定系統效能上限。**業務高頻範圍查詢欄位 (通常是日期) 應佔據唯一聚集索引位置**,主鍵改用非聚集唯一索引。複合索引嚴守前導欄原則。實測數據證明:正確聚集索引可帶來 **50x+ 效能提升**。
關鍵字: SQL Server 索引、聚集索引、非聚集索引、複合索引、前導欄、選擇性、主鍵、填充因子、1000萬筆實測、Key Lookup、DATEDIFF 測速