我是如何在SQLServer中处理每天四亿三千万记录的( 三 ) _SQLServer

于是技术相关人员开会讨论相关的举措。
在不加索引的情况下怎么优化查询？
有同事提出了，where子句的顺序，会影响查询的结果，因为按你刷选之后的结果再处理，可以先刷选出一部分数据，然后继续进行下一个条件的过滤。听起来好像很有道理，但是SQLServer查询分析器不会自动优化吗？原谅我是个小白，我也是感觉而已，感觉应该跟VS的编译器一样，应该会自动优化吧。
具体怎样，还是要用事实来说话：
结果同事修改了客户端之后，测试反馈，有较大的改善。我查看了代码：

文章插图

难道真的有这么大的影响？等等，是不是忘记清空缓存，造成了假象？
于是让同事执行下述语句以便得出更多的信息：

文章插图

结果如下：

文章插图

优化之前反而更好了？
仔细查看IO数据，发现，预读是一样的，就是说我们要查询的数据记录都是一致的，物理读、表扫描也是一直的。而逻辑读取稍有区别，应该是缓存命中数导致的。也就是说，在不建立索引的情况下，where子句的条件顺序，对查询结果优化作用不明显。
那么，就只能通过索引的办法了。
建立索引的尝试
建立索引不是简单的事情，是需要了解一些基本的知识的，在这个过程中，我走了不少弯路，最终才把索引建立起来。
下面的实验基于以下记录总数做的验证：

文章插图

按单个字段建立索引
这个想法，主要是受我建立数据结构影响的，我内存中的数据结构为：Dictionary<MgrObjId,Dictionary<Id,Property>> 。我以为先建立MgrObjId的索引，再建立Id的索引，SQLServer查询时，就会更快。

文章插图

先按MgrObjId建立索引，索引大小为550M，耗时5分25秒。结果，如上图的预估计划一样，根本没有起作用，反而更慢了。
按多个条件建立索引
OK，既然上面的不行，那么我们按多个条件建立索引又如何？CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)
结果，查询速度确实提高了一倍：

文章插图

等等，难道这就是索引的好处？花费7分25秒，用1.1G的空间换取来的就是这些？肯定是有什么地方不对了，于是开始翻查资料，查看一些相关书籍，最终，有了加大的进展。
正确的建立索引首先，我们需要明白几个索引的要点：

索引之后，按索引字段重复最少的来排序，会达到最优的效果。以我们的表来说，如果建立了No的聚集索引，把No放在where子句的第一位是最佳的，其次是Id，然后是MgrObjId，最后是时间，时间索引如果表是一个小时的，最好不要用
where子句的顺序决定了查询分析器是否使用索引来查询。比如建立了MgrObjId和Id的索引，那么where MgrObjId='' and Id='' and Dtime=''就会采用索引查找，而where Dtime='' and MgrObjId='' and Id=''则不一定会采用索引查找。
把非索引列的结果列放在包含列中。因为我们条件是MgrObjId和Id以及Dtime，因此返回结果中只需包含Dtime和Value即可，因此把Dtime和Value放在包含列中，返回的索引结果就有这个值，不用再查物理表，可以达到最优的速度。

跟上述几点原则，我们建立以下的索引：CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)
耗费时间为：6分多钟，索引大小为903M 。
我们看看预估计划：

文章插图

可以看到，这里完全使用了索引，没有额外的消耗。而实际执行的结果，1秒都不到，竟然不用一秒就在1100w的记录中把结果筛选了出来！！帅呆了！！
怎么应用索引?
既然写入完成了、读取完成了，怎么结合呢？我们可以把一个小时之前的数据建立索引，当前一个小时的数据就不建立索引。也就是，不要再创建表的时候建立索引！！
还能怎么优化
可以尝试读写分离，写两个库，一个是实时库，一个是只读库。一个小时内的数据查询实时库，一个小时之前的数据查询只读库；只读库定时存储，然后建立索引；超过一个星期的数据，进行分析处理再存储。这样，无论查询什么时间段的数据，都能够正确处理了——一个小时之内的查询实时库，一个小时到一个星期内的查询只读库，一个星期之前的查询报表库。