第一章 技术人生系列 · 我和数据中心的故事(第九期)SQL优化之基于SQL特征的改写
中亦安图 | 2016-03-21 22:04
前言
今天老K继续与大家分享第九期。
周末老K宅在家观战了两局精彩的“人狗”大战。老K既算不上科技迷,也算不上围棋迷,不过对此颇有感触:阿尔法狗不过是通过左右互博的方式不断学习围棋,然而依赖其最优的学习算法(学习方法)却能再短短的数月之内达到人类围棋水平的最顶端;而李世石在却是依赖其已有的经验结合人类特有的灵感下出“神之一手”,人类终究还是可以战胜拥有超强计算能力的阿尔法狗。这些不禁让老K想起了自己在工作过程中的最有艺术性的部分---“SQL tunning”,一方面要不断学习积累运用不同的优化方法,同时在必要时多一分想象力和灵感,这样面对不同的SQL问题,我们才能下出自己的“神之一手”。
好了,今天老K与大家分享的案例是SQL调优的案例,但老K更希望大家能从中体会到SQL tunning过程中的优化方法和思维方式,真正做到它山之石,可以攻玉。同时,大家如果觉得老K的方法还不错,不妨轻轻的转发一下,分享给身边更多的ORACLE技术爱好者。
今天分析的问题是客户DBA给过来的一条SQL语句,已经困扰其一段时间了,希望老K一起来分析解决。解决这个问题对老K来说并不是特别难,不过在这个问题的分析过程中,老K给出了几种优化的方向,最终选择了不论是对整个系统还是对该条SQL都可谓最佳的一种方式,最后在测试环境执行效果非常不错。
Part 1
摆问题、列信息
对于SQL tunning,老K上手最先关注的是SQL文本、执行计划和执行统计信息,当然也不要忘了关注一下系统/数据库版本。
1.1 环境介绍
操作系统 AIX 6.1
数据库 ORACLE 11.2.0.3 两节点RAC
1.2 SQL文本
1.3 执行计划
1.4 执行统计信息
信息都在这了,我们要关注些什么呢?老K的经验是,先找特征,再根据不同的特征来进一步提取自己需要的信息。
Part 2
找特征、补信息
2.1 SQL文本特征
>> exists子句 (part1)和update set部分(part2)的sql代码基本相同,如下图;
>> part1部分中,标量子查询的结果作为set列的目标值,说明从业务逻辑上能保证该部查询返回记录数最多为1;
2.2 执行计划的特征
>> 该执行计划各过程均使用filter
>> 结合sql文本及predicate information可以看到,对目标表TARGET_BIG_TABLE经过滤条件POST_DATE=:V1后,返回记录数预估为623K条。
2.3 补充信息收集之表统计信息
>> TARGET_BIG_TABLE大约2G大小,SOURCE_SMALL_TABLE大约3M 大小;
>> TARGET_BIG_TABLE表中记录数约250W左右,统计信息估算POST_DATE过滤后返回623K条记录,注意:这是预估值,实际值会随着传入的变量V1而变化。
>> SOURCE_SMALL_TABLE表中记录数约12W左右,ad02_acct_no列的选择度比较高;
2.4 补充信息收集之执行计划解读
注:TARGET_BIG_TABLE简称为T表 SOURCE_SMALL_TABLE 简称为S表
另注:解读关键----理解执行计划中的filter
>> 执行计划分开成两部分来看,其中ID2-7步表示对应SQL文本的part2部分,ID8-12步对应SQL文本的part1部分;
>> part2部分的过程:使用POST_DATE过滤T表,将过滤后的记录迭代入EXISTS子查询(T表的结果集此时作为变量传入子查询),在子查询执行的过程中,如果前面的关联条件符合,再次迭代入第二层子查询(select max()部分)进行匹配;
>> part1部分的过程:针对ID2-7步过滤出的结果集,逐条update,而update的目标值,同样是通过类似2-7步过程中的逐步迭代查询而来;
>> 在各步骤单表访问方式均为全表扫描;
>> 从执行计划中可以看到,在第3步对表T表进行过滤之后结果集估算为623K(rows列),其后对S表过滤后均为1;
>> 由此可以估算执行过程中表访问的情况应为:(老K建议在本分享中记住下面的公式,暂且称之为 “ 访问公式 ” 吧)
过滤过程的表访问=(T表全扫+ 623K 次 ×(S表全扫 +(0或者 1次)×(S表全扫)))
修改过程的表访问=(需要修改的记录数 ×(S表全扫 + (0或者 1次)×(S表全扫)))
总的访问过程=过滤过程的表访问次数 +修改过程的表访问
注意:此处的(0或者 1次)×(S表全扫)表示的是第二层子查询的情况,如果在第一层子查询过程中关联条件就不符合,则不再需要迭代入第二层,即0次S表全扫,否则即是1次S表全扫;所以过滤过程对S表最少需要做623K次全扫,最多需要做1246K次全扫;修改过程同理。
2.5 执行统计信息特征
>> SQL单次执行平均逻辑读为355,245,774(block数)
>> SQL单次执行平均时间约2000秒
>> SQL单次平均修改记录数约为0条
Part 3
思考吧DBA
好了,信息收集完成了,进入老K的既定思考轨道,其实对于任何一个SQL tunning的问题,老K都会提出下面的三个问题,这个也不用例外;
3.1 老K的例行思考
>> 这个执行计划是否为当前SQL语句下最优的执行计划?(选择优化目标)
>> 我们想要的执行计划是什么样的?(确定优化目标)
>> 我们怎么来让SQL跑出我们想要的执行计划?(实现优化目标)
如果可以,正在阅读此文的你,也许也可以思考一下上面的三个问题,或者回忆一下当你面对SQL tunning的问题时你有没有思考过这三个问题,亦或者你会思考/思考过什么呢。
综合前期的分析思考片刻之后,老K郑重地给出了自己的答案:
3.2 老K的答案----不是最优的计划
老K先查看过该SQL的历史执行计划,只有这一个,但这并不意味着着就是该SQL的最优执行计划;
在执行计划解读部分,老K给出了这个执行计划的“访问公式”,从公式中可以知道其实S表虽小,但其实际上是整个执行计划的关键,整个过程中最多可能需要对S表进行1246K×2次访问呢,那我们可不可以提高对S表的访问效率呢?当然可以,从执行计划中的估算可以知道对S表的访问大约返回1-2条记录(这里老K还单独验证过),说明整体选择度比较高,我们只有创建合适的索引,就可以就可以大大将提高S表的访问效率。
我们简单来估算一下使用索引的情况下的执行效率是怎样的。原来对S表全扫所需的逻辑读数为3M(表大小)÷8192=375次,使用索引后预估对S表一次访问最多所需逻辑读数为:(2次索引块访问 + 2次数据块访问)=4次;所以说,使用索引的逻辑读约为使用全扫的的1%,估算创建索引后该语句单次执行平均逻辑读约在350w左右。
那么,新建索引,将S表的全扫都变为索引扫描,这就是老K想要的执行计划吗?
显然不是,这样的执行计划只是原执行计划的一个升级版而已,其过程还是一个迭代的过程,这样执行的时间/消耗的时间基本都会随着原计划中第3步返回的数据量(还记得623K这个值吗,就是它!它是可变的,可能随着传入的)变化而线性变化;所以这个执行计划虽然较原执行计划预计会有非常大的改善,但仍然不是老K想要的执行计划。
3.3 老K的答案----想要的计划
SQL文本告诉我们,其实SQL做的就是使用exists方式将T表和S表进行关联更新,老K想要的执行计划应该是使用NL或者hash join的方式来连接两表,而不是使用filter迭代的方式,这样就能保证SQL执行过程中只需要对T表和S表进行极少的一次或几次扫描,从而降低SQL执行的逻辑读。
3.4 老K的答案----如何生成漂亮的执行计划
要回答这个问题,我们首先要思考为什么SQL当前没有跑出我们想要的执行计划,是因为统计信息不准?索引设计不合理?还是列类型不匹配?
都不是!
我们再次回到SQL语句本身,来看看SQL语句的特别之处。
在这里,我们看到了问题的关键,正是因为最外层的T表与两层子查询均有关联关系,导致ORACLE无法自动改写SQL,最终生成执行计划时无法使用T表与S表进行JOIN,只能生成使用filter方式的执行计划。
所以,最终思考的结果已经出来:
>> 因为两层子查询的原因导致ORACLE无法使用JOIN的方式关联T表和S表
>> 要想生成较好的执行计划必须改写语句
>> 改写后的语句不应该存在类似的最外层表涉及第二层子查询的情况
其中最后一点,指出了我们改写的关键点。
Part 4
改写吧DBA
依据老K的经验,SQL语句的改写通常要求改写者对SQL涉及业务非常了解,通过业务特征重构出合理的SQL语句,才能更好的做到既不改变SQL的业务逻辑,又有效提高SQL性能;不过针对这个SQL,我们已经知道了导致其执行计划不优的根本原因,老K相信可以在不考虑业务特性的情况,利用数据库的特性来进行有效的改写。
4.1 改写的花絮
基于SQL特性中,part1和part2基本相同的特性,老K先随性的对SQL做了如下改写(当然没有针对前面提到的改写关键点);
这一改写方式的几个关键点:
>> 先把post_date字段的过滤条件直接提取出来,与原逻辑一致
>> 基于part1和part2基本相同,使用了nvl函数代替了原来的exists子句
>> 如果select部分能查到记录(类似原来的exists子句成立),则用查询出的结果更新chq_pay_name字段
>> 如果select部分不能查到记录,则用原记录自身进行更新(set chq_pay_name=chq_pay_name),更新前后该记录的数据不变
以上几点保证了改写后的SQL与原SQL逻辑一致,不过有一点不一样的非常值得注意,原SQL只修改极少的几条记录,新SQL却修改了623K条记录,只是其中绝大多数是冗余的修改。
我们再看改写后的SQL执行计划:
与原SQL执行计划类似,不过少了原执行计划的part1部分。
新的执行计划,老K又问了自己一句:
4.2 这样改写真的好吗?
大家是否还记得原执行计划解析过程中老K给出的“访问公式”:
总的访问过程=过滤过程的表访问次数 +修改过程的表访问
那么,在这个执行计划下,因为去掉了冗余的一部分,公式就变成了:
总的访问过程=过滤过程的表访问次数
实际上就可以理解为,SQL在修改数据的过程中可以重用过滤过程中生成的数据;
不过针对这个语句,我们从执行统计信息里知道,每次语句执行最终修改的数据量都非常少,也就是说这样改写所减少的“修改过程的表访问”对整体执行效率影响并不大。
这样改写会带来什么坏处吗?
会!根本原因就在于上面提到的新SQL实际修改的记录数是623K条:
>> 持有行锁范围变大,可能大量导致其他对该表进行DML操作的会话被阻塞
>> 如果修改列上有索引,索引维护的时间将大大增加,导致新SQL执行效率更低
综上,针对这条SQL语句,这种改写方式并不合适。
不过,如果原SQL在执行过程中修改的数据量接近623K条,那么这种改写方式的收益就要高非常多,而其带来的坏处也就不复存在了,这种改写方式只是不适合这种业务环境下(每次只修改极少几条记录),然而却有一定的普遍性,所以老K也把这部分分享给大家,最重要的是解决问题过程中的思路和方法。
4.3 继续改写
前面我们已经分析出改写的关键点:改写后的语句不应该存在类似的最外层表涉及第二层子查询的情况;下面我们就朝着这个目标去改写我们的SQL语句。
改写前信息补充:
改写思路在老K脑中酝酿好后,老K又补查了T表的信息,确认T表存在主键约束,主键列为ACCT_NO和JRNL_NO;
4.4 增加冗余
>> 在exists子句中增加一个冗余的T表,别名为d
>> 增加d表和a表的关联关系,其中jrnl_no列和acct_no列组合为T表的主键,其他冗余列的关联主要为下一步继续的改写作铺垫;
>> 整个SQL语句中没有使用d表与其他表进行关联;
>> 由于d表和a表使用的是主键进行关联,所以能确保对a表的每条记录,都能从d中找到且只能找到一条记录符合语句中的关联关系;
综上,可以知道上述增加冗余完全不改变SQL的逻辑关系。
4.5 关键角色转变:
基于第一步冗余等价关系,将exists子句中的所有a与b、c的关联关系替换为d与b、c的关联关系。
4.6 减少冗余:
因为主键a、d的主键列值相等,即可保证a、d的其他列值必然相等,所以a、d的关联字段只需要保留主键字段即可(保留也是可以的,去掉显得更简洁)
以上一步一步的改写保证了逻辑的一致性,同时实现了最外层的T表不再涉及第二层子查询的关联,我们可以推断执行计划应该与老K预期的相差不远了:
>> 执行计划中b、d、c表使用hash join进行关联
>> join完成后通过一系列SORT/FILTER后形成结果集VW_SQ_2,其中这里的filter部分为结果集内部的比较(即同一条记录的不同列的比较),效率非常高
>> 最后VW_SQ_2和外层的T表使用NL的方式进行join,关联字段为主键字段
执行计划出来以后,我们来估算一下这个SQL在执行过程中的“访问公式”:
总的访问过程 = S表全扫 + T表全扫 + S表全扫 + VW_SQ_2记录数 *(1个T表主键索引块 + 1个T表数据块)
4.7 别忘了”set“
原语句的part2部分修改的跟老K预期的差不多,原语句part1部分与part2部分一致,那么我们简单的修改part1部分成part2部分就可以了吗?显然不是!通常,使用merge into语句能很方便的改写update语句,这里我们更能利用原语句part1和part2一致的特性,改写如下:
>> 将语句改写为merge into的方式;
>> Merge的源与上一步改写的exists子句中的内容一致,只是把与a的关联关系提取到merge语句的on 部分;
>> 这样改写后SQL执行过程中也会锁定需要修改的极少记录。
这里改写后的执行计划与前面的update语句类似,老K也就不单独列出分析了。
Part 5
最后的总览
最后我们再来看看我们改写后的语句及其执行计划:
语句如下:
最终的执行计划:
最终测试效果:
在测试环境,改写后的语句执行了两次,每次平均修改7.5条记录,耗时4s,逻辑读3.4w;细心的读者可能能从最终的执行计划中看到,对T表的全表扫描也许可以避免等,由于篇幅原因以及测试环境的原因,老K没有再在这里深究,毕竟老K分享的是SQL tuning的方法,而如何避免全表扫描以及如何分析避免了全表扫描后对SQL执行效率提升的预估,相信读者你一定已经学到了,不妨自己做一个估算。
写在最后
读到了最后,老K分享了什么,我们不妨来仔细回忆一番。
>> SQL分析过程中如何通过执行计划推算SQL执行的逻辑读
>> 针对CASE中的SQL如何通过添加索引来改善其执行效率
>> 针对CASE中的SQL通过使用NVL的方式进行改写,它在什么场景下是合适的,什么情况下是不合适的。
>> 怎样通过添加冗余关联来引导数据库生成我们想要的执行计划
>> 怎样使用merge语法来改写update语句
最后,老K再一次强调,在SQLtunning的过程中最重要的是优化的思路和对问题的思考方式,希望聪明的读者已从这次分享中得到启示。
编外:老K后来通过与应用开发团队沟通了解文中SQL的业务特征后,再次结合其业务特征改写了SQL,执行效率再次得到了极大的提升,可见,在SQLtunning的过程中,了解业务确实是非常重要的一环。
About Me
....................................................................................................................................................
本文来自于微信公众号转载文章,若有侵权,请联系小麦苗及时删除
ITPUB BLOG:http://blog.itpub.net/26736162
QQ:642808185 若加QQ请注明您所正在读的文章标题
【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】
....................................................................................................................................................
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2083744/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2083744/