(转)SQL Server索引进阶第五篇：索引包含列

SQL Server索引进阶第五篇：索引包含列

索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。

 

本系列文章来自Stairway to SQL Server Indexes，然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助。

 

系列文章索目录：

SQL Server索引进阶第一篇：索引介绍
SQL Server索引进阶第二篇：深入非聚集索引
SQL Server索引进阶第三篇：聚集索引
SQL Server索引进阶第四篇：页和区

SQL Server索引进阶第五篇：索引包含列
SQL Server索引进阶第六篇：书签
SQL Server索引进阶第七篇：过滤的索引

SQL Server索引进阶第八篇：唯一索引
SQL Server索引进阶第九篇：解读执行计划
SQL Server索引进阶第十篇：索引的内部结构
SQL Server索引进阶第十一篇：索引碎片分析与解决（上）
SQL Server索引进阶第十一篇：索引碎片分析与解决（中）-碎片发生原理深度剖析
SQL Server索引进阶第十二篇：索引的创建，修改和删除
SQL Server索引进阶第十三篇：Insert,Update,Delete语句
SQL Server索引进阶第十四篇：索引统计
SQL Server索引进阶第十五篇：索引的最佳实践

 

包含列解析

 

所谓的包含列就是包含在非聚集索引中，并且不是索引列中的列。或者说的更通俗一点就是：把一些底层数据表的数据列包含在非聚集索引的索引页中，而这些数据列又不是索引列，那么这些列就是包含列。同时，这些包含列并不会对索引中的条目有影响。

好吧，为了使得问题稍微清楚一点，我用个简单的图示说明一下：

20120828230517.png(39.88 K)

9/9/2012 11:15:45 AM

 

 

我们可以用下面的语句在创建索引的时候加入包含列，代码如下：

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

复制代码

在上述的代码中，ProductID和ModifiedDate包含在索引键中，而OrderQty, UnitPrice, LineTotal作为包含列。

下面，我们就稍微深入到页级别来看看建立索引前后的状态。首先，我们看看，当建立非聚集索引，但是，索引中没有包含列的时候，索引中的索引页的详细如下：

20120828230709.png(68.84 K)

9/9/2012 11:15:45 AM

 

 

在上图中可以看到，上面两个索引页是整个索引结构中的一部分，此时就包含了2个字段，而且这两个字段都是索引键，另外一个Bookmark是指向底层数据表中数据行的一个指针。

下面，我们再来看看，我们建立了有包含列的非聚集索引之后，索引页的情况，如下图：

03.png(120.39 K)

9/9/2012 11:15:45 AM

 

 

很明显，原本的2个索引页被拆分成为了3个，因为一部分底层数据行的数据的数据包含在了索引页中。从这里就可以知道一点：加入包含列到非聚集索引中，增大了索引结构中页的个数，进而在使用的时候会占用更多的磁盘空间和内存空间。

 

其实把一些列作为包含列放在索引结构中就是一种用“空间换时间”的策略。

这个时候，大家可能就会问了：“何必把列放在包含列中这么麻烦，为什么不直接放在索引中？”。

 

其实把那三个列放在包含列而不是索引列中有以下几个好处：

 

1.可以使得索引键变化引起的波动更小。举个例子，如果索引列中的ProductID或者ModifiedDate发生变化，那么索引结构就会要调整，重新定位到底层的数据行。但是，如果UnitPrice的值发生了变化，整个索引的结构不会发生变化，只是在包含列中的UnitPrice的值进行更新而已。

2.索引中的数据列越少，数据分布的统计维护的成本就越小。

是否把一些作为索引列还是包含了其实也和数据库的类型和用途有很大的关系。例如在OLTP的数据库中，有很多的数据的增删改写的操作，那么建议索引中的列不要太多。如果是Warehouse类型的数据，那么就以大量数据的读取为主，那么可以考虑把很一些列包含在索引列中。

 

包含列实例演示一

 

下面通过是三个不同的小例子作为比较演示，并且以AdventureWorks中的SalesOrderDetail示例数据表：

1.演示没有非聚集索引的例子。

2.演示使用非聚集索引，但是没有包含列的例子

3.演示有非聚集索引和包含列的例子

