SQL SERVER性能优化综述Word文件下载.docx

1、在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。4、 外键的设计外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SER

2、VER 2000当年的新功能，在2005作 了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好 其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。5、 字段的设计字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：A、数据类型尽量用数字型，数字型的比较比字符型的快很多。B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEF

3、AULT代替。D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。E、 自增字段要慎用，不利于数据迁移。6、 数据库物理存储和环境的设计在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。7、 系统设计整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。系统设计阶段应该

4、归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。8、 索引的设计在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。关于索引的选择，应改主意：A、 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。B、 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。C、 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样

5、，也需要把最常用的字段放在前面，把重复率低的字段放在前面。D、 一个表不要加太多索引，因为索引影响插入和更新的速度。三、 编码阶段编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。下面罗列一些编程阶段需要注意的事项：1、 只返回需要的数据返回数据到客户端至少需要数据库提取

6、数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：A、横向来看，不要写SELECT *的语句，而是选择你需要的字段。B、 纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。C、 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。D、对于聚合查询，可以用HAVING子句进一步限定返回的行。2、 尽量少做重复的工作这一

7、点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下： 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。 合并对同一表同一条件的多次UPDATE，比如1. UPDATEEMPLOYEESETFNAME=HAIWERWHEREEMP_ID=VPA30890F2. 3. UPDATELNAME=YANG4. 5. 这两个语句应该合并成以下一个语句 FNAM

8、E=HAIWER,LNAME=YANG2. WHEREE、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。F、 不要写一些没有意义的查询，比如 SELECT * FROM EMPLOYEE WHERE 1=23、 注意事务和锁事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：A、事务操作过程要尽量小，能拆分的事务要拆分开来。B、 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，

9、可能锁定了很多资源。C、 事务操作过程要按同一顺序访问对象。D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。E、 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。4、 注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量

10、的用法，需要注意：A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。B、 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。C、 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。D、其他情况下，应该控制临时表和表变量的使用。E、 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我

11、们做过测试，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。G、 注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEM

12、PDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。5、 子查询的用法子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT

13、 EXISTS引入。关于相关子查询，应该注意：A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：1. SELECTPUB_NAME2. FROMPUBLISHERS3. WHEREPUB_IDNOTIN4. （SELECTPUB_ID5. FROMTITLES6. TYPE=BUSINESS）可以改写成：A.PUB_NAMEPUBLISHERSALEFTJOINTITLESB3. ONB.TYPEANDA.PUB_ID=B.5. WHEREB.PUB_IDISNULLTITLEEXISTSTITLE_IDSALESTITLE_IDTITLES.TITL

14、E_ID）SALES.TITLE_IDTITLES.TITLE_ID4. WHEREB、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。）DISTINCTINNERC、 IN的相关子查询用EXISTS代替，比如可以用下面语句代替：17. PUB_ID=PUBLISHERS.PUB_ID）D、不要用COUNT（*）的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：JOB_DESCJOBSCOUNT（*）JOB_ID=JOBS.JOB_ID）=0应该改成：JOBS.JOB_DESCJOBS2. ONEMPLOYEE

15、.JOB_ID=JOBS.JOB_IDEMPLOYEE.EMP_IDJOB_ID=JOBS.JOB_ID）EXISTS1JOB_ID=JOBS.JOB_ID）6、 慎用游标 数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。另外，我们可以用SQL SERVER

16、的一些特性来代替游标，达到提高速度的目的。A、字符串连接的例子这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：1. DECLARENAMEVARCHAR（20）2. VARCHAR（1000）3. NAME_CURSORCURSORFORSELECTFNAMEJOB_ID=10ORDERBYEMP_IDOPENNAME_CURSORFETCHNEXTRNAME_CURSORINTONAMEWHILEFETCH_STATUS8. BEGIN9. NAMESI

17、SNULL（NAMES+,）+NAME10. 11. END12. CLOSE13. DEALLOCATE可以如下修改，功能相同：ISNULL（NAMES+,）+FNAMEB、 用CASE WHEN 实现转换的例子 很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如:表结构:1. CREATETABLE料件表（2. 料号VARCHAR（30）,3. 名称VARCHAR（100）,4. 主单位VARCHAR（20）,5. 单位16. 单位1参数NUMERIC（18,4

18、）,7. 单位28. 单位2参数NUMERIC（18,4）9. ）10. 11. GO12. 13. CREATE入库表（14. 时间DATETIME,15. 料号16. 单位INT,17. 入库数量18. 损坏数量19. ）20. 21. GO其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：1. DECLARE料号单位参数5. DECLARECUR料号,单位入库表单位7. OPENCUR8. FETCH料号,单位9. WHILEFETCH_STATUS-110. BEGINIF单位=1参数=（SELECT单位1参数料件表料号=料号

19、）14. UPDATE数量=数量*参数,损坏数量=损坏数量*参数,单位=1CURRENTOF15. 16. 单位=217. 18. 19. 20. 21. 22. END23. CLOSE24. DEALLOCATE 可以改写成：2. 数量=CASEA.单位WHENTHENA.数量*B.单位1参数2单位2参数ELSEA.数量5. END,6. 损坏数量=CASEA.损坏数量*B.损坏数量9. END,10. 单位=111. FROM入库表A,12. WHEREA.单位A.料号=B.料号C、 变量参与的UPDATE语句的例子 SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比