数据库查询性能调优和索引优化

查询性能调优是个很大的话题，这里边涉及到的技术非常广泛，但是我们一般可以把它大致分为以下几个层次：

1.减少数据访问。相关的技术就是建立合适的索引，将全表扫描、索引扫描（scan）等耗时的操作转化为索引查找（seek）。建立正确的索引，能让数据库查询性能提升100-1000倍甚至更高，就好比一本非常厚的词典，如果没有任何索引，你要查一个东西，那可是相当费尽，需要整本书查一遍，有索引就可以直接根据索引定位了。这是最重要的改善性能的途径。

2.减少返回的数据。在网络中传输数据，带宽是有限的，如果能按需提取最少量的数据，会起到不错的作用。这里需要注意的是，在SQL中，不要出现select *，而是需要什么字段，就提取什么字段。

3.减少与数据库交互次数。网络资源有限，显然，频繁与数据库交互，也是制约性能的一个因素。一个良好的建议就是，使用存储过程，或者批处理语句，这样能减少与数据库的交互，提升一部分性能。

4.减少CPU的负荷。这里，主要是使用缓存计划。在查询中，尽量使用参数化的查询。这样的话，数据库会对查询参数进行缓存，从而复用查询计划。

5.提升硬件性能。这是最后一招了，如果其他方面都已经做得非常不错了，性能瓶颈在CPU，内存和磁盘上，那采取提升硬件性能的方案就会显得比较合适了，否则还是先去优化其他的地方吧。

以上5个层次的优化带来的性能改善，是依次下降的，是一个倒置的金字塔。

下边详细讨论一下索引的那些事。

百度百科上对索引的描述是：“数据库索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。”

索引，分为聚集索引（clustered index)和非聚集索引（nonclustered index）两种。

a.聚集索引

含有聚集索引的表，叫做聚集表，它的数据行的组织方式，是跟聚集索引的顺序是一致的。聚集索引覆盖的列，叫做聚集键。

用新华字典来比喻的话，正文的每一个字就是一个数据行，他们的组织顺序是根据拼音，如果拼音相同，就会根据笔画（不一定准确，见谅），因此，新华字典里的聚集索引覆盖的列就是拼音和笔画。

很容易理解的是，正文只能按照一种既定的顺序去排序，同理，在一张表里，只能有一个聚集索引，从而决定着数据行的组织方式。

b.非聚集索引

非聚集索引，用新华字典来比喻的话，就是字典正文之前的那些按拼音查找，按部首查找，按笔画查找的附录。它们描述了正文中的文字的排序位置，但是他们跟正文是分开的。非聚集索引，它跟数据的组织顺序是毫无关系的，它用一系列指针来指向数据行，从而来描述数据行的位置。

不含有聚集索引的表，叫做堆表，它的数据行组织顺序，是没有特定顺序的，类似于一堆书，增加一本书就放在这堆书的上面（在堆表中，具体实现方式可能不一样）。

聚集索引对查询性能影响非常大。聚集表中，非聚集索引是根据聚集键来定位的，而堆表中，非聚集索引是根据数据行号来定位的。这将有很大的性能区别，前者的性能大大优于后者。所以，建立合适的聚集索引，是非常必要的。一个好的建议是，使用小字段的且值唯一的列来建立索引，而且最好是单列，可以是代理键。因为如果字段太大太多，用来进行排序的开销将会很大，得不偿失；如果列值不唯一，数据库会为该重复值附加4字节的信息来标识重复值，增加了不必要的开销。

通常，我们在创建表的时候会指定主键，如果不显式指定索引类型的话，将默认创建聚集索引。比如：add constraintpk_tblprimary key (sid)，将创建以sid为序的聚集索引。可以显式指定主键上的索引类型，比如，add constraintpk_tblprimary key nonclustered(sid)，将创建一个非聚集索引的主键。所以，在创建主键的时候，一定得小心了，有多主键的情况，要注意显式指定索引类型。

索引能大幅度提高查询和排序性能，但是，在插入，删除，以及修改了主键的操作中，是需要维护索引顺序的。如果一张频繁变更的表，是不宜建立过多的索引的，索引带来的负面性能影响，将会得不偿失。

索引优化，是一个很考究的事情，它需要找到一个平衡点。

一般来说，有以下几个建议来创建合适的索引：

1.超过300行的数据表要创建索引(无视掉）

2.聚集索引字段不能过多，最好是单字段，而且列值唯一

3.对于数据字段特别多的表，而且这些字段有很多出现在where中，不宜在每一个字段上建立单独的索引，而是创建组合索引。组合索引中，列的顺序是很讲究的，越是选择性大而且唯一的列要放在前面，这对查询优化器优化有很大的帮助。不宜在那些大量重复的列值上建立索引，比如在一个true，false的列上建索引，是毫无意义的。

4.如果查询中，查询的字段不多，可以考虑建立覆盖索引，将字段都包含在索引里，可以仅仅访问索引就能查询到所有数据，而不用表扫描。