以下是性能要求设计阶段需要注意的:
1、 数据库逻辑设计的规范化
数据库逻辑设计的规范化就是我们一般所说的范式,我们可以这样来简单理解范式:
第1规范:没有重复的组或多值的列,这是数据库设计的最低要求。
第2规范:每个非关键字段必须依赖于主关键字,不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖,大部分情况下,数据库设计都应该达到第二范式。
第3规范:一个非关键字段不能依赖于另一个非关键字段。消除传递依赖,达到第三范式应该是系统中大部分表的要求,除非一些特殊作用的表。
更高的范式要求这里就不再作介绍了,个人认为,如果全部达到第二范式,大部分达到第三范式,系统会产生较少的列和较多的表,因而减少了数据冗余,也利于性能的提高。
2、 合理的冗余
完全按照规范化设计的系统几乎是不可能的,除非系统特别的小,在规范化设计后,有计划地加入冗余是必要的。
冗余可以是冗余数据库、冗余表或者冗余字段,不同粒度的冗余可以起到不同的作用。
冗余可以是为了编程方便而增加,也可以是为了性能的提高而增加。从性能角度来说,冗余数据库可以分散数据库压力,冗余表可以分散数据量大的表的并发压力,也可以加快特殊查询的速度,冗余字段可以有效减少数据库表的连接,提高效率。
3、 主键的设计
主键是必要的,SQL SERVER的主键同时是一个唯一索引,而且在实际应用中,我们往往选择最小的键组合作为主键,所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的,这个在下面索引的叙述。
在有多个键的表,主键的选择也比较重要,一般选择总的长度小的键,小的键的比较速度快,同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序,对于组合主键来说,不同的字段次序的主键的性能差别可能会很大,一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
4、 外键的设计
外键作为数据库对象,很多人认为麻烦而不用,实际上,外键在大部分情况下是很有用的,理由是:
外键是最高效的一致性维护方法,数据库的一致性要求,依次可以用外键、CHECK约束、规则约束、触发器、客户端程序,一般认为,离数据越近的方法效率越高。
谨慎使用级联删除和级联更新,级联删除和级联更新作为SQL SERVER 2000当年的新功能,在2005作了保留,应该有其可用之处。我这里说的谨慎,是因为级联删除和级联更新有些突破了传统的关于外键的定义,功能有点太过强大,使用前必须确定自己已经把握好其功能范围,否则,级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。
5、 字段的设计
字段是数据库最基本的单位,其设计对性能的影响是很大的。需要注意如下:
A、数据类型尽量用数字型,数字型的比较比字符型的快很多。
B、 数据类型尽量小,这里的尽量小是指在满足可以预见的未来需求的前提下的。
C、 尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT代替。
D、少用TEXT和IMAGE,二进制字段的读写是比较慢的,而且,读取的方法也不多,大部分情况下最好不用。
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 @NAME 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 @NAME 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、 尽量使用索引
建立索引后,并不是每个查询都会使用索引,在使用索引的情况下,索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引,索引的选择和使用方法是SQLSERVER的优化器自动作的选择,而它选择的根据是查询语句的条件以及相关表的统计信息,这就要求我们在写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‘
D、不要对索引字段进行多字段连接
比如:
WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’
应改为:
WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’
8、 注意连接条件的写法
多表连接的连接条件对索引的选择有着重要的意义,所以我们在写连接条件条件的时候需要特别的注意。
A、多表连接的时候,连接条件必须写全,宁可重复,不要缺漏。
B、 连接条件尽量使用聚集索引
C、 注意ON部分条件和WHERE部分条件的区别
9、 其他需要注意的地方
经验表明,问题发现的越早解决的成本越低,很多性能问题可以在编码阶段就发现,为了提早发现性能问题,需要注意:
A、程序员注意、关心各表的数据量。
B、 编码过程和单元测试过程尽量用数据量较大的数据库测试,最好能用实际数据测试。
C、 每个SQL语句尽量简单
D、不要频繁更新有触发器的表的数据
E、 注意数据库函数的限制以及其性能
10、 学会分辩SQL语句的优劣
自己分辨SQL语句的优劣非常重要,只有自己能分辨优劣才能写出高效的语句。
A、 查看SQL语句的执行计划,可以在查询分析其使用CTRL+L图形化的显示执行计划,一般应该注意百分比最大的几个图形的属性,把鼠标移动到其上面会显示这个图形的属性,需要注意预计成本的数据,也要注意其标题,一般都是CLUSTERED INDEX SEEK 、INDEX SEEK、CLUSTERED INDEX SCAN、INDEX SCAN、TABLE SCAN等,其中出现SCAN说明语句有优化的余地。也可以用语句
SET SHOWPLAN_ALL ON
要执行的语句
SET SHOWPLAN_ALL OFF
查看执行计划的文本详细信息。
B、 用事件探查器跟踪系统的运行,可疑跟踪到执行的语句,以及所用的时间,CPU用量以及I/O数据,从而分析语句的效率。
C、 可以用WINDOWS的系统性能检测器,关注CPU、I/O参数
四、 测试、试运行、维护阶段
测试的主要任务是发现并修改系统的问题,其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方,并进行必要的优化。主要进行语句优化、索引优化等。
试运行和维护阶段是在实际的环境下运行系统,发现的问题范围更广,可能涉及操作系统、网络以及多用户并发环境出现的问题,其优化也扩展到操作系统、网络以及数据库物理存储的优化。
这个阶段的优花方法在这里不再展开,只说明下索引维护的方法:
A、 可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建,索引重建的目的是提高索引的效能。
B、 可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新,其目的是使得统计信息更能反映实际情况,从而使得优化器选择更合适的索引。
C、 可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题,这两个语句也能修复一般的问题。
D、
五、 网上资料中一些说法的个人不同意见
1、 “应尽量避免在 WHERE 子句中对字段进行 NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
SELECT ID FROM T WHERE NUM IS NULL
可以在NUM上设置默认值0,确保表中NUM列没有NULL值,然后这样查询:
SELECT ID FROM T WHERE 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 NUM=@NUM
可以改为强制查询使用索引:
SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”
个人意见:关于局部变量的解释比较奇怪,使用参数如果会影响性能,那存储过程就该校除了,我坚持我上面对于强制索引的看法。
6、 “尽可能的使用 VARCHAR/NVARCHAR代替 CHAR/NCHAR,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。”
个人意见:“在一个相对较小的字段内搜索效率显然要高些”显然是对的,但是字段的长短似乎不是由变不变长决定,而是业务本身决定。在SQLSERVER6.5或者之前版本,不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多,所以对于那些版本,我们都是推荐使用定长字段存储一些关键字段。而在2000版本,修改了不定长字符串字段的比较方法,与定长字段的比较速度差别不大了,这样为了方便,我们大量使用不定长字段。
7、 关于连接表的顺序或者条件的顺序的说法,经过测试,在SQL SERVER,这些顺序都是不影响性能的,这些说法可能是对ORACLE有效。