关于SQL优化的一些思想和原则（★）

第一篇：关于SQL优化的一些思想和原则

关于SQL优化的一些思想和原则

一、优化的目的对应用程序的优化通常可分为两个方面：源代码优化和SQL语句优化。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高(尤其是对正在使用中的系统进行优化)。另一方面，源代码的优化对数据库系统性能的提升收效有限，因为应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。

对SQL语句优化有以下一些直接原因：

1.SQL语句是对数据库(数据)进行操作的惟一途径，应用程序的执行最终要归结为SQL语句的执行，SQL语句的效率对数据库系统的性能起到了决定性的作用。

2.SQL语句消耗了70%～90%的数据库资源。

3.SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑，相对于对程序源代码的优化，对SQL语句优化在时间成本和风险上的代价都很低。

4.SQL语句可以有不同的写法，不同的写法在性能上的差异可能很大。

5.SQL语句易学，难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关，不存在普遍适用的规律来提升性能。

二、优化数据库的思想

1、关键字段建立索引。

2、使用存储过程，它使SQL变得更加灵活和高效。

3、备份数据库和清除垃圾数据。

4、SQL语句语法的优化。

5、清理删除日志。

三、SQL语句优化的原则

不要以为只有SELECT语句是查询。实际上，带有任何WHERE条件的DML(INSERT、UPDATE、DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指SELECT语句，也有可能指DML语句中的查询部分。

我们知道，SQL语句同其它语言(如C语言)的语句不一样，它是非过程化(non-procedural)的语句，即当你要取数据时，不需要告诉数据库通 过何种途径去取数据，如到底是通过索引取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方式取数据(即全表扫描)。

为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据生成一个执行计划(execution plan)。典型的，对于同一个查询，可能有几个执行计划都符合要求，都能得到符合条件的数据。例如，参与连接的表可以有多种不同的连接方法，这取决于连接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划，优化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次数、CPU等)，这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。

第二篇：SQL语句性能优化

我也做了很长时间医疗软件，也写过不少sql优化，没有详细记录下来，个人感觉下面转载的更符合医院医疗软件实际业务，很认可大部分所写的原则，固转载过来，以作借鉴。软件的根本还是在于更细更精，在于从客户的实际使用考虑问题。

性能优化原则1:永远避免困境

利用缓存把字典数据取到中间服务器或是客户端替代直接sql查询,如,门诊医生站把字典下载到客户端,减少执行次数。

一次性取数据到客户端，然后再逐条处理，而不是分次取数据，处理好一条数据再取下一条再处理。例：门诊收费取hjcfmxk例子，原来是一张处方条明细都查询一次，查询后再处理，现改为一次把所有明细都取过来，然后一条条处理

尽量减少光标，看能不能用临时表

性能优化原则2:kiss原则

对于where 条件中的左边可以利用索引的字段Keep it simple stupid，左边尽量避免用函数(substring,isnull,upper,lower)，参加计算+,-*/

例子1：select * from ZY_BRFYMXK where substring(zxrq,1,8)='20081212‘

select * from ZY_BRFYMXK where zxrq between '2008121200' and '2008121224' 例子2：

select * from zy_detail_charge where SUBSTRING(patient_id,1,10)=

substring('000005090600',1,10)这句耗时30秒以上

select * from zy_detail_charge where patient_id like substring('000005090600',1,10)+'%' 这句耗时2秒以内

性能优化原则3:尽可能利用到索引

例：select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0

select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0 and b.syxh=3

性能优化原则4:or,避而远之

对于索引字段尽力避免用or，普通字段可以用or，解决要么分解成多个sql,要么用业务规则避免，例：declare @rq1 ut_rq16,@syxh ut_syxh

select @rq1='20081201'

select @syxh=157

性能优化原则5:避免大批量数据取到前台

例： select * from ZY_BRSYK cyrq between ‘20080901’ and ‘20081201‘,对于大医院每天100多人，90天是9000条数据

性能优化原则6:事务,尽可能的短吧

