从此爱上SQL Monitor!记一次反常理的鉴权查询优化

黄浩 2017-11-30 15:24:00

作者介绍

黄浩:从业十年,始终专注于SQL。十年一剑,十年磨砺。3年通信行业,写就近3万条SQL;5年制造行业,遨游在ETL的浪潮;2年性能优化,厚积薄发自成一家。

 

 
1、案例
 
好天气,坏SQL

 

金秋10月,如同阳春三月般,是一个令人难以忘怀与期待的季节。而在这个美好的季节了,我拿到了一个不怎么令人愉悦的SQL。

 

优化小组的测试MM给我发了封关于性能问题的邮件,在邮件里面,问题描述是这样的:权限配置越少,性能越差,当配置了全部(2万)du的权限时,只需要2s,当只配置了120个DU的权限时,需要半小时以上。

 

看到这个描述,我心里也咯噔了一下:这是违背了常理的。一般来说,只有越大越慢,现在反而是越小越慢。

 

按照习惯,我还是想先见识见识这个一反常理的SQL,看看到底是何方神圣。我找开发人员拿到了SQL,打开代码如下:

                                              

 

从体量上看,这个SQL并不大,总共才130多行。这在I项目组中是比较常见的。而从体型上看,似乎不怎么协调:尾巴太大。在WHERE条件子句,拖着5个EXISTS条件,并且都是OR的关系。这已经很不寻常了,会不会就是问题中描述的问题所在呢?

 

我向开发人员咨询了这5个EXISTS子查询的业务功能,得到的信息是:

 

1、这5个EXISTS子查询的功能是鉴权,即权限鉴别;

2、不同EXISTS子查询代表不同类型的权限集合;

3、鉴权的对象粒度是DU,即每个EXISTS子查询与EXISTS子查询的关联字段都是LINE.DU_ID

 

从SQL自身看,找不到明显的“破绽”,我就尝试着看看执行计划,在SQL DEV中按下了F5,显示的执行计划如下:

 

 

执行计划比较长,我们可以只看exists部分,发现基本上都是索引扫描,cost值也非常低,也就是说执行计划中也看不出问题。那问题到底出在哪里呢?

笨方法,好效果

 

当时就在想:是单个exists慢?还是5个放在一起慢呢?

 

为了弄清楚这个原因,我就逐个注释掉EXISTS,并观察注释后的性能。虽然这个办法有些笨拙,甚至很多人都不齿于该方法,但有些时候这确实也是定位问题的有效的手段和途径。

 

通过反复注释加测试,诡异的现象出现了:

 

1、5个exists条件单独作用时,没有性能问题;

2、第一个和第三个exists条件联合作用时,也没有性能问题;

3、第三个、第四个和第五个exists条件联合作用时,性能问题就凸显了。

 

由此看来,问题越来越复杂了。Oracle在执行这条SQL时到底发生了什么呢?千头万绪理还乱,一筹莫展想不通。万般无奈之下,只能祭出必杀神器:SQL Monitor。

神器不出,莫与争锋

 

在拿到SQL Monitor的结果后,似乎一切都明朗起来了,SQL Monitor的截图如下(由于当时的原始数据丢失,以下仅给出模拟数据):

 

 

因为已经明确了是在exists子查询存在性能问题,我就重点关注了EXISTS的Monitor信息,希望能从中发现有价值的信息与启示。

 

在对比了5个exists的执行计划后,“执行次数”引起了我的注意:5个EXISTS的执行次数及实际行数竟然不一样!

 

 

(1)这组数字之间也有着巧妙的联系:第一个的执行次数为20000,及恰好是总的DU数量,第二个的执行次数等于第一个的执行次数-第一个实际行数,即满足如下算法:

其中f(n)为第n个exists的执行次数,e(n)为第n个exists的实际返回行数,并且n>1。

 

(2)第一个和第二个exists的实际返回行数的和是120,恰恰是邮件中提及的权限配置数量;而第三个19880加上120正好等于2万,又恰恰是全部DU数。难道这一切仅仅是巧合而已?还是另有隐情呢?

 

基于以上两点信息,我豁然开朗恍然大悟,个中缘由了然于胸。我们可以大致推断出Oracle的执行原理如下图所示:

 

 

按照上面的流程与算法,就很容易理解上述那组数字了。同样的,也明白了为何权限配置越少的时候性能越差,配置越多的时候性能反而越好。

 

为了更好地理解,这里可以举两个极端的例子。如果有没有配置任何权限,那么每个DU都需要遍历5个exists子查询,就意味着总共要执行10万次(2万DU,每个DU执行5次)exists子查询。反过来,如果我们将2万DU都配置了权限,而且是第一类权限(即第一个exists的权限),那么每个DU只需要执行第一个exists,后面4个exists子查询不需要执行。因此只需要执行2万次。2万次与10万次的差别(另外还需要考虑不同exists之间本身性能也是有差异的),对性能的影响还是非常明显的。

