专栏首页学习有记包含列的索引:SQL Server索引进阶 Level 5

包含列的索引:SQL Server索引进阶 Level 5

作者David Durant,2011/07/13

关于系列

本文属于Stairway系列:Stairway to SQL Server Indexes

索引是数据库设计的基础,并告诉开发人员使用数据库关于设计者的意图。 不幸的是,当性能问题出现时,索引往往被添加为事后考虑。 这里最后是一个简单的系列文章,应该使他们快速地使任何数据库专业人员“快速”。


前面的级别引入了聚簇和非聚簇索引,突出了以下各个方面:

  • 表中每一行的索引总是有一个条目(我们注意到这个规则的一个例外将在后面的级别中进行讨论)。 这些条目始终处于索引键序列中。
  • 在聚集索引中,索引条目是表的实际行。
  • 在非聚集索引中,条目与数据行分开; 由索引键列和书签值组成,以将索引键列映射到表的实际行。

前面句子的后半部分是正确的,但不完整。 在这个级别中,我们检查选项以将其他列添加到非聚集索引(称为包含列)。 在检查书签操作的级别6中,我们将看到SQL Server可能会单方面向您的索引添加一些列。

包括列

在非聚集索引中但不属于索引键的列称为包含列。 这些列不是键的一部分,因此不影响索引中条目的顺序。 而且,正如我们将会看到的那样,它们比键列造成的开销更少。

创建非聚集索引时,我们指定了与键列分开的包含列; 如清单5.1所示。

CREATE NONCLUSTERED INDEX FK_ProductID_ ModifiedDate
       ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
       INCLUDE (OrderQty, UnitPrice, LineTotal)

清单5.1:创建包含列的非聚集索引

在本例中,ProductID和ModifiedDate是索引键列,OrderQty,UnitPrice和LineTotal是包含的列。

如果我们没有在上面的SQL语句中指定INCLUDE子句,那么结果索引看起来应该是这样的:

ProductID   ModifiedDate   Bookmark

Page n:

707         2004/07/25        =>  
707         2004/07/26        =>  
707         2004/07/26        =>  
707         2004/07/26        =>  
707         2004/07/27        =>  
707         2004/07/27        =>  
707         2004/07/27        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  

Page n+1:

707         2004/07/29        =>  
707         2004/07/31        =>  
707         2004/07/31        =>  
707         2004/07/31        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  

但是,告诉SQL Server包含OrderQty,UnitPrice和LineTotal列时,索引如下所示:

:- Search Key Columns -:      :---  Included Columns  ---:     : Bookmark :

ProductID   ModifiedDate      OrderQty    UnitPrice   LineTotal       

Page n-1:

707         2004/07/29        1           34.99       34.99       =>  
707         2004/07/31        1           34.99       34.99       =>  
707         2004/07/31        3           34.99      104.97       =>  
707         2004/07/31        1           34.99       34.99       =>  
708         2001/07/01        5           20.19      100.95       =>  

Page n:

708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        2           20.19       40.38       =>  
708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        2           20.19       40.38       =>  

708         2001/12/01        7           20.19      141.33       =>  
708         2001/12/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  

Page n+1:

708         2002/01/01        2           20.19       40.38       =>  
708         2002/01/01        5           20.19      100.95       =>  
708         2002/02/01        1           20.19       20.19       =>  
708         2002/02/01        1           20.19       20.19       =>  
708         2002/02/01        2           20.19       40.38       =>  

检查显示的这个索引的内容,显然这些行按索引键列排序。例如,修改日期为2002年1月1日(以粗体突出显示)的产品708的五行在索引中是连续的,每隔一个ProductID / ModifiedDate组合的行也是如此。

你可能会问“为什么甚至包括列?为什么不简单地将OrderQty,UnitPrice和LineTotal添加到索引键?“索引中有这些列但索引键中没有这些列有几个优点,例如:

  • 不属于索引键的列不会影响索引内条目的位置。这反过来又减少了让他们在索引中的开销。例如,如果行中的ProductID或ModifiedDate值被修改,那么该行的条目必须在索引内重新定位。但是,如果行中的UnitPricevalue被修改,索引条目仍然需要更新,但不需要移动。
  • 在索引中查找条目所需的努力较少。
  • 指数的大小会略小。
  • 索引的数据分布统计将更容易维护。

当我们查看索引的内部结构以及由SQL Server维护的用于优化查询性能的一些附加信息时,大多数这些优势在以后的级别中将更有意义。

确定索引列是否是索引键的一部分,或只是包含的列,不是您将要做的最重要的索引决定。也就是说,频繁出现在SELECT列表中但不在查询的WHERE子句中的列最好放在索引的包含列部分。

