说说复合索引

写索引的博客太多了，一直不想动手写，有一下两个原因：

一是觉得有炒剩饭的嫌疑，有兄弟曾说：索引吗，只要在查询条件上建索引就行了，真的可以这么暴力吗？

二来觉得，索引是个非常大的话题，很难概括出所有的情况，你不整出点新意来，倒是有抄袭照搬的嫌疑

既然写了，就写一点稍微不一样的东西出来，

好了，废话打住，开搞

 

搭建测试环境：

创建一张表，模拟实际业务中的一个表，往里面填入数据，

时间字段上，相对按照时间均匀地填充，其他字段以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=0while @i<1000000begin    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+1end

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

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的语法如下：

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

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

ALTER TABLE HumanResources.TerminationReason
ADD LegalDescription varchar(max)

DROP INDEX
HumanResources.TerminationReason.NI_TerminationReason_TerminationReason_DepartmentID

CREATE NONCLUSTERED INDEX NI_TerminationReason_TerminationReason_DepartmentID
ON HumanResources.TerminationReason (TerminationReason, DepartmentID)
INCLUDE (LegalDescription)

解析

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

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