SQL Server 索引優化實戰：索引效能與選擇性原則

為什麼不在每個欄位都建索引？

索引加速讀取，但每次 INSERT/UPDATE/DELETE 都需同步維護所有索引，增加寫入負擔。頁面已滿時插入還會觸發頁面分裂產生碎片。唯有「查詢效能優於寫入效能」的欄位才值得建索引。

優先考慮建索引的欄位

1. 主鍵 (PK 自動建立叢集/非叢集索引)。
2. 外鍵 (JOIN 條件、刪除父表時掃描子表)。
3. WHERE / JOIN / ORDER BY / GROUP BY 高頻引用欄位 (模糊查詢 LIKE '%xxx%' 除外)。

不建議建索引的欄位

1. 極少或從未在查詢條件中出現的欄位。
2. 低選擇性欄位：僅有少數幾個值 (如性別 M/F、狀態 0/1/2) — 掃描成本低於索引查找 + 書籤查找。
3. 小表 (列數極少) — 全表掃描比索引查找更快。

SQL Server 自動統計資料與索引選擇

建立索引後，SQL Server 會建立統計資料。查詢優化器根據預估回傳列數佔表總列數比例決定是否使用索引：

• 回傳比例高 (如 > 30%) → 傾向全表掃描 (Clustered Index Scan)。
• 回傳比例低 → 使用索引查找 + 書籤查找 (Index Seek + Key Lookup)。

實測驗證

-- 建立測試資料：2000 列，1999 列 ForumID=2，1 列 ForumID=3
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Posts WHERE ForumID = 2;  -- 未使用索引 (Clustered Index Scan)
SELECT * FROM Posts WHERE ForumID = 3;  -- 使用索引 (Index Seek)

大量資料異動策略

大批量 INSERT/UPDATE/DELETE 前：刪除索引 → 異動資料 → 重建索引，避免逐筆維護索引開銷。

複合索引 (Composite Index) 設計鐵律

1. 最左前綴原則：WHERE 條件必須包含索引第一欄才會使用該複合索引。
   例：INDEX (ThreadID, ForumID) → WHERE ThreadID=10 使用索引；WHERE ForumID=2 不使用。
2. 索引鍵長度 ≤ 8 bytes 為宜 (int=4B, smallint=2B, bigint=8B)，過長增加 I/O 且降低頁面密度。
3. 第一欄選擇性最高 (最唯一)，依序遞減。
   例：A 重複 30%、B 重複 10%、C 重複 25% → 索引順序應為 B, C, A。

總結檢核表