成为覆盖指标

在级别4中,我们表示与AdventureWorks数据库的设计者达成协议,决定将SalesOrderID / SalesOrderDetailID作为SalesOrderDetail表的聚集索引。针对此表的大多数查询都将请求按销售订单编号排序或分组的数据。然而,可能来自仓库人员的一些查询将需要产品序列中的信息。这些查询将受益于清单5.1所示的索引。

为了说明在索引中包含列的潜在好处,我们将查看两个针对SalesOrderDetailtable的查询,每个查询我们将执行三次,如下所示:

  • 运行1:没有非聚集索引
  • 运行2:使用不包含列的非聚簇索引(只有两个关键列)
  • 运行3:使用清单5.1中定义的非聚集索引

正如我们在前面的级别所做的那样,我们再次使用读取次数作为主要度量标准,但是我们也使用SQL Server Management Studio的“显示实际执行计划”选项来查看每个执行的计划。这会给我们一个额外的指标:在非读取活动上花费的工作量的百分比,例如在将相关数据读入内存之后进行匹配。这使我们更好地了解查询的总成本。

测试第一个查询:产品的活动总数

清单5.2中显示的第一个查询是按特定产品的日期提供活动总计的查询。

SELECT  ProductID ,
        ModifiedDate ,
        SUM(OrderQty) AS 'No of Items' ,
        AVG(UnitPrice) 'Avg Price' ,
        SUM(LineTotal) 'Total Value'
FROM    Sales.SalesOrderDetail
WHERE   ProductID = 888
GROUP BY ProductID ,
        ModifiedDate ;

清单5.2:“按产品的活动总计”查询

由于索引可以影响查询的性能,但不影响结果; 对这三个不同的索引方案执行这个查询总是产生下面的行集合:

ProductID   ModifiedDate    No of Rows  Avg Price         Total Value

----------- ------------    ----------- -----------------------------
888         2003-07-01      16          602.346           9637.536000
888         2003-08-01      13          602.346           7830.498000
888         2003-09-01      19          602.346           11444.574000
888        2003-10-01       2           602.346           1204.692000
888         2003-11-01      17          602.346           10239.882000
888         2003-12-01      4           602.346           2409.384000
888         2004-05-01      10          602.346           6023.460000
888         2004-06-01      2           602.346           1204.692000

这八行输出从表中的三十九个“ProductID = 888”行聚合而成,每个日期有一个或多个“ProductID = 888”销售的输出行。进行测试的基本方案是 如代码5.3所示。 在运行任何查询之前,请确保您运行SET STATISTICS IO ON。

IF EXISTS ( SELECT  1
            FROM    sys.indexes
            WHERE   name = 'FK_ProductID_ModifiedDate'
                    AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') ) 
    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;
GO

--RUN 1: Execute Listing 5.2 here (no non-clustered index)

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID, ModifiedDate) ;

--RUN 2: Re-execute Listing 5.2 here (non-clustered index with no include)

