【译】索引进阶（五）：带有包含列的索引
【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】
原文链接：传送门。
之前的系列文章介绍了聚集索引和非聚集索引，着重讲解了以下几个方面的内容：
- 对于表中的每一行来说，索引中总是存在这一个条目与它对应（注：此例外规则将在以后予以关注）。这些条目总是按索引键排序。
- 在聚集索引中，索引键就是实际的表数据行。
- 在非聚集索引中，条目是与数据行分开的，它包含一个索引键值和一个映射到实际表数据的书签值。
此前一句话的后半部分是正确但不是完整的，在本章节我们将检查一种可选方案：给非聚集索引包含额外的列，成为“包含列（included columns）”。在第六章，我们将讲解书签操作，将会看到SQL SERVER或许会单方面的为你的索引添加一些列。
包含列
那些在非聚集索引中，但不是索引键的一部分的列称为包含列。这些列不属于索引的一部分，因此不会影响索引中条目的顺序。同样我们会看到，包含列比索引列导致更少的开销。
当创建非聚集索引时，我们将包含列与索引列分开指定，如下所示：
CREATE NONCLUSTERED INDEX FK_ProductID_ ModifiedDate ON Sales.SalesOrderDetail (ProductID, ModifiedDate) INCLUDE (OrderQty, UnitPrice, LineTotal)
在这个示例中，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 =>
查看这个索引键显示的内容，很显然数据行是按照索引键列排序的。ProductID为708的产品，更新时间为 2002/01/01的5条数据在索引中是连续的，如同其他的ProductID/ ModifiedDate的组合行一样。
或许你会问：“为什么要指定包含列？为什么不简单的把OrderQty，UnitPrice，LineTotal 加到索引键上？”。把这些列包含进索引中但不包含进索引键中有如下几个优点：
- 不是索引键一部分的列不会影响索引中条目的位置，这样便会减小把它们包含进索引的开销。举个例子，如果行中 ProductID 或者ModifiedDate 的值被更新了，那么那条数据在索引中的对应条目肯定会被重新定位，但是，如果数据行的UnitPricevalue 值被更新了，那么索引条目虽然仍然需要被更新，但是不需要移动位置。
- 更少的努力去定位索引条目。
- 索引的大小会稍微小点。
- 索引的数据分配统计会更容易维护。
后续几个章节，当我们了解了索引的内部结构，以及SQL SERVER 为了优化查询性能而维护的其他额外信息后，这些优点会变得更有意义。
决定一个索引列是索引键的一部分还是包含索引，并不是你做的最重要的索引决策。也就是说，频繁出现在SELECT 语句列表中但不在WHERE 字句的列最好就放置在包含索引列中（最佳实践）。
成为覆盖索引
在第四章节，我们对AdventureWorks数据库的设计者做的将SalesOrderID/ SalesOrderDetailID 作为SalesOrderDetail表的聚集索引这一决策表示认同，这张表的大部分查询都会请求按销售单号排序或者分组的数据。然而，也有许许多多的查询，或许来自于便利店的员工，需要以产品序列组织的信息。正是这些查询会从如上所示的索引中受益。
为了演示在那个索引中带有包含索引的潜在益处，我们将 会审视SalesOrderDetail表上的两个查询，他们每一个都会执行三次，如下所示：
- 第一次执行：无非聚集索引。
- 第二次执行：使用没有包含列的非聚集索引（只有两个索引键）。
- 第三次执行：使用之前定义的非聚集索引（具有包含列）。
就像我们在之前章节所做的那样，我们再次使用逻辑读作为首要评判标准，但是我们也使用SSMS的“显示实际的执行计划”选项来查看各个执行的计划，这会给我们一个附加的评判标准：非读动作所占的工作量的百分比，比如在数据被读入内存后匹配关联的数据，这使得我们对于查询总消耗有一个更好的理解。
测试第一个查询：商品总数
我们的第一个查询，提供了某一个特定商品按日期分组区分的总数：
列表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 ;
因为索引只会影响查询的性能，而不会影响查询的结果，在三种不同的索引架构下执行这个查询总会产出如下的行集：
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
输出的8行数据是从表中39行“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：我们要执行的查询模式
在各个索引模式下执行查询所需要的相关工作量显示在下表中：
|
第一次执行：没有非聚集索引 |
表SalesOrderDetail：扫描计数1次，逻辑读1238次，非读活动：8% |
|
第二次执行：有非聚集索引，没有包含列 |
表SalesOrderDetail：扫描计数1次，逻辑读131次，非读活动：0% |
|
第三次执行：有非聚集索引：有包含列 |
Table 'SalesOrderDetail'. Scan count 1, logical reads 3. Non read activity: 1%. |
从这些结果你可以看到：
第一次执行需要对SalesOrderDetail 表的完整扫描，每一行需要被读并且检查以决定其是否参与到返回的结果集。
第二次执行使用了非聚集索引快速的找到了所请求的３９行数据的书签，但它仍然需要从表中返回各个数据行。
第三次执行在非聚集索引中找到它需要的所有东西，并且在最优的顺序中（ModifiedDate／ProductID.），它快速跳转到第一个所请求的条目上，读取了39个连续的条目，当它读的同时对各个条目做了聚合计算，这样一切便完成了。
测试第二个查询：基于日期的总数
我们的第二个查询和第一个查询是相同的，只是在WHERE子句的一点小小的改变，这次仓库将请求基于日期而不是基于产品的信息。我们必须在最右边的检索键列（ModifiedDate）进行过滤，而不是最左边的列（ProductID）。新的查询展示如下：
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 行数据的输出。
在各个索引架构下执行查询所需要的工作量如下表列出所示：
|
第一次执行：没有非聚集索引 |
表SalesOrderDetail：扫描计数1次，逻辑读1238，非读活动：10% |
|
第二次执行：有非聚集索引，但没有包含列 |
表SalesOrderDetail：扫描计数1次，逻辑读1238，非读活动10% |
|
第三次执行：有非聚集索引，有包含列 |
表SalesOrderDetail：扫描计数1次，逻辑读761，非读活动 8% |
第一个和第二个查询导致了相同的执行计划：完整扫描了表theSalesOrderDetail 。正是因为第四章详细讲述的原因，WHERE子句没有足够的高选择性从而从非含索引中受益，除此之外，组成任何一个分组得数据行都是分散在整张表中，当表被读取时，每一行都会匹配到它的对应分组中，这些操作会消耗处理器时间和内存。第三个查询在非聚集索引中找到了它所需要的一切，但是，不像之前的查询，它发现数据行在索引中是不连续存储的，但是分组本身又延展了整个索引长度，因此，SQL SERVER扫描了整个索引。
索引扫描比起表扫描有如下两个优点：
- 索引比表小，因而需要更少的读取。
- 数据已经是分组过了，需要更少的非读活动。
结论
对于大量的查询来说，包含列使得非聚集索引成为覆盖索引，它提升了这些查询的性能，有时颇具有戏剧性。包含列增加了索引的大小，但却几乎不增加索引的开销。任何时候当你创建非聚集索引时，尤其在一个外键列上，问下自己：“在这个索引中我应该包含什么额外的列呢？”
