SQL等价改写优化案例精选

SQL等价改写优化案例精选梁敬彬前言近一个月来，我介入了各个项目组的数据库脚本审核工作，根据自己的优化案例，做了一些总结。个人认为，优化SQL的最高境界是回归需求，只有正确的理解了需求，才能写出高效的SQL，因此改写SQL往往是优化SQL的重要手段。本次总结我故意不提任何关于执行计划的内容，虽然说读懂执行计划是SQL优化中极其重要的环节，但是如果不能读懂需求，写出复杂繁琐绕了九曲十八弯的SQL语句，通过分析执行计划来调优，往往还是会陷入到束手无策之中。文中案例的SQL语句全部来自我们开发人员之手，SQL语句大多非常繁琐，希望大家有空的时候能克服烦躁，耐心阅读完。我在审核这样的SQL时不但要看懂这么繁琐的语句，还要完成高效的等价改写来调优，这需要读懂开发人员代码编写的需求背后的真正含义。低效语句往往在开发投入生产初期由于数据量少而不能被识别出来，等运行了一段时间数据量大涨后才暴露出来。根据经验，生产中和数据库相关的80%的故障都来自不良SQL，因此今天根据近期一个月审核SQL的心得，总结为如下三部分，共计12个经典案例，希望对大家有用。一．统一天下解释为：巧用各类技巧合并代码，减少表扫描次数。案例1注重COUNT(CASEWHEN)之类的合并技巧selectdistinctne_state.peer_idpeer_name,to_char(ne_state.ne_state)peer_state,(casewhenne_state.ne_state=0thento_char(0)else(selectdistinctto_char(nvl(ne_active.active,0))fromdcc_sys_log,(selectpeer_id,decode(action,'active',1,'de-active',0,0)active,max(log_time)fromdcc_sys_logwhereaction='active'oraction='de-active'groupby(peer_id,action))ne_activewheredcc_sys_log.peer_id=ne_active.peer_id(+)anddcc_sys_log.peer_id=ne_state.peer_id)end)peer_active,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl((selectcount(*)fromdcc_ne_logwheredcc_ne_log.result1andpeer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdategroupby(peer_id)),0)))end)err_cnt,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl((selectcount(*)fromdcc_ne_login_dnlwherein_dnl.direction='recv'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)))end)recv_cnt,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl((selectsum(length)fromdcc_ne_login_dnlwherein_dnl.direction='recv'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)))end)recv_byte,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl((selectcount(*)fromdcc_ne_login_dnlwherein_dnl.direction='send'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)))end)send_cnt,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl((selectsum(length)fromdcc_ne_login_dnlwherein_dnl.direction='send'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)))end)send_bytefromdcc_ne_log,(selectdistinctdsl1.peer_idpeer_id,nvl(ne_disconnect_info.ne_state,1)ne_statefromdcc_sys_logdsl1,(selectdistinctdnl.peer_idpeer_id,decode(action,'disconnect',0,'connect',0,1)ne_statefromdcc_sys_logdsl,dcc_ne_logdnlwheredsl.peer_id=dnl.peer_idand((dsl.action='disconnect'anddsl.cause='关闭对端')or(dsl.action='connect'anddsl.cause='连接主机失败'))anddsl.log_time=(selectmax(log_time)fromdcc_sys_logwherepeer_id=dnl.peer_idandlog_type='对端交互'))ne_disconnect_infowheredsl1.peer_id=ne_disconnect_info.peer_id(+))ne_statewherene_state.peer_id=dcc_ne_log.peer_id(+)该SQL语句复杂冗长，从执行计划可看出该语句执行了多次的表扫描和复杂表连接，读者应该可以看出，当前主要的优化方案在于如何等价改写SQL以减少表扫描次数。首先想到本案例中为构造出err_cnt，recv_cnt,recv_byte,send_cnt,send_byte这5个字段而完成的5个SQL结果集能否简化。由于这些字段都来自同一张dcc_ne_log表，仅是条件不同而已，因此可考虑用CASE语句改造代码，将原来5个结果集的写法改造成1个结果集的写法，这样dcc_ne_log这个大表扫描次数就由5次缩减为1次了。经适当转换，这构造5个字段的代码可改写如下：selectpeer_id,COUNT(CASEWHENRESULT1THEN1END)err_cnt,COUNT(CASEWHENdirection='recv'THEN1END)recv_cnt,SUM(CASEWHENdirection='recv'THENlengthEND)recv_byte,COUNT(CASEWHENdirection='send'THEN1END)send_cnt,SUM(CASEWHENdirection='send'THENlengthEND)send_bytefromdcc_ne_logwherelog_time=trunc(sysdate)GROUPBYpeer_id进一步分析如下ne_disconnect_info结果集的写法：selectdistinctdsl1.peer_idpeer_id,nvl(ne_disconnect_info.ne_state,1)ne_statefromdcc_sys_logdsl1,(selectdistinctdnl.peer_idpeer_id,decode(action,'disconnect',0,'connect',0,1)ne_statefromdcc_sys_logdsl,dcc_ne_logdnlwheredsl.peer_id=dnl.peer_idand((dsl.action='disconnect'anddsl.cause='关闭对端')or(dsl.action='connect'anddsl.cause='连接主机失败'))andlog_type='对端交互'anddsl.log_time=(selectmax(log_time)fromdcc_sys_logwherepeer_id=dnl.peer_idandlog_type='对端交互'))ne_disconnect_infowheredsl1.peer_id=ne_disconnect_info.peer_id(+)为了清晰的将结构展现出来，将上述两次改造的脚本用WITH子句分别封装为ne_state和dcc_ne_log_time，最终整体SQL语句改写完毕后，代码量大大减少的同时性能极大的提升了，表扫描次数从总计7次缩减为2次，执行完成时间从原先的300秒缩短为5秒。完整改造后的最终SQL代码优雅精致，具体如下：withne_stateas(SELECTa.peer_id,CASEWHENdnl.peer_idISNOTNULLANDstrIN('disconnect关闭对端','connect连接主机失败')THEN'0'ELSE'1'ENDne_stateFROM(SELECTpeer_id,MIN(action||cause)KEEP(DENSE_RANKLASTORDERBYlog_time)strFROMdcc_sys_logdslWHERElog_type='对端交互'GROUPBYpeer_id)a,(SELECTDISTINCTpeer_idFROMdcc_ne_log)dnlWHEREa.peer_id=dnl.peer_id(+)),dcc_ne_log_timeas(selectpeer_id,COUNT(CASEWHENRESULT1THEN1END)err_cnt,COUNT(CASEWHENdirection='recv'THEN1END)recv_cnt,SUM(CASEWHENdirection='recv'THENlengthEND)recv_byte,COUNT(CASEWHENdirection='send'THEN1END)send_cnt,SUM(CASEWHENdirection='send'THENlengthEND)send_bytefromdcc_ne_logwherelog_time=trunc(sysdate)----betweentrunc(sysdate)andsysdateGROUPBYpeer_id)selectdistinctne_state.peer_idpeer_name,to_char(ne_state.ne_state)peer_state,(casewhenne_state.ne_state=0thento_char(0)elseNVL((select'1'fromdcc_sys_logwherepeer_id=ne_state.peer_idandaction='active'andrownum=1),'0')end)peer_active,decode(ne_state.ne_state,0,'0',nvl(dnlt.ERR_CNT,0))ERR_CNT,---注意NVL改造decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_cnt,0))recv_cnt,decode(ne_state.ne_stat