如何写出高性能的SQL语句，及如何进行SQL性能分析与调优

最新推荐文章于 2024-07-18 10:25:45 发布

踏雪寻梅i

最新推荐文章于 2024-07-18 10:25:45 发布

阅读量2.6k

点赞数 3

分类专栏： Sqlserver 文章标签：数据库sql性能优化 SQL语句性能分析如何写出高性能的sql sqlserver优化

本文链接：https://blog.csdn.net/weixin_41660162/article/details/86429175

版权

Sqlserver 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.尽量使用索引

索引是数据库中重要的存储结构，对于查询耗时影响甚大，应避免导致索引无效的sql语句

索引失效的场景：
1、缺失索引				
2、where 条件中的or
3、where条件表字段使用函数		
4、where条件中存在隐式转换
5、where条件中查询范围过大		
6、select * from 
7、使用like（带通配符(%)的like语句）

缺失索引
说明：一方面是常作为查询条件的字段没有建立索引，另一方面可能是有索引但是没有使用，我们要尽可能利用到索引
例子（syxh是a、b两表的索引）：

//存在未使用的b表索引syxh
select * from VW_BRFYMXK a(nolock),VW_LSYZK b(nolock) where a.syxh=13 
and a.yzxh=b.xh and a.fylb=0
//用上b表的索引syxh
select * from VW_BRFYMXK a(nolock),VW_LSYZK b(nolock) where a.syxh=13 
and a.yzxh=b.xh and a.fylb=0 and b.syxh=13

Where 条件中的or
说明：对于索引字段尽力避免用or，普通字段可以用or，解决要么分解成多个sql,要么用业务规则避免
例子：

declare @rq1 ut_rq16,@syxh ut_syxh
select @rq1='20081201',@syxh=157
//修改前
select syxh from BQ_FYQQK WHERE (syxh=@syxh or @syxh is null)
//修改后
If @syxh is null
    select  syxh from BQ_FYQQK
else 
      select syxh from BQ_FYQQK WHERE syxh=@syxh

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

//修改前
select * from ZY_BRFYMXK where substring(zxrq,1,8)='20090115'
select sjh,hzxm,fph,fpjxh from SF_BRJSK where isnull(fph,0)=1
//修改后
select * from ZY_BRFYMXK where zxrq between '2009011500' and '2009011524'e
select sjh,hzxm,fph,fpjxh from SF_BRJSK where fph=1e

2.SQL书写技巧

用exists、in代替distinct
说明：Distinct实际上是先收集再删除这样两步都耗资源，Exists,in会隐式过滤掉重复的记录
例子：

//修改前
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>10 
//修改后
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>10 )

合并sql语句
说明：可以把很多功能相似的sql语句，合并为一条，以达到减少系统资源开销的目的
很多存储里面会先插入数据到临时表，然后底下反复更新，update是很耗性能的，它实质是delete然后insert
例子：

declare @ptghs int,@jzghs int
//修改前
select @ptghs=0,@jzghs=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 @ptghs=0,@jzghs=0
select @ptghs=sum(case when ghlb=0 then 1 else 0 end),
          @jzghs=sum(case when ghlb=1 then 1 else 0 end) 
from GH_GHZDK where ghrq>'2009'

负向查询改为正向查询
说明：not in 导致不能使用到索引，应修改为正逻辑lft join 后来筛选。
例子：

//修改前
SELECT * FROM SF_BRJSK where sjh not in (select jssjh from dbo.SF_FPMXDYK)
//修改后
SELECT * FROM SF_BRJSK a(nolock) left join SF_FPMXDYK b(nolock) on a.sjh=b.jssjh
where b.jssjh is null

调整join操作顺序
说明：join操作是自顶向下的，多表关联时，应把可以快速减少记录的关联表放在前面进行关联。
例子：

//修改前
SELECT * FROM dbo.SF_CFMXK a(nolock)
left join dbo.SF_MZCFK b(nolock) on a.cfxh=b.xh
inner join dbo.SF_HJCFMXK c(nolock) on a.hjmxxh=c.xh
//修改后
SELECT * FROM dbo.SF_CFMXK a(nolock)
left join dbo.SF_HJCFMXK c(nolock) on a.hjmxxh=c.xh
inner join dbo.SF_MZCFK b(nolock) on a.cfxh=b.xh

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

--例子：更新发票对应明细库的fph---------------------------------------------------
declare @sjh ut_sjh
select @sjh='20170607000002'
//传统的更新方式：
if exists(select 1 from SF_FPMXDYK where jssjh=@sjh)
	update SF_FPMXDYK set fph=0 where jssjh=@sjh
else
	update SF_NFPMXDYK set fph=0 where jssjh=@sjh
	
//可改进的更新方式：
update SF_FPMXDYK set fph=0 where jssjh=@sjh
if @@ROWCOUNT=0
	update SF_NFPMXDYK set fph=0 where jssjh=@sjh
	

--例子：更新药品库存---------------------------------------------------
//传统的更新方式：
begin tran
If  exists(select 1 from YK_YKZKC WHERE cd_idm=100 and kcsl>=50 )
begin
    update YK_YKZKC  set kcsl=kcsl-50 where cd_idm=100
End
Else begin
	rollback tran
	Select  'F库存不够'
	return
end

//可改进的更新方式：
update YK_YKZKC  set kcsl=kcsl-50 where cd_idm=100 and kcsl>=50
If @@rowcount<=0
Begin
	rollback tran
	select  'F库存不够'
	return
end
rollback tran

