SQL审核：OR展开与子查询优化案例详解

黄廷忠（网名：认真就输）

云和恩墨技术专家

个人博客：http://www.htz.pw/

本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例，SQL 优化及 SQL审核，是从源头解决性能问题的根本手段，无论是开发人员还是 DBA，都应当持续深入的学习 SQL 开发技能，从而为解决性能问题打下根基。

第一篇为：性能为王：SQL标量子查询的优化案例分析

本篇为系列案例之二：OR展开与子查询优化案例详解。

本案例 SQL 是15年给一个省电信系统做优化时遇到的。

SQL性能问题诊断

下面来看看一条 SQL，主查询使用 OR 与子查询联合一起使用，导致子查询不能展开，只能走 FILTER，子查询被轮询很多次，消耗大量逻辑读。

这里需要注意红色框部分，可以看到括号中存在主查询列过滤，并且在后面跟一条子查询做OR运算。

 

下面来查看此 SQL 的执行计划：

执行计划中，可以看到在谓词信息部分有多个 FILTER，在执行计划中有3个 FILTER，但是在 SQL*PLUS 中，只有两个 OR，所以需要弄清楚哪些 FILTER 是过滤，哪些 FILTER 是子查询没有展开导致的。

 

两个红色框的部分就是子查询没有展开导致的，箭头部分只是用于列过滤的。这里有一个判断准则：

1. 当 FILTER 下面有两个儿子表（结果集）的时候，此时FILTER就是子查询没有展开导致，此时的 FILTER 可以看成是执行完子查询后的过滤；

2. 当 FILTER 下面只有一个儿子表（结果集），此时的 FILTER 是做行过滤的；

 

FIlTER 的原理跟 NL 的原理类型，当驱动表换回一行时，被驱动表执行一次。但是 FILTER 还与 NL 有2点不同的是:

1. 当驱动表返回有重复值时，被驱动不会执行

2. 当被驱动表找到匹配的行时，立即终止本次循环

 

在 SQL 中见到出现 FILTER 时，不能直接说性能不好。那到底怎么判断有 FILTER 时，SQL 是的性能是否好呢？其实觉得可以根据主查询返回行数很少时，使用 FILTER 性能可能很好，主查询返回的行数很多时，走 FILTER 性能肯定不好。 

基础信息分析

下面来看看 v$sql 中的统计信息：

每个字段的值在上面案列中已经提过，这里就不再说明了。

 

可以看到平均返回一行，消耗的逻辑读是2076324：

通过 SQL 每次执行返回的逻辑读与 SQL 关联的表占用的大小，可以间接的推断此 SQL 由于不停的轮询消耗大量的逻辑读，可以知道主表返回的行数肯定很多的(这里还有一种特殊情况就是消耗 TEMP 的情况)。

 

下面查看一下主表返回的行数：

这里只简单的查询表 GROUP BY 的值，这里根据上面的值估计主表返回的结果集很多，如果要准确的值，可以关联上面2张表查询。

SQL 改写

现在知道原因了，那么这个 SQL 优化基本完成70%的工作了，现在就是想办法来怎么处理了：让 SQL 不走 FILTER。

 

由于原来 SQL 就使用 DISTINCT ，所以这里不需要考虑重复值的情况。根据本 SQL 的特征，将 SQL 修改成如下的:

这里将 OR 修改成 UNION 的方式。

SQL性能优化效果

修改后的 SQL 的执行计划：

从执行计划中，我们已经没有看到 FILTER 信息了。

 

下面查看 SQL 执行的统计信息

这里看到 SQL 执行的逻辑都从原来的2,076,324降到现在的11了。效果很明显。

总结

本条 SQL 优化是通过改写 SQL 来完成的，意味着业务需要修改 SQL，可能会出现业务修改完 SQL 再上线，这中间可能会消耗大量的时间，并且如果 SQL 后期出现性能问题，需要再次修改 SQL 的成本也会更高。建议在优化的 SQL 时候，可以通过提示+SQL PROFILE 来固定执行，达到不修改 SQL 的前提下，优化 SQL。这样业务可以不需要修改代码，并且后期随时可以通过修改 SQL PROFILE 中的提示信息来修改 SQL 的执行计划。

良好的应用性能，必须依赖高度优化的SQL性能，我们推荐用户通过SQL审核在更前的时段发现和修正问题，从而防患于未然。

文章转自数据和云公众号，原文链接