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

  1. 云栖社区>
  2. 数据和云>
  3. 博客>
  4. 正文

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

知与谁同 2017-07-18 14:00:44 浏览1260
展开阅读全文

640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy
黄廷忠(网名:认真就输)

云和恩墨技术专家

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


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


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


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

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


SQL性能问题诊断

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

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=


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

 

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

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

执行计划中,可以看到在谓词信息部分有多个 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 中的统计信息:

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

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

 

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

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=


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

 

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

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=


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


SQL 改写


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

 

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

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

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

SQL性能优化效果


修改后的 SQL 的执行计划:

640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

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

 

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


640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=


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

总结


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


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


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

网友评论

登录后评论
0/500
评论
知与谁同
+ 关注
所属云栖号: 数据和云