选择索引视图的聚集索引有哪些因素？

简而言之
，查询优化器选择索引视图的索引有哪些因素？

对我来说，索引视图似乎违背了我对优化器如何选择索引的理解。我以前见过这个问题，但是 OP 并没有受到太大的欢迎。 我真的在寻找路标，但我会编造一个伪示例，然后发布带有大量 DDL、输出、示例的真实示例。

假设我使用的是 Enterprise 2008+，了解with(noexpand)

伪示例

以这个伪示例为例：我创建了一个包含 22 个连接、17 个过滤器和一个跨越 1000 万行表的马戏团小马的视图。这种观点实现起来很昂贵（是的，大写的 E）。我将对视图进行 SCHEMABIND 和索引。然后一个 SELECT a,b FROM AnIndexedView WHERE theClusterKeyField < 84. 在我无法理解的优化器逻辑中，执行了底层连接。

结果：

• 无提示：720 行 4825 次读取，47 cpu 超过 76 毫秒，估计子树成本为 0.30523。
• 提示：17 次读取，720 行，15 个 CPU 超过 4 毫秒，估计子树成本为 0.007253

那么这里发生了什么？我已经在Enterprise 2008、2008-R2 和 2012 中尝试过。根据我能想到的每一个指标，使用视图索引的效率要高得多。我没有参数嗅探问题或倾斜数据，因为这是临时性的。

一个真实（长）的例子

除非你是一个触摸受虐狂，否则你可能不需要或不想阅读这部分。

是的，企业版。

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 版权所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n 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 行正在使用提示。

SQLPlan 格式的两个执行计划的执行计划GitHub Gist

No Hint 执行计划——为什么不使用 SQL 先生给你的聚集索引呢？它聚集在 3 个过滤器字段上。试试看，你可能会喜欢。

使用提示时的简单计划。