一 、分析阶段
一般来说,在系统分析阶段往往有太多需要关注的地方,系统各种功能性、可用性 、可靠性 、安全性需求往往吸引了我们大部分的注意力,但是,我们必须注意,性能是很重要的非功能性需求,必须根据系统的特点确定其实时性需求、响应时间的需求 、硬件的配置等 。最好能有各种需求的量化的指标 。
另一方面,在分析阶段应该根据各种需求区分出系统的类型,大的方面,区分是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 、只返回需要的数据
返回数据到客户端至少需要数据库提取数据、网络传输数据 、客户端接收数据以及客户端处理数据等环节,如果返回不需要的数据,就会增加服务器 、网络和客户端的无效劳动,其害处是显而易见的,避免这类事件需要注意:
①、横向来看,不要写SELECT *的语句,而是选择你需要的字段 。
②、纵向来看,合理写WHERE子句,不要写没有WHERE的SQL语句 。
③、注意SELECT INTO后的WHERE子句,因为SELECT INTO把数据插入到临时表,这个过程会锁定一些系统表,如果这个WHERE子句返回的数据过多或者速度太慢,会造成系统表长期锁定,诸塞其他进程 。
④ 、对于聚合查询,可以用HA VING子句进一步限定返回的行 。
2、尽量少做重复的工作
这一点和上一点的目的是一样的,就是尽量减少无效工作,但是这一点的侧重点在客户端程序,需要注意的如下:
① 、控制同一语句的多次执行,特别是一些基础数据的多次执行是很多程序员很少注意的 。
② 、减少多次的数据转换,也许需要数据转换是设计的问题,但是减少次数是程序员可以做到的。
③、杜绝不必要的子查询和连接表,子查询在执行计划一般解释成外连接,多余的连接表带来额外的开销。
④、合并对同一表同一条件的多次UPDA TE,比如:
UPDA TE EMPLOYEE
SET FNAME='HAIWER'
WHERE EMP_
UPDA TE EMPLOYEE
SET LNAME='Y ANG'
WHERE EMP_
这两个语句应该合并成以下一个语句
UPDA TE EMPLOYEE
SET FNAME='HAIWER'
,LNAME='Y ANG'
WHERE EMP_
⑤ 、UPDA TE操作不要拆成DELETE操作+INSERT操作的形式,虽然功能相同,但是性能差别是很大的 。
⑥ 、不要写一些没有意义的查询,比如:
SELECT * FROM EMPLOYEE WHERE 1=2
3、注意事务和锁
事务是数据库应用中和重要的工具,它有原子性、一致性 、隔离性 、持久性这四个属性,很多操作我们都需要利用事务来保证数据的正确性 。在使用事务中我们需要做到尽量避免死锁 、尽量减少阻塞 。具体以下方面需要特别注意:
① 、事务操作过程要尽量小,能拆分的事务要拆分开来。
② 、事务操作过程不应该有交互,因为交互等待的时候,事务并未结束,可能锁定了很多资源 。
③ 、事务操作过程要按同一顺序访问对象。
④、提高事务中每个语句的效率,利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间 。
⑤、尽量不要指定锁类型和索引,SQL SERVER允许我们自己指定语句使用的锁类型和索引,但是一般情况下,SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的,我们指定的可能只是在目前情况下更有,但是数据量和数据分布在将来是会变化的。
⑥ 、查询时可以用较低的隔离级别,特别是报表查询的时候,可以选择最低的隔离级别(未提交读) 。
4、注意临时表和表变量的用法
在复杂系统中,临时表和表变量很难避免,关于临时表和表变量的用法,需要注意:
① 、如果语句很复杂,连接太多,可以考虑用临时表和表变量分步完成 。
②、如果需要多次用到一个大表的同一部分数据,考虑用临时表和表变量暂存这部分数据。
③ 、如果需要综合多个表的数据,形成一个结果,可以考虑用临时表和表变量分步汇总这多个表的数据。
④ 、其他情况下,应该控制临时表和表变量的使用 。
⑤、关于临时表和表变量的选择,很多说法是表变量在内存,速度快,应该首选表变量,但是在实际使用中发现,这个选择主要考虑需要放在临时表的数据量,在数据量较多的情况下,临时表的速度反而更快 。
⑥、关于临时表产生使用SELECT INTO和CREA TE TABLE + INSERT INTO的选择,我们做过测试,一般情况下,SELECT INTO会比CREA TE TABLE + INSERT INTO的方法快很多,但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES 、SYSCOLUMNS,在多用户并发环境下,容易阻塞其他进程,所以我的建议是,在并发系统中,尽量使用CREA TE TABLE + INSERT INTO,而大数据量的单个语句使用中,使用SELECT INTO 。
⑦ 、注意排序规则,用CREA TE TABLE建立的临时表,如果不指定字段的排序规则,会选择TEMPDB的默认排序规则,而不是当前数据库的排序规则 。如果当前数据库的排序规则和TEMPDB的排序规则不同,连接的时候就会出现排序规则的冲突错误 。一般可以在CREA TE TABLE建立临时表时指定字段的排序规则为DA TABASE_DEFAULT来避免上述问题。
5 、子查询的用法
子查询是一个SELECT 查询,它嵌套在SELECT、INSERT、UPDA TE、DELETE 语句或其它子查询中 。任何允许使用表达式的地方都可以使用子查询 。
子查询可以使我们的编程灵活多样,可以用来实现一些特殊的功能 。但是在性能上,往往一个不合适的子查询用法会形成一个性能瓶颈 。
如果子查询的条件中使用了其外层的表的字段,这种子查询就叫作相关子查询 。相关子查询可以用IN 、NOT IN 、EXISTS 、NOT EXISTS引入 。
关于相关子查询,应该注意:
①、NOT IN 、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如:
SELECT PUB_NAMEFROM PUBLISHERS
WHERE PUB_ID NOT IN (SELECT PUB_ID
FROM TITLES
WHERE TYPE = 'BUSINESS')
可以改写成:
SELECT A.PUB_NAMEFROM PUBLISHERS A
LEFT JOIN TITLES B
ON B.TYPE = 'BUSINESS' AND A.PUB_BUSINESS')
可以改写成:
SELECT DISTINCT A.PUB_NAMEFROM PUBLISHERS A
INNER JOIN TITLES B
ON B.TYPE = 'BUSINESS' AND
A.PUB_BUSINESS')
可以用下面语句代替:
SELECT PUB_NAMEFROM PUBLISHERS
WHERE EXISTS (SELECT 1
FROM TITLES WHERE TYPE = 'BUSINESS' AND
PUB_,','')[email protected]
FETCH NEXT FROM NAME_CURSOR INTO @NAME
END
CLOSE NAME_CURSOR
DEALLOCA TE NAME_CURSOR
可以如下修改,功能相同:
DECLARE @NAME V ARCHAR(1000)
SELECT @NAMES = ISNULL(@NAMES+',','')+FNAME
FROM EMPLOYEE WHERE JOB_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'--不应修改
③、不要对索引字段使用函数
WHERE LEFT(NAME, 3)='ABC'
或者
WHERE SUBSTRING(NAME,1, 3)='ABC'
应改为:
WHERE NAME LIKE 'ABC%'
日期查询的例子:
WHERE DA TEDIFF(DAY, 日期,'2005-11-30')=0
应改为:
WHERE 日期>='2005-11-30' AND 日期＜'2005-12-1’
WHERE DA TEDIFF(DAY, 日期,'2005-11-30')>0
应改为:
WHERE 日期＜'2005-11-30’
WHERE DA TEDIFF(DAY, 日期,'2005-11-30')>=0
应改为:
WHERE 日期＜'2005-12-01’
WHERE DA TEDIFF(DAY, 日期,'2005-11-30')＜0
应改为:
WHERE 日期>='2005-12-01’
WHERE DA TEDIFF(DAY, 日期,'2005-11-30')＜=0
应改为:
WHERE 日期>='2005-11-30’
③ 、不要对索引字段进行多字段连接,比如:
WHERE FAME+ '.'+LNAME=‘HAIWEI.Y ANG'
应改为:
WHERE FNAME=‘HAIWEI' AND LNAME=‘Y ANG'
8 、注意连接条件的写法
多表连接的连接条件对索引的选择有着重要的意义,所以我们在写连接条件条件的时候需要特别的注意 。
①、多表连接的时候,连接条件必须写全,宁可重复,不要缺漏 。
②、连接条件尽量使用聚集索引
③ 、注意ON部分条件和WHERE部分条件的区别
9 、其他需要注意的地方
经验表明,问题发现的越早解决的成本越低,很多性能问题可以在编码阶段就发现,为了提早发现性能问题,需要注意:
① 、程序员注意、关心各表的数据量 。
② 、编码过程和单元测试过程尽量用数据量较大的数据库测试,最好能用实际数据测试 。
③、每个SQL语句尽量简单
④、不要频繁更新有触发器的表的数据
⑤ 、注意数据库函数的限制以及其性能
10、学会分辩SQL语句的优劣
自己分辨SQL语句的优劣非常重要,只有自己能分辨优劣才能写出高效的语句 。
①、查看SQL语句的执行计划,可以在查询分析其使用CTRL+L图形化的显示执行计划,一般应该注意百分比最大的几个图形的属性,把鼠标移动到其上面会显示这个图形的属性,需要注意预计成本的数据,也要注意其标题,一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等,其中出现SCAN说明语句有优化的余地 。也可以用语句
SET SHOWPLAN_ALL ON
要执行的语句
SET SHOWPLAN_ALL OFF
查看执行计划的文本详细信息。
②、用事件探查器跟踪系统的运行,可疑跟踪到执行的语句,以及所用的时间,CPU用量以及I/O数据,从而分析语句的效率 。
③ 、可以用WINDOWS的系统性能检测器,关注CPU、I/O参数
四 、测试 、试运行、维护阶段
测试的主要任务是发现并修改系统的问题,其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方,并进行必要的优化 。主要进行语句优化 、索引优化等 。
试运行和维护阶段是在实际的环境下运行系统,发现的问题范围更广,可能涉及操作系统 、网络以及多用户并发环境出现的问题,其优化也扩展到操作系统 、网络以及数据库物理存储的优化 。
这个阶段的优花方法在这里不再展开,只说明下索引维护的方法:
①、可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建,索引重建的目的是提高索引的效能 。
② 、可以用语句UPDA TE STA TISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新,其目的是使得统计信息更能反映实际情况,从而使得优化器选择更合适的索引。
③ 、可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题,这两个语句也能修复一般的问题 。
④、五 、网上资料中一些说法的个人不同意见
(1)、“应尽量避免在WHERE 子句中对字段进行NULL 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
SELECT ID FROM T WHERE NUM IS NULL
可以在NUM上设置默认值0,确保表中NUM列没有NULL值,然后这样查询:
SELECT ID FROM T WHE RE NUM=0”
个人意见:经过测试,IS NULL也是可以用INDEX SEEK查找的,0和NULL是不同概念的,以上说法的两个查询的意义和记录数是不同的 。
(2) 、“应尽量避免在WHERE 子句中使用!=或＜>操作符,否则将引擎放弃使用索引而进行全表扫描 。”
个人意见:经过测试,＜>也是可以用INDEX SEEK查找的。
(3) 、“应尽量避免在WHERE 子句中使用OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
SELECT ID FROM T WHERE NUM=10 OR NUM=20
可以这样查询:
SELECT ID FROM T WHERE NUM=10
UNION ALL
SELECT ID FROM T WHERE NUM=20”
个人意见:主要对全表扫描的说法不赞同 。
(4)、“IN 和NOT IN 也要慎用,否则会导致全表扫描,如:
SELECT ID FROM T WHERE NUM IN(1,2,3)
对于连续的数值,能用BETWEEN 就不要用IN 了:
SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”
个人意见:主要对全表扫描的说法不赞同 。
(5)、“如果在WHERE 子句中使用参数,也会导致全表扫描 。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项 。如下面语句将进行全表扫描:
SELECT ID FROM T WHERE [email protected]
可以改为强制查询使用索引:
SELECT ID FROM T WITH(INDEX(索引名)) WHERE [email protected]”
个人意见:关于局部变量的解释比较奇怪,使用参数如果会影响性能,那存储过程就该校除了,我坚持我上面对于强制索引的看法 。
(6) 、“尽可能的使用V ARCHAR/NV ARCHAR 代替CHAR/NCHAR ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些 。”
个人意见:“在一个相对较小的字段内搜索效率显然要高些”显然是对的,但是字段的长短似乎不是由变不
变长决定,而是业务本身决定 。在SQLSERVER6.5或者之前版本,不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多,所以对于那些版本,我们都是推荐使用定长字段存储一些关键字段 。而在2000版本,修改了不定长字符串字段的比较方法,与定长字段的比较速度差别不大了,这样为了方便,我们大量使用不定长字段 。
(7) 、关于连接表的顺序或者条件的顺序的说法,经过测试,在SQL SERVER,这些顺序都是不影响性能的,这些说法可能是对ORACLE有效 。
。
