SQL Server 資料存取路徑深度解析：Table Scan、Index Scan、Index Seek、Clustered Index Scan 差異與優化

核心概念對照表

書籤查找 / Key Lookup / RID Lookup

• 非叢集索引 **不覆蓋** 查詢所需所有欄位時，需根據書籤 (Heap 用 RID，叢集表用叢集鍵) 回查基底表。
• 執行計畫顯示 `Nested Loops` → `Index Seek` (非叢集) + `Key Lookup` (叢集) / `RID Lookup` (Heap)。
• **臨界點**：若回查列數佔表總列 > ~1-2% (視頁面密度)，優化器傾向改用 `Clustered Index Scan`。此為 **「書籤查找臨界點」**。
• 解決：`INCLUDE` 所需欄位建立 **覆蓋索引**，消除 Lookup。

實驗驗證 (AdventureWorks 範例)

1. Heap + 無索引：`SELECT * FROM SalesOrderDetail WHERE UnitPrice > 200` → **Table Scan**。
2. Heap + 非叢集索引 (SalesOrderID)：`WHERE SalesOrderID < 43664` → **Index Seek**；`WHERE SalesOrderID < 80000` (選擇性降低) → 仍 **Index Seek** (統計資料準確時)；無條件 → **Index Scan**。
3. 加上叢集索引 (SalesOrderDetailID)：同樣 `UnitPrice > 200` → **Clustered Index Scan** (成本同 Table Scan)。
4. 再加非叢集索引 (UnitPrice)：`UnitPrice > 200` → **Index Seek (Nonclustered) + Key Lookup (Clustered)**。
   若 `SELECT SalesOrderID, UnitPrice` (覆蓋) → 僅 **Index Seek**。
   若 `SELECT *` 且結果集大 → 優化器可能轉為 **Clustered Index Scan** (實測 CPU 515ms vs Lookup 2995ms)。

Index Seek vs Index Scan 選擇邏輯 (優化器決策)

優化器基於 **統計資料** 估算成本，關鍵因素：

1. 資料表大小：小表 (數百頁) 掃描可能比 Seek + Lookup 更快。
2. 回傳列比例：> 5-10% 總列傾向 Scan；< 1% 傾向 Seek。
3. 索引鍵選擇性：高基數 (唯一值多) 適合索引；低基數 (如性別、狀態碼) 索引效益低，甚至有害。
4. 統計資料準確性：過期統計導致誤判，定期 `UPDATE STATISTICS` / `AUTO_UPDATE_STATISTICS`。

I/O 模型：邏輯讀 vs 物理讀

• 邏輯讀：從緩衝區 (Buffer Pool) 讀取 8KB 頁。每次存取頁計 1 次。
• 物理讀：頁不在緩衝區，從磁碟讀入。成本高得多 (ms 級 vs ns 級)。
• Read-Ahead：掃描操作觸發大量預讀 (64KB-4MB)，將物理讀轉為順序 I/O，降低延遲。
• 監控：`SET STATISTICS IO ON` 觀察 `logical reads`、`physical reads`、`read-ahead reads`。

最佳實務檢查清單

• [ ] 核心查詢執行計畫無 **Table Scan / Clustered Index Scan** (除非刻意全表掃描)。
• [ ] 關鍵 `WHERE`/`JOIN`/`ORDER BY` 欄位有 **Seekable 索引** (SARGable 條件、無函數包裹、型別一致)。
• [ ] 非叢集索引 **INCLUDE** 熱點查詢欄位，消除 **Key Lookup**。
• [ ] 統計資料 **自動更新開啟** 且大表啟用 **增量統計** (2014+)。
• [ ] 監控 **PLE (Page Life Expectancy)** > 3000 秒、**Buffer Cache Hit Ratio** > 99% (但不以此為唯一指標)。
• [ ] 定期檢視 `sys.dm_db_index_usage_stats` 移除 **未使用/重複索引**。