SQL server 覆盖索引和复合索引的区别

ゝ一世哀愁。 2021-11-29 05:34 465阅读 0赞

# 覆盖索引 #

所谓的包含列就是包含在非聚集索引中，并且不是索引列中的列。或者说的更通俗一点就是：把一些底层数据表的数据列包含在非聚集索引的索引页中，而这些数据列又不是索引列，那么这些列就是包含列。同时，这些包含列并不会对索引中的条目有影响。  
好吧，为了使得问题稍微清楚一点，我用个简单的图示说明一下：

![aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFQxQi0wLmdpZg][]

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

<table> 双击代码全选 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>CREATE</code>&nbsp;<code>NONCLUSTERED&nbsp;</code><code>INDEX</code>&nbsp;<code>FK_ProductID_ ModifiedDate</code> <code>ON</code>&nbsp;<code>Sales.SalesOrderDetail (ProductID, ModifiedDate)</code> <code>INCLUDE (OrderQty, UnitPrice, LineTotal)</code> </td> 
 </tr> 
 </tbody> 
</table>

在上述的代码中，ProductID和ModifiedDate包含在索引键中，而OrderQty, UnitPrice, LineTotal作为包含列。  
下面，我们就稍微深入到页级别来看看建立索引前后的状态。首先，我们看看，当建立非聚集索引，但是，索引中没有包含列的时候，索引中的索引页的详细如下：

![aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFIzOTUtMS5naWY][]

在上图中可以看到，上面两个索引页是整个索引结构中的一部分，此时就包含了2个字段，而且这两个字段都是索引键，另外一个Bookmark是指向底层数据表中数据行的一个指针。  
下面，我们再来看看，我们建立了有包含列的非聚集索引之后，索引页的情况，如下图：

![aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFFBNC0yLmdpZg][]

很明显，原本的2个索引页被拆分成为了3个，因为一部分底层数据行的数据的数据包含在了索引页中。从这里就可以知道一点：加入包含列到非聚集索引中，增大了索引结构中页的个数，进而在使用的时候会占用更多的磁盘空间和内存空间。  
  
其实把一些列作为包含列放在索引结构中就是一种用“空间换时间”的策略。  
这个时候，大家可能就会问了：“何必把列放在包含列中这么麻烦，为什么不直接放在索引中？”。  
其实把那三个列放在包含列而不是索引列中有以下几个好处：  
1. 可以使得索引键变化引起的波动更小。举个例子，如果索引列中的ProductID或者ModifiedDate发生变化，那么索引结构就会要调整，重新定 位到底层的数据行。但是，如果UnitPrice的值发生了变化，整个索引的结构不会发生变化，只是在包含列中的UnitPrice的值进行更新而已。  
2.索引中的数据列越少，数据分布的统计维护的成本就越小。  
是否把一些作为索引列还是包含了其实也和数据库的类型和用途有很大的关系。例如在OLTP的数据库中，有很多的数据的增删改写的操作，那么建议索引中的列不要太多。如果是Warehouse类型的数据，那么就以大量数据的读取为主，那么可以考虑把很一些列包含在索引列中。

包含列实例演示一  
下面通过是三个不同的小例子作为比较演示，并且以AdventureWorks中的SalesOrderDetail示例数据表：  
1.演示没有非聚集索引的例子。  
2.演示使用非聚集索引，但是没有包含列的例子  
3.演示有非聚集索引和包含列的例子  
在讲述的过程中，我们会结合实际的执行详情说明问题。  
示例一：没有非聚集索引  
执行语句如下：

<table> 双击代码全选 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>SET</code>&nbsp;<code>STATISTICS</code>&nbsp;<code>IO&nbsp;</code><code>ON</code> &nbsp;&nbsp;&nbsp;&nbsp; <code>SELECT</code>&nbsp;<code>ProductID ,</code> <code>ModifiedDate ,</code> <code>SUM</code><code>(OrderQty)&nbsp;</code><code>AS</code>&nbsp;<code>'No of Items'</code>&nbsp;<code>,</code> <code>AVG</code><code>(UnitPrice)&nbsp;</code><code>'Avg Price'</code>&nbsp;<code>,</code> <code>SUM</code><code>(LineTotal)&nbsp;</code><code>'Total Value'</code> <code>FROM</code>&nbsp;<code>Sales.SalesOrderDetail</code> <code>WHERE</code>&nbsp;<code>ProductID = 888</code> <code>GROUP</code>&nbsp;<code>BY</code>&nbsp;<code>ProductID ,</code> <code>ModifiedDate ;</code> &nbsp;&nbsp;&nbsp;&nbsp; <code>SET</code>&nbsp;<code>STATISTICS</code>&nbsp;<code>IO&nbsp;</code><code>OFF</code> </td> 
 </tr> 
 </tbody> 
