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

相关文章

来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3195
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

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

Spring Reactor 项目核心库Reactor Core

Non-Blocking Reactive Streams Foundation for the JVM both implementing a Reactiv...

2192
来自专栏一个爱瞎折腾的程序猿

sqlserver使用存储过程跟踪SQL

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_s...

2070
来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

5566
来自专栏落花落雨不落叶

canvas画简单电路图

63311
来自专栏大内老A

The .NET of Tomorrow

Ed Charbeneau(http://developer.telerik.com/featured/the-net-of-tomorrow/) Exciti...

32010
来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.2K7
来自专栏陈仁松博客

ASP.NET Core 'Microsoft.Win32.Registry' 错误修复

今天在发布Asp.net Core应用到Azure的时候出现错误InvalidOperationException: Cannot find compilati...

4878
来自专栏hbbliyong

WPF Trigger for IsSelected in a DataTemplate for ListBox items

<DataTemplate DataType="{x:Type vm:HeaderSlugViewModel}"> <vw:HeaderSlug...

4064

扫码关注云+社区