IF EXISTS ( SELECT  1
            FROM    sys.indexes
            WHERE   name = 'FK_ProductID_ModifiedDate'
                    AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') ) 
    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;
GO

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
INCLUDE (OrderQty, UnitPrice, LineTotal) ;

--RUN 3: Re-execute Listing 5.2 here (non-clustered index with include)

清单5.3:测试“按产品的活动总计”查询

表5.1显示了对每个索引方案执行查询所需的相对工作量。

Run 1: No Nonclustered Index

Table 'SalesOrderDetail'. Scan count 1, logical reads 1238. Non read activity: 8%.

Run 2:

Index – No Included Columns Table 'SalesOrderDetail'. Scan count 1, logical reads 131. Non read activity: 0%.

Run 3:

With Included Columns Table 'SalesOrderDetail'. Scan count 1, logical reads 3. Non read activity: 1%.

表5.1:使用不同的非聚集索引可运行第一次查询三次的结果

正如你可以从这些结果看到的:

  • 运行1需要完整扫描SalesOrderDetail表; 每一行都必须阅读和检查,以确定是否应该参与结果。
  • 运行2使用非聚集索引为39个请求的行快速查找书签,但它必须从表中单独检索每个行。
  • 运行3在非聚集索引中找到了所需的所有内容,并以最有利的顺序 - 产品ID中的ModifiedDate。 它迅速跳到第一个要求的条目,阅读了39个连续的条目,对每个条目进行了总计算,读取完成。

测试第二个查询:基于日期的活动总数

我们的第二个查询与第一个查询是相同的,除了WHERE子句的更改。 这次仓库正在根据日期而不是产品请求信息。 我们必须过滤最右边的搜索键列ModifiedDate; 而不是最左边的一列ProductID。 新的查询如清单5.4所示。

SELECT  ModifiedDate ,
        ProductID ,
        SUM(OrderQty) 'No of Items' ,
        AVG(UnitPrice) 'Avg Price' ,
        SUM(LineTotal) 'Total Value'
FROM    Sales.SalesOrderDetail
WHERE   ModifiedDate = '2003-10-01'
GROUP BY ModifiedDate ,
        ProductID ;

清单5.4:“按日期的活动总计”查询

生成的行集部分是:

ProductID   ModifiedDate    No of Items Avg Price             Total Value
----------- ------------    ----------- --------------------- ----------------
                                   :
                                   :
782         2003-10-01      62          1430.9937             86291.624000
783         2003-10-01      72          1427.9937             100061.564000
784         2003-10-01      52          1376.994              71603.688000
792         2003-10-01      12          1466.01               17592.120000
793         2003-10-01      46          1466.01               67436.460000
794         2003-10-01      37          1466.01               54242.370000
795         2003-10-01      22          1466.01               32252.220000
                                   :
                                   :
(164 row(s) affected)

WHERE子句将表格过滤为1492个符合条件的行; 其中,分组时,产生了164行的产出。

要运行测试,请按照代码5.3中所述的相同方案,但使用代码清单5.4中的新查询。 结果是表5.2显示了对每个索引方案执行查询所需的相对工作量。

Run 1:

No Nonclustered Index Table 'SalesOrderDetail'. Scan count 1, logical reads 1238. Non read activity: 10%.

Run 2:

With Index – No Included Columns Table 'SalesOrderDetail'. Scan count 1, logical reads 1238. Non read activity: 10%.

Run 3:

With Included Columns Table 'SalesOrderDetail'. Scan count 1, logical reads 761. Non read activity: 8%.

表2:使用可用的不同非聚簇索引三次运行第二个查询的结果

第一次和第二次测试都是相同的计划。对SaleOrderDetail表的完整扫描。由于第4级中详细说明的原因,WHERE子句没有足够的选择性从非覆盖索引中受益。而且,包含任何一个组的行都散布在整个表格中。正在读表时,每一行都必须与其组相匹配。以及消耗处理器时间和内存的操作。

第三个测试发现了它在非聚集索引中需要的一切;但与前面的查询不同,它没有找到索引内连续的行。构成每个单独组的行在索引内是连续的;但是这些群体本身分散在指数的长度上。因此,SQL Server扫描索引。

扫描索引而不是表格有两个好处:

  • 索引小于表,需要更少的读取。
  • 行已经分组,需要较少的非阅读活动。

结论

包含的列使非聚集索引能够覆盖各种查询的索引,从而提高这些查询的性能; 有时相当戏剧性。 包含的列增加了索引的大小,但在开销方面增加了很少的内容。 每当你创建一个非聚集索引,特别是在一个外键列上时,问问自己 - “我应该在这个索引中包含哪些额外的列?


本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 深入非聚集索引:SQL Server索引进阶 Level 2

    Woodson
  • 聚集索引:SQL Server 进阶 Level 3

    Woodson
  • SQL Server索引简介:SQL Server索引进阶 Level 1

    Woodson
  • 深度剖析一站式分布式事务方案Seata-Cient

    在之前的文章中已经介绍过Seata的总体介绍,如何使用以及Seata-Server的原理分析,有兴趣的可以阅读下面的文章:

    用户5397975
  • Flutter 学习笔记6 - 常用 Widgets

    下面的 widget 分为两类:widgets library中的标准 widget 和 Material Components library 中的专用 wi...

    七适散人
  • ElasticSearch排序引起的all shards failed异常原因分析

    在我们的日志系统里需要一些系统索引,这些系统索引在应用初始化的时候就会被添加到ElasticSearch中去,这些在ElasticSearch中的系统索引在没有...

    九州暮云
  • 简单的考勤系统

    连接数据库类 package com.lianrui.it; import java.sql.Connection; import java.sql.Driv...

    用户1220053
  • Spring Cloud netflix ribbon源码分析

    用户1215919
  • MongoDB Driver 简单的CURD

    c#中我们可以使用MongoDB.Driver驱动进行对MongoDB数据库的增删改查.

    莫问今朝
  • Netty 之 ChannelPipeline 源码解析

    ChannelPipleline 是 ChannelHandler 的管理容器,它内部维护了一个 ChannelHandler 的链表,可以方便的实现 Chan...

    java404

扫码关注云+社区

领取腾讯云代金券