主页 / dba / 问题 / 18637
Accepted
Mark S. Rasmussen
Asked: 2012-06-01 06:49:05 +0800 CST

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

  • 772

我有如下查询：

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，让他们对细节有所了解 :)

query sql-server-2008-r2

3 个回答

  1. Best Answer

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

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

    原计划

    连接的内侧本质上是针对 的每个相关值运行以下形式的查询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);

    带顶 (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');

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

    没有顶级 DOP 1 计划

    并且没有 MAXDOP 1 限制，并行计划：

    没有顶级并行计划

    另一种“修复”原始查询的方法是创建BrowserID执行计划报告的缺失索引。当内侧被索引时，嵌套循环最适合。在最好的情况下，估计半连接的基数是具有挑战性的。没有适当的索引（大表甚至没有唯一键！）根本没有帮助。

    我在Row Goals, Part 4: The Anti Join Anti Pattern中写了更多关于此的内容。

    • 61

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

    计划

    计划中显示的表基数是

    • tblFEStatsPaperHits：48063400
    • tblFEStatsBrowsers：339

    因此它估计需要执行tblFEStatsPaperHits339 次扫描。每个扫描都有相关的谓词tblFEStatsBrowsers.BrowserID=tblFEStatsPaperHits.BrowserID AND tblFEStatsPaperHits.BrowserID IS NOT NULL被下推到扫描运算符中。

    然而，该计划并不意味着将进行 339 次完整扫描。由于它在反半连接运算符下，一旦找到每次扫描的第一个匹配行，它就可以将其余部分短路。该节点的估计子树成本为1.32603，整个计划的成本为1.41337。

    对于哈希连接，它给出了以下计划

    哈希连接

    整个计划的成本为418.415（大约是嵌套循环计划的 300 倍），其中单个完整聚集索引扫描的tblFEStatsPaperHits成本206.8仅为 将此与1.32603之前给出的 339 次部分扫描的估计值进行比较（平均部分扫描估计成本 = 0.003911592）。

    因此，这表明每次部分扫描的成本比完整扫描低 53,000 倍。如果成本计算与行数成线性关系，那么这意味着假设平均而言，每次迭代只需处理 900 行，然后才能找到匹配的行并可以短路。

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

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

    1470.003911592给出与at最接近的估计子树成本0.0039113。无论哪种方式，很明显它是基于假设每次扫描只需要处理表的一小部分，按数百行而不是数百万行的顺序计算的。

    我不确定它究竟基于什么数学假设，并且它并没有真正与计划其余部分中的行数估计相加（来自嵌套循环连接的 236 估计行意味着有 236根本没有找到匹配行并且需要进行全面扫描的情况）。我认为这只是建模假设有所下降并使嵌套循环计划的成本大大低于成本的情况。

    • 22

  3. 在我的书中，即使一次扫描 50M 行也是不可接受的......我通常的技巧是实现不同的值并委派引擎使其保持最新：

    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

    这为您提供了每个 BrowserID 一行的物化索引，无需扫描 50M 行。引擎将为您维护它，并且 QO 将在您发布的语句中“按原样”使用它（没有任何提示或查询重写）。

    缺点当然是争吵。任何插入或删除操作tblFEStatsPaperHits（我猜是一个带有大量插入的日志表）都必须序列化对给定 BrowserID 的访问。如果您愿意购买的话，有一些方法可以使这个可行（延迟更新、2 分阶段日志记录等）。

    • 20

相关问题