SQL Server创建复合索引时，复合索引列顺序对查询的性能影响

说说复合索引

写索引的博客太多了，一直不想动手写，有一下两个原因：
一是觉得有炒剩饭的嫌疑，有兄弟曾说：索引吗，只要在查询条件上建索引就行了，真的可以这么暴力吗？
二来觉得，索引是个非常大的话题，很难概括出所有的情况，你不整出点新意来，倒是有抄袭照搬的嫌疑

既然写了，就写一点稍微不一样的东西出来，
好了，废话打住，开搞

 

搭建测试环境：

创建一张表，模拟实际业务中的一个表，往里面填入数据，
时间字段上，相对按照时间均匀地填充，其他字段以GUID填充

Create table BusinessInfoTable
(
    BuniessCode1 varchar(50),
    BuniessCode2 varchar(50),
    BuniessCode3 varchar(50),
    BuniessCode4 varchar(50),
    BuniessStatus1 tinyint,
    BuniessStatus2 tinyint,
    BuniessDateTime1 Datetime,
    BuniessDateTime2 Datetime,
    OtherColumn1 varchar(50),
    OtherColumn2 varchar(50),
    OtherColumn3 varchar(50)
)



declare @i int=0
while @i<1000000
begin
    insert into BusinessInfoTable 
    values 
    (
        NEWID(),NEWID(),NEWID(),NEWID(),RAND()*100,RAND()*100,
        DATEADD(MI,@i,GETDATE()),DATEADD(MI,@i,GETDATE()),NEWID(),NEWID(),NEWID()
    )
    set @i=@i+1
end

现在有这么一个查询（实际上查询远比这个复杂，我简化一点，不要说我刻意造环境）

    select OtherColumn2,
           BuniessStatus1,
           BuniessStatus2,
           BuniessDateTime1,
           BuniessDateTime2
    from BusinessInfoTable 
    where  BuniessDateTime1 between '2016-6-21' and '2016-6-28'
        and BuniessDateTime2 between '2016-6-21' and '2016-6-28'
        and BuniessStatus1    =    55
        and BuniessStatus2    =    66

郑重的说明一点：

暂时不考虑聚集索引，毕竟一个表上只能有一个聚集索引，
别人也不是傻子，不会轻易去建聚集索引，聚集索引早被占用了
既然被占用了，我的原则是一般不去动别人现有的东西的，比如别人建了聚集索引，你给人家删了，根据自己的情况建聚集索引
这不是找骂么

有经验的你一定考虑符合索引了，同时考虑到为避免Key Lookup导致的书签查找，我们把查询索要的OtherColumn2列include进来
比如这样

CREATE NONCLUSTERED INDEX IDX_1 ON BusinessInfoTable 
(BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2)
INCLUDE(OtherColumn2)

 

或者这样，只是索引列顺序不一样

CREATE NONCLUSTERED INDEX IDX_1 ON BusinessInfoTable
(BuniessDateTime1,BuniessDateTime2,BuniessStatus1,BuniessStatus2)
INCLUDE(OtherColumn2)

 

当然可以随意调整四个列的顺序，我就不过多地做演示了，有兴趣的自己试

这里的前导列的顺序并不会影响到索引的使用，查询的时候都是非聚集索引Seek，绝对的

那么问题来了，完全一样的查询条件，结果一样，使用不同的索引，索引的区别仅仅是列顺序不一样，其代价一样吗，先猜测一下，有区别吗？

 

 

　　同样查询，使用不同索引的结果（分别是上面的IDX_1和IDX_2）：

下面看图说话

看看IO情况

 

原因分析

　　看来是有点差别吧，好似乎这个差别还真不小（以往写文章，我测试环境弄不好，对比出来的效果不明显，感觉没啥说服力，这次对比还是比较明显的）

　　究竟原因在何？

　　索引是以平衡树（B树）的方式存在的，复合索引的列的顺序决定了B树的信息的存储的顺序

