sql-server – 选择Indexed View的Clustered Index有哪些因素？

发布时间：2020-12-12 16:58:20 所属栏目：MsSql教程来源：网络整理

导读：简要地他们查询优化器选择索引视图索引的因素是什么？对我来说,索引视图似乎无法理解我对Optimizer如何选择索引的理解.我见过this asked before,但OP并不太受欢迎.我真的在寻找路标,但我会编写一个伪示例,然后使用大量的DDL,输出和示例发布真实示例. Assume

简要地
他们查询优化器选择索引视图索引的因素是什么？

对我来说,索引视图似乎无法理解我对Optimizer如何选择索引的理解.我见过this asked before,但OP并不太受欢迎.我真的在寻找路标,但我会编写一个伪示例,然后使用大量的DDL,输出和示例发布真实示例.

Assume I’m using Enterprise 2008+,understand with(noexpand)

伪示例

以这个伪示例为例：我创建了一个包含22个连接,17个过滤器和一个跨越一堆1000万行表的马戏团小马的视图.这个观点是昂贵的(是的,有大写字母E)来实现.我将SCHEMABIND和索引视图.然后是SELECT a,b FROM AnIndexedView WHERE theClusterKeyField< 84.在优化器逻辑中,我无法执行基础连接. 结果：
>没有提示：4825读取720行,47 cpu超过76ms,和
估计子树成本为0.30523.
>使用提示：17个读取,720行,超过4ms的15个CPU,以及一个
估计子树成本为0.007253

那么这里发生了什么？我已经在Enterprise 2008,2008-R2和2012中尝试过了.我可以想到使用视图索引的每个指标都非常有效.我没有参数嗅探问题或倾斜数据,因为这是广告.

真实(长)的例子

除非你是一个自虐的人,否则你可能不需要或不想阅读这部分内容.

版本
是的,企业.

Microsoft SQL Server 2012 – 11.0.2100.60 (X64) Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 6.2 (Build 9200: ) (Hypervisor)

风景

CREATE VIEW dbo.TimelineMaterialized    WITH SCHEMABINDING
AS
SELECT  TM.TimelineID,TM.TimelineTypeID,TM.EmployeeID,TM.CreateUTC,CUL.CultureCode,CASE 
           WHEN TM.CustomerMessageID    > 0 THEN TM.CustomerMessageID
           WHEN TM.CustomerSessionID    > 0 THEN TM.CustomerSessionID
           WHEN TM.NewItemTagID         > 0 THEN TM.NewItemTagID
           WHEN TM.OutfitID             > 0 THEN TM.OutfitID
           WHEN TM.ProductTransactionID > 0 THEN TM.ProductTransactionID
           ELSE 0 END  As HrefId,CASE 
          WHEN TM.CustomerMessageID    > 0 THEN IsNull(C.Name,'N/A')   
          WHEN TM.CustomerSessionID    > 0 THEN IsNull(C.Name,'N/A')
          WHEN TM.NewItemTagID         > 0 THEN IsNull(NI.Title,'N/A')
          WHEN TM.OutfitID             > 0 THEN IsNull(O.Name,'N/A')
          WHEN TM.ProductTransactionID > 0 THEN IsNull(PT_PL.NameLocalized,'N/A')
                 END as HrefText

FROM       dbo.Timeline TM
INNER JOIN dbo.CustomerSession    CS    ON TM.CustomerSessionID    = CS.CustomerSessionID
INNER JOIN dbo.CustomerMessage    CM    ON TM.CustomerMessageID    = CM.CustomerMessageID
INNER JOIN dbo.Outfit             O     ON PO.OutfitID             = O.OutfitID
INNER JOIN dbo.ProductTransaction PT    ON TM.ProductTransactionID = PT.ProductTransactionID
INNER JOIN dbo.Product            PT_P  ON PT.ProductID            = PT_P.ProductID
INNER JOIN dbo.ProductLang        PT_PL ON PT_P.ProductID          = PT_PL.ProductID
INNER JOIN dbo.Culture            CUL   ON PT_PL.CultureID         = CUL.CultureID
INNER JOIN dbo.NewsItemTag        NIT   ON TM.NewsItemTagID        = NIT.NewsItemTagID
INNER JOIN dbo.NewsItem           NI    ON NIT.NewsItemID          = NI.NewsItemID
INNER JOIN dbo.Customer           C     ON  C.CustomerID = CASE 
                                             WHEN TM.TimelineTypeID = 1 THEN CM.CustomerID 
                                             WHEN TM.TimelineTypeID = 5 THEN CS.CustomerID
                                             ELSE 0 END

WHERE        CUL.IsActive = 1

聚集指数

CREATE UNIQUE CLUSTERED INDEX PK_TimelineMaterialized  ON 
                   TimelineMaterialized (EmployeeID,CreateUTC,CultureCode,TimelineID)

测试SQL

-- NO HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *                 --yes yes,star is bad ...just a test example
FROM    TimelineMaterialized TM 
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

-- WITH HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *               
FROM    TimelineMaterialized TM with(noexpand)
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

结果= 11行输出

Profiler输出
前4行没有提示.底部的4行是使用提示.

执行计划
GitHub Gist for both Execution Plans in SQLPlan format

没有提示执行计划 – 为什么不使用我给你的SQL先生的聚集索引？它集中在3个过滤器字段上.尝试一下,你可能会喜欢它.

使用提示时的简单计划.

解决方法

匹配索引视图是一个相对昂贵的操作*,因此优化器首先尝试其他快速简单的转换.如果那些碰巧产生了一个便宜的计划(在你的情况下为0.05个单位),优化会提前结束.赌注是持续优化将消耗比保存更多的时间.请记住,优化器的主要目标是快速实现“足够好”的计划.

在视图上使用聚簇索引本身并不昂贵,但是将逻辑查询树与潜在的索引视图匹配的过程可以是.正如我在另一个问题的评论中提到的,查询中的视图引用在优化之前被扩展,因此优化器不知道你首先在视图上编写了查询 – 它只看到了展开的树(就像视图已被列入内容).

“足够好的计划”意味着优化者找到了一个体面的计划,并在探索阶段提前停止. “TimeOut”意味着它超过了在当前阶段开始时将其自身设置为“预算”的优化步骤数.

预算是根据前一阶段中找到的最佳计划的成本设定的.使用如此低成本的查询(0.05),预算移动的数量将非常小,并且在给定样本查询中涉及的连接数量的情况下通过常规转换很快耗尽(例如,有很多方法可以重新排列内部连接) .

如果您有兴趣了解为什么索引视图匹配代价昂贵,因此留待优化的后期阶段和/或仅考虑更昂贵的查询,则有关于主题here(pdf)和here(citeseer)的两篇Microsoft研究论文).

另一个相关因素是索引视图匹配在优化阶段0(事务处理)中不可用.

进一步阅读：

Indexed Views and Statistics

*仅适用于企业版(或同等版本)

（编辑：李大同）

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