主页 / dba / 问题 / 52129
Accepted
GarethD
Asked: 2013-10-25 00:31:33 +0800 CST

持久计算列上的索引需要键查找来获取计算表达式中的列

  • 772

我在一个表上有一个持久计算列，它只是由连接列组成，例如

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, B, 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 IS NOT NULL并且我正在排序Comp，并且没有引用索引之外的任何列，所以计算列上的索引可用于扫描 t1 和 t2，但我看到了一个聚集索引扫描。

在此处输入图像描述

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

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 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

哪个给出了这个计划

在此处输入图像描述

这表明正在使用 Key 查找，其详细信息是：

在此处输入图像描述

现在，根据 SQL-Server 文档：

如果在 CREATE TABLE 或 ALTER TABLE 语句中将该列标记为 PERSISTED，则可以在使用确定性但不精确的表达式定义的计算列上创建索引。这意味着数据库引擎将计算值存储在表中，并在计算列所依赖的任何其他列更新时更新它们。数据库引擎在为列创建索引以及在查询中引用索引时使用这些持久化值。当数据库引擎无法准确证明返回计算列表达式的函数（尤其是在 .NET Framework 中创建的 CLR 函数）是否具有确定性和精确性时，此选项使您能够在计算列上创建索引。

因此，如果，正如文档所说“数据库引擎将计算值存储在表中”，并且该值也存储在我的索引中，为什么在未引用 A、B 和 C 时需要进行键查找来获取它们查询呢？我假设它们被用来计算 Comp，但是为什么呢？另外，为什么查询可以在 上使用索引t2，但不能在 上使用t1？

SQL Fiddle 上的查询和 DDL

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

sql-server sql-server-2008

3 个回答

  1. Best Answer

    为什么在查询中根本没有引用 A、B 和 C 时需要使用 Key Lookup 来获取它们？我假设它们被用来计算 Comp，但是为什么呢？

    列在查询计划中A, B, and C 被引用——它们被 seek on 使用T2。

    另外，为什么查询可以使用 t2 上的索引，而不能使用 t1 上的索引？

    优化器决定扫描聚集索引比扫描过滤的非聚集索引然后执行查找以检索列 A、B 和 C 的值更便宜。

    解释

    真正的问题是为什么优化器觉得需要为索引搜索检索 A、B 和 C。我们希望它Comp使用非聚集索引扫描读取列，然后在同一索引（别名 T2）上执行查找以定位 Top 1 记录。

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

    当优化器遇到相关子查询时，它会尝试将其“展开”到它发现更容易推理的形式。如果它找不到更有效的简化，它会求助于将相关子查询重写为应用（相关连接）：

    应用重写

    碰巧的是，这种应用展开将逻辑查询树放入了一种不能很好地与项目规范化配合使用的形式（稍后阶段，它看起来将通用表达式与计算列相匹配，等等）。

    在您的情况下，查询的编写方式与优化器的内部详细信息交互，因此扩展的表达式定义不匹配回计算列，并且您最终会得到一个引用列A, B, and 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 内列上的不等式测试Drejects NULLs，因此明显冗余的谓词WHERE T1.D IS NOT NULL被优化掉。

    如果没有该显式谓词，过滤索引匹配逻辑将决定它不能使用过滤索引。有很多方法可以解决第二个副作用，但最简单的可能是将交叉应用更改为外部应用（反映之前在相关子查询上执行的优化器重写的逻辑）：

    SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    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列两边：

    外部申请计划

    This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.

    Persisted computed columns

    As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:

    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;

    The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.

    • 23

  2. Although this might be a bit of a co-incidence due to the artificial nature of your test data, being as you mentioned SQL 2012 I tried a rewrite:

    SELECT  ID,
        Comp,
        D,
        D2 = LEAD(D) OVER(PARTITION BY COMP ORDER BY D)
