SQL数据库优化方法

SQL数据库优化方法

目录

1 系统优化介绍 (1)

2 外围优化 (1)

3 SQL优化 (2)

3.1 注释使用 (2)

3.2 对于事务的使用 (2)

3.3 对于与数据库的交互 (2)

3.4 对于SELECT *这样的语句， (2)

3.5 尽量避免使用游标 (2)

3.6 尽量使用count(1) (3)

3.7 IN和EXISTS (3)

3.8 注意表之间连接的数据类型 (3)

3.9 尽量少用视图 (3)

3.10 没有必要时不要用DISTINCT和ORDER BY (3)

3.11 避免相关子查询 (3)

3.12 代码离数据越近越好 (3)

3.13 插入大的二进制值到Image列 (4)

3.14 Between在某些时候比IN 速度更快 (4)

3.15 对Where条件字段修饰字段移到右边 (4)

3.16 在海量查询时尽量少用格式转换。 (4)

3.17 IS NULL 与IS NOT NULL (4)

3.18 建立临时表， (4)

3.19 Where中索引的使用 (5)

3.20 外键关联的列应该建立索引 (5)

3.21 注意UNion和`UNion all 的区别 (5)

3.22 Insert (5)

3.23 order by语句 (5)

3.24 技巧用例 (6)

3.24.1 Sql语句执行时间测试 (6)

1系统优化介绍

在我们的项目中，由于客户的使用时间较长或客户的数据量大，造成系统运行速度慢，系统性能下降就容易造成数据库阻塞。这是个非常痛苦的事情，用户的查询、新增、修改等需要花很多时间，甚至造成系统死机的现象。速度慢的原因主要是来自于资源不足。

数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来最多只占数据库系统性能提升的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、提高网速。

3SQL优化

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.这样不