主页 / dba / 问题 / 164463
Accepted
sam.bishop
Asked: 2017-02-16 12:39:42 +0800 CST

当表变量具有主键时对执行计划的影响

  • 772

在阅读了大量有关 SQL Server 中临时表和表变量之间差异的文章后，我正在尝试从主要使用临时表切换到主要使用表变量。（它们似乎更适合我通常使用的查询类型。）

在这些查询中，表包含驱动查找过程的唯一标识符。我的习惯是，在使用临时表时，包含一个PRIMARY KEY约束，以便查询优化器知道它不会看到任何重复项。PRIMARY KEY但是，鉴于优化器（在大多数情况下，对于我的查询）假定表变量仅包含一行*，根据定义这是唯一的，如果存在约束，查询优化器是否会做出不同的选择？

* 从技术上讲，它假设没有行，但是用一个替换了零。（因为零与估计过程的其余部分交互非常差。）但这也取决于在编译查询时是否填充表变量。这里有一些背景信息：What's the difference between a temp table and table variable in SQL Server? .

我目前正在使用 SQL Server 2014，但如果新版本的行为发生变化，我会很好奇。

正如已经指出的那样，PRIMARY KEY聚集索引附带了一个约束，它为查询优化器提供了更多关于如何从表变量中获取数据的选择。我知道这一点并考虑了查询计划的其余部分。但是在试图澄清我的问题之后，我认为我试图提出的问题过于宽泛，而且可能特别针对我的极端情况。（除了对 5000 亿行表的导航类型查询，期望亚秒级性能。）所以我将按原样保留我的问题。

sql-server optimization

3 个回答

  1. PRIMARY KEY...如果存在约束，查询优化器是否会做出不同的选择？

    是的，它可能会。估计一行（理解估计可能不正确）不同于知道该表仅包含唯一值。例如，某些计划空间探索需要一把钥匙。

    一个好的一般经验法则是尽可能多地向优化器提供有关数据和查询任务的信息。如果有钥匙，请明确说明。在大多数情况下，声明密钥并不会增加很多成本（除了一些键盘工作之外）。

    我个人很少使用表变量。缺少统计信息（包括分布和密度）和基数信息（所有单独的考虑因素）向优化器提供的信息少于等效的临时表。我的经验是，表变量计划不能很好地适应随时间变化的环境。

    我只在有特殊原因时才使用表变量，以确保从查询优化的角度来看它总是足够的。只有你有足够的关于你的数据库和查询的信息来说明你的情况是否正确。

    这个问题相当广泛（没有具体的例子），这个答案也是如此。

    • 7
  2. Best Answer

    由于在表变量上声明 PRIMARY KEY 会隐式地为键列创建索引（事实上，这是在 SQL Server 2014 之前索引表变量的唯一方法），它的存在肯定会对生成的查询计划产生影响。优化器将在适当的时候使用那个主键索引。通过在启用执行计划的情况下运行这个简短的脚本，您可以看到实际效果 - 表扫描将更改为聚集索引查找：

    --No primary key/index
DECLARE @t1 TABLE (
    id int NOT NULL,
    data varchar(50) NOT NULL
)

INSERT INTO @t1 VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee')

SELECT * FROM @t1 WHERE id = 4

--With primary key/index
DECLARE @t2 TABLE (
    id int NOT NULL PRIMARY KEY CLUSTERED,
    data varchar(50) NOT NULL
)

INSERT INTO @t2 VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee')

SELECT * FROM @t2 WHERE id = 4

    现在，至于声明 PRIMARY KEY 而不是普通的 CLUSTERED INDEX（2014 年允许您这样做）是否会导致不同的查询计划？我不能权威地说。这个人为的测试仍然是一个聚集索引查找：

    DECLARE @t3 TABLE (
    id int NOT NULL,
    data varchar(50) NOT NULL,
    INDEX IX1 CLUSTERED (id)
)

INSERT INTO @t3 VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee')

SELECT * FROM @t3 WHERE id = 4

    我怀疑在对表变量使用非聚簇索引时，事情会变得更加不确定，优化器需要估计潜在 RID 查找的成本，并将它们与表扫描进行权衡。

    • 6

  3. 这个问题只询问唯一性，但聚集索引（由于PRIMARY KEY约束而创建）也做了一些非常有益的事情，为了使这些答案广泛适用，这似乎值得一提。

    聚簇索引对其键强加了顺序。因此，即使只读取表变量，如果查询优化器允许 SQL Server 稍后跳过它可能需要做的工作，查询优化器可能会决定保留该顺序。考虑这个简单的场景，例如：

    DECLARE @Driver TABLE (id int NOT NULL PRIMARY KEY CLUSTERED)
INSERT INTO @Driver VALUES
    (1), (2), (3)
DECLARE @DataTable TABLE (id int NOT NULL PRIMARY KEY, data varchar(50))
INSERT INTO @DataTable VALUES
    (1, 'partridge in a pear tree'),
    (2, 'turtle doves'),
    (3, 'french hens'),
    (4, 'calling birds'),
    (5, 'golden rings')
SELECT dt.*
    FROM @Driver AS d
    JOIN @DataTable AS dt ON dt.id = d.id
    ORDER BY dt.id DESC

    在这种情况下，SQL Server 能够使用聚集索引的顺序来获得所需的输出：

    身份证数据
---------- -------------------------------------- ----------
3只法国母鸡
2只斑鸠
梨树上的 1 只鹧鸪

    无需显式排序：

      |--嵌套循环（内部连接，外部参考：（[d].[id]））
       |--聚集索引扫描（对象：（@Driver AS [d]），向后排序）
       |--聚集索引查找（对象：（@DataTable AS [dt]），查找：（[dt]。[id] = @Driver.[id] as [d]。[id]）ORDERED FORWARD）

    请注意，SQL Server 能够利用聚簇索引顺序，尽管它与请求的顺序相反！

    在更复杂的场景中，聚簇索引提供的顺序可以允许使用流聚合运算符而不是哈希聚合运算符。（前者使用更少的内存并更快地开始返回数据。）如果读取也按特定顺序完成，磁盘 I/O 可能会更有效率。简而言之，排序可能有很多好处，具体取决于查询。

    • 1

相关问题