FROM    dbo.T 
WHERE   D IS NOT NULL
ORDER BY Comp;

    这使用您的索引产生了一个不错的低成本计划，并且读取次数明显低于其他选项（并且您的测试数据的结果相同）。

    Plan Explorer 费用有四个选项：原始； 带提示的原创； 外申请和铅

    我怀疑您的真实数据更复杂，因此在某些情况下，此查询的行为在语义上与您的不同，但它确实表明有时新功能可以产生真正的影响。

    我确实尝试了一些更多样化的数据，并发现了一些匹配的场景，而另一些则不匹配：

    --Example 1: results matched
TRUNCATE TABLE dbo.t

-- Generate some more interesting test data
;WITH cte AS
(
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT T (A, B, C, D)
SELECT  'A' + CAST( a.rn AS VARCHAR(5) ),
        'B' + CAST( a.rn AS VARCHAR(5) ),
        'C' + CAST( a.rn AS VARCHAR(5) ),
        DATEADD(DAY, a.rn + b.rn, '1 Jan 2013')
FROM cte a
    CROSS JOIN cte b
WHERE a.rn % 3 = 0
 AND b.rn % 5 = 0
ORDER BY 1, 2, 3
GO


-- Original query
SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY D
            )
INTO #tmp1
FROM    dbo.T t1 
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;
GO

SELECT  ID,
        Comp,
        D,
        D2 = LEAD(D) OVER(PARTITION BY COMP ORDER BY D)
INTO #tmp2
FROM    dbo.T 
WHERE   D IS NOT NULL
ORDER BY Comp;
GO


-- Checks ...
SELECT * FROM #tmp1
EXCEPT
SELECT * FROM #tmp2

SELECT * FROM #tmp2
EXCEPT
SELECT * FROM #tmp1


Example 2: results did not match
TRUNCATE TABLE dbo.t

-- Generate some more interesting test data
;WITH cte AS
(
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT T (A, B, C, D)
SELECT  'A' + CAST( a.rn AS VARCHAR(5) ),
        'B' + CAST( a.rn AS VARCHAR(5) ),
        'C' + CAST( a.rn AS VARCHAR(5) ),
        DATEADD(DAY, a.rn, '1 Jan 2013')
FROM cte a

-- Add some more data
INSERT dbo.T (A, B, C, D)
SELECT A, B, C, D 
FROM dbo.T
WHERE DAY(D) In ( 3, 7, 9 )


INSERT dbo.T (A, B, C, D)
SELECT A, B, C, DATEADD( day, 1, D )
FROM dbo.T
WHERE DAY(D) In ( 12, 13, 17 )


SELECT * FROM #tmp1
EXCEPT
SELECT * FROM #tmp2

SELECT * FROM #tmp2
EXCEPT
SELECT * FROM #tmp1

SELECT * FROM #tmp2
INTERSECT
SELECT * FROM #tmp1


select * from #tmp1
where comp = 'A2-B2-C2'

select * from #tmp2
where comp = 'A2-B2-C2'
    • 6

  3. 当我尝试执行相同的操作时，得到了另一个结果。首先，我对没有索引的表的执行计划如下：在此处输入图像描述

    正如我们从聚集索引扫描 (t2) 中看到的，谓词用于确定需要返回的行（因为条件）：

    在此处输入图像描述

    添加索引时，不管是用 WITH 操作符定义与否，执行计划如下：

    在此处输入图像描述

    我们可以看到，聚集索引扫描被索引扫描所取代。正如我们在上面看到的，SQL Server 使用计算列的源列来执行嵌套查询的匹配。在聚集索引扫描期间，可以同时获取所有这些值（无需额外操作）。comp添加索引时，根据索引从表中（在主选择中）过滤必要的行，但仍然需要获取计算列的源列的值（最后操作嵌套循环） .

    在此处输入图像描述

    因此，使用 Key Lookup 操作 - 获取计算的源列的数据。

    PS 看起来像 SQL Server 中的一个错误。

    • -1

相关问题