為什麼我看到所有讀取行的鍵查找，而不是匹配 where 子句的所有行？

我有一個如下表：

create table [Thing]
(
   [Id] int constraint [PK_Thing_Id] primary key,
   [Status] nvarchar(20),
   [Timestamp] datetime2,
   [Foo] nvarchar(100)
)

Status在andTimestamp欄位上具有非聚集、非覆蓋索引：

create nonclustered index [IX_Status_Timestamp] on [Thing] ([Status], [Timestamp] desc)

如果我查詢這些行的“頁面”，使用如下偏移/獲取，

select * from [Thing]
where Status = 'Pending'
order by [Timestamp] desc
offset 2000 rows
fetch next 1000 rows only

我知道該查詢需要讀取總共 3000 行才能找到我感興趣的 1000 行。然後我希望它對這 1000 行中的每一行執行鍵查找以獲取索引中未包含的欄位。

但是，執行計劃表明它正在對所有 3000 行進行鍵查找。我不明白為什麼，當唯一的標準（過濾

$$ Status $$並訂購$$ Timestamp $$) 都在索引中。

如果我用 cte 改寫查詢，如下所示，我或多或少地得到了我期望第一個查詢執行的操作：

with ids as
(
   select Id from [Thing]
   where Status = 'Pending'
   order by [Timestamp] desc
   offset 2000 rows
   fetch next 1000 rows only
)

select t.* from [Thing] t
join ids on ids.Id = t.Id
order by [Timestamp] desc

來自 SSMS 的一些統計數據來比較這兩個查詢：

乍一看，CTE 版本似乎“更好”，儘管我不知道它會為工作台帶來記憶體授權這一事實有多大的重視。（來自的消息set statistics io on表明工作台上的任何類型的讀取為零）

我說第一個查詢應該能夠首先隔離相關的 1000 行（即使這需要先讀取 2000 其他行），然後只對這 1000 行進行鍵查找，我錯了嗎？不得不嘗試使用 CTE 查詢“強制”該行為似乎有點奇怪。

（作為次要的第二個問題：我假設 CTE 方法的最後一部分需要根據order by連接結果自行處理，即使 CTE 本身俱有order by，因為在連接過程中可能會失去排序。是這對嗎？）

從根本上說，這是一個長期存在的優化器限制。

SQL Server 不考慮將Key Lookup轉換為Clustered Index Seek。Key Lookup必須幾乎立即跟隨與其關聯的非聚集索引訪問（出於I/O 原因，可能存在中間排序）。

有幾種方法可以重寫查詢以盡可能長時間地僅對索引鍵進行操作，而無需引入範例中看到的排序：

WITH IDs AS
(
   SELECT T.* 
   FROM dbo.Thing AS T
   WHERE T.[Status] = N'Pending'
   ORDER BY T.[Timestamp] DESC
   OFFSET 2000 ROWS
   FETCH NEXT 1000 ROWS ONLY
)
SELECT 
   T.* 
FROM IDs AS I
JOIN dbo.Thing AS T
   ON T.Id = I.Id
ORDER BY
   I.[Timestamp] DESC;

或者

SELECT 
   T2.* 
FROM dbo.Thing AS T
JOIN dbo.Thing AS T2
   ON T2.Id = T.Id
WHERE 
   T.[Status] = N'Pending'
ORDER BY 
   T.[Timestamp] DESC
   OFFSET 2000 ROWS
   FETCH NEXT 1000 ROWS ONLY;

通過幫助優化器“查看”保留排序順序，消除了排序的需要。

進一步閱讀：

• 查找只是沒有選擇的加入 Erik Darling
• 我優化伺服器端分頁
• 使用 OFFSET / FETCH 進行分頁： Aaron Bertrand的更好方法
• 高級 TSQL 調整：為什麼內部知識對我很重要

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