您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 索引優化實戰：從欄位選擇、叢集策略到碎片整理

編輯：關於SqlServer

索引如書目錄，讓查詢避開全表掃描，直接定位資料頁。但索引非越多越好——每筆 DML 都需同步維護索引結構。本文系統整理索引優化五大決策面向。

1. 對什麼欄位建索引？

核心原則：依據高頻查詢的 WHERE、JOIN、ORDER BY、GROUP BY 欄位
範例：論壇回覆表，高頻查詢「指定主題的分頁回覆」 → ParentPostID 必須建索引
工具輔助：sys.dm_db_missing_index_details、查詢存儲、Execution Plan 建議索引 (綠色提示)

2. 叢集索引一定要建在主鍵上嗎？

場景	建議
主鍵為 IDENTITY 且為熱點查詢鍵	預設叢集於 PK (最佳，插入有序、無碎片)
表分區鍵 ≠ PK	叢集索引必須包含分區鍵，改建於分區欄位
查詢多為範圍掃描非 PK	考慮將叢集移至範圍查詢欄位 (如 `CreateDate`)
PK 為 GUID / 可變長字串	勿叢集於 PK，改用 `NEWSEQUENTIALID()` 或 surrogate int PK

叢集鍵三原則：窄 (Narrow)、唯一、不變。叢集鍵變更會導致資料列物理搬移、頁面分裂、所有非叢集索引鍵值同步更新 — 代價極大。

3. 組合索引欄位順序決定成敗

組合索引以 B-Tree 結構儲存：先按第一欄排序，葉層再按第二欄排序…

-- 實驗：10 萬筆測試資料
CREATE NONCLUSTERED INDEX IX_Name ON dbo.testIndexOrder (FirstName, LastName);

-- 查詢 1：WHERE LastName = '...'  → 索引掃描 (無法 Seek)
-- 查詢 2：WHERE FirstName = '...' → 索引 Seek (極快)

決策規則：最高選擇性 (Selectivity) + 最常用於等值篩選的欄位放最左；若查詢常同時帶入 A、B 且 A 選擇性高 → (A, B)；若 B 常單獨查詢 → 需額外單欄索引或調整順序。

4. 索引數量的取捨

每個索引增加 INSERT/UPDATE/DELETE 開銷
OLTP 表建議 ≤ 5 個非叢集索引；DSS/資料倉庫可放寬
定期用 sys.dm_db_index_usage_stats 找出 user_seeks = user_scans = user_lookups = 0 的廢棄索引並刪除

5. 碎片整理：何時、如何重建

-- 檢視碎片
DBCC SHOWCONTIG('testIndexOrder');  -- 舊版
-- 新版推薦
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('testIndexOrder'), NULL, NULL, 'DETAILED');

-- 重建所有索引 (離線，鎖表)
DBCC DBREINDEX('testIndexOrder', '', 80);  -- fillfactor 80%

-- 線上重建 (Enterprise Edition)
ALTER INDEX ALL ON testIndexOrder REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);

-- 輕量整理 (僅葉層重排，線上)
ALTER INDEX ALL ON testIndexOrder REORGANIZE;

Fillfactor 策略：唯讀/極少寫入表 → 100 (或 0)；高頻插入表 → 70~80，預留空間減少頁面分裂。

索引優化是持續迭代過程：監控 → 分析缺失/冗余 → 調整 → 驗證。結合 Query Store、Extended Events、定期碎片整理作業，讓索引策略隨業務演進而優化。