消除執行計劃中的鍵查找

我有以下查詢：

DECLARE @p__linq__0 UNIQUEIDENTIFIER

SET @p__linq__0 = '... some guid ...'

SELECT TOP 1
   [EventId] AS [EventId],       
   [DateCreated] AS [DateCreated],       
   [LocationId] AS [LocationId],       
   [SourceName] AS [SourceName],       
   [SourceState] AS [SourceState],       
   [Priority] AS [Priority],       
   [EventDescription] AS [EventDescription],       
   [FirstTrigger] AS [FirstTrigger]
FROM [dbo].[Watchdog]
WHERE 
   [LocationId] = @p__linq__0
   AND 
   [FirstTrigger] = 1
ORDER BY [DateCreated] DESC

Watchdog表定義了 2 個指標：

1. EventId主鍵列上的聚集索引
2. DateCreated列上的非聚集索引

這是查詢的實際執行計劃：

閱讀有關如何消除鍵查找的其他文章，我添加了另一個非聚集索引，其中包括來自SELECT

CREATE NONCLUSTERED INDEX [LocationId_FirstTrigger] ON [dbo].[Watchdog]
(
   [LocationId] ASC,
   [FirstTrigger] ASC
)
INCLUDE (   [EventId],
   [DateCreated],
   [SourceName],
   [SourceState],
   [Priority],
   [EventDescription]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

然而，這並沒有幫助，實際的執行計劃是相同的。如果我查看鍵查找，則輸出實際上包含在新添加的非聚集索引中。

我的問題是，為什麼它仍然在做key lookup而不是 index scan/seek ？

---

更新

根據評論中的一些建議，我刪除了新創建的非聚集索引，而是在DateCreated包含來自SELECT.

現在執行計劃如下：

查詢執行時間也從 1+ 分鐘下降到幾秒（該表有 18+ 百萬行）。

這是否意味著由於ORDER BY非聚集索引而完成了鍵查找？

我的問題是，為什麼它仍然在做 key lookup 而不是 index scan/seek ？

該查詢指定結果應按 排序DateCreated。由於您已經有一個非聚集索引DateCreated，優化器決定進行鍵查找的成本低於對所有數據進行排序的成本DateCreated。

這是否意味著由於非聚集索引上的 ORDER BY 已完成鍵查找？

本質上，是的。據估計，按所需順序讀取數據並通過鍵查找獲取任何其他欄位會更便宜*，而不是從單個索引中讀取所有欄位，然後按DateCreated.

您可以通過比較兩者之間的估計成本來確認這一點

• 原始查詢（帶有原始索引），以及
• 帶有索引提示的原始查詢

索引提示將是這樣的FROM：

FROM [dbo].[Watchdog] WITH (INDEX (LocationId_FirstTrigger))

這應該產生一個沒有鍵查找的計劃（因為LocationId_FirstTrigger覆蓋了該查詢）和一個Sort運算符。我預計“估計成本”會更高，因此選擇了另一個計劃。

---

• 在這裡解釋優化器的選擇：

查詢中的TOP (1)表示優化器設置了一個行目標，這意味著該計劃旨在快速生成一行。優化器希望從索引掃描中快速找到與您的謂詞匹配的一行LocationId，因為它假設值是均勻分佈的。這在現實中可能是真的，也可能不是。索引掃描之後的一次Key Lookup的成本非常小。

因此，掃描 + 查找選項對於優化器來說看起來比使用LocationId_FirstTrigger和排序查找匹配項更便宜。OPTION (QUERYTRACEON 4138)您可以通過添加提示來關閉查詢的行目標邏輯作為測試。您可能會發現優化器隨後選擇了LocationId_FirstTrigger沒有索引提示的索引。

儘管如此，最好的選擇還是按照 Mikael Eriksson 的建議修改您的索引。

引用自：https://dba.stackexchange.com/questions/246649