主页 / dba / 问题 / 302443
Accepted
Charlieface
Asked: 2021-11-11 15:21:48 +0800 CST

插入父行时未省略外键的索引视图维护

  • 772

TL;博士;

给定一个索引视图，该视图在JOIN两个表之间具有一个外键关系以及父表上的谓词。 当插入外键的父表时，编译器将索引维护添加到计划中，即使可以证明不存在匹配的行。WHERE

这是一个错误，还是错过了优化？或者我是否存在一些逻辑或代数谬误？

设置

CREATE TABLE Parent (Id int identity primary key, SomeCol bit not null, OtherCol int not null);

CREATE TABLE Child (Id int identity primary key, ParentId int not null references Parent (Id) INDEX IX_Parent NONCLUSTERED);

CREATE VIEW dbo.vChild
WITH SCHEMABINDING
AS
SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
-- WHERE p.SomeCol = 0;  -- problem dependent on this line

CREATE UNIQUE CLUSTERED INDEX CX_vChild ON vChild (Id)

db<>fiddle with WHERE
db<>fiddle without WHERE

在这个阶段，UPDATE视图中的任何影响列以及视图表中的任何一个DELETE都非常正确地触发视图维护。编译器将获取修改后的行，将它们汇总并通过视图的连接提供它们，将任何结果输出到视图的索引中。

对于对 的插入也可以这样说Child，因为可能已经存在一行Parent（符合WHERE），因此新Child行可能符合联接的条件。

问题

插入时Parent，证明不需要做索引维护。由于外键关系，匹配行还不能存在Child，因此插入中没有符合视图条件的行。

如果您要运行以下脚本，您将看到未完成任何视图维护。

INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

粘贴计划

在此处输入图像描述

很明显，编译器可以推断这里不需要视图维护。

但是，如果您WHERE p.SomeCol = 0在视图定义中取消注释该行，您会突然得到视图维护。因此，向视图添加另一列，它不是连接列并且没有外键关系，会导致这种情况。尽管应用了相同的关系逻辑：插入仍然应该证明不符合视图的条件，因为外键列仍然存在。

粘贴计划

在此处输入图像描述

奇怪的是，编译器仍然可以识别插入不符合视图条件的某些情况（尽管这个特定示例是自动参数化的）。

在这里，编译器识别SomeCol失败WHERE，并且不需要进行索引维护。

INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

粘贴计划

在此处输入图像描述

sql-server t-sql

1 个回答

  1. Best Answer

    优化器不会做你描述的那种推理。

    相反，它依赖于一组标准的常用、易于实现、快速检查的功能，如矛盾检测和冗余连接删除，以产生有用的简化。

    正是这些模块化特征之间的相互作用，才会产生明显复杂的行为，人们有时会误认为是广泛的优化和深度的语义分析。

    您观察到的行为可以通过参考这些标准优化器功能以及使用delta algebra维护索引视图的方式来解释。

    此查询仅涉及执行计划中的子表，因为父行保证存在：

    SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId

    当您在 Parent 表上添加谓词时，这在逻辑上不再可能：

    SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
WHERE p.SomeCol = 0;  -- problem dependent on this line

    相同的底层机制负责删除通过 delta 代数产生的维护子树中的连接。当调试输出包括：

    Full Join removed for table TBL: dbo.Parent

    对于插入，产生的增量与上述示例性查询非常相似。对于更新、删除或合并，增量可能不同，因为维护视图可能需要不同的信息。（在您的特定示例中，很难看到 Parent 表上可能会更新什么，但问题主要是关于插入。）

    当该WHERE子句存在时，该谓词的过滤器将出现在计划的维护部分中。

    对于此语句，检测到 SomeCol = 0 上的过滤器与插入中指定的 SomeCol 值之间存在矛盾。这种矛盾导致整个维护子树被保证为空，因此被删除：

    INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

    对于这个说法，没有矛盾，但是Filter是多余的，所以去掉了。维护子树不保证为空，因此不会被删除：

    INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

    如果您使用例如局部变量或参数而不是文字，过滤器将重新出现。

    给出的例子不是简单的参数化的。它有资格考虑，但参数化并不确定是否安全。不要被@1文本中存在的标记所误导。请参阅为什么具有 FULL 优化的计划显示简单的参数化？

    • 3

相关问题