所有计算、对临时表的更新都应但放在事务外，事务中最好只有更新和插入正式表操作.因为事务中产生的锁只有在commit tran是才会释放。

性能优化原则7:热表，留在最后吧

热表是频繁调用的表。如：sf_mzcfk,zy_brfymxk,bq_fyqqk.对于热表尽量放在事务最后：这样锁的时间短。大家都坚持这样，死锁的可能性就小。如果都是热表各个存储过程更新表的顺序应当一样这样可以避免死锁

性能优化原则8:创建临时表一定要避免在事务中作

如create #tempXX(…)

Select * into #tempXX from …

因为创建临时表会锁tempdb的系统表

例：生成#temp1放在事务内外，用sp_lock2 ‘’观察结果

if object_id('tempdb..#temp1','U')is not null

drop table #temp1

begin tran

select * into #temp1 from ZY_BRSYK where ryrq>'20080901‘

select * from #temp1

waitfor delay '00:00:10'

commit

性能优化原则9:大的报表查询避免与正常业务碰撞

如果没有查询服务器，那要在存储过程中限制不能操作加上如：

declare @rq1 ut_rq16,@rq2 ut_rq16,@now ut_rq16

select @rq1=convert(varchar(8),getdate(),112)+'08:00:00'

select @rq1=convert(varchar(8),getdate(),112)+'11:30:00'

select @now=convert(char(8),getdate(),112)+convert(char(8),getdate(),8)

if @now>@rq1 and @now<@rq2

begin

select '上午繁忙时间段不能作此查询'

return

end

性能优化原则10:存储过程避免大的if…else…

这个常出项在业务相同表不同的存储过程中，因为这样常到if …else …原来医技接口中很多这种存储过程，当时把门诊住院业务放在一个存储过程中。这样最大的问题是sql server会根据sql语句来compile存储，这个过程会生成优化计划，决定用那个索引。如果存储过程用到门诊表compile一下，到用到住院表是发现不对，又会compile一下，这样不停compile.compile很号时间要1-2秒，而且一个存储过成在compile是，所有调用这个存储过程的进程都要在排队等候,因为他会独占锁这个存储过程

例：usp_yjjk_getwzxxm_old.sql,后改为：

usp_yjjk_getwzxxm.sql, usp_yjjk_getwzxxm_mz.sql,usp_yjjk_getwzxxm_zy.sql

性能优化原则11:进攻是最好的防守

在普通编程语句对于数据校验总是用防守办法先判断,后再作相应处理。而在sql中先处理再判断性能会好很多。

--更新药品库存。

If exists(select 1 from YK_YKZKC WHERE idm=100 and kcsl>50)

begin

update YK_YKZKC set kcsl=kcsl-50 where idm=100

End

Else begin

rollback tran

Select ‘F库存不够’

return

end

--改为

update YK_YKZKC set kcsl=kcsl-50 where idm=100 and kcsl>50

If @@rowcount<=0

Begin

Rollbakc tran

Select ‘F库存不够’

end

--取未执行的医技项目,日表没有数据就到年表中查找

if exists(select a.* from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

begin

select a.* into #temp1 from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

end

else begin

select a.* into #temp1 from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)

end

--改为

Insert into #temp1 select a.*

from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

If @@rowcount=0

Begin

Insert into #temp1 select a.*

from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)

end

性能优化原则12:trig最后的手段

Trig（触发器）的处理的处理机制是满足条件时就会在源语句后面加上trig中的代码进行执行。

它有两个致命的弊端：（1）不清楚有trig的人会对于执行结果感到迷惑。如常有在插入一张表如果主键是indentity的值常取用select @@identity。但如是有trig,tring中有表插入操作，这时的@@identity可能就不是想要的值。（2）trig会束缚选择。如：有一套单据主表和明细表，当明细表的金额更新时，要同步主表的金额，当程序是一条条更新明细时用trig的作法是每当更新一条明细记录时都算一处所有明细表的总金额，再去更新主表的金额。这样有多少条明细就要算多少次，好的作法是不要trig,直接在sql语句中明细更新完明后，一次性算出总金额每条单据的总金额，再更新主表的金额。

