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审核在更前的时段发现和修正问题,从而防患于未然。

近期文章

新年贺礼:云和恩墨大讲堂期刊第二期

删繁就简-云和恩墨的一道面试题解析

用SQL解一道数学题:Gauss和Poincare

新年贺礼:云和恩墨大讲堂期刊发行

2015 Oracle 十大热门文章精选

Oracle 12c ASM 防火防盗新特性揭秘

DBA入门之路:学习与进阶之经验谈

DBA入门之路:关于日常工作的建议

业务架构

电子渠道(网络销售)分析系统、数据治理

IT基础架构

分布式存储解决方案 | zData一体机 | 容灾环境建设

数据架构

Oracle DB2 MySQL NoSQL

专项服务:架构/安全/容灾/优化/整合/升级/迁移

运维服务:运维服务 代维服务

人才培养:个人认证 企业内训

软件产品:SQL审核、监控、数据恢复

应用架构

应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-02-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Golang语言社区

Go 语言构建高并发分布式系统实践

你知道互联网最抢手的技术人才有哪些吗?最新互联网职场生态报告显示,最抢手的十大互联网技术人才排名中Go语言开发人员位居第三,从中不难见得,Go语言的渗透率越来越...

4815
来自专栏吴生的专栏

消息中间件选型分析——从Kafka与RabbitMQ的对比来看全局

消息队列中间件(简称消息中间件)是指利用高效可靠的消息传递机制进行与平台无关的数据交流,并基于数据通信来进行分布式系统的集成。通过提供消息传递和消息排队模型,它...

4117
来自专栏数据和云

实战演练:洞若观火--治堵之道在清源

堵塞往往是一件可怕的事情,交通堵塞让人心烦意乱,水道堵塞城市就会臭气冲天,言路堵塞则是非难辨。数据库出现会话堵塞,则很可能造成系统业务中断,这对于 DBA 来说...

945
来自专栏杨建荣的学习笔记

Oracle和MySQL的高可用方案对比(二)

昨天聊了一篇关于高可用方案中Oracle的RAC和MySQL的MHA的对比。 今天来说下Oracle的DG和MySQL的方案对比,相比来说,可能这方面MySQ...

2734
来自专栏杨建荣的学习笔记

浅谈事务(一) (r5笔记第24天)

关于事务,在Oracle中似乎是习以为常的,但是在学习MySQL的过程中,发现各种灵活的存储引擎,一个很大的焦点就是对于事务的支持,足以看出事务的实现还是有一定...

3406
来自专栏IT技术精选文摘

聊聊C10K问题及解决方案

1 C10K问题 大家都知道互联网的基础就是网络通信,早期的互联网可以说是一个小群体的集合。互联网还不够普及,用户也不多。一台服务器同时在线100个用户估计在当...

3209
来自专栏windealli

后台开发必备知识——容灾

后台开发的目标是要提供高可用的后台服务,其中很重要的一点是保证业务连续性(服务不中断,或中断时间在允许范围内)。

2254
来自专栏一名叫大蕉的程序员

分布式架构的套路No.74

今天小蕉跟大伙一起聊聊分布式系统的架构的套路。在开始说套路之前,大家先思考一个问题,为什么要进行分布式架构? 大多数的开发者大多数的系统可能从来没接触过分布式...

1777
来自专栏原创

浅析开源数据库MySQL架构

数据库是所有应用系统的核心,故保证数据库稳定、高效、安全地运行是所有企业日常工作的重中之重。数据库系统一旦出现问题无法提供服务,有可能导致整个系统都无法继续工作...

44512
来自专栏Java技术

漫谈分布式架构的几种套路

今天小蕉跟大伙一起聊聊分布式系统的架构的套路。在开始说套路之前,大家先思考一个问题,为什么要进行分布式架构?

561

扫描关注云+社区