SQL性能优化

经常听说SQLServer最难的部分是性能优化，不禁让人感到优化这个工作很神秘，这种事情只有高手才能做。很早的时候我在网上看到一位高手写的博客，介绍了SQL优化的问题，从这些内容来看，优化并不都是一些很复杂的问题，掌握了基本的知识之后也可以尝试优化自己的SQL程序，甚至是其他相关的程序。优化是一些工作积累之后的经验总结和代码意识，只要平时注意积累，你也可以做优化的工作。这一篇随笔是转载，不过我强烈推荐给所有对数据库优化有兴趣的博友，读了这一篇之后下一次别人谈论优化的时候我们这些小菜就可以跟别人侃侃而谈了，不至于面面相觑，不知所措。

 

　　我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。

 

 

 

　　一、分析阶段

 

　　一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。

 

 

 

　　二、设计阶段

 

　　设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程——数据库设计。在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。

 

　　以下是性能要求设计阶段需要注意的：

 

　　1、数据库逻辑设计的规范化

 

　　数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

• 第1规范：没有重复的组或多值的列，就是一个表中的列不可再分，这是数据库设计的最低要求。
• 第2规范：每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。就是说一个表中的行可以唯一标识。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
• 第3规范：一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

　　更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

 

 

　　2、合理的冗余

 

　　完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

 

　　冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

　　冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

 

 

　　3、主键的设计

 

　　主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的。

 

　　在有多个键的表，主键的选择也比较重要，一般会选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

 

 

　　4、外键的设计

 

　　外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法。数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

 

　　谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

 

 

      5、字段的设计

 

　　字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

• 数据类型尽量用数字型，数字型的比较比字符型的快很多。
•  数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
• 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
• 少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
•  自增字段要慎用，不利于数据迁移。

 

　　6、数据库物理存储和环境的设计

 

　　在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。这里需要注意文件组的作用，使用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

 

 

　　7、系统设计

 

　　整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。

 

　　系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

 

 

　　8、索引的设计

 

　　在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

 

　　关于索引的选择，应该注意：

• 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
•  根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
• 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
•  一个表不要加太多索引，因为索引影响插入和更新的速度。

 

 

      三、编码阶段

 

　　编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。

 

　　编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。

 

　　下面罗列一些编程阶段需要注意的事项：

 

　　1、只返回需要的数据

 

　　返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：

 

　　A、横向来看，不要写SELECT *的语句，而是选择你需要的字段。

 

　　B、纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。

 

　　C、注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

 

　　D、对于聚合查询，可以用HAVING子句进一步限定返回的行。

 

 

      2、尽量少做重复的工作

 

　　这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：

 

　　A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

 

　　B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

 

　　C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

 

　　D、合并对同一表中同一条件的多次UPDATE，比如

UPDATE EMPLOYEE SET FNAME='HAIWER' WHERE EMP_ID='VPA30890F'
UPDATE EMPLOYEE SET LNAME='YANG' WHERE EMP_ID='VPA30890F'

      这两个语句应该合并成以下一个语句

UPDATE EMPLOYEE SET FNAME='HAIWER',LNAME='YANG' 
WHERE EMP_ID='VPA30890F'

 

      E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

 

　　F、不要写一些没有意义的查询，比如： SELECT * FROM EMPLOYEE WHERE 1=2

 

 

　   3、注意事务和锁

 

　　事务是数据库应用中重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：

 

　　A、事务操作过程要尽量小，能拆分的事务要拆分开来。

 

　　B、事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。

 

　　C、事务操作过程要按同一顺序访问对象。

 

　　D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

 

　　E、尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更优，但是数据量和数据分布在将来是会变化的。

 

　　F、查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

 

 

　　4、注意临时表和表变量的用法

 

　　在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

 

　　A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

 

　　B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

 

　　C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

 

　　D、其他情况下，应该控制临时表和表变量的使用。

 

　　E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

 

      F、关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO会比CREATE TABLE +INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。

 

　　G、注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和 TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。

 

 

　　5、子查询的用法

 

　　子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。

 

　　子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

 

