使用WHERE IN进行删除操作期间发生意外扫描

DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
    SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)

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)
)

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
)

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)

#1 楼

“我想知道为什么查询优化器会使用它当前使用的计划。”


换句话说，问题是为什么以下计划看起来如此与替代方案（其中有很多替代方案）相比，对优化器而言最便宜。



联接的内侧实际上在运行以下形式的查询： 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_NULLS是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 / [全部密度] = 1 / 0.006024096 = 166 ）。 Costing假设不同的值在物理行上均匀分布，因此“聚簇索引扫描”的行目标设置为166.302（考虑到由于收集了抽样统计信息而导致的表基数变化）。

扫描预期的166行的估计成本不是很大（即使执行339次，对于BrowserID的每次更改一次）-聚集索引扫描显示的估计成本为1.3219单位，显示了行目标的缩放效果。 I / O和CPU的未缩放操作员成本分别显示为153.931和52.8698：



实际上，从索引扫描的前166行（以它们碰巧返回的顺序）不太可能包含每个BrowserID值中的一个。尽管如此，DELETE计划的总成本为1.40921单位，因此由优化器选择。巴特·邓肯（Bart Duncan）在最近的一篇题为《行进目标消失的盗贼》（Row Goals Gone Rogue）中展示了这种类型的另一个例子。

还值得注意的是执行计划中的Top运算符与Anti Semi Join（in特别是马丁提到的“短路”）。首先，通过禁用名为GbAggToConstScanOrTop的探索规则，可以开始查看“顶部”的来源：

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，并显示“顶部”替换了“按汇总分组”（按相关列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单位。

没有从“分组依据”到“顶部”的转换，优化器“自然地”选择在反半联接之前使用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上的索引。当对内侧进行索引时，嵌套循环最适用。在最佳时机，估计半联接的基数具有挑战性。没有适当的索引（大表甚至没有唯一的键！）根本没有帮助。

我在“行目标”的第4部分：反联接反模式中写了更多有关此内容。

#2 楼

当我运行您的脚本以创建仅统计信息的数据库以及问题中的查询时，我得到以下计划。



计划中显示的表基数是



tblFEStatsPaperHits：48063400


tblFEStatsBrowsers：339


所以它估计它将需要在tblFEStatsPaperHits上执行339次扫描。每次扫描都有相关的谓词tblFEStatsBrowsers.BrowserID=tblFEStatsPaperHits.BrowserID AND tblFEStatsPaperHits.BrowserID IS NOT NULL，该谓词被下推到扫描运算符中。

该计划并不意味着将进行339次完整扫描。由于它位于反半联接运算符的控制之下，因此一旦在每次扫描中找到第一条匹配行，它就会使其余部分短路。该节点的估计子树成本为1.32603，整个计划的成本为1.41337。对于哈希联接，它给出了以下计划



总体计划的成本为418.415（比嵌套循环计划高300倍），而tblFEStatsPaperHits上的单个完整聚簇索引扫描的成本仅为206.8。将其与之前给出的339次局部扫描的1.32603估计值进行比较（平均局部扫描估计成本= 0.003911592）。

因此，这表明每次局部扫描的成本比完整扫描的成本低53,000倍扫描。如果成本是随行数线性增长的，那么这意味着它假设平均而言，在找到匹配的行并可能发生短路之前，每次迭代仅需要处理900行。

但是，我认为成本核算并不会以线性方式扩展。我认为它们还包含固定启动成本的某些要素。在以下查询中尝试TOP的各种值

SELECT TOP 147 BrowserID 
FROM [dbo].[tblFEStatsPaperHits] 

147在0.003911592处给出最接近0.0039113的估计子树成本。不管哪种方式，很明显，这都是基于每次扫描仅需处理表的一小部分（大约几百行而不是几百万行）的假设进行的成本计算。

我不确定该假设所基于的数学原理，也未真正与计划其余部分中的行数估计值相加（嵌套循环中出现的236个估计行数意味着存在236种情况）根本没有找到匹配的行，因此需要完整扫描）。我认为这只是一种情况，其中建模假设有所下降，并使嵌套循环计划的成本大大降低。

#3 楼

在我的书中，即使扫描5000万行也是不可接受的。我通常的技巧是具体化不同的值并委派引擎以使其保持最新状态：

create view [dbo].[vwFEStatsPaperHitsBrowserID]
with schemabinding
as
select BrowserID, COUNT_BIG(*) as big_count
from [dbo].[tblFEStatsPaperHits]
group by [BrowserID];
go

create unique clustered index [cdxVwFEStatsPaperHitsBrowserID] 
  on [vwFEStatsPaperHitsBrowserID]([BrowserID]);
go

这为您提供了每个浏览器ID一行的物化索引，从而无需扫描5000万行。引擎将为您维护它，并且QO将在您发布的语句中使用“原样”（不带任何提示或查询重写）。

缺点当然是争执。 tblFEStatsPaperHits中的任何插入或删除操作（我想是带有大量插入的日志记录表）都必须序列化对给定BrowserID的访问。如果您愿意购买，可以通过多种方法使它可行（延迟更新，两阶段日志记录等）。