SQL Server 索引視圖和 TOP

December 9, 2020

我正在努力說服查詢計劃按我認為的那樣行事。在查詢索引視圖時添加 TOP 子句會導致次優計劃，我希望在排序方面有所幫助。
環境
SQL Server 2019
StackOverflow2013 數據庫（50GB 版本），Compat Mode 150（問題不是這個版本特有的）
設置：
首先，我創建了一個視圖來回報每個人的高聲譽：
CREATE VIEW vwHighReputation
WITH SCHEMABINDING
AS
SELECT  [Id],
       [DisplayName],
       [Reputation]
FROM    [dbo].[Users]
WHERE   [Reputation] &gt; 10000
接下來，由於我將按顯示名稱進行搜尋，因此我在視圖上創建了幾個索引：
CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id])
GO
CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation)
GO
如果我通過視圖查詢，我可以看到我的非聚集索引正在被使用：
SELECT  *
FROM    [dbo].[vwHighReputation]
WHERE   [DisplayName] LIKE 'J%'
計劃：（https://www.brentozar.com/pastetheplan/?id=Sy2EoJaiv）
到現在為止還挺好。我什至可以將我的視圖用作帶有 OUTER APPLY 的更複雜查詢的一部分，並且我仍然會在我的索引中僅獲得 63 次讀取（這顯然是一個人為的範例，但有助於說明我將要解決的問題):
SELECT  [U].[Id],
       [A].[Reputation],
       [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
       OUTER APPLY (
                       SELECT  *
                       FROM    [dbo].[vwHighReputation] AS [v]
                        WHERE   [v].[Id] = [U].[Id]
                   ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%'; 
計劃：https ://www.brentozar.com/pastetheplan/?id=HJaw3y6ov
但是，如果我將 TOP 1 添加到我的 OUTER APPLY：
SELECT  [U].[Id],
       [A].[Reputation],
       [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
       OUTER APPLY (
                       SELECT  TOP 1 *
                       FROM    [dbo].[vwHighReputation] AS [v]
                       WHERE   [v].[Id] = [U].[Id]
                   ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';
然後情況變得糟糕……非常非常糟糕……
計劃：https ://www.brentozar.com/pastetheplan/?id=HyOS6yaiw
我對這個觀點的邏輯閱讀計數現在接近 500 萬。從計劃中我可以看出，SQL Server 現在選擇以使用者 ID 作為謂詞對聚集索引執行搜尋，但這樣做大約 250 萬次。它還在掃描整個使用者表。它不再尋找視圖的索引。
顯然優化器決定這是最有效的方法，但我不明白為什麼！我認為這可能與基礎表的排序方式有關，但我不確定。
順便說一句，將其重寫為簡單的 SUB QUERY 而不是 CROSS APPLY 會產生相同的結果。
任何幫助或建議都會很棒！

外應用
您正在使用OUTER APPLY，但帶有會拒絕NULL值的 where 子句。
它被轉換為沒有以下內容的內部連接TOP (1)：
SELECT  
   U.Id,
   A.Reputation,
   A.DisplayName
FROM dbo.Users AS U
OUTER APPLY 
(
   SELECT  
       v.*
   FROM dbo.vwHighReputation AS v
   WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
我已經對您的程式碼進行了一些格式化，並添加了一個ORDER BY以跨查詢驗證結果。沒有冒犯的意思。
外塗 + TOP (1)
當您使用時TOP (1)，連接是LEFT OUTER多種多樣的：
SELECT  
   U.Id,
   A.Reputation,
   A.DisplayName
FROM dbo.Users AS U
OUTER APPLY 
(
   SELECT TOP (1)
       v.*
   FROM dbo.vwHighReputation AS v
   WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
內部顯然TOP (1)使OUTER APPLY優化器無法將相同的轉換應用於內部連接，即使使用冗餘謂詞：
SELECT  
   U.Id,
   A.Reputation,
   A.DisplayName
FROM dbo.Users AS U
OUTER APPLY 
(
   SELECT TOP (1)
       v.*
   FROM dbo.vwHighReputation AS v
   WHERE v.Id = U.Id
   AND   v.DisplayName LIKE 'J%'
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
注意剩余謂詞以評估Id和DisplayName列是否為NULL。
這也不僅僅是一個TOP (1)問題——您可以替換最大 int max (9223372036854775807) 的任何值並查看相同的計劃。
如果您完全跳過視圖，也會發生這種情況。
SELECT  
   U.Id,
   A.Reputation,
   A.DisplayName
FROM dbo.Users AS U
OUTER APPLY 
(
   SELECT TOP (1)
       v.Id,
       v.DisplayName,
       v.Reputation
   FROM dbo.Users AS v
   WHERE v.Reputation &gt; 10000 
   AND   v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id
OPTION(EXPAND VIEWS);
重寫
TOP (1)獲得與沒有各種優化器副作用相同的效果的一種方法TOP是使用ROW_NUMBER
SELECT  
   U.Id,
   A.Reputation,
   A.DisplayName
FROM dbo.Users AS U
OUTER APPLY 
(
   SELECT
       v.*
   FROM
   (
       SELECT 
           v.*,
           ROW_NUMBER() OVER 
           (
               PARTITION BY 
                   v.Id
               ORDER BY
                   v.Id
           ) AS n
       FROM dbo.vwHighReputation AS v
   ) AS v
   WHERE v.Id = U.Id
   AND   v.n = 1
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
這將為您提供原始計劃：