在讲述的过程中，我们会结合实际的执行详情说明问题。

 

示例一：没有非聚集索引

执行语句如下：

1. SET STATISTICS IO ON
3. SELECT ProductID ,
4. ModifiedDate ,
5. SUM(OrderQty) AS 'No of Items' ,
6. AVG(UnitPrice) 'Avg Price' ,
7. SUM(LineTotal) 'Total Value'
8. FROM Sales.SalesOrderDetail
9. WHERE ProductID = 888
10. GROUP BY ProductID ,
11. ModifiedDate ;
13. SET STATISTICS IO OFF

复制代码

数据结果如下：

 

20120828231032.png(68.18 K)

9/9/2012 11:15:45 AM

 

 

示例二：使用非聚集索引，但是没有包含列

首先运行下面的语句，建立索引：

1. IF EXISTS ( SELECT 1 FROM sys.indexes
2. WHERE name = 'FK_ProductID_ModifiedDate'
3. AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') )
4. DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate
7. CREATE NONCLUSTERED INDEX
8. FK_ProductID_ModifiedDateON Sales.SalesOrderDetail (ProductID, ModifiedDate)

复制代码

然后再次运行示例中的查询。

 

示例三：有非聚集索引和包含列

首先执行下面的代码：

1. IF EXISTS ( SELECT 1 FROM sys.indexes
2. WHERE name = 'FK_ProductID_ModifiedDate'
3. AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') )
4. DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate
6. CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDateON Sales.SalesOrderDetail
7. (ProductID, ModifiedDate) INCLUDE (OrderQty, UnitPrice, LineTotal) ;

复制代码

然后再次运行之前的查询。

好了，三个例子完成之后，我们就来比较一下结果，如下：

示例 1:No Nonclustered Index	Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.Non read activity:  8%.
示例2:Index – No Included Columns	Table 'SalesOrderDetail'. Scan count 1, logical reads 131.Non read activity:  0%.
示例3:With Included Columns	Table 'SalesOrderDetail'. Scan count 1, logical reads 3.Non read activity:  1%.

 

总结如下：

1.示例1中对整个表进行扫描，每一个行都要进行扫描，所以进行大量的IO活动。

2.例2首先使用非聚集索引找到ProductID的数据，然后通过书签查找找到查询中请求的其他的数据列。

3.示例3，只要在非聚集索引中查找需要的数据就行了，因为查询中所有列的数据都在里面了，不用查找底层的数据表，所以查询只是查找了索引结构，消耗的IO最少。

-------------------------------------------------------------------------------------------------------------------------------------------------

下面，我们就来看看第二个示例。

 

包含列示例演示二

 

第二个查询和第一个查询类似，只是将Where条件语句改为了按照ModifiedDate里过滤。语句如下：

 

1. SELECT ModifiedDate ,
2. ProductID ,
3. SUM(OrderQty) 'No of Items' ,
4. AVG(UnitPrice) 'Avg Price' ,
5. SUM(LineTotal) 'Total Value'
6. FROM Sales.SalesOrderDetail
7. WHERE ModifiedDate = '2003-10-01'
8. GROUP BY ModifiedDate ,
9. ProductID ;

复制代码

 

 

 

 

查询的结果如下显示：

20120831164306.png(43.14 K)

8/31/2012 4:43:41 PM

 

在查询执行的过程中，通过Where条件，选择出了1496条数据，最后通过Group语句，使得最后显示的结果只有164行数据。

在三种不同的情况下，运行上面的查询，结果比较如下：

 

示例 1:No Nonclustered Index	Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.Non read activity:  10%.
示例2:With Index – No Included Columns	Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.Non read activity:  10%.
示例3:With Included Columns	Table 'SalesOrderDetail'. Scan count 1, logical reads 761.Non read activity:  8%.

 

我们可以知道，第一种情况和第二种情况下面的执行计划是一样的，都对整表进行扫描。而在第三种情况中，因为此时的ModifiedDate已经包含在了索引的包含列中，此时就没有对整表进行扫描，而是对非聚集索引结构进行扫描。所以，可以知道：如何把一些列作为包含列放在索引中，那么就可以在一定的程度上面提示效率，可以把原本需要整表扫描的操作，改为非聚集索引扫描，这样的成本更小。