DB2 Sql优化与锁Word文件下载.docx
- 文档编号:16230676
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:13
- 大小:169.22KB
DB2 Sql优化与锁Word文件下载.docx
《DB2 Sql优化与锁Word文件下载.docx》由会员分享,可在线阅读,更多相关《DB2 Sql优化与锁Word文件下载.docx(13页珍藏版)》请在冰豆网上搜索。
FROMBIZ.WF_TASKC
LEFTJOIN
BIZ.REI_FORMB
ONC.RECEIPT_NO=B.REI_FORM_ID
WHEREC.TASK_STATUS='
01'
ANDC.HANDLE_ID='
1234560000102'
AND(C.RECEIPT_TYPE='
02'
ORC.RECEIPT_TYPE='
03'
)
GROUPBYB.APPROVE_STATUS
WITHUR
执行cost:
如果sql写成(增加一个表连接字段):
SELECTB.APPROVE_STATUS,count(*)ASNUM
对比结果,我们可以看到,当连接条件存在多个时,cost会高很多,因为多做了一次表连接。
如果是小表,看不出差别,如果是大表关联,则结果很明显。
当进行表连接时,请确保连接条件只有一个,尤其是大表连接。
1.2合理使用Notin和NotExists
虽然Notin和Notexits可以实现相同的功能,但是两者本身的实现方式不同:
NotIn:
是自内向外操作,即先得到子查询结果,然后执行外层查询。
包含notin子句的执行顺序是:
首先取外部一个查询结果与内部子集比较,不管是否存在,它都要遍历整个子集,往往无法利用到索引,因而是由内向外过程。
所以,当内部查询子集很大时,就会具有较高的查询代价。
NotExists:
恰恰相反,是外向内操作。
即先执行外部查询结果,然后再执行内部操作,
是集合操作。
包含notexists子句的执行顺序是:
首先取外部一个查询结果与内部子集比较,若存在即刻返回,而不需要便利整个子集,如果存在索引,就会使用索引,因而是个自外而内的过程。
所以,当内部子集很大时,相对来说,性能要优于Notin。
因而,总的来说,Notexits在整体性能上要由于Notin。
当子查询结果集较大时,Notexists较Notin具有较高的性能提升;
当子查询结果集较小时(个数或者百数以内),两者相差不多,一般来说,此时Notin会教优于Notexists。
就好像表数据小时,全表扫描总是要由于索引扫描;
当子查询具有一定的复杂度时(即sql关联关系较多,如子查询句中包含多个表查询),由于内部查询的复杂度,会导致Notexists查询具有较大的复杂度,降低性能。
此时可以考虑采用Notin。
IN与Exists两者相差不多,这里不做比较,思路形同。
1.3改写OR和不等于(!
=||<
>
我们在编写sql时,通常都会按照程序逻辑去写,此时,当我们遇到如下场景:
我要查询企业员工表(employee)中的员工状态为实习(type=’01’)或者兼职的所有员工(type=’08’),假设状态共有10种
此时,我们立马会写如下Sql:
Select*fromemployeeAwhereA.type=’01’orA.type=’08’或者
select*fromemployeewheretypein(‘01’‘08’)
我们假设,在type列上存在索引。
而此Sql含有or运算,对于优化器来说,因为无法运用到一个范围,所以无法利用索引扫描。
而通常此种情况需要遍历所有记录或者所有索引。
这样会明显提高查询cost。
我们希望是通过索引的方式,毕竟该表是个大表,如果出现大表扫描,多系统性能有很大的影响。
那么可以采取用UNION改写OR子句,如下:
Select*fromemployeeAwhereA.type=’01’union
Select*fromemployeeAwhereA.type=’02
改写成上述sql,优化器会分别执行两个查询子集,然后union合并。
这样就可以利用到索引(type=‘01’)。
当然Union包含去除重复元素的功能,即相当于distinct,这样就会有排序存在,如果业务场景允许,可以考虑使用unionall,它和union不同的是,它无需排序去重,只需要两个子集合并即刻。
效率要高于union。
当存在大表链接且连接条件较多,并且连接条件包含Or子句时,建议使用Union/Unionall来替换。
对于不等与来说也是类似,不等于在逻辑上其实是类似于Not的概念。
如,对如下sql:
Sql_stmt_2:
Select*fromemployeewheretype!
=’01’
所以我们可以有如下改写方式:
1)将<
改写为Notin操作,即
Select*fromemployeewheretypenotin(‘01’)
2)select*fromemployeewheretype>
’01’andtype<
’01’
3)将<
改写为大于和小于的结合
Select*fromemployeewheretype>
’01’union
Select*fromemployeewheretype<
’01’(当然如果你知道一个大于已经足够,那么完全可以省略掉小于的操作,这就是分析sql的业务场景)
显然,对于1)的改法,它适用与Notin子集中有多个值的情况;
对于2)改法,要要由于1),因为它可以利用到Type列上的索引。
当存在大表链接且连接条件较多,并且连接条件包含不等于(<
||!
=)子句时,建议使用Union/Unionall联合大于小于操作来替换。
1.4缩小结果集来提高查询效率
DB2优化器其实本身会自动根据where字句后的条件来缩小结果集,形如简单的
Select*fromA,BwhereA.id=B.idandA.type=3andb.type=4
DB2会自动改写该Sql,先根据Where字句后的谓词条件,将结果集缩小,然后再进行表连接。
但是有时候,DB2无法自动做这事。
这时候可以先用Sql缩小结果集来达到效果。
将查询结果作为子查询,主要是为了减少扫描的数据量,以及利用索引进行数据检索。
尤其是针对大表来说。
它的特点就是,在进行查询之前,先用子查询将结果集过滤到最小,并且通常这时候的过滤谓词是存在索引的。
假设如下情况:
索引:
biz.xx_testonbiz.rei_busi_apply(start_date)
biz.prmary_keyoninst1.history(busi_apply_id)
查询:
selecta.apply_user_namefrombiz.rei_busi_applya,biz.rei_busi_apply_detailbwherea.busi_apply_id=b.busi_apply_idanda.start_date>
currenttimestamp-30daysorb.seq>
2;
上面的查询用于查询一个月前的出差单或者出差地大于2个的单据。
由于存在Or条件,且Or字段SEQ不存在索引,即使start_date上存在索引,也无法利用索引。
那么办法就是,想办法构造一个子查询,让它属于一个部分,先执行。
那么改写成如下方式,可以奏效:
withtmpas(selecta.busi_apply_idfrombiz.rei_busi_applyawherea.start_date>
currenttimestamp-100days)selecta.apply_user_namefrombiz.rei_busi_applya,biz.rei_busi_apply_detailbwherea.busi_apply_idin(selectbusi_apply_idfromtmp)andb.seq>
这样改写以后,子查询结果tmp就会走索引biz.xx_test,并且会过滤掉表rei_busi_apply一定的结果。
然后再与acct表连接,虽然由于Sql的原因可能是TABSCAN。
当进行大表查询,若存在OR子句,如果谓词条件很多且OR后谓词条件不存在索引,那么可以采用Union/Unionall来替代;
当谓词条件少时且OR后谓词条件不存在索引,此时可以考虑采用构建子查询,来利用索引缩减扫描的数据量。
大表查询,能用索引则尽量要用索引。
注:
当然如果能后合理的在OR子句的字段上建立索引,是最好的。
1.5其他注意小点
Ø
避免select*from。
。
的使用,取所需的列即可。
当表很小时,看不出来fetch所有列带来的开销,但是当表很大时,除了索引列外,*号代表取出所有字段将会有很高的fetch
避免在索引列上做运算,如substr分割字符串,它将会失去索引的判断性,很有可能无法利用到索引
当查询返回结果集较多时,而我们却不需要这么多,那么可以采用fetchfirstNrowsonly,N为大于0的整数,这样即使你做了全表扫描,但是我只会取前100行数据,会大大降低执行时间。
对于查询出来的结果集是只读的,使用select…forreadonly(或者fetchonly),意味着后续不会对该些数据行做update或delete操作,这可以帮助DB2提高Fetch性能。
因为它允许DB2执行块操作,一个fetch操作可以请求返回多行数据
根据业务逻辑选择合适粒度的隔离级别。
UR对于系统有最大的并发性,但也有更多的数据问题,脏读、幻想读都会发生。
如果系统可以接受这样的查询结果,那么UR是最好的选择。
DB2默认是CS隔离级别,在大并发下,该隔离级别是有可能导致大量锁等待和死锁。
所以在编写sql时,考虑业务场景,针对查询,为sql赋予一定的隔离级别。
避免不必要的排序。
这通常对查询出来结果集较大情况下,排序是数据库中资源消耗比较大的一种操作。
在业务允许下,通过添加索引(索引本身就是有序的,确保索引排序和业务排序相符);
在distinct、groupby、orderby子句涉及的列上创建索引(注意索引的排序是升序asc还是降序desc,或者直接指定allowreversescans,它允许你降序或者升序搜索)
2.如何建立合理的索引
在项目开发过程中,我们也可以根据需要定义索引,比如当表结构和Sql稳定后,我们便可以根据该sql执行的频率来决定是否需要为该sql建立索引。
Sql中类似where子句后就单个谓词,我们比较容易建立索引,而如果是多表关联并且谓词关系较多时,我们可以先采用Db2提供的索引优化工具Db2Advis来帮助我们建立索引,至少它可以综合各个表的存量和各个列的占比为我们提供建议。
语法如下:
db2advis-ddbname-itest1.sql-nschema_name-t5>
wf_task.adv
将上述加粗的参数用自己的数据库参数代替;
dbname是数据库名称
Test1.sql是存放你待提供建议sql的文件名称
schema_name是你建立索引所在的视图,这里和表保持一致就好
或者用如下Sql:
db2advis-ddbname-s"
SELECT*FROMT1ORDERBYEMPNO"
-mIMCP
这里只需要将dbname换成自己的数据库名称,sqlstatement换成自己的
为了对比索引建立前后带来的不同,除了cost之外,我们还可以去查看它的执行计划,观察走索引到底比没有索引快了多少。
我们仍然使用DB2提供的工具dynexpln或者db2expln语法如下:
db2expln–ddbname–s–g–q“sqlstatement”–t或者
dynexpln–ddbname–s–g–q“sqlstatement”-t
此外在建立索引时,需要注意以下几点:
1)根据条件中谓词的选择度创建索引
可以简单的通过selectcount(*)fromtabnamewherecol=’X’这样的方式,观察每个谓词条件过滤的总数。
过滤出结果集越小,代表选择度越高,如果是建立组合索引,那么应该将该谓词放在首位
2)避免在建有索引的列上使用函数
3)在需要被排序的列上建立索引(注意索引的顺序与排序顺序一致),对大表很有效
4)使用include关键词创建索引
存在这种情况,当表足够大时(通常是百万级),我们需要通过一个谓词col(是唯一的)来获取列中的colA,这时候,如果将colA加入索引很浪费,因为它并没有很大选择性,而如果不加入,当表很大时,fetchcost太高。
这是可以用include来将列colA包含进索引,这样不再有多余并且耗时的fetch,include列也不会影响索引的选择性。
建立include类索引,必须要求索引字段是唯一的,否则无法include。
3.避免死锁和锁等待
数据库中之所以会存在死锁或者锁等待,是因为某一事务执行时间过长,导致锁没有及时释放,那么我们的解决办法就是,事务过程尽量要短,并且事务中的sql执行要快,这样才不会有过多的锁等待。
还有一个原因,就是一些执行糟糕的sql,比如走了全表扫描,那么它会占据表中大量的锁,导致锁住了其他行,其他用户只能等待。
解决锁等待,要注意以下几点:
优化查询Sql,采用db2advis建立合适的索引,使得其能够走索引查询,由于索引的范围和排序,可以直接跳过许其他行,定位到符合我们需要的行。
采用合适的隔离级别。
由于DB2默认是CS的隔离级别,它的原理是,游标每到一行就会锁住改行,对于一般应用来说是足够了,但是如果遇到全表扫描,那么CS模式会锁住表中大量的行,直到查询完毕。
所以可以根据业务需求,将其改为UR模式,它不会对表加任何行锁。
或者在JDBC中设置隔离级别(IsolationLevels)
合理设置锁超时参数,它主要是用来避免事务长时间被占用,导致锁和连接无法释放,影响系统的并发。
可以设置DB参数
更新操作一定要走索引,否则很容易产生死锁。
(针对边更新边查的操作)
避免出现锁升级现象,当锁等待达到一定程度时(行锁的个数超过loctList*percentoflocklist),就会出现行锁升级为表锁,即锁升级。
因为一旦出现锁升级,那么锁住的就不再是行,而是表,那么其他事务要想访问该表中的任意行,必须等待事务将锁释放。
修改Locktimeout(-1代表不检测锁超时),一般来说,该参数默认为10s足矣。
当系统存在严重的锁等待时,可以通过以下sql,定位到锁等待Sql
db2"
selectAGENT_ID,substr(STMT_TEXT,1,100)asstatement,STMT_ELAPSED_TIME_MSfromtable(SNAPSHOT_STATEMENT('
dbname'
-1))asBwhereAGENT_IDin(selectAGENT_ID_HOLDING_LKfromtable(SNAPSHOT_LOCKWAIT(‘dbname’,-1))asAorderbyLOCK_WAIT_START_TIMEASCFETCHFIRST20ROWSONLY)orderbySTMT_ELAPSED_TIME_MSDESC"
运行结果如下:
死锁比锁超时更加可怕,因为它将随机回滚一个事务,而这个不受应用程序控制,不可控的错误十分可怕,所以一旦出现死锁,必须解决掉。
如何观察DB2是否存在死锁呢,有以下两种方式:
1)开启lock快照监控
db2updatemonitorswitchesusingLOCKon
执行如下命令:
db2getsnapshotfordatabaseondbname|grep-i"
LOCK"
结果如下:
可以看到其中有DeadLocksdetected
2)采用db2top工具(db2V9.1后才有)
Db2top–ddbname
然后键盘输入‘d’,如下:
可以看到其中有个DeadLocks计数。
如何定位死锁也有很多方式,如:
1)创建死锁监控器(需要针对文件分析,复杂度较高)
2)采用db2pd(最稳定,一般可以定位到)
3)采用db2top监控组件(最快,但不一定能抓到)
附件中,讲述方法2),该方法我在差旅测试过程中发现,并且总是可以精确定位到。
附上:
如何精确定位死锁
——谁占有锁,谁等待锁,供开发参考
本次差旅发现过很多死锁,有很多死锁定位方式。
但是能精确定位的还是比较少。
通过本次差旅实践,发现通过dbpd来捕捉是最好的,也是最精确的。
方法我总结如下:
1)启用死锁监控
db2pdcfg–catchdeadlock
当死锁触发时,会自动执行db2cos脚本(在%db2dump%/bin目录下)。
这个脚本里调用了db2pd来将当前信息捕捉下来,其中主要捕捉的信息包含如下:
Db2pd共捕捉了locks(锁)、transaction(事务)、agents(代理进程)、application(应用)、dynamic(动态sql,这个最重要,是用于定位到sql)
2)坐等死锁。
当死锁触发时,将自动生成db2cos.process_id_application_id.txt,该文件在db2的诊断目录下(可以通过db2getdbmcfg|grep“DIA”来获取诊断目录位置)
3)分析db2cos文件:
a)首先看-locksshowlocks模块,截图如下:
找到其中包含【Sts】为W*,并且根据【ReleaseFlg】定位到相同slot的,类似
,总共会有两处,一处状态为W*(是死锁)、一处是G(代表锁拥有)。
Slot相同,就代表他们是在同一个数据对象上等待。
寻找后的数据,如下截图:
从上图,可以看出如下信息:
在tabled=514和tabspacesId=4上等待(可以在syscat.tables视图上根据信息定位到表名称selectTABNAME,TABSCHEMAfromsyscat.tableswhereTABLEID=514andTBSPACEID=4)
占据表锁的transactionHandleId为28,是X排他锁
等待表锁的transactionHandleId为55,是NS(下一键共享锁)
有了这两个Id接着往下走。
我们已经知道了谁在等待、谁在占有。
b)查看-transaction,获取transactionid对应的agentid
可以看到Apphandle对应值应有了,下面就需要根据这个Apphandle去找sql执行的信息,已经离目标不远了哈。
c)查看-dynamic信息
观察如下列【C-AnchID】、【C-StmtUID】、【L-AnchID】、【L-StmtUID】
其中C-AnchID是代表当前正在执行的sql槽号,L-AnchID是代表上次执行的sql槽号。
OK,我们就需要通过这个槽号来找到对应的Sql。
定位到如下模块:
然后根据【C-AnchID】、【C-StmtUID】、【L-AnchID】、【L-StmtUID】列值找到对应sql(AnchID->
C-AnchID,StmtUID->
C-StmtUID),如下:
自此,我们发现了导致死锁的Sql:
哪个sql在等待锁?
状态为W*:
SELECTCOUNT(*)
FROMBIZ.WF_TASKA
INNERJOIN
ONA.RECEIPT_NO=B.REI_FORM_ID
WHEREA.TASK_STATUS=?
ANDA.HANDLE_ID=?
ANDA.RECEIPT_TYPE=?
ANDB.APPROVE_STATUS=?
ANDB.REI_FORM_NO=?
哪个Sql在占有锁?
状态为G:
UPDATEBIZ.REI_FORM
SETAPPROVE_STATUS=?
WHEREREI_FORM_ID=?
整个过程截图如下:
定位到Sql之后,我们就可以按照第三节中叙述的方法,该建索引就建立索引,sql写的不规范就调整sql。
另外附上,我在调优过程中常用的Sql。
也省的大家敲命令了。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 Sql优化与锁 Sql 优化