　　如果是以BuniessStatus1列为前导列，因为BuniessStatus1分布的范围（相对）较小，

　　这样在查询的时候通过BuniessStatus1=55就可以过滤出来一个比较小的结果集，后面依次用其他条件过滤就相对较快了

　　比如BuniessStatus1=55过滤出来符合条件的数据有5条，

　　加上BuniessStatus2 BuniessDateTime1 BuniessDateTime2 这三个条件再过滤，出来一条数据。

 

　　如果BuniessDateTime1 是索引的前导列，用BuniessDateTime1 between '2016-6-21' and '2016-6-28'过滤

　　可能会有10000条数据，然后依次再用 BuniessDateTime2，BuniessStatus1， BuniessStatus2过滤

　　最后也只有一条符合条件的数据。

 　　差别就在于：一开始的过滤条件，决定了查询多少page初步确定满足条件的数据，再进一步的进行过滤

　　如果最开始就相对精确地确定了满足查询条件的数据范围，后面可以通过相对较小的代价来最终确认出满足条件的数据

　　如果最开始相对模糊地却确定了满足查询条件的数据范围，那么这个过程的代价就相对比较大，虽然后面通过每一个条件的过，结果是一样的

 

　　当然这种索引的建立跟数据分布有关，

　　但是，我没有下结论说，复合索引一定要按照什么什么顺序来是最好的

　　还是那句话：具体问题具体分析，避免经验主义，没有一刀切的手段可以解决所有的问题。

 

 

 

5.3.1　使用索引INCLUDE

在这个技巧中，将展示如何在非聚集索引中包含非键列。覆盖查询（covering query）是指其引用的所有列均能在非聚集索引中找到的查询。这个方案经常会带来出众的查询性能，因为SQL Server不需要从聚集索引或堆中检索实际的数据--它只需要读取在非聚集索引中存储的数据。但是它的缺点是最多只能包含16列或900字节的索引键。

这个问题的一个解决方案是新引入的关键字INCLUDE，它允许添加最多1 023个非键列到非聚集索引，通过创建覆盖索引帮助提升查询性能。这些非键列没有存储在索引的所有级别上，而只是存在于非聚集索引的叶级别上。

使用INCLUDE的CREATE NONCLUSTERED INDEX的语法如下：

1. CREATE NONCLUSTERED INDEX index_name   
2.     ON table_or_view_name ( column [ ASC | DESC ] [ ,...n ] )  
3. INCLUDE ( column [ ,... n ] ) 

第一个列列表是键索引列，在INCLUDE之后的列列表是非键列。在这个示例中，将新的大型对象数据类型列添加到TerminationReason表中。删除并重建在DepartmentID上的既有索引，这次添加新的非键值到索引中：

1. ALTER TABLE HumanResources.TerminationReason  
2. ADD LegalDescription varchar(max)  
3.  
4. DROP INDEX 
5. HumanResources.TerminationReason.NI_
   TerminationReason_TerminationReason_DepartmentID  
6.  
7. CREATE NONCLUSTERED INDEX NI_TerminationReason_
   TerminationReason_DepartmentID  
8. ON HumanResources.TerminationReason 
   (TerminationReason, DepartmentID)  
9. INCLUDE (LegalDescription) 

解析

这个技巧演示了增强非聚集索引有效性的新技术。示例从创建新的varchar(max)数据类型列开始。因为它的数据类型，不可以在索引中将它当作键值来使用，但在关键字INCLUDE中使用它将允许你引用新的大型对象数据类型。然后使用INCLUDE和新的非键列删除并重建表TerminationReason上的既有索引。

只可以对非聚集索引使用INCLUDE（在覆盖查询上派得上用场），并且仍然不可以包含废弃的image、ntext以及text数据类型。如果额外的非键值使索引大小增长得太显著，你可能会损失一些覆盖查询可以给予的查询性能，因此要确认测试比较之前和之后的性能。