【翻译】SQL Server索引进阶：第五级，包含列

louyaoluan

　　原文地址：
　　Stairway to SQL Server Indexes: Level 5, Included Columns
　　本文是SQL Server索引进阶系列（Stairway to SQL Server Indexes）的一部分。
　　之前的文章介绍了聚集索引和非聚集索引，包含下面几条很重要的内容:
　　

• 表中的每一行在索引中总是有一个入口（这条规则有一个意外，在后面的级别中我们会讲到）。这些入口总是用索引键排序。
  
• 在聚集索引中，索引的入口就是表的实际行。
  
• 在非聚集索引中，入口和数据行是分开的，索引由索引键列和标签组成，标签是索引键列到表数据行的映射。
  

　　第三句的后半部分是正确的，但是不完整。今天我们将测试在非聚集索引中包括额外列的情况，这些额外列叫做“包含列”。在第六级中，将会测试标签的操作，我们将会看到SQL Server可能会单方面的给你的索引添加一些列。
　　包含列
　　

　　
在非聚集索引中有一些列，不是索引键的一部分，被叫做“包含列”。这些列不是键的一部分，不影响索引入口的排序。同样，我们将会看到，相比较键列，它们带来的消耗也较小。
　　

　　
在创建非聚集索引的时候，在索引列之外，需要单独的指定包含列，就像下面的一样。
　　

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

　　

　　

　　
在上面的例子中，ProductID和ModifiedDate是索引键的列，OrderQty，UnitPrice和LineTotal是包含列。
　　

　　
如果我们不指定包含列，索引可能是下面的样子。
　　

　　
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        =>
　　

　　
但是，如果我们指定了包含列，索引就是下面的样子。
　　

　　
:- 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       =>
　　

　　
你可能会问：“为什么要有包含列？为什么不简单的把OrderQty，UnitPrice和LineTotal加入索引键？”。把这些列加入索引，但是不作为索引键，有些面的好处：
　　

• 这些列不是索引键的一部分，它们不会影响索引入口的排序。反过来，减少它们在索引中的消耗。举个例子，如果需要修改一行数据的ProductID或者ModifiedDate的值，这行在索引中对应的入口就会被重新分配。但是如果修改UnitPrice的值，只会更新索引的入口，但是不需要移动。
  
• 分配索引的入口带来的消耗会更小。
  
• 索引占用的空间会更小。
  
• 索引的分布统计的维护会更容易。
  

　　大部分的有点在后面的级别中会更有意义，后面我们会看到索引的内部结构，SQL Server为了优化查询而维护的一些额外信息。
　　

　　
决定一个索引列是否是索引键的一部分，还是只是一个包含列，在你做索引的决定的时候不是最重要的。也就是说，那些经常出现在select中的，而不是where子句中的列，最好是放在包含列中。
　　

　　
在第四级中，我们对于设计者在SalesOrderDetail表建立SalesOrderID/SalesOrderDetailID的聚集索引的决定表示支持。对于这张表的大部分查询都是有序的，或者是以订单分组的。但是，也有一部分的查询，可能是从仓库的工作人员发出的，会需要产品序列的信息。这些查询将会从本文开头创建的索引中受益。
　　

　　为了说明包含列的带来的好处，我们看一下在SalesOrderDetail表执行的两个查询，每个查询会执行三次：

• 第一次，没有非聚集索引。
  
• 第二次，有非聚集索引，但是没有包含列，只有两个键列。
  
• 第三次，使用文章开头定义的非聚集索引，既有索引键，也有包含列。
  

　　和我们之前的文章一样，再次使用IO读取的次数作为主要的衡量指标，但是我们也是用SQL Server管理器的“显示实际的执行计划”选项来查看每次执行的执行计划。这给我们增加了一个衡量指标：消耗在非读取活动上的工作量所占的百分比，例如，在读取到内存之后，进行数据匹配的工作。这给我们一个，关于查询总共的消耗，更好的理解。
　　

　　测试第一个查询：从产品角度产生的全部活动
　　

　　
查询语句如下
　　

　　

　　

• 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 ;
  

　　

　　
因为索引影响的是查询的性能，而不是查询的结果。在三个不同索引下查询的结果都是下面的内容。
　　

　　
上面的8行结果，是在39行ProductID=888的基础上聚合而成的。在每次查询之前都需要做一些事前工作，还需要打开IO统计，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)
  

　　

　　
三次查询的统计结果如下：
　　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%.
　　从上面的结果可以看出：

• 第一次，需要全表扫描，每一行都会被读取，来判断是否满足查询的条件。
  
• 第二次，通过非聚集索引快速的定位，只有39次请求，但是还是要从表中获取其他列的信息。
  
• 第三次，非聚集索引包括了请求的全部信息，是一个最优的排序。直接跳到第一个入口，然后连续的读取39个入口，进行聚合计算，然后返回结果就行了。
  

　　测试第二个查询：从日期角度产生的全部活动
　　查询语句如下
　　

• 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 ;
  

　　

　　查询的结果如下
　　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行结果。
　　查询的统计如下
　　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%.
　　第一次和第二次查询的执行计划是相同的，都是全表扫描。具体原因在第四级中已经介绍过，where子句没有从非聚集索引中受益。同样的，每一组在表中都很分散，读取表的时候，需要读取每一行来查看匹配的组，这些操作会消耗处理器时间和内存。
　　第三次查询在索引中发现了所需要的全部信息，但是不想第一个查询，发现在索引中的行，不是连续的。
　　扫描索引，而不是扫描表，有两个好处：

• 索引占用的空间比表小，需要的读取更少。
  
• 行已经被分组，需要的非读取活动更少。
  

　　非读取活动，就是开启执行计划之后，执行完查询之后，在执行计划tab中显示的，除表扫描，索引扫描之外的活动，例如：计算标量，流聚合等等。
　　读取活动，就是执行计划中显示的扫描，表扫描，以及索引扫描。
　　结论
　　包含列使得非聚集索引可以覆盖各种查询，提高这些查询的性能，有时候是很吸引人的。包含列增加了索引的大小，增加了一些维护工作。在你创建非聚集索引的时候，尤其是包含外键的时候，问一问自己：“我应该在索引中增加哪些额外的列呢？”。