SQL数据库优化方法.doc

SQL数据库优化方法SQL数据库优化方法I目录1系统优化介绍12外围优化13SQL优化23.1注释使用23.2对于事务的使用23.3对于与数据库的交互23.4对于SELECT *这样的语句，23.5尽量避免使用游标23.6尽量使用count(1)33.7IN和EXISTS33.8注意表之间连接的数据类型33.9尽量少用视图33.10没有必要时不要用DISTINCT和ORDER BY33.11避免相关子查询33.12代码离数据越近越好33.13插入大的二进制值到Image列43.14Between在某些时候比IN 速度更快43.15对Where条件字段修饰字段移到右边43.16在海量查询时尽量少用格式转换。43.17IS NULL 与 IS NOT NULL43.18建立临时表，43.19Where中索引的使用53.20外键关联的列应该建立索引53.21注意UNion和UNion all 的区别53.22Insert53.23order by语句53.24技巧用例63.24.1Sql语句执行时间测试61 系统优化介绍在我们的项目中，由于客户的使用时间较长或客户的数据量大，造成系统运行速度慢，系统性能下降就容易造成数据库阻塞。这是个非常痛苦的事情，用户的查询、新增、修改等需要花很多时间，甚至造成系统死机的现象。速度慢的原因主要是来自于资源不足。数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来最多只占数据库系统性能提升的40%左右(我将此暂时称之为外围优化)；其余大部分系统性能提升来自对应用程序的优化，对于应用程序的优化可以分为对源代码的优化及数据库SQL语句的优化。在本文档只介绍外围优化及SQL语句的优化，对于源代码的优化需要相关方面的专家，形成统一的规范。一个数据库系统的生命周期可以分成：设计、开发和成品三个阶段。在设计阶段进行数据库性能优化的成本最低，收益最大。在成品阶段进行数据库性能优化的成本最高，收益最小。规范的代码和高性能的语句，功在平时，利在千秋。2 外围优化1、 将操作系统与SQL数据库的补丁打到最高版本，WIN2003最高补丁是SP4，SQL SERVER2000最高补丁是SP4(版本号：2039)。2、 在服务器上不要安装与VA程序任何无相关的软件，甚至一些与VA运行无关的服务都可以停掉。一般只安装SQL数据库、VA服务端服务及杀毒软件。3、 杀毒软件避免对大文件进行扫描，特别是数据库（MDF和LDF）文件，一定要从杀毒软件的范围内排除掉。4、 在进行服务器分区时，分区不要太多，两三个分区就可以了。分区最好都使用NTFS格式。5、 定时对磁盘进行扫描和磁盘整理，减少系统文件错误及减少磁盘碎片，进行磁盘整理时最好不要使用WINDOWS本般的扫描功能（扫描之前一定要对数据库作异地备份）。6、 可以考虑设置增大磁盘的缓存区，减少对磁盘的读写次数。7、 升级硬件，整机使用更高配置的硬件。或者可以单独增加CPU个数、增大内存等。8、 提高网速。3 SQL优化3.1 注释使用在语句中多写注释，注释不影响SQL语句的执行效率。增加代码的可读性。3.2 对于事务的使用尽量使事务处理达到最短，如果事务太长最好按功能将事务分开执行(如：可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞，用户操作速度变慢或死机情况。3.3 对于与数据库的交互尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作，最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理。3.4 对于SELECT *这样的语句，不要使用SELECT *这样的语句，而应该使用SELECT table1.column1这样的语句，明确指出要查询的列减少数据的通讯量并且这样的代码可读性好，便于维护。3.5 尽量避免使用游标它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。3.6 尽量使用count(1)count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。3.7 IN和EXISTS EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。3.8 注意表之间连接的数据类型避免不同类型数据之间的连接。 3.9 尽量少用视图对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰。3.10 没有必要时不要用DISTINCT和ORDER BY这些动作可以改在客户端执行，它们增加了额外的开销。3.11 避免相关子查询一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。3.12 代码离数据越近越好所以优先选择Default,依次为Rules,Triggers, Constraint(约束如外健主健CheckUNIQUE,数据类型的最大长度等等都是约束),Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。3.13 插入大的二进制值到Image列使用存储过程，千万不要用内嵌Insert来插入。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。3.14 Between在某些时候比IN 速度更快Between能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where title in (男,女) Select * from chineseresume where between 男 and 女 是一样的。由于in会在比较多次，所以有时会慢些。3.15 对Where条件字段修饰字段移到右边任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。3.16 在海量查询时尽量少用格式转换。3.17 IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。3.18 建立临时表，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度;如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。临时表是tempdb数据库实际的表，没有主键、索引，应该避免在临时表中存储大量的数据。3.19 Where中索引的使用WHERE条件顺序尽量把索引字段放在前面（主键的唯一性最高），复合索引字段顺序与where条件顺序保持一致。Sql自动查找使用那个索引。3.20 外键关联的列应该建立索引(如子表id)主子表单据肯定要建视图，2个表的关联以2个表中的MainID为关系，所以，需要给子表的MainID单独建索引，这将很大地提高视图的速度。例如Gy_InOutSub中的InoutMainid增加索引。3.21 注意UNion和UNion all 的区别UNION all执行效率高。3.22 InsertInsert into 表 values()应该为Insert into 表 (字段) values()3.23 order by语句ORDER BY语句决定了如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中