快捷搜索:

SQL Server性能优化综述(1) - SQL Server性能优化

近期因事情必要,盼望对照周全的总结下SQL Server数据库机能优化相关的留意事变,在网上搜索了一下,发明很多文章,有的都列出了上百条,然则仔细看发明,有很多似是而非或者逾期(可能对SQL SERVER6.5曩昔的版本或者ORACLE是适用的)的信息,只好自己根据曩昔的履历和测试结果进行总结了。

我始终觉得,一个系统的机能的前进,不单单是试运行或者掩护阶段的机能调优的义务,也不单单是开拓阶段的工作,而是在全部软件生命周期都必要留意,进行有效事情才能达到的。以是我盼望按照软件生命周期的不合阶段来总结数据库机能优化相关的留意事变。

一、 阐发阶段

一样平常来说,在系统阐发阶段每每有太多必要关注的地方,系统各类功能性、可用性、靠得住性、安然性需求每每吸引了我们大年夜部分的留意力,然则,我们必须留意,机能是很紧张的非功能性需求,必须根据系统的特征确定着实时性需求、相应光阴的需求、硬件的设置设置设备摆设摆设等。最好能有各类需求的量化的指标。

另一方面,在阐发阶段应该根据各类需求区分出系统的类型,大年夜的方面,区分是OLTP(联机事务处置惩罚系统)和OLAP(联机阐发处置惩罚系统)。

二、 设计阶段

设计阶段可以说是今后系统机能的关键阶段,在这个阶段,有一个关系到今后险些所有机能调优的历程—数据库设计。

在数据库设计完成后,可以进行初步的索引设计,好的索引设计可以指示编码阶段写出高效率的代码,为全部系统的机能打下优越的根基。

以下是机能要求设计阶段必要留意的:

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

数据库逻辑设计的规范化便是我们一样平常所说的范式,我们可以这样来简单理解范式:

第1规范:没有重复的组或多值的列,这是数据库设计的最低要求。

第2规范: 每个非关键字段必须依附于主关键字,不能依附于一个组合式主关键字的某些组成部分。打消部分依附,大年夜部分环境下,数据库设计都应该达到第二范式。

第3规范: 一个非关键字段不能依附于另一个非关键字段。打消通报依附,达到第三范式应该是系统中大年夜部分表的要求,除非一些特殊感化的表。

更高的范式要求这里就不再作先容了,小我觉得,假如整个达到第二范式,大年夜部分达到第三范式,系统会孕育发生较少的列和较多的表,因而削减了数据冗余,也利于机能的前进。

2、 合理的冗余

完全按照规范化设计的系统险些是弗成能的,除非系统特其余小,在规范化设计后,有计划地加入冗余是需要的。

冗余可所以冗余数据库、冗余表或者冗余字段,不合粒度的冗余可以起到不合的感化。

冗余可所认为了编程方便而增添,也可所认为了机能的前进而增添。从机能角度来说,冗余数据库可以分散数据库压力,冗余表可以分散数据量大年夜的表的并发压力,也可以加快特殊查询的速率,冗余字段可以有效削减数据库表的连接,前进效率。

3、 主键的设计

主键是需要的,SQL SERVER的主键同时是一个独一索引,而且在实际利用中,我们每每选择最小的键组相助为主键,以是主键每每得当作为表的凑集索引。凑集索引对查询的影响是对照大年夜的,这个鄙人面索引的论述。

在有多个键的表,主键的选择也对照紧张,一样平常选择总的长度小的键,小的键的对照速率快,同时小的键可以使主键的B树布局的层次更少。

主键的选择还要留意组合主键的字段序次,对付组合主键来说,不合的字段序次的主键的机能区别可能会很大年夜,一样平常应该选择重复率低、零丁或者组合查询可能性大年夜的字段放在前面。

4、 外键的设计

系统设计阶段应该归纳一些营业逻辑放在数据库编程实现,数据库编程包括数据库存储历程、触发器和函数。用数据库编程实现营业逻辑的好处是削减收集流量并可更充分使用数据库的预编译懈弛存功能。

8、 索引的设计

在设计阶段,可以根据功能和机能的需求进行初步的索引设计,这里必要根据估计的数据量和查询来设计索引,可能与将来实际应用的时刻会有所差别。

关于索引的选择,应改主见:

A、 根据数据量抉择哪些表必要增添索引,数据量小的可以只有主键。

B、 根据应用频率抉择哪些字段必要建立索引,选择常常作为连接前提、筛选前提、聚合查询、排序的字段作为索引的候选字段。

C、 把常常一路呈现的字段组合在一路,组成组合索引,组合索引的字段顺序与主键一样,也必要把最常用的字段放在前面,把重复率低的字段放在前面。

D、 一个表不要加太多索引,由于索引影响插入和更新的速率。

三、 编码阶段

