SQL Server 索引優化系列一：叢集與非叢集索引底層運作原理圖解

索引本質：B+ 樹結構

SQL Server 索引採用 **B+ 樹** (Balanced Tree)：**根節點 → 中間層 → 葉節點**。所有查找從根節點開始，逐層向下，直到葉節點。樹高通常 2~4 層，百萬列資料僅需 3~4 次頁面讀取即可定位。

叢集索引

結構特徵

• 葉節點 = 資料頁：資料列按叢集鍵 **物理有序** 儲存 (邏輯順序 = 物理順序)。
• 一張表**最多一個**叢集索引 (資料只能有一種物理排序)。
• 若無叢集索引 → **Heap (堆積)**：資料無序、以 RID (File:Page:Slot) 定位。

查找流程圖解 (以 LastName 為叢集鍵)

根節點 (Page 140)
 ├─ 'Martin' → Page 141
 │    ├─ 'Martin' ... (Page 100)
 │    └─ 'Smith'  ... (Page 110)
 └─ 'Smith'  → Page 145
      ├─ 'Smith' ... (Page 120)
      └─ 'White' ... (Page 130)

查找 'Rudd'：
1. 根節點比較：'Rudd' > 'Martin' → 走右分支 (Page 145)
2. Page 145 比較：'Rudd' < 'Smith' → 走左分支 (Page 120)
3. Page 120 葉節點掃描 → 找到 'Rudd'

**無索引掃描**：從第一頁順序掃描所有頁面，百萬列需讀取所有頁 → 極慢。

叢集索引設計黃金法則

1. 窄 (Narrow)： 鍵值越小，非叢集索引葉節點儲存的叢集鍵越小 → 非叢集索引越瘦、越快。**首選 INT/BIGINT IDENTITY**。
2. 唯一 (Unique)： 避免 SQL Server 隱性加入 4-byte Uniquifier；主鍵天然唯一。
3. 靜態 (Static)： 鍵值不更新，避免頁分裂、資料移動、非叢集索引維護開銷。
4. 遞增 (Ever-increasing)： 新資料總是插入尾端，減少頁分裂、碎片、鎖爭用。**IDENTITY / NEWSEQUENTIALID() / DATETIME2** 符合；`NEWID()` (隨機 GUID) **違反**，會導致大量頁分裂。

非叢集索引

結構特徵

• **葉節點 = 索引鍵值 + 叢集鍵 (或 RID)**：不包含完整資料列 (除非 `INCLUDE` 覆蓋)。
• 資料頁**物理順序不變**，索引另建一份有序副本。
• 一張表可建 **999 個** 非叢集索引 (SQL 2008+)。

查找流程圖解

非叢集索引 B+ 樹 (Page 60 根)
 ├─ 'Martin' → Page 61
 │    ├─ 'Martin', ClusterKey=1001
 │    └─ 'Rudd',    ClusterKey=470501  ← 找到目標鍵值
 └─ 'Smith'  → Page 62

回查叢集索引 (Key Lookup / Bookmark Lookup)：
1. 非叢集索引葉節點取得 ClusterKey = 470501
2. 叢集索引 B+ 樹搜尋 470501 → 定位資料頁 → 取得完整列

覆蓋索引 — 避免 Key Lookup 的關鍵

-- 查詢：SELECT LastName, FirstName, Email FROM Users WHERE LastName = 'Rudd';
-- 非叢集索引：
CREATE INDEX IX_Users_LastName ON Users(LastName) INCLUDE (FirstName, Email);
-- 查詢計畫：Index Seek → 直接從非叢集索引葉節點取得所有欄位 → 無 Key Lookup

• `INCLUDE` 欄位**不計入鍵長度限制** (900 bytes)，可包含 LOB 型別 (MAX)。
• 代價：寫入時需維護更多欄位、索引體積增大。

Heap (無叢集索引) vs 叢集索引：非叢集索引的指標差異

結論： **幾乎所有表都應有叢集索引** (窄、唯一、靜態、遞增)，Heap 僅適用極少量、純插入、無點查的暫存表。

實戰決策樹

需要點查/範圍查/排序/群組？
 ├─ 是 → 有無適合的窄唯一靜態遞增欄位？
 │    ├─ 有 → 設為叢集索引 (通常是 PK/IDENTITY)
 │    └─ 無 → 建立 Surrogate Key (INT IDENTITY) 當叢集索引，業務鍵加 UNIQUE 非叢集
 └─ 否 (純掃描/大量載入/暫存) → 考慮 Heap 或叢集索引最小化維護

非叢集索引策略：
1. WHERE/JOIN/ORDER BY/GROUP BY 欄位 → 建立非叢集索引
2. SELECT 清單其餘欄位 → INCLUDE 覆蓋 (評估寫入開銷)
3. 多欄位索引：等值欄位在前、範圍欄位在後、選擇性高在前
4. 定期 sys.dm_db_index_usage_stats 檢視未使用索引 → 刪除
5. 定期 sys.dm_db_index_physical_stats 重建/重組碎片 > 30% / 10%