主页 / dba / 问题 / 108352
Accepted
8kb
Asked: 2015-07-29 10:03:32 +0800 CST

为什么表变量强制索引扫描而临时表使用查找和书签查找？

  • 772

我试图理解为什么使用表变量会阻止优化器使用索引查找然后书签查找与索引扫描。

填充表格：

CREATE TABLE dbo.Test 
(
    RowKey INT NOT NULL PRIMARY KEY, 
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL 
) 

INSERT dbo.Test 
(
    RowKey, 
    ForeignKey
) 
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)     
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2 

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey)

使用单个记录填充表变量，并尝试通过搜索外键列来查找主键和第二列：

DECLARE @Keys TABLE (RowKey INT NOT NULL) 

INSERT @Keys (RowKey) VALUES (10)

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey

下面是执行计划：

在此处输入图像描述

现在使用临时表进行相同的查询：

CREATE TABLE #Keys (RowKey INT NOT NULL) 

INSERT #Keys (RowKey) VALUES (10) 

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    #Keys k
ON
    t.ForeignKey = k.RowKey

此查询计划使用搜索和书签查找：

在此处输入图像描述

为什么优化器愿意使用临时表而不是表变量进行书签查找？

此示例中使用 table 变量来表示来自存储过程中用户定义的表类型的数据。

我意识到如果外键值出现数十万次，则索引搜索可能不合适。在这种情况下，扫描可能是更好的选择。对于我创建的场景，没有值为 10 的行。我仍然认为该行为很有趣，并想知道是否有原因。

SQL小提琴

添加OPTION (RECOMPILE)并没有改变行为。UDDT 有一个主键。

@@VERSION是 SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)（内部版本 7601：Service Pack 1）（管理程序）

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    该行为的原因是 SQL Server 无法确定与 ForeignKey 匹配的行数，因为没有以 RowKey 作为前导列的索引（它可以从 #temp 表的统计信息中推断出这一点，但那些不表变量/UDTTs 存在），因此它估计有 100,000 行，扫描比查找+查找更好地处理。当 SQL Server 意识到只有一行时，为时已晚。

    您可能能够以不同的方式构建您的 UDTT；在更现代的 SQL Server 版本中，您可以在表变量上创建二级索引，但此语法在 2008 R2 中不可用。

    顺便说一句，如果您尝试通过提示嵌套循环连接来避免位图/探针，您可以获得搜索行为（至少在我有限的试验中）：

    DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt

INSERT @Keys (RowKey) VALUES (10);

SELECT 
     t.RowKey
    ,t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey
    OPTION (LOOP JOIN);

    几年前，我从 Paul White 那里学到了这个技巧。当然，您应该小心在生产代码中放置任何类型的连接提示 - 如果人们对底层对象进行更改并且特定类型的连接不再可能或不再是最佳连接，这可能会失败。

    对于更复杂的查询，并且当您迁移到 SQL Server 2012 或更高版本时，跟踪标志 2453可能会有所帮助。不过，那个标志对这个简单的连接没有帮助。同样的免责声明也适用——如果没有大量的文档和严格的回归测试程序，这只是你通常不应该做的另一种事情。

    此外，Service Pack 1 已不再受支持，您应该使用Service Pack 3 + MS15-058。

    • 15

  2. 表变量和临时表有多种不同的处理方式。这里有一个很好的答案，其中有很多关于它们不同之处的细节。

    特别是在你的情况下，我猜想临时表可以生成额外的统计信息和并行计划，而表变量的统计信息更有限（没有列级统计信息）并且没有并行计划是你的罪魁祸首。

    在存储过程期间，您最好将表变量转储到临时表中。

    • 3

相关问题