最具影响力的数字化技术在线社区

168大数据

 找回密码
 立即注册

QQ登录

只需一步,快速开始

1 2 3 4 5
开启左侧

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

[复制链接]
发表于 2016-2-19 10:01:32 | 显示全部楼层 |阅读模式

马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

黄廷忠(网名:认真就输)
云和恩墨技术专家
个人博客: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 是子查询没有展开导致的。
两个红色框的部分就是子查询没有展开导致的,箭头部分只是用于列过滤的。这里有一个判断准则:
  • 当 FILTER 下面有两个儿子表(结果集)的时候,此时FILTER就是子查询没有展开导致,此时的 FILTER 可以看成是执行完子查询后的过滤;
  • 当 FILTER 下面只有一个儿子表(结果集),此时的 FILTER 是做行过滤的;

FIlTER 的原理跟 NL 的原理类型,当驱动表换回一行时,被驱动表执行一次。但是 FILTER 还与 NL 有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审核在更前的时段发现和修正问题,从而防患于未然。

本文已授权转载

楼主热帖
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

关于我们|小黑屋|Archiver|168大数据 ( 京ICP备14035423号|申请友情链接

GMT+8, 2024-3-29 21:47

Powered by BI168大数据社区

© 2012-2014 168大数据

快速回复 返回顶部 返回列表