使用 WHERE IN 進行刪除操作期間的意外掃描

November 10, 2019

我有如下查詢：
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
   SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
tblFEStatsBrowsers 有 553 行。
tblFEStatsPaperHits 有 47.974.301 行。
tblFEStats瀏覽器：
CREATE TABLE [dbo].[tblFEStatsBrowsers](
   [BrowserID] [smallint] IDENTITY(1,1) NOT NULL,
   [Browser] [varchar](50) NOT NULL,
   [Name] [varchar](40) NOT NULL,
   [Version] [varchar](10) NOT NULL,
   CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)
tblFEStatsPaperHits:
CREATE TABLE [dbo].[tblFEStatsPaperHits](
   [PaperID] [int] NOT NULL,
   [Created] [smalldatetime] NOT NULL,
   [IP] [binary](4) NULL,
   [PlatformID] [tinyint] NULL,
   [BrowserID] [smallint] NULL,
   [ReferrerID] [int] NULL,
   [UserLanguage] [char](2) NULL
)
tblFEStatsPaperHits 上有一個不包含 BrowserID 的聚集索引。因此，執行內部查詢需要對 tblFEStatsPaperHits 進行全表掃描——這完全沒問題。
目前，對 tblFEStatsBrowsers 中的每一行執行一次完整掃描，這意味著我對 tblFEStatsPaperHits 進行了 553 次全表掃描。
重寫為 WHERE EXISTS 不會改變計劃：
DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
   SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)
但是，正如 Adam Machanic 所建議的，添加 HASH JOIN 選項確實會產生最佳執行計劃（只需掃描一次 tblFEStatsPaperHits）：
DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
   SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)
現在這不是如何解決這個問題的問題 - 我可以使用 OPTION (HASH JOIN) 或手動創建臨時表。我更想知道為什麼查詢優化器會使用它目前執行的計劃。
由於 QO 在 BrowserID 列上沒有任何統計資訊，我猜它假設最差 - 5000 萬個不同的值，因此需要相當大的記憶體/tempdb 工作表。因此，最安全的方法是對 tblFEStatsBrowsers 中的每一行執行掃描。兩個表的BrowserID列之間沒有外鍵關係，所以QO不能從tblFEStatsBrowsers中扣除任何資訊。
這就是聽起來那麼簡單的原因嗎？
更新 1
給出幾個統計數據： OPTION (HASH JOIN):
208.711 logical reads (12 scans)
OPTION (LOOP JOIN, HASH GROUP):
11.008.698 邏輯讀取 (~scan per BrowserID (339))
沒有選項：
11.008.775 邏輯讀取（~scan per BrowserID (339)）
更新 2
優秀的答案，你們所有人 - 謝謝！很難只挑一個。雖然 Martin 是第一個，而 Remus 提供了一個很好的解決方案，但我必須把它交給 Kiwi，讓他們對細節有所了解 :)

“我更想知道為什麼查詢優化器會使用它目前執行的計劃。”
換句話說，問題是為什麼與替代方案（其中有很多）相比，以下計劃對優化器來說看起來最便宜。
連接的內側本質上是針對的每個相關值執行以下形式的查詢BrowserID：
DECLARE @BrowserID smallint;

SELECT 
   tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
   tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);
請注意，估計的行數是185,220（不是289,013），因為相等比較隱式排除NULL（除非ANSI_NULLSis OFF）。上述計劃的估計成本為206.8個單位。
現在讓我們添加一個TOP (1)子句：
DECLARE @BrowserID smallint;

SELECT TOP (1)
   tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
   tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);
現在估計成本為0.00452單位。Top 物理運算符的添加在 Top 運算符處設置了1 行的行目標。那麼問題就變成瞭如何為聚集索引掃描導出“行目標”；也就是說，在一行與BrowserID謂詞匹配之前，掃描應該處理多少行？
可用的統計資訊顯示166 個不同的BrowserID值（1/
$$ All Density $$= 1/0.006024096 = 166)。成本核算假設不同的值均勻分佈在物理行上，因此聚集索引掃描的行目標設置為166.302（考慮到自收集抽樣統計數據以來表基數的變化）。掃描預期的 166 行的估計成本不是很大（即使執行了 339 次，每次更改一次BrowserID） - Clustered Index Scan 顯示估計成本為1.3219個單位，顯示了行目標的縮放效果。I/O 和 CPU 的未縮放運算元成本分別顯示為153.931和52.8698：
在實踐中，從索引掃描的前 166 行（以它們碰巧返回的任何順序）不太可能包含每個可能的值BrowserID。儘管如此，該DELETE計劃的總成本為1.40921個單位，因此由優化器選擇。Bart Duncan 在最近的一篇題為Row Goals Gone Rogue的文章中展示了這種類型的另一個例子。
值得注意的是，執行計劃中的 Top 運算符與 Anti Semi Join無關（特別是 Martin 提到的“短路”）。我們可以通過首先禁用名為GbAggToConstScanOrTop的探索規則來開始查看 Top 的來源：
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
   SELECT DISTINCT BrowserID 
   FROM tblFEStatsPaperHits WITH (NOLOCK) 
   WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');
該計劃的估計成本為364.912，並顯示 Top 替換了 Group By Aggregate （按相關列分組BrowserID）。聚合不是由於DISTINCT查詢文本中的冗餘：它是可以通過兩個探索規則LASJNtoLASJNonDist和LASJOnLclDist引入的優化。禁用這兩個也會產生這個計劃：
DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
   SELECT DISTINCT BrowserID 
   FROM tblFEStatsPaperHits WITH (NOLOCK) 
   WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');
該計劃的估計成本為40729.3個單位。
如果沒有從 Group By 到 Top 的轉換，優化器“自然地”選擇BrowserID在反半連接之前具有聚合的雜湊連接計劃：
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
   SELECT DISTINCT BrowserID 
   FROM tblFEStatsPaperHits WITH (NOLOCK) 
   WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');
並且沒有 MAXDOP 1 限制，並行計劃：
另一種“修復”原始查詢的方法是創建BrowserID執行計劃報告的缺失索引。當內側被索引時，嵌套循環最適合。在最好的情況下，估計半連接的基數是具有挑戰性的。沒有適當的索引（大表甚至沒有唯一鍵！）根本沒有幫助。
我在Row Goals, Part 4: The Anti Join Anti Pattern中寫了更多關於此的內容。