尽量不使用游标
说明：尽量避免用。写的时候很爽，执行起来很容易出问题，因为不停循环。
（1）如果一定要用，要避免：
游标中再调用存储过程(即时调用，也应该尽量简单)
对实体业务表发的操作(可改成临时表)
做复杂查询
（2）避免思路：把单条记录处理，合并成批量处理；实在不行宁可让前台程序去处理
（3）把游标当作编程语言的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
	update ZY_BRFYMXK set zfdj=0,zfje=0 where syxh=@syxh
	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'

跨服务器的表关联
说明：本地表与链接服务器表关联时，常常默认会把链接服务器那边对应表的所有数据取到本地服务器再与本地表做关联处理，这样常常会导致链接服务的表全表扫描
例子：

1.避免通过链接服务器关联取数据，宁可调用链接服务的存储过程，通过存储过程得到数据。
2.通过OPENQUERY取数据。
//例子：需要通过查询另一个服务器大表数据，HIS服务器关联CIS表取数据。OPENQUERY 性能比直接用链接服务器要好十倍。
select * into #gh_temp
FROM THIS4.dbo.GH_GHZDK a with(nolock) 
where a.ghrq >'2017111212' 
//OPENQUERY脚本：   
SELECT a.xh,isnull(b.EMRXH,-1) as emrxh  FROM #gh_temp a with(nolock) 
inner JOIN OPENQUERY (CISDB, 'SELECT GHXH,EMRXH FROM CISDB.dbo.OUTP_JZJLK') b on  a.xh = b.GHXH
//链接服务器脚本：  
SELECT a.xh,isnull(b.EMRXH,-1) as emrxh FROM #gh_temp a with(nolock) 
inner JOIN CISDB.CISDB.dbo.OUTP_JZJLK  b with(nolock) on  a.xh = b.GHXH

尽量避免使用trig
说明：Trig（触发器）的处理的处理机制是满足条件时就会在源语句后面加上trig中的代码进行执行。

优缺点比较：
好处：（1）相对于外部程序、存储过程，触发器可以更快更高效的维护数据
坏处：（1）在插入一张表如果主键是indentity的值常取用select @@identity。但如是有trig,trig中有表插入操作，这时的@@identity就是trig中插入生成的值；需要使用scope_identity()。
（2）触发器会使编程时源码的结构被迫打乱，为将来的程序修改、源码阅读带来很大不便。

对于trig如果有其他手段就一定要避免用trig

事务使用技巧
说明：事务具有原子性、一致性、隔离性和持久性的特点，可以有效的保证逻辑数据一致性与可恢复性，是必不可少的利器之一。

事务使用准则：
（1）事务的执行时间应尽可能地短，所有计算、对临时表的更新都应但放在事务外，事务中最好只有更新和插入正式表操作.因为事务中产生的锁只有在commit tran是才会释放。
（2）创建临时表应该放在事务外，因为创建临时表会锁tempdb的系统表。
（3）热表的处理应放在最后，这样锁的时间就会减少。
（4）对业务表的处理顺序在各个存储中应该一致，这样可以大大减少死锁的概率

sql书写小技巧

（1）对于从大表取数据，一次就应当取好，如果发现要取两次，还不不如取一次到临时表，处理好临时表数据，然后用临时表代替大表做关联，这个在union 中常出现。
（2）不要把太多的记录放到临时表中，这样会导致TEMPDB性能低下。
（3）如果非必须，那么些查询语句不要加order by ,group by，因为会放在tempdb里处理，耗临时表的资源，并且排序是消耗时间的，如果必须，可以考虑在排序字段上建索引。
（4）避免isnull的使用，尽可能有默认值，尽可能 not null。
（5）按需取值，select *会取所有字段浪费内存、存储、网络带宽。
（6）应避免存储过程参数类型与表中字段类型不一致。不一致会导致隐含转换，白白消耗性能。

3.如何进行SQL性能分析与优化

Profile使用
功能：Sql的监视工具，对于提高语句的性能方法有很大的帮助。
作用：可以根据指定的spid跟踪比较耗时的sql语句
查看执行计划
（1）先纵向看百分比，找出百分比大的
（2）再横向找百分比大的，然后一个个具体分析
（3）最有价值数字：子树成本（越小越好）
语句执行计划及编译执行耗时
SET STATISTICS PROFILE on–输出语句的执行计划，以及语句运行在每一步的实际返回行数统计
SET STATISTICS TIME on --输出语句的执行耗时(分析/编译/cpu执行/io等待…)
SET STATISTICS PROFILE on–输出语句的执行计划，以及语句运行在每一步的实际返回行数统计
查看索引情况

//DBCC SHOWCONTIG ('表名')
//如果逻辑扫描碎片>10%以上就是不好，需要重建索引
DBCC SHOWCONTIG ('SF_NHJCFMXK')

//dbcc dbreindex('表名')可以重建索引
dbcc dbreindex('SF_NHJCFMXK')

在这里插入图片描述

例子：

//清除buffer pool里的所有缓存数据
DBCC DROPCLEANBUFFERS
//清除buffer pool里的所有缓存的执行计划
DBCC freeproccache
go
SET STATISTICS PROFILE on//输出语句的执行计划，以及语句运行在每一步的实际返回行数统计
SET STATISTICS TIME on //输出语句的执行耗时(分析/编译/cpu执行/io等待...)
SET STATISTICS io on //输出语句做的物理读和逻辑读的数目
go
SELECT * FROM dbo.SF_CFMXK a(nolock)
inner join dbo.SF_HJCFMXK c(nolock) on a.hjmxxh=c.xh
where c.xh > 50
go
SET STATISTICS PROFILE off 
SET STATISTICS TIME off 
SET STATISTICS io off 
go