拨开云雾不等于立见天日

 

笼罩在诡异性能问题上的云雾终于被揭开了,但我却丝毫没有欣喜之感。问题的原因虽然已经“大白于天下”,但解决方案让我一筹莫展。

 

一开始,我尝试着基于现有SQL通过SQL Hint干预执行计划,但是性能毫无起色。我又尝试着改写这个SQL,将OR EXISTS子查询改写成LEFT JOIN,性能问题却变本加厉。我还尝试着创建基于该SQL的特定索引,仍旧无济于事。

回归本源,方得圆满

 

多次尝试无果,在万般无奈之下,我又回到了问题的本原。

 

这个SQL,在本场景中,除了第一个exists子查询执行了100次,第二个exists子查询执行了20次,其它四个exists子查询执行的19880*4次都是没有意义的。既然没有意义,那是否可以省略掉呢?我很为自己这个天马行空不着边际的想法振奋。

 

因为就如开始测试时,将后面三个exists注释掉后,性能非常好。也就是说如果能成功避开无用的EXISTS子查询,也是可以达到性能优化之目标的。

 

但很显然,Oracle在执行SQL前,是无法识别哪些EXISTS子查询是必须执行的?哪些EXISTS子查询是无须执行的?难道自己的这个想法就这样夭折了吗?

不见兔子不撒鹰

 

我继续着自己天马行空的想法。

 

既然Oracle在执行SQL的时候未卜先知,那么我们在写这个SQL时,是否可以先卜上一卦,如果某类权限没有配置,就不在SQL中拼凑对应的EXISTS子查询。这样,本案例SQL就会只剩下两个EXISTS子查询了。性能也自然能得到满足。

 

以上想法仅仅是我一厢情愿的理想主义,其在实际应用中是否可行还是未知之数:这个SQL在Java代码中是固定的还是拼凑的?某类权限是否配置的判断是否复杂?是否也会存在性能问题?如此等等,不寒而栗。但就如小马过河,不去尝试又怎么知道是否真实可行呢?

 

于是,我带着这个不太正经的方案与开发人员沟通。开发人员的表现让人喜忧参半。喜的是,他并不反对这个方案,如果真的能解决性能问题,他也是乐于接受该方案;忧的是,这段5个exists子查询的SQL并不是他控制的。原来该案例的SQL所在的系统模块是任务管理,而5个EXISTS子查询是鉴权功能,隶属于权限模块。这些EXISTS子查询都是由权限模块来开发和维护的。用任务管理模块开发人员的话说就是“这5个EXISTS是通过调用权限模块的服务获取的,如果权限模块给我们3个EXISTS,我们就拼凑三个EXISTS子查询,如果他们不给我们EXISTS,我就不拼凑EXISTS子查询。”

 

于是,我带着这个方案又去“游说”权限模块的开发人员。

 

当我找到权限模块的开发人员时,我们并没有直接拖出我的方案,而是把性能问题表述了一边。意想不到的是,这位开发人员很是淡定,好像这一切早就知道了;却也满脸的无奈,他说:“这个性能问题还是暴露出来了,没有办法,当初权限这块的设置就是这么复杂,我们也不想如此复杂。”

 

见时机成熟,我就把我的方案全盘托出。没想到,这位开发人员听完后,两眼大放异彩,一脸容光焕发,说到:“这很好,非常不错,我现在就按照你的方案改写。这不单单是你的这个SQL有问题,其它所有涉及到鉴权的SQL都会有这个问题。”

 

接下来,一切都水到渠成了。

 

 

 
二、心得
 

 

从此爱上SQL Monitor

 

该案例的优化过程甚为曲折,几近山穷水尽半途而废。在为几个exists弄得焦头烂额一筹莫展之际,幸得SQL Monitor之助,方能拨开云雾,终见青天。从explain plan中,我们能得知Oracle优化器的意图,而通过SQL Monitor,我们能获取到运行时的很多信息,比如本案例中涉及到的“实际返回行数”、“执行次数”。这一些对我们定位问题及原因分析非常有用。

 

感谢SQL Monitor

头疼医头,脚疼医脚

 

该案例对应的BUG单很快被关闭,但作为优化方案的设计者,我非常清楚这个方案的局限性和漏洞。没错,针对该案例,“不见兔子不撒鹰”式的方案的确能药到病除,但也仅仅是适用于该案例的业务场景。该方案还存在一个致命的缺陷:随着配置的权限类型越多,其对整个SQL的性能影响越大。我们将权限配置对SQL的性能影响设为P,则P的计算公式为:

 

由公式可见,当N=0时,是没有影响的,而当N=5时,影响是最大的。

 

事后,我将这种隐患口头上与组长交流过,但组长也是无奈:“我也认真研究过I项目的权限机制,发现存在一定的不合理的地方,要不然也不至于写出如此复杂的鉴权语句。但是,目前来看,不可能将权限机制推倒重来。先就这样子吧。”

 

 
活动预告