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

一、案例

好天气，坏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条件联合作用时，性能问题就凸显了。

由此看来，问题越来越复杂了。***在执行这条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数。难道这一切仅仅是巧合而已？还是另有隐情呢？

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

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

为了更好地理解，这里可以举两个极端的例子。如果有没有配置任何权限，那么每个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子查询，也是可以达到性能优化之目标的。

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

不见兔子不撒鹰

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

既然***在执行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中，我们能得知***优化器的意图，而通过SQL Monitor，我们能获取到运行时的很多信息，比如本案例中涉及到的“实际返回行数”、“执行次数”。这一些对我们定位问题及原因分析非常有用。

感谢SQL Monitor！

头疼医头，脚疼医脚

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

由公式可见，当N=0时，是没有影响的，而当N=5时，影响是***的。

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。