1.案例好天气，坏SQL金秋十月，如春三月，是一个难忘又充满期待的季节。而在这个美丽的季节，我得到了一个不太愉快的SQL。优化组的测试MM给我发邮件说性能问题。邮件中，问题描述如下：权限配置越少，性能越差。当配置所有（20,000）个du权限时，只需要2s，当只配置120个DU权限时，需要半个多小时。看到这样的描述，我心里也有点觉得：这有违常理。一般来说，越大越慢，但现在越小越慢。按照习惯，还是想先看看这个不合理的SQL，看看它在什么地方。找开发者获取SQL，打开代码如下：从体量上看，这条SQL并不大，一共130多行。这在我的项目组中比较常见。从体型来看，似乎不太协调：尾巴太大了。在WHERE条件子句中，有5个EXISTS条件拖拽，都是OR关系。这已经很不正常了，难道是问题描述的问题？我向开发者咨询了这5个EXISTS子查询的业务功能，得到的信息是：1、这5个EXISTS子查询的功能是认证，即权限识别；2、不同的EXISTS子查询代表不同类型的权限Collection；3、认证的对象粒度为DU，即每个EXISTS子查询与EXISTS子查询之间的关联字段为LINE.DU_ID从SQL本身，我找不到明显的“缺陷”，所以我会尝试看看执行计划，在SQLDEV中按F5，显示的执行计划如下：执行计划比较长，我们只能看exists的部分，发现基本都是索引扫描，cost值也很大low，也就是说，在执行计划中也看不出问题。那么问题出在哪里呢？方法笨，效果好，我当时在想：是不是singleexistsslow？还是把5个放在一起比较慢？为了找出原因，我把EXISTS一一注释掉，注释完再观察表现。虽然这种方法有些笨拙，甚至被很多人鄙视这种方法，但有时候确实是一种有效的定位问题的手段和途径。经过反复标注和测试，出现了奇怪的现象：1.5个existence条件单独工作时，没有性能问题；2.当第一个和第三个存在条件一起工作时，不存在性能问题；3.第一个存在条件当第三个、第四个、第五个存在条件组合在一起时，性能问题就凸显出来了。从这个角度来看，问题越来越复杂了。Oracle执行这条SQL时到底发生了什么？万千思绪还乱成一团，想不通。无奈之下，只能求助于杀手级神器：SQLMonitor。得到了SQLMonitor的结果后，莫和正风似乎什么都清楚了。SQLMonitor截图如下（由于当时原始数据丢失，下面只给出模拟数据）：因为已经搞清楚exists子查询存在性能问题，所以重点关于EXISTS的Monitor信息，希望能从中找到有价值的信息和启示。对比了5个exists的执行计划后，“执行次数”引起了我的注意：5个exists的执行次数和实际行数不一样！（1）这些数字之间还有一个巧妙的联系：第一个的执行次数是20000，刚好是DU的总数，第二个的执行次数等于执行次数ofthefirstone-***实际行数，满足如下算法：其中f(n)为第n个存在的执行次数，e(n)为第n个存在的实际返回行数,n>1。(2)first和secondexists实际返回行数之和为120，正好是邮件中提到的权限配置数；而第三个19880加上120正好等于20000，正好是所有DU的个数。这一切只是巧合吗？还是另有隐情？基于以上两点信息，我顿时豁然开朗，明白了其中的道理。我们可以大致推断出Oracle的执行原理如下图所示：根据上面的流程和算法，很容易理解上面这组数字。同样，我也明白了为什么权限配置越少性能越差，而配置越多性能越好。为了更好地理解，这里有两个极端的例子。如果没有配置权限，每个DU需要遍历5次exists子查询，也就是说总共需要执行10万次（20000个DU，每个DU执行5次）exists子查询。反之，如果我们为20000个DU配置权限，并且都是***类型的权限（即第一个exists的权限），那么每个DU只需要执行***exists，后面四个exists的查询不需要需要被执行。所以它只需要执行20,000次。20000次和100000次的区别（另外还要考虑不同存在的性能也不同），对性能的影响还是很明显的。拨云见日不等于见天日奇怪的性能问题的阴云终于散去，但我却没有丝毫的喜悦感。虽然问题的原因已经“大白于天下”，但解决方案却让我无所适从。一开始，我尝试在已有SQL的基础上，通过SQLHint来干预执行计划，但性能并没有提升。我再次尝试重写这条SQL，将OREXISTS子查询重写为LEFTJOIN，但是性能问题变得更糟了。我还尝试根据该SQL创建特定索引，但仍然无济于事。回到原点，方德元尝试了很多次都无济于事。无奈之下，我又回到了问题的原点。这条SQL，在这个场景下，除了第一个exists子查询执行了100次，第二个exists子查询执行了20次，其他四个exists子查询执行了19880*4次没有意义。既然没有意义，能不能省略呢？我对自己的这个天马行空的想法感到非常兴奋。因为就像开始测试的时候一样，注释掉后三个存在之后，性能非常好。也就是说，如果能成功避免无用的EXISTS子查询，也能达到性能优化的目的。但是很明显，Oracle不能识别在执行SQL之前必须执行哪些EXISTS子查询？哪些EXISTS子查询不需要执行？难道自己的idea就这么死了？不见兔，不散鹰。我继续我的疯狂想法。既然Oracle在执行SQL的时候是不可预测的，那我们写这个SQL的时候，能不能先预测一下呢？如果某类权限没有配置，我们就不会在SQL中拼凑对应的EXISTS子查询。这样，本例SQL中就只剩下两个EXISTS子查询了。成绩自然也令人满意。以上想法只是我的一厢情愿和理想主义。在实际应用中是否可行还不得而知：这个SQL是固定的还是Java代码拼凑的？判断是否配置了某类权限复杂吗？也会有性能问题吗？等等，不寒而栗。但就像小马过河一样，不去尝试怎么知道真实可行呢？于是，我和开发者沟通了这个不太认真的方案。开发人员的表现喜忧参半。好消息是他并不反对这种解决方案。如果真的能解决性能问题，他也愿意接受这个方案。担心的是5个exists子查询的sql不在他的控制范围内。原来本例SQL所在的system模块是任务管理，5个EXISTS子查询是认证功能，属于权限模块。这些EXISTS子查询由权限模块开发和维护。用任务管理模块开发者的话说，“这5个EXISTS是调用权限模块的服务得到的，如果权限模块给我们3个EXISTS，我们就拼凑3个EXISTS子查询，如果不给我们EXISTS，我们将不组成EXISTS子查询。”于是，我就拿这个方案去“游说”权限模块的开发者。当我找到权限模块的开发人员时，我们并没有只是拖出我的解决方案，而是将性能问题放在一边。没想到开发商很淡定，就好像这一切他早就知道一样；但他也满脸无奈，他说：“这个性能问题还是暴露了，没办法，原来权限设置这么复杂，我们不想这么复杂。”时机成熟的时候，我就把自己的方案说了出来，没想到开发者听了之后，眼睛一亮，满脸通红，说道：“这个很好，很好，我现在就按照你的方案重写。”这不仅仅是你的SQL有问题，其他所有涉及到鉴权的SQL都会有这个问题。”那么，一切就水到渠成了。2.体验从此爱上了SQLMonitor。优化过程这个案例非常曲折，差点半途而废。当我对几个exists不知所措的时候，多亏了SQLMonitor的帮助，才得以拨开乌云，终于看到了蓝天。从explainplan来看，我们可以知道Oracle优化器的意图，通过SQLMonitor，我们可以在运行时得到很多信息，比如本例涉及的“实际返回行数”、“执行次数”等，这些对于帮我们定位问题，分析原因，感谢SQLMonitor！头疼就医，脚疼就医‘tscatteraneagle’解法确实可以成功，但只适用于t本案例的业务场景。这个方案还有一个致命的缺陷：配置的权限种类越多，对整个SQL性能的影响就越大。我们设权限配置对SQL性能的影响为P，P的计算公式为：从公式可以看出，当N=0时，没有影响，当N=5时，影响是绝对的.事后，我和组长口头沟通了这个隐患，组长也很无奈：“我也仔细研究过I项目的权限机制，发现有些不合理的地方，不然我也不会写这么复杂的认证语句，不过，目前还不可能推翻权限机制，重新来过，就这样吧。