</table>

数据结果如下：![aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFZBOC0zLmdpZg][]

示例二：使用非聚集索引，但是没有包含列  
首先运行下面的语句，建立索引：

<table> 双击代码全选 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>IF EXISTS (&nbsp;</code><code>SELECT</code>&nbsp;<code>1&nbsp;</code><code>FROM</code>&nbsp;<code>sys.indexes&nbsp;</code> <code>WHERE</code>&nbsp;<code>name</code>&nbsp;<code>=&nbsp;</code><code>'FK_ProductID_ModifiedDate'</code> <code>AND</code>&nbsp;<code>OBJECT_ID = OBJECT_ID(</code><code>'Sales.SalesOrderDetail'</code><code>) )&nbsp;</code> <code>DROP</code>&nbsp;<code>INDEX</code>&nbsp;<code>Sales.SalesOrderDetail.FK_ProductID_ModifiedDate</code> &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; <code>CREATE</code>&nbsp;<code>NONCLUSTERED&nbsp;</code><code>INDEX</code> <code>FK_ProductID_ModifiedDateON Sales.SalesOrderDetail (ProductID, ModifiedDate)</code> </td> 
 </tr> 
 </tbody> 
</table>

然后再次运行示例中的查询。  
示例三：有非聚集索引和包含列  
首先执行下面的代码：

<table> 双击代码全选 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>IF EXISTS (&nbsp;</code><code>SELECT</code>&nbsp;<code>1&nbsp;</code><code>FROM</code>&nbsp;<code>sys.indexes&nbsp;</code> <code>WHERE</code>&nbsp;<code>name</code>&nbsp;<code>=&nbsp;</code><code>'FK_ProductID_ModifiedDate'</code> <code>AND</code>&nbsp;<code>OBJECT_ID = OBJECT_ID(</code><code>'Sales.SalesOrderDetail'</code><code>) )&nbsp;</code> <code>DROP</code>&nbsp;<code>INDEX</code>&nbsp;<code>Sales.SalesOrderDetail.FK_ProductID_ModifiedDate</code> &nbsp;&nbsp;&nbsp;&nbsp; <code>CREATE</code>&nbsp;<code>NONCLUSTERED&nbsp;</code><code>INDEX</code>&nbsp;<code>FK_ProductID_ModifiedDateON Sales.SalesOrderDetail&nbsp;</code> <code>(ProductID, ModifiedDate) INCLUDE (OrderQty, UnitPrice, LineTotal) ;</code> </td> 
 </tr> 
 </tbody> 
</table>

然后再次运行之前的查询。  
好了，三个例子完成之后，我们就来比较一下结果，如下：

<table> 
 <tbody> 
 <tr> 
 <td>示例 1:No Nonclustered Index</td> 
 <td>Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.Non read activity: &nbsp;8%.</td> 
 </tr> 
 <tr> 
 <td>示例2:Index – No Included Columns &nbsp;</td> 
 <td>Table 'SalesOrderDetail'. Scan count 1, logical reads 131.Non read activity: &nbsp;0%.</td> 
 </tr> 
 <tr> 
 <td>示例3:With Included Columns</td> 
 <td>Table 'SalesOrderDetail'. Scan count 1, logical reads 3.Non read activity: &nbsp;1%.</td> 
 </tr> 
 </tbody> 
</table>

总结如下：  
1.示例1中对整个表进行扫描，每一个行都要进行扫描，所以进行大量的IO活动。  
2.例2首先使用非聚集索引找到ProductID的数据，然后通过书签查找找到查询中请求的其他的数据列。  
3.示例3，只要在非聚集索引中查找需要的数据就行了，因为查询中所有列的数据都在里面了，不用查找底层的数据表，所以查询只是查找了索引结构，消耗的IO最少。

[aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFQxQi0wLmdpZg]: /images/20211129/3ee4754dbac94a2085c384c0d07f8305.png
[aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFIzOTUtMS5naWY]: /images/20211129/428d85a842c14ab89bce0a56c46bc953.png
[aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFFBNC0yLmdpZg]: /images/20211129/3806de9be76b40cfaaa35007cd17aaed.png
[aHR0cDovL3d3dy5kZWRlY21zLmNvbS91cGxvYWRzL2FsbGltZy8xMjA5MTIvMFoxNFZBOC0zLmdpZg]: /images/20211129/165f3b7c8ce64109a017e7e583d0201e.png