Sql Server索引和索引优化

目录

一、 索引介绍

二、 索引的概述

三、 索引的特点

四、 设计索引的注意事项

五、 索引的类型

六、 利用索引处理大数据

---

一、 索引介绍

视图 的存在简化了查询语句，为了 SQL Server 提升了工作效率；但当数据库中存储的数据量过于庞大时，这种程度的简化就显得微不足道了，而索引能解决这一问题的另一种数据库对象；

二、 索引的概述

索引是一个单独的、存储在磁盘中的数据库结构，包含了所有记录的引用指针；索引就是一个目录；比如：我们想在一本很厚的书籍中找寻某些特定内容，最方便的查找方式就是先在目录中查找特定内容的所属页码，就能很快找到特定的内容了；当数据库中存储的数据非常庞大时，可以把它看做一本书籍，而其中的某一条数据就是需要查找的某个知识点，这时候就能体现出索引的重要性；

三、 索引的特点

1. 使用索引能提升数据库的性能，主要体现在以下几个方面：
极大地提升数据库的查询进度，这也是其最主要的优点；
通过创建唯一索引，能保证数据库中的各行数据具有唯一性；
建立在外码上的索引能加速表与表之间的连接，益于实现数据的参照完整性；
显著减少分组和排序查询所使用的时间；
2. 在提升数据性能的同时，索引有一些负面影响
索引会占用物理存储空间，需要一定的额外物理存储来存放索引文件；
索引的维护需要耗费时间，对数据表更新数据之后，相应的索引也需要动态维护；

四、 设计索引的注意事项

索引设计不合理会对数据库及应用程序造成不良影响，所以设计索引时要注意以下几点：

索引并非越多越好，一个数据库中若含有许多索引，不仅占用磁盘空间，也会造成大量的维护开销；对经常用于查询的列可建立索引的提高效率，但需要避免插入不必要的数据；数据量较小的数据表最好不使用索引，因为可能不会产生明显的优化效果；对于不同值较少的列，不要建立索引，这样会降低更新数据的速度。

五、 索引的类型

SQL Server 数据库的索引可分为聚集索引、非聚集索引与其他索引3类；

 聚集索引
聚集索引确定了数据存储的顺序，数据表内的数据按照聚集索引兼职的顺序存储；由于物理存储顺序只有一个，即一个数据表值能包含一个聚集索引；
默认情况下，系统会对 PRIMARY KEY约束自动创建聚集索引，这就是数据表中的数据行通常按照主码排列的原因；

非聚集索引
非聚集索引与聚集索引有类似的索引结构，不同的是，非聚集索引的索引排序与物理排序相互独立；即数据行的物理存储顺序和索引键的逻辑顺序不一致；
与聚集索引类似，非聚集索引能够有效提升数据的查询数据，同时也会降低更新数据的数据，并占用一定的存储空间；
当一个数据表需要频繁第更新数据或存储空间有限时，应限制非聚集索引的数量；

其他索引
SQL Server 数据库还提供了其他类型的索引，

唯一索引能够保证索引中不包含重复值，使数据表中的每一行在某种条件下具有唯一性；聚集索引和非聚集索引都能是唯一索引；
注意： 创建 UNIQUE约束时，将默认创建唯一非聚集索引，若该表不存在聚集索引，可指定唯一聚集索引；
视图索引 即是在视图上建立的索引，它将视图具体化，并把结果集永久储存在聚集索引中，若在查询中频繁引用进行复杂处理的视图，可对其创建唯一聚集索引；
注意： 若经常更新视图索引中的数据表，维护视图索引的开销将远大于使用它的效率收益；

全文索引 是一种特殊类型的基于标记的索引，由 SQL Server 全文引擎生成及维护，用于快速查询某个字符出现的位置。

六、 利用索引处理大数据

正确的建立索引
首先，我们需要明白几个索引的要点：索引之后，按索引字段重复最少的来排序，会达到最优的效果。以我们的表来说，如果建立了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的记录中把结果筛选了出来

怎么应用索引?
既然写入完成了、读取完成了，怎么结合呢？我们可以把一个小时之前的数据建立索引，当前一个小时的数据就不建立索引。也就是，不要再创建表的时候建立索引！！

还能怎么优化
可以尝试读写分离，写两个库，一个是实时库，一个是只读库。一个小时内的数据查询实时库，一个小时之前的数据查询只读库；只读库定时存储，然后建立索引；超过一个星期的数据，进行分析处理再存储。这样，无论查询什么时间段的数据，都能够正确处理了——一个小时之内的查询实时库，一个小时到一个星期内的查询只读库，一个星期之前的查询报表库。

如果不需要物理分表，则在只读库中，定时重建索引即可。

总结
如何在SQLServer中处理亿万级别的数据(历史数据)，可以按以下方面进行：

去掉表的所有索引
用SqlBulkCopy进行插入
分表或者分区，减少每个表的数据总量
在某个表完全写完之后再建立索引
正确的指定索引字段
把需要用到的字段放到包含索引中(在返回的索引中就包含了一切)