sql-server – Persisted Computed列上的索引需要键查找以获取计

发布时间：2020-12-12 06:21:00 所属栏目：MsSql教程来源：网络整理

导读：我在表上有一个持久的计算列,它简单地组成了连续的列,例如 CREATE TABLE dbo.T ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,A VARCHAR(20) NOT NULL,B VARCHAR(20) NOT NULL,C VARCHAR(20) NOT NULL,D DATE NULL,E VARCHAR(20) NULL,Com

我在表上有一个持久的计算列,它简单地组成了连续的列,例如

CREATE TABLE dbo.T 
(   
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,A VARCHAR(20) NOT NULL,B VARCHAR(20) NOT NULL,C VARCHAR(20) NOT NULL,D DATE NULL,E VARCHAR(20) NULL,Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL 
);

在这个Comp不是唯一的,D是从A,B,C的每个组合的日期开始的有效,因此我使用以下查询来获得每个A,C的结束日期(基本上是下一个开始日期为相同的Comp)值：

SELECT  t1.ID,t1.Comp,t1.D,D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

然后,我在计算列中添加了一个索引,以协助此查询(以及其他查询)：

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp,D) WHERE D IS NOT NULL;

然而,查询计划让我感到惊讶.我会想到,因为我有一个where子句,声明D不是NULL并且我按Comp排序,并且没有引用索引之外的任何列,计算列上的索引可用于扫描t1和t2,但是我看到了聚集索引扫描.

所以我强迫使用这个索引来看看它是否产生了更好的计划：

SELECT  t1.ID,D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

哪个给了这个计划

这表明正在使用Key查找,其详细信息如下：

现在,根据SQL-Server文档：

You can create an index on a computed column that is defined with a deterministic,but imprecise,expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table,and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column,and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions,particularly a CLR function that is created in the .NET Framework,is both deterministic and precise.

因此,如果文档说“数据库引擎将计算值存储在表中”,并且该值也存储在我的索引中,为什么在未在引用时获取A,B和C所需的密钥查找查询完全？我假设它们被用来计算Comp,但为什么呢？另外,为什么查询在t2上使用索引,而在t1上却没有？

Queries and DDL on SQL Fiddle

注：我已经标记了SQL Server 2008,因为这是我的主要问题所在的版本,但我在2012年也得到了相同的行为.

解决方法

Why is a Key Lookup required to get A,B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp,but why?

列A,B和C在查询计划中引用 – 它们由T2上的搜索使用.

Also,why can the query use the index on t2,but not on t1?

优化器决定扫描聚簇索引比扫描筛选的非聚簇索引更便宜,然后执行查找以检索列A,B和C的值.

说明

真正的问题是为什么优化器认为需要为索引搜索检索A,B和C.我们希望它使用非聚集索引扫描读取Comp列,然后对同一索引(别名T2)执行搜索以找到Top 1记录.

查询优化器在优化开始之前扩展计算列引用,以使其有机会评估各种查询计划的成本.对于某些查询,扩展计算列的定义允许优化程序找到更有效的计划.

当优化器遇到相关子查询时,它会尝试将其“展开”到它更容易推理的表单.如果找不到更有效的简化,它会将相关子查询重写为apply(相关联接)：

恰好这种应用展开会将逻辑查询树放入一个不能很好地与项目规范化相关的形式(后续阶段看起来将一般表达式与计算列匹配等).

在您的情况下,编写查询的方式与优化程序的内部细节交互,使得展开的表达式定义不匹配回计算列,最终得到引用列A,B和C的搜索,而不是计算列,Comp.这是根本原因.

解决方法

解决此副作用的一个想法是将查询手动编写为应用：

SELECT
    T1.ID,T1.Comp,T1.D,CA.D2
FROM dbo.T AS T1
CROSS APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

不幸的是,这个查询不会像我们希望的那样使用过滤后的索引. apply中的D列上的不等式测试拒绝NULL,因此显然冗余的谓词WHERE T1.D IS NOT NULL被优化掉了.

如果没有该显式谓词,过滤后的索引匹配逻辑会决定它不能使用过滤后的索引.有很多方法可以解决这个第二个副作用,但最简单的方法是将交叉应用更改为外部应用(镜像重写优化器先前在相关子查询上执行的逻辑)：

SELECT
    T1.ID,CA.D2
FROM dbo.T AS T1
OUTER APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

现在优化器不需要使用apply rewrite本身(因此计算的列匹配按预期工作)并且谓词也没有被优化掉,所以过滤的索引可以用于两个数据访问操作,并且seek使用Comp双方专栏：

这通常比在过滤索引中添加A,B和C作为INCLUDEd列更受欢迎,因为它解决了问题的根本原因,并且不需要不必要地扩展索引.

持久计算列

作为旁注,如果您不介意在CHECK约束中重复其定义,则无需将计算列标记为PERSISTED：

CREATE TABLE dbo.T 
(   
    ID integer IDENTITY(1,1) NOT NULL,A varchar(20) NOT NULL,B varchar(20) NOT NULL,C varchar(20) NOT NULL,D date NULL,E varchar(20) NULL,Comp AS A + '-' + B + '-' + C,CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),CONSTRAINT PK_T_ID 
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp,D) 
WHERE D IS NOT NULL;

如果要使用NOT NULL约束或在CHECK约束中直接引用Comp列(而不是重复其定义),则计算列只需要在这种情况下为PERSISTED.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!