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 条评论
登录 后参与评论

相关文章

来自专栏大数据和云计算技术

大数据和云计算技术周报(第50期):NoSQL特辑

本期有 Redis、分布式、HBase、impala与hive、protobuf、Phoenix。 希望大家会喜欢!欢迎喜欢的同学打赏、转发支持社区!

713
来自专栏牛客网

秋招时间规划,知识点汇总,以及面试总结一、知识储备二、面试问题三、心态变化四、总结

秋招已结束,作为一个平时潜水的牛友,很感激牛客网和广大牛友们。在我无知时,给与我知识;在我烦恼时,给与我慰藉;现在自己也拿到了心仪的offer,就简单写写这段时...

36111
来自专栏一个会写诗的程序员的博客

附录C Java编程简史附录C Java编程简史丰富多彩的JVM生态参考资料

在20世纪60年代,软件曾出现过严重危机,由软件错误而引起的信息丢失、系统报废事件屡有发生。为此,1968年,荷兰E.W.Dijkstra提出了程序设计中常用的...

914
来自专栏python3

mysql-外键的三种关系

mysql> create table press(id int primary key auto_increment,name varchar(20));

883
来自专栏WebHub

盘点那些奇形怪状的编程语言

有的语言是多面手,在很多不同的领域都能派上用场。这类编程语言叫 general-purpose language,简称 GPL。大家学过的编程语言很多都属于这一...

1022
来自专栏企鹅号快讯

C语言的前世今生,及其特点、利弊和入门须知三把斧

C语言的开展前史: ? 20世纪70年代初,贝尔实验室的Dennis Richie 等人在B语言基础上开发C语言,最初是作为UNIX的开发语言; 20世纪70年...

1846
来自专栏大数据

爬虫无烦恼,大牛教你怎么简单爬36kr网,带源码

抓取36kr网站数据 爬虫代码和数据sql脚本在下方链接,抓取过程主要是抓包找到url递归解析的规律,三个主要的函数 以及 表结构脚本如下: #建表语句(写爬虫...

2119
来自专栏Java技术栈

最新后端架构师技术图谱!附学习资料~

2122
来自专栏美团技术团队

SQL解析在美团点评中的应用

1523
来自专栏数据和云

新书连载:Oracle数据库的跟踪和分析方法

编辑说明:《Oracle性能优化与诊断案例精选》出版以来,收到很多读者的来信和评论,我们会通过连载的形式将书中内容公布出来,希望书中内容能够帮助到更多的读者朋友...

3249

扫码关注云+社区