编码阶段是本文的重点,由于在设计确定的环境下,编码的质量险些抉择了全部系统的质量。

编码阶段首先是必要所有法度榜样员有机能意识,也便是在实现功能同时有斟酌机能的思惟,数据库是能进行聚拢运算的对象,我们应该只管即便的使用这个对象,所谓聚拢运算实际是批量运算,便是只管即便削减在客户端进行大年夜数据量的轮回操作,而用SQL语句或者存储历程代替。关于思惟和意识,很难说得很清楚,必要在编程历程中来体会。

下面列举一些编程阶段必要留意的事变:

1、 只返回必要的数据

返回数据到客户端至少必要数据库提取数据、收集传输数据、客户端接管数据以及客户端处置惩罚数据等环节,假如返回不必要的数据,就会增添办事器、收集和客户真个无效劳动,其害处是显而易见的,避免这类事故必要留意:

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。

SELECTPUB_NAME

FROMPUBLISHERS

WHEREPUB_IDIN

(SELECTPUB_ID

FROMTITLES

WHERETYPE='BUSINESS')

可以改写成:

SELECTDISTINCTA.PUB_NAME

FROMPUBLISHERSAINNERJOINTITLESB

ONB.TYPE='BUSINESS'AND

A.PUB_ID=B.PUB_ID

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

SELECTPUB_NAME

FROMPUBLISHERS

WHEREPUB_IDIN

(SELECTPUB_ID

FROMTITLES

WHERETYPE='BUSINESS')

可以用下面语句代替:

SELECTPUB_NAME

FROMPUBLISHERS

WHEREEXISTS

(SELECT1

FROMTITLES

WHERETYPE='BUSINESS'AND

PUB_ID=PUBLISHERS.PUB_ID)

D、不要用COUNT(*)的子查询判断是否存在记录,最好用LEFT JOIN或者EXISTS,比如有人写这样的语句:

SELECTJOB_DESCFROMJOBS

WHERE(SELECTCOUNT(*)FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)=0

应该改成:

SELECTJOBS.JOB_DESCFROMJOBSLEFTJOINEMPLOYEE

ONEMPLOYEE.JOB_ID=JOBS.JOB_ID

WHEREEMPLOYEE.EMP_IDISNULL

SELECTJOB_DESCFROMJOBS

WHERE(SELECTCOUNT(*)FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)

应该改成:

SELECTJOB_DESCFROMJOBS

WHEREEXISTS(SELECT1FROMEMPLOYEEWHEREJOB_ID=JOBS.JOB_ID)

6、 慎用游标

数据库一样平常的操作是聚拢操作,也便是对由WHERE子句和选择列确定的结果集作聚拢操作,游标是供给的一个非聚拢操作的道路。一样平常环境下,游标实现的功能每每相称于客户真个一个轮回实现的功能,以是,大年夜部分环境下,我们把游标功能搬到客户端。

SQL ERVER的语句对照机动,变量介入的UPDATE语句可以实现一些游标一样的功能,比如:

SELECTA,B,C,CAST(NULLASINT)AS序号

INTO#T

FROM表

ORDERBYA,NEWID()

孕育发生临时表后,已经按照A字段排序,然则在A相同的环境下是乱序的,这时假如必要变动序号字段为按照A字段分组的记录序号,就只有游标和变量介入的UPDATE语句可以实现了,这个变量介入的UPDATE语句如下:

DECLARE@AINT

DECLARE@序号INT

UPDATE#TSET

@序号=CASEWHENA=@ATHEN@序号+1ELSE1END,

@A=A,

序号=@序号

D、假如必须应用游标,留意选择游标的类型,假如只是轮回取数据,那就应该用只进游标(选项FAST_FORWARD),一样平常只必要静态游标(选项STATIC)。

E、 留意动态游标的不确定性,动态游标查询的记录集数据假如被改动,会自动刷新游标,这样使得动态游标有了不确定性,由于在多用户情况下,假如其他进程或者本身变动了记载,就可能刷新游标的记录集。

7、 只管即便应用索引

建立索引后,并不是每个查询都邑应用索引,在应用索引的环境下,索引的应用效率也会有很大年夜的区别。只要我们在查询语句中没有强制指定索引,索引的选择和应用措施是SQLSERVER的优化器自动作的选择,而它选择的根据是查询语句的前提以及相关表的统计信息,这就要求我们在写SQL语句的时刻只管即便使得优化器可以应用索引。

为了使得优化器能高效应用索引,写语句的时刻应该留意:

A、不要对索引字段进交运算,而要设法主见子做变换,比如

SELECTIDFROMTWHERENUM/2=100

应改为:

SELECTIDFROMTWHERENUM=100*2

SELECTIDFROMTWHERENUM/2=NUM1

假如NUM有索引应改为:

您可能还会对下面的文章感兴趣: