sql-server - 为什么当我更新未包含在索引中的视图值时，视图上的聚集索引会更新并重新排序？

Question

我有表属性：

attributeId     INT IDENTITY,
entity          NVARCHAR(150) NOT NULL,
rank            INT NOT NULL,
label           NVARCHAR(500) NOT NULL,
CONSTRAINT pk_attribute PRIMARY KEY CLUSTERED (attributeId)

表属性值：

attributeId         INT NOT NULL,
entityId            INT NOT NULL,
value               SQL_VARIANT NOT NULL,
CONSTRAINT pk_attributeValues PRIMARY KEY CLUSTERED (attributeId, entityId),

所以我提出了一个观点：

CREATE VIEW dbo.vw_attributevalues
WITH SCHEMABINDING, VIEW_METADATA
AS
    SELECT  a.entity, av.entityId, a.attributeId, av.value,
            a.rank, a.label,
    FROM    dbo.attribute a
    JOIN    dbo.attributeValue v
            ON a.attributeId = v.attributeId
GO

CREATE UNIQUE CLUSTERED INDEX idx_vw_attributevalues_for_entity_entityId
    ON dbo.vw_attributevalues (entity, entityId, attributeId) WITH (DATA_COMPRESSION = PAGE);
GO

当我更新排名（它不在索引中）时，它会更新索引并重新排序：

UPDATE dbo.attribute
SET rank = 25000
WHERE attributeId = 100011;

执行计划

这部分成本很高。我不明白为什么 sql server 重新排序和更新索引。

标签： sql-servertsql

Answer 1

由于索引视图是物化的，因此当视图中引用的任何物化对象被更新时，该视图上的关联索引也会被更新。在创建索引视图时，这是一个非常重要的考虑因素。

每提单：

在由大量索引视图或较少但非常复杂的索引视图引用的表上执行 DML1 时，这些引用的索引视图也必须更新。因此，DML 查询性能会显着降低，或者在某些情况下，甚至无法生成查询计划。在这种情况下，在生产使用之前测试您的 DML 查询，分析查询计划并调整/简化 DML 语句。

如 UPDATE、DELETE 或 INSERT 操作。

当您更新 dbo.attribute 时，您将更新两个索引：pk_attribute 和 idx_vw_attributevalues_for_entity_entityId。idx_vw_attributevalues_for_entity_entityId 使用三列集群键，其中包括entityNVARCHAR(150) 列。根据 dbo.attribute 和 dbo.attributeValue 中的记录数量，这将是一个代价高昂的更新。

请注意以下 DDL 和执行计划：

-- sample data
CREATE TABLE dbo.a(c INT PRIMARY KEY CLUSTERED);
CREATE TABLE dbo.b(c INT PRIMARY KEY CLUSTERED);
INSERT dbo.a(c) VALUES(1),(2),(3);
INSERT dbo.b(c) VALUES(1),(2),(5);
GO

-- Sample indexed view
CREATE VIEW dbo.vw_ab
WITH SCHEMABINDING AS
SELECT ac = a.c, bc = b.c
FROM   dbo.a
JOIN   dbo.b ON a.c < b.c;
GO
CREATE UNIQUE CLUSTERED INDEX uq_dbo_vw_ab ON dbo.vw_ab(ac, bc);
GO

在这里，我们加入 dbo.a 和 dbo.b，然后添加一个索引视图。请注意执行计划，当更新 dbo.a 或 dbo.b 时，我会更新每个表上的聚集索引以及视图上的索引。注意插入和相关的执行计划：

-- Inserts:
INSERT dbo.a(c) VALUES(20);
INSERT dbo.b(c) VALUES(50);

执行计划：

为了更好地理解为什么你的更新/插入/删除代价高昂，开始测试通过删除不同的索引，添加/修改数据，然后比较有和没有索引的影响。我怀疑 NVARCHAR(150) 列是在这里杀死你的原因。