SQL Server 索引效能優化實戰：叢集、非叢集、覆蓋索引選擇與維護全攻略

前言

在聯機交易處理 (OLTP) 系統中，查詢速度決定系統成敗。索引是加速查詢最直接手段，但不當索引反拖累寫入效能。本文系統梳理索引類型選擇、設計技巧、維護策略與避坑指南。

一、叢集索引

將實體資料依指定欄位重新排序儲存，索引葉節點即資料頁。每表僅一個，建立需 120% 額外空間。

適用場景

1. 大多數表應有叢集索引或分區，降低熱點頁競爭（高併發環境尾頁鎖定嚴重影響吞吐）。
2. 範圍查詢（BETWEEN, >, <, ≥, ≤）、GROUP BY/ORDER BY 欄位：資料物理連續，找到首筆即可順序讀取，避免大範圍掃描。
3. Join 鍵值欄位。

避坑原則

• 勿建於單調遞增欄位 (如 IDENTITY)：頻繁插入導致尾頁熱點鎖定衝突。
• 勿包含頻繁修改欄位：值變更導致整列物理移動，產生分頁分裂與鎖定。
• 基於 WHERE 子句與 Join 類型選擇。

候選欄位

1. 主鍵欄位（WHERE 常用、插入隨機）。
2. 範圍存取欄位（如 pri_order BETWEEN 100 AND 200）。
3. GROUP BY/ORDER BY 常用欄位。
4. 極少修改欄位。
5. Join 條件欄位。

二、非叢集索引

不重組資料，葉節點儲存索引鍵值 + 書籤指向資料列（Heap 情況為 RID，叢集表為叢集鍵）。預設建立類型，無叢集索引表最多 250 個非叢集索引。

權衡考量

• 索引空間佔用。
• 欄位穩定性（頻繁更新代價高）。
• 索引鍵選擇性（掃描效果）。
• 重複值比例（高重複值選擇性低）。

適用場景

1. 常用於彙總函數（SUM, AVG 等）欄位。
2. 常用於 JOIN, ORDER BY, GROUP BY 欄位。
3. 查詢回傳資料量 < 20% 表總量（選擇性高）。

更新頻繁表上，非叢集索引維護成本 > 叢集索引 > 無索引。每列移動/刪除需同步更新所有非叢集索引書籤，甚至引發索引分頁分裂。

三、覆蓋索引

非叢集索引葉節點包含查詢所需所有欄位（INCLUDE 欄位或複合索引涵蓋），查詢無需回表（Key Lookup），速度可超越叢集索引。

代價：索引體積大、UPDATE 導致索引值變更維護成本高。僅對高頻、關鍵查詢建立；低頻查詢反降低整體效能。

四、索引選擇實戰技巧

測試環境：89 萬列住房公積金明細表，C/S 架構，客戶端 PII 350/64MB，伺服器 Alpha 1000A/128MB，Sybase 11.0.3（原理通用）。

結論：索引欄位順序決定成敗。查詢 1 需 op_date 先行（範圍條件），查詢 2 需 pay_month 先行。複合索引首欄必須為最高選擇性/最常用範圍條件欄位。

設計思路總結

1. 主鍵常用於 WHERE/Join，建叢集索引（插入隨機時）。
2. 大量重複值 + 範圍查詢/排序/分組 + 高頻存取 → 考慮叢集索引。
3. 多欄位常同時存取且有重複值 → 複合索引覆蓋查詢，最高頻欄位為首欄，力求覆蓋查詢。
4. 索引鍵值唯一 → 定義 UNIQUE 索引，優化器可生成更優計畫。
5. 頻繁插入表 → 使用 FILLFACTOR 預留空間減少分頁分裂，提升併發降低死鎖；唯讀表設 100。
6. 選擇小型資料型別（INT 優於 BIGINT/CHAR），單頁容納更多鍵值，減少樹高與 I/O。整數鍵值存取最快。

五、索引維護

重建索引

重新組織 B-Tree 空間，消除碎片，恢復大塊 I/O 效率。觸發條件：

• 資料/使用模式劇變。
• 排序順序改變。
• 大量插入前後。
• 大塊 I/O 查詢磁碟讀取超預期。
• 大量修改導致頁面利用率低、空間超估。
• DBCC 檢測索引異常。

注意：重建叢集索引會連帶重建所有非叢集索引。

更新統計資訊

優化器依賴分布頁（密度向量 + 直方圖）判斷索引效用。統計資訊非即時更新，資料分佈變動後必須手動 UPDATE STATISTICS：

• 大量插入/刪除/修改改變資料分佈。
• TRUNCATE TABLE 後重新載入資料。
• 索引鍵值被修改。

六、不當索引反模式

• WHERE/JOIN 從未引用的欄位：索引純屬負擔。
• 極低基數欄位（性別、是/否、狀態碼 < 3 個值）：全表掃描更快。

權衡策略：將表置於一個檔案群組，非叢集索引置於另一獨立物理裝置檔案群組，平衡讀寫 I/O。