SQL Server 查詢調優實戰：從索引設計到執行計劃深度解析

面對一條看似簡單的查詢，如何通過索引設計引導優化器生成最優計劃？本文以 Sales.SalesOrderDetail 表為例，完整演示從「全掃描 + 排序溢寫 TempDB」到「覆蓋索引 + 有序輸出」的調優全過程，揭示優化器的決策邏輯與陷阱。

問題查詢與初始執行計劃

DECLARE @i INT = 999;
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID < @i
ORDER BY CarrierTrackingNumber;

初始計劃缺陷：

• 索引掃描：無支持索引，掃描整個非聚集索引，1382 邏輯讀，耗時 ~800ms。
• Filter 運算符：逐行比對 ProductID < @i。
• Sort 運算符溢寫 TempDB：本地變量 + 不等值謂語導致基數估算錯誤（硬編碼 30%），估算 3.6 萬行，實際 12 萬行，內存授予不足。

調優迭代過程

步驟 1：嘗試單列索引 ProductID

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID);

結果：優化器拒絕使用。強制提示 WITH (INDEX(idx_Test)) 後，出現 並行計劃 + 書簽查找，邏輯讀暴增至 37 萬，性能反而更差。原因：查詢返回絕大多數行（超過臨界點），書簽查找開銷遠超全掃描。

步驟 2：構建覆蓋索引（INCLUDE 選列表列）

CREATE NONCLUSTERED INDEX idx_Test 
ON Sales.SalesOrderDetail(ProductID) 
INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
WITH (DROP_EXISTING = ON);

結果：優化器自動選用，邏輯讀降至 757，但 Sort 運算符仍在，仍溢寫 TempDB（基數估算未變）。耗時仍 ~800ms。

步驟 3：將 ORDER BY 列前置到索引鍵中（預排序）

CREATE NONCLUSTERED INDEX idx_Test 
ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID) 
INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
WITH (DROP_EXISTING = ON);

結果：Sort 運算符消失，改為索引有序掃描 + Residual Predicate 過濾。邏輯讀 763，耗時降至 600ms（提升 25%）。無 TempDB 溢寫，基數估算誤差不再影響性能。

步驟 4：對比優化器建議的「缺失索引」

-- 優化器建議：ProductID 為鍵，INCLUDE 所有選列表列
CREATE NONCLUSTERED INDEX [...] ON Sales.SalesOrderDetail(ProductID) 
INCLUDE (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal);

驚人發現：創建後，優化器使用舊索引（步驟 3 的索引），而非新建議索引。新索引僅增加寫入開銷，佔用空間，從未被查詢使用。

核心心法

1. 不要盲目信任缺失索引建議：它只基於單條查詢的選擇列表，不考慮排序、分組、現有索引複用、寫入開銷。
2. 小步快跑，驗證為王：每次調整索引後立即對比執行計劃、邏輯讀、耗時。
3. 索引鍵順序決定一切：WHERE 等值列優先，ORDER BY 列緊隨其後，INCLUDE 覆蓋選列表。
4. 本地變量 + 不等值 = 基數估算陷阱：可考慮 OPTION (RECOMPILE) 或參數化處理。

調優是與優化器博弈的過程：理解其成本模型，用索引結構表達業務意圖，再用事實數據校正預期。