sql-server-2008-r2 – 使用WHERE IN在删除操作期间发生意外扫描

我有一个如下查询：

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行.

tblFEStatsBrowsers：

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
给出一些统计数据：
选项(HASH JOIN)：
208.711次逻辑读取(12次扫描)

选项(LOOP JOIN,HASH GROUP)：
11.008.698逻辑读取(?每个BrowserID扫描一次(339))

没有选择：
11.008.775逻辑读取(?每个BrowserID扫描(339))

更新2
非常好的答案,各位 – 谢谢！艰难挑选一个.虽然马丁是第一个,而雷木思提供了一个很好的解决方案,但我必须把它交给新西兰人,让他们了解细节:)

解决方法

“I’m more wondering why the query optimizer would ever use the plan it currently does.”

换句话说,问题是为什么以下计划对于优化器来说看起来最便宜,与替代方案相比(其中有很多).

连接的内侧实际上是为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运算符中设置了一行row goal.那么问题就变成了如何为聚集索引扫描推导出“行目标”;也就是说,在一行与BrowserID谓词匹配之前,扫描期望处理多少行？

可用的统计信息显示166个不同的BrowserID值(1 / [All Density] = 1 / 0.006024096 = 166).成本计算假设不同的值在物理行上均匀分布,因此聚簇索引扫描的行目标设置为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没有关联(特别是’短路’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,RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');

该计划的估计成本为40729.3单位.

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!