萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 索引結構與實戰應用 (一):聚集 vs 非聚集、選型誤區與 1000 萬筆實測數據

一、深入淺出理解索引結構

聚集索引 = 字典正文

漢語字典正文按拼音排序,查「安」自然翻前頁,查「張」翻後頁。**正文本身就是目錄**,找到即到達資料,無需二次查找。
→ 每張表只能有一個聚集索引,因為物理排序只能有一種順序。

非聚集索引 = 部首檢字表

不認發音時,查部首 → 檢字表得頁碼 → 翻頁。
檢字表順序 (馳→張→弩) ≠ 正文順序,**目錄純粹是目錄,需二次定位**。
→ 非聚集索引儲存 (鍵值 + 書籤/叢集鍵),查詢需 Key Lookup 回表。

二、何時用聚集 vs 非聚集 — 決策表 (核心參考)

動作描述聚集索引非聚集索引
欄位常被 GROUP BY/ORDER BY✅ 應✅ 應
回傳範圍資料✅ 應❌ 不應
極少不同值 (如性別)❌ 不應❌ 不應
少數不同值 (如部門)✅ 應❌ 不應
大量不同值 (如身分證號)❌ 不應✅ 應
頻繁更新的欄位❌ 不應✅ 應
外鍵欄位✅ 應✅ 應
主鍵欄位✅ 應✅ 應
頻繁修改索引欄位❌ 不應✅ 應

三、實戰誤區與 1000 萬筆實測

誤區 1:主鍵預設叢集索引 = 資源浪費

IDENTITY 自增 ID 唯一但業務上從不查詢,佔用唯一聚集索引位置。
辦公自動化系統核心查詢永遠圍繞 日期 與 使用者。

誤區 2:建立索引就能提速

同一欄位 fariqi (發文日期),非聚集 vs 聚集 速度差 22 倍:

場景索引配置查詢語句耗時
1PK 聚集,無其他索引全表掃描128,470 ms (128 秒)
2PK 聚集 + fariqi 非聚集WHERE fariqi > DATEADD(day,-90,GETDATE())53,763 ms (54 秒)
3fariqi 聚集WHERE fariqi > DATEADD(day,-90,GETDATE())2,423 ms (2 秒)

結果集皆 25 萬筆。聚集索引將掃描範圍從 1000 萬縮減至 25 萬,**物理順序 = 邏輯順序** 才是王道。

誤區 3:複合聚集索引欄位順序不重要

複合聚集索引 (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 測速

copyright © 萬盛學電腦網 all rights reserved