主页 / user-7823

EBarr's questions

Martin Hope
EBarr
Asked: 2012-10-16 09:47:57 +0800 CST
  • 19

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

对我来说，索引视图似乎违背了我对优化器如何选择索引的理解。我以前见过这个问题，但是 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 行输出

11 行输出 - 两个查询相同

Profiler 输出
前 4 行没有任何提示。底部的 4 行正在使用提示。

探查器

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

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

使用提示时的简单计划。

使用提示 - 简单的执行计划

sql-server index-tuning
Martin Hope
EBarr
Asked: 2012-09-26 11:19:37 +0800 CST
  • 13

我经常在数据库中遇到一种情况，其中给定的表可以 FK 到许多不同的父表之一。我已经看到了解决这个问题的两种方法，但都不是个人满意的。我很好奇你还看到了哪些其他模式？有更好的方法吗？

一个人为的例子
假设我的系统有Alerts. 可以接收各种对象的警报——客户、新闻和产品。给定的警报可以针对一项且仅一项。无论出于何种原因，客户、文章和产品都在快速移动（或本地化），因此在创建警报时无法将必要的文本/数据提取到警报中。鉴于此设置，我已经看到了两种解决方案。

注意：下面的 DDL 适用于 SQL Server，但我的问题应该适用于任何 DBMS。

解决方案 1 -- 多个可为空的 FKey

在此解决方案中，链接到多表之一的表具有多个 FK 列（为简洁起见，以下 DDL 未显示 FK 创建）。 好的- 在这个解决方案中，我有外键很好。FK 的零可选性使得添加准确数据变得方便且相对容易。不好的查询不是很好，因为它需要N LEFT JOINS 或N UNION 语句来获取相关数据。在 SQL Server 中，特别是 LEFT JOINS 会阻止创建索引视图。

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    ProductID   int null,
    NewsID      int null,
    CustomerID  int null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

ALTER TABLE Alert WITH CHECK ADD CONSTRAINT CK_OnlyOneFKAllowed 
CHECK ( 
    (ProductID is not null AND NewsID is     null and CustomerID is     null) OR 
    (ProductID is     null AND NewsID is not null and CustomerID is     null) OR 
    (ProductID is     null AND NewsID is     null and CustomerID is not null) 
)

解决方案 2 - 每个父表中的一个 FK
在此解决方案中，每个“父”表都有一个到警报表的 FK。它使检索与父级关联的警报变得容易。不利的一面是，从警报到谁引用没有真正的链。此外，数据模型允许孤立警报——其中警报与产品、新闻或客户无关。同样，多个 LEFT JOIN 来找出关联。

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

这只是关系数据库中的生活吗？是否有您发现更令人满意的替代解决方案？

database-design