SQL数据库优化方法.doc

1、SQL 数据库优化方法ISQL 数据库优化方法SQL 数据库优化方法I目录1 系统优化介绍 .12 外围优化 .13 SQL 优化 .23.1 注释使用 .23.2 对于事务的使用 .23.3 对于与数据库的交互 .23.4 对于 SELECT *这样的语句， .23.5 尽量避免使用游标 .23.6 尽量使用 count(1).33.7 IN 和 EXISTS.33.8 注意表之间连接的数据类型 .33.9 尽量少用视图 .33.10 没有必要时不要用 DISTINCT 和 ORDER BY .33.11 避免相关子查询 .33.12 代码离数据越近越好 .33.13 插入大的二进制值到 I

2、mage 列 .43.14 Between 在某些时候比 IN 速度更快 .43.15 对 Where 条件字段修饰字段移到右边 .43.16 在海量查询时尽量少用格式转换。 .43.17 IS NULL 与 IS NOT NULL.43.18 建立临时表， .43.19 Where 中索引的使用 .53.20 外键关联的列应该建立索引 .53.21 注意 UNion 和UNion all 的区别 .53.22 Insert .53.23 order by 语句 .53.24 技巧用例 .63.24.1 Sql 语句执行时间测试 .6SQL 数据库优化方法第 1 页 共 8 页1 系统优化介绍

3、在我们的项目中，由于客户的使用时间较长或客户的数据量大，造成系统运行速度慢，系统性能下降就容易造成数据库阻塞。这是个非常痛苦的事情，用户的查询、新增、修改等需要花很多时间，甚至造成系统死机的现象。速度慢的原因主要是来自于资源不足。数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来最多只占数据库系统性能提升的 40%左右(我将此暂时称之为外围优化)；其余大部分系统性能提升来自对应用程序的优化，对于应用程序的优化可以分为对源代码的优化及数据库 SQL 语

4、句的优化。在本文档只介绍外围优化及 SQL 语句的优化，对于源代码的优化需要相关方面的专家，形成统一的规范。一个数据库系统的生命周期可以分成：设计、开发和成品三个阶段。在设计阶段进行数据库性能优化的成本最低，收益最大。在成品阶段进行数据库性能优化的成本最高，收益最小。规范的代码和高性能的语句，功在平时，利在千秋。2 外围优化1、将操作系统与 SQL 数据库的补丁打到最高版本，WIN2003 最高补丁是SP4，SQL SERVER2000 最高补丁是 SP4(版本号：2039)。2、在服务器上不要安装与 VA 程序任何无相关的软件，甚至一些与 VA 运行无关的服务都可以停掉。一般只安装 SQL

5、数据库、VA 服务端服务及杀毒软件。3、杀毒软件避免对大文件进行扫描，特别是数据库（MDF 和 LDF）文件，一定要从杀毒软件的范围内排除掉。4、在进行服务器分区时，分区不要太多，两三个分区就可以了。分区最好SQL 数据库优化方法第 2 页 共 8 页都使用 NTFS 格式。5、定时对磁盘进行扫描和磁盘整理，减少系统文件错误及减少磁盘碎片，进行磁盘整理时最好不要使用 WINDOWS 本般的扫描功能（扫描之前一定要对数据库作异地备份） 。6、可以考虑设置增大磁盘的缓存区，减少对磁盘的读写次数。7、升级硬件，整机使用更高配置的硬件。或者可以单独增加 CPU 个数、增大内存等。8、提高网速。3 SQ

6、L 优化3.1 注释使用在语句中多写注释，注释不影响 SQL 语句的执行效率。增加代码的可读性。3.2 对于事务的使用尽量使事务处理达到最短，如果事务太长最好按功能将事务分开执行(如：可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞，用户操作速度变慢或死机情况。3.3 对于与数据库的交互尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作，最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理。3.4 对于 SELECT *这样的语句，不要使用 SELECT *这样的语句，而应该使用 SELECT table1.column1这样的语句，明确指出要查询的

7、列减少数据的通讯量并且这样的代码可读性好，便于维护。SQL 数据库优化方法第 3 页 共 8 页3.5 尽量避免使用游标它占用大量的资源。如果需要 row-by-row 地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table 变量，用子查询，用 Case 语句等等。如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。3.6 尽量使用 count(1)count 函数只有在统计表中所有行数时使用，而且 count(1)比 count(*)更有效率。3.7 IN 和 EXISTSEXISTS 要远比 IN 的效率高。里面关系到 full table scan 和 range s

8、can。几乎将所有的 IN 操作符子查询改写为使用 EXISTS 的子查询。3.8 注意表之间连接的数据类型避免不同类型数据之间的连接。 3.9 尽量少用视图对视图操作比直接对表操作慢,可以用 stored procedure 来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰。3.10 没有必要时不要用 DISTINCT 和 ORDER BY这些动作可以改在客户端执行，它们增

9、加了额外的开销。3.11 避免相关子查询一个列的标签同时在主查询和 where 子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要SQL 数据库优化方法第 4 页 共 8 页在子查询中过滤掉尽可能多的行。3.12 代码离数据越近越好所以优先选择 Default,依次为 Rules,Triggers, Constraint(约束如外健主健CheckUNIQUE,数据类型的最大长度等等都是约束 ),Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。3.13 插入大的二

10、进制值到 Image 列使用存储过程，千万不要用内嵌 Insert 来插入。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。3.14 Between 在某些时候比 IN 速度更快Between 能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where

11、 title in (男,女) Select * from chineseresume where between 男 and 女 是一样的。由于 in 会在比较多次，所以有时会慢些。3.15 对 Where 条件字段修饰字段移到右边任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。3.16 在海量查询时尽量少用格式转换。3.17 IS NULL 与 IS NOT NULL不能用 null 作索引，任何包含 null 值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有 null，该列就会从索引中排除。也就是说如果某列

12、存在空值，即使对该列建索引也不会提高性能。任何在 where 子句中使用 is null 或 is not null 的语句优化器是不允许使用索引的。SQL 数据库优化方法第 5 页 共 8 页3.18 建立临时表，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免 log，提高速度 ;如果数据量不大，为了缓和系统表的资源，建议先 create table，然后 insert。临时表是 tempdb 数据库实际的表，没有主键、索引，应该避免在临时表中存储大量的数据。3.19 Where 中索引的使用WHERE 条件顺序尽量把索引字段放在前面（主

13、键的唯一性最高） ，复合索引字段顺序与 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 语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等） 。任何在 Order by 语句的非索引项或者有计算表达式都将降低查询速度。仔细检查 order by 语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写 order by 语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在 order by 子句中使用表达式。SQL 数据库优化方法第 6 页 共 8 页3.24 技巧用例3.24.1Sql 语句执行时间测试