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

SQL 索引全攻略：原理、類型、建立、維護與實戰優化

編輯：關於SqlServer

一、索引概述

索引是對資料表中一或多欄位值進行排序的資料結構（通常為 B+ 樹），提供指標指向實際資料行。類似書本目錄：查目錄找頁碼，再翻到該頁。正確使用索引可將查詢效能提升數十至數百倍。

二、索引兩大類型

特性	叢集索引	非叢集索引
資料儲存順序	資料頁按索引鍵值物理排序儲存	資料頁順序不變，索引另建結構
每表數量	僅能有一個	可建多個（SQL Server 上限 999）
葉層內容	資料頁本身	索引鍵值 + 行定位器（RID 或叢集鍵）
適用場景	範圍查詢、排序、頻繁存取的主鍵	精確查詢、覆蓋索引、高基數欄位
插入效能影響	較大（需維持物理順序，易產生頁分裂）	較小

關鍵概念：行定位器

堆疊表：行定位器 = RID（檔案號:頁號:槽號）。
有叢集索引表：行定位器 = 叢集索引鍵值（書籤查找 / Key Lookup）。

三、特殊索引類型

唯一索引：確保索引鍵值不重複，兼具索引與約束功能。
複合/覆蓋索引：包含多欄位；若查詢所需欄位全在索引中，可避免回表（Key Lookup），效能極佳。
篩選索引 (Filtered Index, SQL Server 2008+)：CREATE INDEX ... WHERE status = 1，針對熱門資料子集，體積小、維護快。
Columnstore 索引 (SQL Server 2012+)：列式儲存，專為 OLAP/大量掃描聚合查詢設計，壓縮率高 10x+。

四、索引建立語法


-- CREATE INDEX
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX idx_name
ON table_name (col1 [ASC|DESC], col2 [ASC|DESC], ...)
[INCLUDE (col3, col4, ...)]  -- 覆蓋索引包含欄位
[WHERE filter_predicate];    -- 篩選索引

-- CREATE TABLE 時指定
CREATE TABLE t (
    id INT PRIMARY KEY CLUSTERED,      -- 主鍵預設叢集
    code VARCHAR(20) UNIQUE NONCLUSTERED,
    status TINYINT,
    INDEX idx_status (status)          -- SQL Server 2016+ 簡潔語法
);

填充因子

FILLFACTOR = 80 保留 20% 頁面空間，減少頁分裂。適用於頻繁 INSERT/UPDATE 的叢集索引。預設 0（=100，填滿）。

五、索引維護

命令	用途	特性
`DBCC DBREINDEX`	重建所有/指定索引	離線、鎖表、速度快、記錄大、需額外磁碟空間
`DBCC INDEXDEFRAG` / `ALTER INDEX ... REORGANIZE`	整理碎片（葉層重排序）	線上、可隨時中斷、記錄小、速度慢、不更新統計資料
`ALTER INDEX ... REBUILD`	重建（可 ONLINE=ON，企業版）	企業版支援線上重建，標準版離線
`UPDATE STATISTICS`	更新統計資料	查詢優化器依賴統計資料制定計畫

碎片判斷標準


SELECT
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10;

< 5%：無需處理
5%–30%：REORGANIZE
> 30%：REBUILD

六、查看索引與執行計畫

sp_helpindex 'table_name' / sp_help 'table_name'
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('table_name')
執行計畫關鍵運算子：Index Seek（佳）、Index Scan（全索引掃描）、Key Lookup（回表）、RID Lookup（堆疊表回表）、Table Scan（全表掃描，避免）。
強制使用索引：SELECT * FROM t WITH (INDEX(idx_name)) WHERE ...（謹慎使用）。

七、實戰索引設計原則（轉貼精華）

避免表掃描：檢查 WHERE/JOIN/ORDER BY/GROUP BY 欄位，建立對應索引。
窄索引優於寬索引：1-2 欄位索引每頁容納更多行，樹高更低，I/O 更少。
複合索引遵循「最左前綴」：INDEX (a,b,c) 可支援 WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3，但不支援 WHERE b=2 或 WHERE c=3。
高選擇性優先：COUNT(DISTINCT col)/COUNT(*) > 0.1 適合建索引；性別/狀態等低基數欄位不適合單獨建索引（可做複合索引次序或篩選索引）。
覆蓋索引消除 Key Lookup：INCLUDE (col3, col4) 將非鍵欄位放入葉層，查詢 SELECT col1, col3 FROM t WHERE col1=1 直接在索引完成。
避免重複/疊加索引：INDEX(a) + INDEX(a,b) 前者多餘；外鍵欄位通常需索引支援 JOIN/刪除檢查。
叢集索引選擇原則：窄、唯一、常用於範圍查詢/排序、盡量避免隨機 GUID（導致頁分裂）。Identity INT/BIGINT 為首選。
定期維護統計資料與碎片：AUTO_UPDATE_STATISTICS ON、排程 REBUILD/REORGANIZE。

八、常見反模式

反模式	後果	改善
所有欄位都建索引	寫入極慢、空間浪費、優化器混淆	僅針對高頻查詢欄位建立
叢集索引用 GUID	嚴重頁分裂、碎片率高	用 INT IDENTITY 或有序鍵
WHERE 對索引欄位用函數	索引失效（SARGable 違反）	改寫為範圍查詢
複合索引欄位順序錯誤	無法命中索引	遵循最左前綴、高選擇性在前
從不維護統計資料	優化器誤判、選錯計畫	開啟自動更新、定期手動更新