对于trig如果有其他手段就一定要避免用trig.性能优化原则13:用户说好才是真的好

1）有时sql语句性能难以优化，但用户对于系统响应速度还是不满意。这时可以从业务分析处理。

如：我们退费模块录入发票号原来是用fph like ‘XXX%’。用户报怨慢，后来改为先用fph=‘XXX’来查，如查不到再fph like ‘XXX%’。这样在绝大部情况下速度都非常快，同时也满足小部分情况下模糊查询的需求。

如：我们的程序要查日表和年表。如果通过日表union表视图去查会非常慢，性能也难以优化。程序改为普通情况下不查年表，用户勾上年表标志时才查年表。

（2）查询统计很多数据时间比较长，就以查询完一部分数据后可以显示这部分数据或是用提示，这样用户清楚系统在作事情也知道大概进度。这样情绪上会好很多。

（3）查询模块常有一进入时也默认一个查询，如果性能好，查询又合用户心意，这种设计非常好，如果性能不好，那就不是好的设计。用户对于进入都困难的模块是没有好感的。

（4）有户的耐心与查询出的记录成正比。用户痛恨等待很久却没有查询出记录。

对于非常慢的查询，如果有些子查询非常快可以先作这样查询以避免查询很久却没有数据出来的情况。如：按病历号查在院病人所有费有明细，可以先查一下这个病历是不是有对应病人。

实战技巧1:用exists、in代替distinct

Distinct实际上是先收集再删除这样两步都耗资源。

Exists,in会隐式过滤掉重复的记录

例查自2009年以来有金额大于100的药品的病人

select distinct a.blh,a.hzxm from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and c.zxrq>'2009' and c.zje>100--改为

select a.blh,a.hzxm from ZY_BRXXK a where exists(select 1 from ZY_BRSYK

b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and

c.zxrq>'2009'and c.zje>100)

实战技巧2:缩短union

select …from A,B,C,D,E1

where(E1的条件)

and(其他表联接条件)

union

select …from A,B,C,D,E2

where(E2的条件)

and(其他表接接条件)

改为

select …from A,B,C,D,(select...from E1where(E1条件)

union

select …from E2where(E2条件))E where(其他条件)

当涉及ABCD表部分耗资源而E1,E2不耗资源时是这样，如果反过来则改后的性能不一定好。查2009年4月后入院的在院病人在2905病区发生的所有费用明细

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YK_YPCDMLK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm

union all

select a.hzxm,b.cyrq,d.name,d.xmgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YY_SFXXMK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.ypdm=d.id and c.idm=0

--改为

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),(select ypmc,ypgg,ypdm,idm idm from YK_YPCDMLK union select name,xmgg,id,0 from YY_SFXXMK)d

where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm and c.ypdm=d.ypdm

实战技巧3:合并sql

把表和where条件类似的两个或是多个sql合并为一个sql.--查2009年以后的普通、急诊、专家挂号人数

declare @ptghs int,@jzghs int,@zjghs int

select @ptghs=0,@jzghs=0,@zjghs=0

select @ptghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=0

select @jzghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=1

select @zjghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=2

select @ptghs,@jzghs,@zjghs

--改为

select @ptghs=0,@jzghs=0,@zjghs=0

select @ptghs=sum(case when ghlb=0 then 1 else 0 end),@jzghs=sum(case when ghlb=1 then 1 else 0 end), @zjghs=sum(case when ghlb=2 then 1 else 0 end)

from GH_GHZDK where ghrq>'2009'

select @ptghs,@jzghs,@zjghs

实战技巧4:去掉游标

把游标当作编程语言的for,do---while的方式，很多情况下都可以去掉,如果光标中间sql语句只有一条一般都是可以去掉光标改为一句sql。

--查当天出院出院日期在2009年4月1到9日间病人的zfdj,zfje置为0

declare @syxh ut_syxh

declare cur1 cursor for select syxh from ZY_BRSYK where cyrq>='20090401' and cyrq<'20090410'

open cur1

fetch cur1 into @syxh