　　关于相关子查询，应该注意：

 

      A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID NOT IN 
   (SELECT PUB_ID 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS') 

      可以改写成： 

SELECT A.PUB_NAME 
FROM PUBLISHERS A LEFT JOIN TITLES B 
ON     B.TYPE = 'BUSINESS' AND 
          A.PUB_ID=B.PUB_ID 
WHERE B.PUB_ID IS NULL 

 

     又如：

SELECT TITLE 
FROM TITLES 
WHERE NOT EXISTS 
   (SELECT TITLE_ID 
   FROM SALES 
   WHERE TITLE_ID = TITLES.TITLE_ID) 

     可以改写成： 

SELECT TITLE 
FROM TITLES LEFT JOIN SALES 
ON SALES.TITLE_ID = TITLES.TITLE_ID 
WHERE SALES.TITLE_ID IS NULL

 

     B、如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN 
   (SELECT PUB_ID 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS') 

     可以改写成： 

SELECT DISTINCT A.PUB_NAME 
FROM PUBLISHERS A INNER JOIN TITLES B 
ON     B.TYPE = 'BUSINESS' AND 
          A.PUB_ID=B.PUB_ID

 

     C、IN的相关子查询用EXISTS代替，比如

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN 
   (SELECT PUB_ID 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS') 

     可以用下面语句代替： 

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE EXISTS 
   (SELECT 1 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS' AND 
   PUB_ID= PUBLISHERS.PUB_ID)

 

     D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如：

SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0 

     应该改成： 

SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE  
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID 
WHERE EMPLOYEE.EMP_ID IS NULL 

 

     又如：

SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0 

     应该改成： 

SELECT JOB_DESC FROM JOBS 
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

 

 

      6、慎用游标

 

　　数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。

 

　　游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源(特别是内存和锁资源)的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。

 

　　另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。

 

　　A、字符串连接的例子

 

　　这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：

DECLARE @NAME VARCHAR(20) 
 DECLARE @NAMES VARCHAR(1000) 
 DECLARE NAME_CURSOR CURSOR FOR 
 SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID 
 OPEN NAME_CURSOR 
 FETCH NEXT FROM RNAME_CURSOR INTO @NAME 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
   SET @NAMES = ISNULL(@NAMES+', ', '')+@NAME 
   FETCH NEXT FROM NAME_CURSOR  INTO @NAME  
 END 
 CLOSE NAME_CURSOR 
 DEALLOCATE NAME_CURSOR 

      可修改为下面的语句，其功能相同： 

DECLARE @NAMES VARCHAR(1000) 
SELECT @NAMES = ISNULL(@NAMES+', ', '')+FNAME 
   FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID

 

      B、用CASE WHEN 实现转换的例子

 

　　很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如：

 

     表结构

CREATE TABLE 料件表( 
料号           VARCHAR(30), 
名称           VARCHAR(100), 
主单位         VARCHAR(20), 
单位1         VARCHAR(20), 
单位1参数      NUMERIC(18,4), 
单位2         VARCHAR(20), 
单位2参数      NUMERIC(18,4) 
) 

GO 

CREATE TABLE 入库表( 
时间               DATETIME, 
料号               VARCHAR(30), 
单位               INT, 
入库数量           NUMERIC(18,4), 
损坏数量           NUMERIC(18,4) 
) 

GO

     其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：

DECLARE @料号     VARCHAR(30), 
        @单位   INT, 
        @参数      NUMERIC(18,4), 

DECLARE CUR CURSOR FOR 
        SELECT 料号,单位 FROM 入库表 WHERE 单位 <>0 
OPEN CUR 
FETCH NEXT FROM CUR INTO @料号,@单位 
WHILE @@FETCH_STATUS<>-1 
BEGIN 
  IF @单位=1 
  BEGIN 
    SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) 
    UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR 
  END 
  IF @单位=2 
  BEGIN 
    SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) 
    UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR 
  END 
  FETCH NEXT FROM CUR INTO @料号,@单位 
END 
CLOSE CUR 
DEALLOCATE CUR 

     可以改写成： 

UPDATE A SET  
数量=CASE A.单位 WHEN 1 THEN      A.数量*B. 单位1参数 
                            WHEN 2 THEN      A.数量*B. 单位2参数 
                            ELSE A.数量 
END,                   
损坏数量=CASE A.单位 WHEN 1 THEN    A. 损坏数量*B. 单位1参数 
                                  WHEN 2 THEN    A. 损坏数量*B. 单位2参数 
                                  ELSE A. 损坏数量 
END, 
单位=1  
FROM入库表 A, 料件表 B 
WHERE A.单位<>1 AND A.料号=B.料号

 

      C、变量参与的UPDATE语句的例子

 

　　SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：

SELECT A,B,C,CAST(NULL AS INT) AS 序号 
INTO #T 
FROM 表 
ORDER BY A ,NEWID()

      产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为按照A字段分组的记录序号，就只有游标和变量参与的UPDATE语句可以实现了，这个变量参与的UPDATE语句如下：

DECLARE @A INT 
DECLARE @序号 INT 
UPDATE #T SET 
   @序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END, 
   @A=A, 
   序号=@序号

 

      D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项FAST_FORWARD），一般只需要静态游标（选项STATIC）。

 

　　E、注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。

 

 

      7、尽量使用索引

 

　　建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQL SERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。

 

　　为了使得优化器能高效使用索引，写语句的时候应该注意：

 

　　A、不要对索引字段进行运算，而要想办法做变换，比如：

SELECT ID FROM T WHERE NUM/2=100

     应改为: 

SELECT ID FROM T WHERE NUM=100*2 

 

     又如，

SELECT ID FROM T WHERE NUM/2=NUM1 

     如果NUM有索引应改为: 

SELECT ID FROM T WHERE NUM=NUM1*2 

     如果NUM1有索引则不应该改。 

 

     发现过这样的语句：

SELECT 年,月,金额 FROM 结余表 
WHERE 100*年+月=2007*100+10 

     应该改为： 

SELECT 年,月,金额 FROM 结余表 
WHERE 年=2007 AND 月=10

 

      B、不要对索引字段进行格式转换

 

　　日期字段的例子：

WHERE CONVERT(VARCHAR(10), 日期字段, 120)='2008-08-15' 

      应该改为：

WHERE 日期字段 >= '2008-08-15' AND 日期字段 < '2008-08-16'

     ISNULL转换的例子：

WHERE ISNULL(字段, '') <> ''        应改为：WHERE 字段 <> ''
WHERE ISNULL(字段, '') = ''          不应修改 
WHERE ISNULL(字段, 'F') = 'T'      应改为：WHERE字段 = 'T' 
WHERE ISNULL(字段, 'F') <> 'T'    不应修改

 

     C、不要对索引字段使用函数

WHERE LEFT(NAME, 3)='ABC' 或者 WHERE SUBSTRING(NAME,1, 3)='ABC' 

     应改为: 

WHERE NAME LIKE 'ABC%'

    日期查询的例子：

WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0   应改为：WHERE 日期 >= '2005-11-30' AND 日期 < '2005-12-1'
WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0   应改为：WHERE 日期 <'2005-11-30'
WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0 应改为：WHERE 日期 <'2005-12-01'
WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0   应改为：WHERE 日期>='2005-12-01'
WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0 应改为：WHERE 日期>='2005-11-30'

 

 

原文地址：http://www.51testing.com/?uid-275878-action-viewspace-itemid-242973