影响SQL效能的几个关键因素

摘自：http://blog.sina.com.cn/s/blog_4bbd0b93010007gb.html

 

一.数据库设计必须注意的原则
             数据库的逻辑设计，包括表和表之间的关系是优化数据库性能的核心。一个好的数据库设计不但可以提高数据库本身的效能，而且还可以使应用程序变得简单，为良好的程序开发打下一个良好的基础。
             我不想在本来就不好的精力中考虑太多范式的问题，何况，我从来就不认为完全符合范式的数据库设计是成功的！但是，我们还是要尽量的去多用小表（就是列不要太多的表），不要用太多列的长数据库大表。
             还有，最好要有一个主线作为整个数据库的贯穿，就好像一篇文章一样，一定要有一个主线一样。这样，我们就不会因为过多的过复杂的连接关系，从而降低服务器的性能。
             上面做法的好处是：
                                  由于自小表的列比较少，所以我们在使用排序和建立索引的时候会更为高效。
                                  也正是由于我们把原本比较大的表，划列成多个小表，这就使多簇索引变得更有可能。
                                  也正是由于小表的分裂，使索引变得更狭窄和更紧凑。
                                  由于小表，我们需要建立的针对这个表的索引就会相对较少一些，所以，间接的降低了对数据库操作的开销，而对于那些只要把Select优化好就万事大吉的程序员们来说，也许是一个不错的好消息，因为毕竟Insert，Update和Delete语句有时候也会对系统产生很大的影响（比如说：这个表的索引很多的情况下）。
                                  建立良好的主外键关系，由于主外键关系是有比较严格的要求，所以，很多DB设计的没有主外键关系，这样当然方便设计，也方便开发，但是却会损失一些效能。
二.我是如何看待索引的（前面讲什么都没有离开索引，所以我们还是要好好认识一下索引）
             关于索引，从我上大学的时候几乎每个老师讲到索引的时候都会用书目来做讲解，好，我也是俗人一个，那我也用书来做例子吧！为什么每本书都会有一个目录呢？答案很简单，也很明确，那就是为了提高查阅的速度。因为有了目录，所以我们不需要知道每一页写的是什么就可以轻而易举的知道我们要找的内容，但是，我们要为此多付几张纸钱！索引也是一样的道理，索引是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。SQL Server中的索引记录了表中的关键字，提供了指向表中行的指针。这种物理结构提供了一种以一列或多列的值为基础迅速查找表中行的能力。索引是针对一个表建立的，它是由除存放表的数据页面以外的索引页面组成的。每个索引页面中的行都含有逻辑指针（指向表中的行），以便加速检索物理数据。索引可是创建在一列上，也可以创建在多列的组合上，当然，也可以在数据库表的多个列上建立不同的索引。
             知道了索引是概念，那么我们就要将目光放在如何设计索引了，以及使用索引的时候应该注意什么？
             1.尽量的避免整个表都去扫描
                这里主要是说where语句，where语句将是最重要的，也是最值得我们关注的部分。用where语句查询数据的时候，每一个列都有可能是候补索引，我们为了可以达到最优的性能，通常要考虑：在查询的条件所对应的列上有一个单索引，或者在这个表上有多个索引，但是查询条件所对应的列却是这多个索引的第一个索引列。同时，我们还需要注意的是单索引比多索引和复合索引更能有效的推进系统性能。
                还有一点需要我们了解，SQL Server维持一个在所有列的索引上的密度统计（用于联合）和在第一个索引上的统计图（柱形），在一定的周期，根据统计结果，如果在复合索引上的第一个索引很少被选择使用，那么优化器对很多列查询请求将不会使用索引。索引会提高检索语句的性能。但是，我们更应该了解索引使用不当，给我们所带来的困扰，这样，我们才能合理的去应用它。
                由于改变一个表的内容，将会影响索引，也就是说我们只要进行insert，update和delete语句将会使性能下降一些，所以，在这里要告诫大家的是一定不要在一个单表上用大量的索引，也不要在共享列上使用重叠索引。
                在某一个列上检查唯一的数据个数，并且与表中的数据的行数做一个比较，这是一个经验，用这个比较的结果将会帮助你决定是否将某一列作为候选的索引列。
             2.簇索引和非簇索引的选择
                簇索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个簇索引。由于update,delete语句要求相对多一些的读操作，因此簇索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个簇索引。
                在下面的几个情况下，你可以考虑用簇索引：            
                           例如：某列包括的不同值的个数是有限的（但是却又不是极少的），顾客表的州名列有50个左右的不同州名的缩写值，可以使用镞索引。
                           例如：对返回一定范围内值的列可以使用镞索引，比如用between,>,>=,<,<=等等来对列进行操作的列上。
                           例如：对查询时返回大量结果的列可以使用镞索引。
                           当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立簇索引。如果你建立了簇的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页，当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟簇索引的逻辑次序一样的。
                           一个非簇的索引就是行的物理次序与索引的次序是不同的。一个非簇索引的叶级包含了指向行数据页的指针。在一个表中可以有多个非簇索引，你可以在以下几个情况下考虑使用非簇索引。在有很多不同值的列上可以考虑使用非簇索引，例如：一个列在一个表中查询语句中用order by 子句的列上可以考虑使用簇索引。
 三.检索语句的设计和优化
             1.SQL Server优化器
                SQL Server优化器通过分析查询语句，自动对查询进行优化并决定最有效的执行方案。优化器分析查询语句来决定那个子句可以被优化，并针对可以被优化查询的子句来选择有用的索引。最后优化器比较所有可能的执行方案并选择最有效的一个方案出来。在执行一个查询时，用一个where子句来限制必须处理的行数，除非完全需要，否则应该避免在一个表中无限制地读并处理所有的行。
            2.用经验去感觉
               a.有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；
               b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
               c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。
               d.is null 和 is not null
                 任何包含null值的列都将不会被包含在索引中，如果有多列索引，只要这些列中有一列中含有null值，那么，该列就会从索引中被排除掉。换句话说，如果该列中存在null值的话，即便是在该列中建立索引，也是不会起作用的。同理，任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
               e.in和exists
                  exists要远比in的效率高。里面关系到full table scan和range scan。几乎将所有的in操作符子查询改写为使用exists的子查询。
               f.我非常喜欢用cast和convert进行数据转换，但是，要注意的是在进行海量查询的时候，尽量不要用格式转换。
               g.order by和group by
                  在使用这两个短句的时候，任何一种索引都会提高SQL语句的效能的。但是这个并不会与d项矛盾的。
               h.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
               i.in、or子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。
               g.写一般的SQL时，我们可以使用SET SHOWPLAN_ALL ON 来查询SQL语句的执行方案。
               h.尽量不要去使用游标，如果非要使用游标，那么就将需要遍历的数据提炼到最少