while @@fetch_status=0

begin

fetch cur1 into @syxh

end

close cur1

deallocate cur1

--改为

update ZY_BRFYMXK set zfdj=0,zfje=0

from ZY_BRFYMXK a,ZY_BRSYK b

where a.syxh=b.syxh and b.cyrq>='20090401' and b.cyrq<'20090410'

实战技巧5:取代count

利用内部函数代替

declare @count int

select * into #tmep1 from ZY_BRFYMXK WHERE zxrq>'200901'

select @count=@@rowcount—可以得到count值

select @count

select @count=count(*)from #tmep1—可以被取代

select @count

利用exists而不count判断有没有记录

declare @count int

Select @count=count(1)from ZY_BRFYMXK WHERE zxrq>'2009‘

If @count>0 … else ….--改为

If exists(Select 1 from ZY_BRFYMXK WHERE zxrq>'2009’)… else ….

第三篇：SQL语句的优化方法

SQL语句的优化方法

1.1注释使用

在语句中多写注释，注释不影响SQL语句的执行效率。增加代码的可读性。

1.2对于事务的使用

尽量使事务处理达到最短，如果事务太长最好按功能将事务分开执行(如：可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞，用户操作速度变慢或死机情况。

1.3对于与数据库的交互

尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作，最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理。

1.4对于SELECT *这样的语句，不要使用SELECT *这样的语句，而应该使用SELECT table1.column1这样的语句，明确指出要查询的列减少数据的通讯量并且这样的代码可读性好，便于维护。

1.5尽量避免使用游标

它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

1.6尽量使用count(1)

count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

1.7IN和EXISTS

EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

1.8注意表之间连接的数据类型

避免不同类型数据之间的连接。

1.9尽量少用视图

对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰。

1.10没有必要时不要用DISTINCT和ORDER BY

这些动作可以改在客户端执行，它们增加了额外的开销。

1.11避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

1.1注意UNion和`UNion all 的区别

UNION all执行效率高。

1.1外键关联的列应该建立索引

(如子表id)主子表单据肯定要建视图，2个表的关联以2个表中的MainID为关系，所以，需要给子表的MainID单独建索引，这将很大地提高视图的速度。例如Gy_InOutSub中的InoutMainid增加索引。

第四篇：SQL优化要点总结

SQL优化要点总结

文章比较简短,但很实用,SQL优化要点总结:

1、尽量避免大表的全表扫描，建立合适的索引,务必注意索引是把双刃剑，不能滥用，用好则已，用不好伤人害已。

2、能不排序则不排序，除了order by，注意一些隐含的操作也会有排序，比如UNION,DISTINCT操作

3、尽量少用子查询改用多表联接，因为子查询最终优化器也是转换成多表联接，当语句很复杂的时候，优化器不一定能转换对

4、必要时使用Hints干扰执行计划，优化器不是万能的，很多时候误判

5、合理使用动态绑定，语句共享,减少SQL语句的硬解析次数，尤其是OLTP系统，数据仓库系统后台ETL计算有循环语句执行需考虑

6、多层嵌套，考虑使用中间表或临时表做转储

7、数据量大考虑建立表分区、索引分区，减少数据检索范

8、物化视图的使用,query rewrite特性激动人心

9、在资源充足的情况下充分使用并行，比较常用的场景比如：大索引的创建、大查询、大批量的DML

第五篇：思想优化工程实施方案

中共射洪县住房和城乡建设局委员会 “解放思想大讨论”思想优化工程实施方案

一、学习时间：

二、学习地点：

三、参加人员：

四、主 持 人：

五、学习内容：

1、党的十八大精神。

2、遂宁市委六届五次全会精神。

3、射洪县委十二届四次全会精神。

4、习近平、王岐山同志在第十八届中央纪律委员会第二次全体会议上的讲话。

5、杨洪波同志在县委常委班子2012民主生活会上的讲话。

6、中共射洪县住房和城乡建设局委员会2013年党建工作要点。

7、射洪县住房和城乡建设局2013年工作要点。

8、其他。