資料庫調優筆記系列（六）：書簽查找、索引覆蓋與執行計劃深度解讀

緣起：執行計劃中的 Bookmark Lookup

在執行計劃中看到 `Bookmark Lookup` (SQL 2005+) 或 `Key Lookup` (SQL 2008+)，代表非叢集索引查找後，仍需回叢集索引/堆積表取回非索引欄位。這是效能殺手之一，理解其機制是優化關鍵。

核心知識點整理

1. 非叢集索引結構與書簽

• 非叢集索引葉層儲存：索引鍵值 + **書簽 (Bookmark/Row Locator)**。
• 書簽指向資料列：堆積表 = RID (File:Page:Slot)；叢集表 = 叢集索引鍵值。
• `sysindexes` (舊) / `sys.indexes` (新) 中：`indid=0` 堆積、`indid=1` 叢集索引、`indid=2~250` 非叢集索引。

2. 索引覆蓋

若查詢所需欄位**全部包含在非叢集索引鍵值 + 叢集鍵 (若叢集表) 中**，則**不需書簽查找**，直接從索引葉層返回，稱為「索引覆蓋」。實現方式：

• 將查詢欄位納入索引鍵 (謹慎，影響寫入/大小)。
• **包含欄位** (`INCLUDE` 子句，SQL 2005+)：非鍵欄位僅存葉層，不計入 B 樹導航，最佳實踐。

3. 查詢效能排序 (經驗法則)

關鍵洞見：**叢集表上的非叢集索引查找比堆積表更貴**，因書簽為叢集鍵，需二次 B 樹尋找 (Key Lookup)；堆積表書簽為 RID，直接定位頁面。但「索引覆蓋」可完全消除此差異。

4. IAM 頁面

Index Allocation Map，追蹤表/索引使用的配置單元 (Extent)，`DBCC IND` 可見。

實戰感悟：從看天書到漸入佳境

初讀《寫有效率的 SQL 查詢 (V)》如閱天書。隨經驗積累，重讀每有新得。「叢集表非叢集索引效能較差」並非絕對，關鍵在於**是否形成覆蓋**。持續閱讀經典、結合實戰驗證，是技術成長唯一捷徑。

延伸議題：參數化查詢與計畫快取

IBatis.NET 使用 `Map` 導致動態 SQL 長度不一，`sp_executesql` 參數型別推導為 `NVARCHAR(n)` (n=實際長度)，造成同樣邏輯產生大量計畫快取條目 (如 `NVARCHAR(6)` vs `NVARCHAR(7)`)。解決：明確指定參數長度/型別，或改用預存程序強制參數化。SQL 2005+ 引入 `OPTIMIZE FOR AD HOC WORKLOADS` 緩解單次計畫佔用，但根治仍在參數化規範。

總結

• `Bookmark/Key Lookup` = 非叢集索引未覆蓋查詢欄位的代價。
• **覆蓋索引 (`INCLUDE`) 是消除 Lookup 的銀彈**。
• 叢集表非叢集索引雙重 B 樹查找成本高，設計時權衡叢集鍵選擇。
• 參數化不徹底 → 計畫快取膨脹 → 編譯風暴 → CPU 飆高。
• 經典文章需反芻，結合版本新特性 (如 `INCLUDE`、`OPTIMIZE FOR AD HOC`) 內化為己用。