SQLServer数据库性能优化

SQLServer数据库性能优化一、设计阶段1.主键的设计主键是必要的，SQLSERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。在有多个键的表，一般选择总的长度小的键作为主键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。2.字段的设计字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：A、数据类型尽量用数字型，数字型的比较比字符型的快很多。B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。C、尽量不要允许NULL，除非必要。可以用NOTNULL+DEFAULT代替。D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。E、自增字段要慎用，不利于数据迁移。3.索引的设计关于索引的选择，应改主意：A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。在频繁进行排序或分组（即进行groupby或orderby操作）的列上建立索引。在条件表达式中经常用到的不同值较多的列上建立检索，C、在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度.D、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。E、一个表不要加太多索引，因为索引影响插入和更新的速度。二、编码阶段1.查询慢的常见原因没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)I/O吞吐量小，形成了瓶颈效应。没有创建计算列导致查询不优化。内存不足网络速度慢查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。返回了不必要的行和列查询语句不好，没有优化2.查询优化建议NOTIN操作符是强烈推荐不使用的，因为它不能应用表的索引。推荐方案：用NOTEXISTS或（外连接+判断为空）方案代替ISNULL,,!=,!,!,NOT,NOTEXISTS,NOTIN,NOTLIKE,LIKE'%500'，因为他们不走索引全是表扫描。注意UNion和UNionall的区别。UNION比unionall多做了一步distinct操作。能用unionall的情况下尽量不用union。查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。对单个表检索数据时，不要使用指向多个表的视图，没有必要时不要用DISTINCT和ORDERBY，这些动作可以改在客户端执行，它们增加了额外的开销。这同UNION和UNIONALL一样的道理。使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数一次更新多条记录比分多次更新每次一条快,就是说批处理好尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。3.只返回需要的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：A、横向来看，不要写SELECT*的语句，而是明确选择好你需要的字段。B、纵向来看，合理写WHERE子句，尽量不要写没有WHERE的SQL语句。C、注意SELECTINTO后的WHERE子句，因为SELECTINTO把数据插入到临时表，该过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定、诸塞其他进程。D、对于聚合查询，可以用HAVING子句进一步限定返回的行。4.尽量少做重复的工作这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。D、合并对同一表同一条件的多次UPDATE，比如UPDATEEMPLOYEESETFNAME=’HAI’WHEREEMP_ID=’VPA’UPDATEEMPLOYEESETLNAME=’YAN’WHEREEMP_ID=’VPA’这两个语句应该合并成以下一个语句UPDATEEMPLOYEESETFNAME=’HAI’,LNAME=’YAN’WHEREEMP_ID=’VPA’E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。F、不要写一些没有意义的查询，比如SELECT*FROMEMPLOYEEWHERE1=25.注意事务和锁A、事务操作过程要尽量小，能拆分的事务要拆分开来。B、事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。C、事务操作过程要按同一顺序访问对象。D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。E、尽量不要指定锁类型和索引F、查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）6.注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。D、其他情况下，应该控制临时表和表变量的使用。E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。F、关于临时表产生使用SELECTINTO和CREATETABLE+INSERTINTO的选择，我们做过测试，一般情况下，SELECTINTO会比CREATETABLE+INSERTINTO的方法快很多，但是SELECTINTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATETABLE+INSERTINTO，而大数据量的单个语句使用中，使用SELECTINTO。7.子查询的用法子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOTIN、EXISTS、NOTEXISTS引入。关于相关子查询，应该注意：A、NOTIN、NOTEXISTS的相关子查询可以改用LEFTJOIN代替写法。比如：SELECTPUB_NAMEFROMPUBLISHERSWHEREPUB_IDNOTIN(SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS')可以改写成：SELECTA.PUB_NAMEFROMPUBLISHERSALEFTJOINTITLESBONB.TYPE='BUSINESS'ANDA.PUB_ID=B.PUB_IDWHEREB.PUB_IDISNULLB、如果保证子查询没有重复，IN、EXISTS的相关子查询可以用INNERJOIN代替。比如：SELECTPUB_NAMEFROMPUBLISHERSWHEREPUB_IDIN(SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS')可以改写成：SELECTDISTINCTA.PUB_NAMEFROMPUBLISHERSAINNERJOINTITLESBONB.TYPE='BUSINESS'ANDA.PUB_ID=B.PUB_IDC、IN的相关子查询用EXISTS代替，比如SELECTPUB_NAMEFROMPUBLISHERSWHEREPUB_IDIN(SELECTPUB_IDFROMTITLESWHERETYPE='BUSINESS')可以用下面语句代替：SELECTPUB_NAMEFROMPUBLISHERSWHEREEXISTS(SELECT1FROMTITLESWHERETYPE='BUSINESS'ANDPUB_ID=PUBLISHERS.PUB_ID)D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFTJOIN或者EXISTS，比如：SELECTJOB_DESCFROMJOBSWHERE(SELECTCOUNT(*)FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)0应该改成：SELECTJOB_DESCFROMJOBSWHEREEXISTS(SELECT1FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)8.慎用游标数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。另外，我们可以用SQLSERVER的一些特性来代替游标，达到提高速度的目的比如字符串连接的例子这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把所有JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：可以如下修改，功能相同：DECLARE@NAMEVARCHAR(1000)SELECT@NAMES=ISNULL(@NAMES+’,’,’’)+FNAMEFROMEMPLOYEEWHEREJOB_ID=10ORDERBYEMP_ID9.尽量使用索引不要对索引字段进行运算，而要想办法做变换比如SELECTIDFROMTWHERENUM/2=100应改为SELECTIDFROMTWHERENUM=100*2不要对索引字段进行格式转换日期字段的例子：WHERECONVERT(VARCHAR(10),日期字段,120)=’2008-08-15’应该改为WHERE日期字段〉=’2008