数据分布决定SQL写法

标签: 函数  sql

这是2016年8月份上海MOORACLE大会上陈宏义老师(老K)分享的一个案例,将一个merge SQL,通过改写成plsql的方式,大大提高了执行效率。 老虎刘在看到这个案例的时候,开始没有注意到执行计划里面显示的各表实际记录数,不认为plsql的改写方式比分析函数的写法更高效,还与陈老师有过几次邮件讨论,直到后来仔细查看了执行计划。

原SQL如下:

merge into t_customer c using

(

select a.cstno, a.amount from t_trade a,

(select cstno,max(trade_date) trade_date from t_trade

group by cstno) b

where a.cstno = b.cstno and a.trade_date=b.trade_date

) m

on(c.cstno = m.cstno)

when matched then

update set c.amount = m.amount;

这个SQL是将用户交易明细表(t_trade )的最近的一笔消费额,更新到用户信息表(t_customer)的消费额字段,使用的是merge操作。

执行计划:
这里写图片描述
老虎刘注:

在没有掌握分析函数的写法前,SQL的红色部分是group by后取其他字段信息的一个较为常见的写法,也是这个SQL执行效率差的根本原因。

原SQL还有一个隐患,就是如果t_trade的某个cstno对应的最大trade_date有重复,那么这个SQL会报ORA-30926 错误无法执行。

如果不仔细看执行计划(两表的真实数据量信息),这种SQL的惯用优化方法是使用分析函数改写:

改写方法1:

merge into t_customer c using

(

select a.cstno,a.amount from

(select trade_date,cstno,amount,

row_number()over(partition by cstno order by trade_date desc) RNO from t_trade)a

where RNO=1

)  m

on(c.cstno = m.cstno)

when matched then

update set c.amount = m.amount;

这种改写方法会比原SQL效率提高很多,而且不存在某个cstno对应的max trade_date 重复时报错的问题。

但是陈老师没有使用分析函数的改写方法,而是根据两表数据量相差较大的特点,将SQL改写成一段更为高效的plsql:

改写方法2:

declare

vamount number;

begin

for v in (select * from t_customer )

loop

select amount into vamount from

(select amount from t_trade where cstno=v.cstno order by trade_date desc)

where rownum<2;

update t_customer set amount = vamount where cstno=v.cstno;

end loop

commit;

end;

根据原SQL的执行计划我们知道,t_customer表的记录数比较少,只有1000多条,而t_trade表有1000万条,比例为1:10000(不知道这是真实数据还是测试数据,只有1000多个用户,而且一个用户平均1万个消费明细,看起来不像真实数据)。

在这样一个两表数据相差较大的特殊情况下,plsql写法确实是比分析函数的写法要高效。这个改写非常巧妙。

我们再来分析一下这两种改写的优缺点:

1、plsql的改写方式,适合在t_customer表比较小,而且t_customer 和 t_trade 两表的记录数比例比较大的情况下,执行效率才会比分析函数的改写高一些。在本例中,如果t_customer表的记录数是10万,那么分析函数的写法反而要比plsql的写法快上几十到上百倍。

3、plsql这种改写的前提是必须存在t_trade表cstno + trade_date 两字段的联合索引。而分析函数的改写就不需要任何索引的支持。

4、对于t_trade这种千万记录级别的表,使用分析函数的写法可以通过开启并行来提速;plsql的改写,如果要提高效率,需要先将t_customer表按cstno分组,用多个session并发执行。

我们再来看看,陈老师的这段plsql,是不是可以用单个sql来实现,我做了一个尝试,SQL代码如下:

merge into t_customer c using

(

select tc.cstno,

(select amount

from t_trade td1

where td1.cstno=tc.cstno and td1.trade_date = (select max(trade_date) from t_trade td2 where tc.cstno = td2.cstno) and rownum=1 ) as amount

from t_customer tc

)  m

on(c.cstno = m.cstno)

when matched then

update set c.amount = m.amount;

执行计划大致如下:

这里写图片描述
这种写法也是需要t_trade表存在cstno+trade_date 联合索引(IDX_T_TRADE),而且T_customer 表的数据量远低于T_trade。

根据执行计划,这个sql的执行效率应该比plsql写法的效率不相上下。

总结:

SQL优化,除了要避免低效的SQL写法,主要还是要看表的数据量与数据分布情况,plsql的改写方法,在少数比较特殊的情况下会体现出较高的效率,在某些数据分布的情况下,效率可能还不如原SQL。但是,优化思路非常值得借鉴。

而分析函数的改写方式,则不论数据如何分布,都会比原SQL要高效,通用性更强。

对于本例改写前的SQL,应该还有很多开发人员和DBA在使用,在了解了分析函数的使用方法后,原SQL的低效写法就应该被彻底抛弃了。

最后的plsql改写成单SQL,逻辑看起来比较复杂难懂,一般不会用到这样的改写,大家了解一下就好了。

还是那句话,优化无定式,优化器是死的,人脑是活的,只有掌握了原理,才能让SQL执行效率越来越高。

转载自:

http://www.yunweipai.com/archives/18609.html

版权声明:本文为u013553804原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u013553804/article/details/78877578

智能推荐

小程序基础与实战案例

小程序开发工具与基础 小程序开发准备: 申请小程序账号( appid ) 下载并安装微信开发者工具 具体步骤如下: 先进入 微信公众平台 ,下拉页面,把鼠标悬浮在小程序图标上 然后点击 小程序开发文档 照着里面给的步骤,就可以申请到小程序账号了。 然后就可以下载 开发者工具 了 下载完打开后的界面就是这个样子 下面让我们来新建一个小程序开发项目: 在AppID输入自己刚刚注册的AppID就可以,或...

VMware centOS7 下通过minikube部署Kubernetes

1、环境准备: VMware CentOS-7-x86_64 CPU:2*2core 内存:8G 宿主机和虚拟机需网络互通,虚拟机外网访问正常 Centos发行版版本查看:cat /etc/centos-release root用户操作 2、禁用swap分区 Kubernetes 1.8开始要求关闭系统的Swap,可暂时关闭或永久禁用, 使用 $ free -m 确认swap是否为开启状态 $ s...

逻辑回归与scikit-learn

欢迎关注本人的微信公众号AI_Engine LogisticRegression 算法原理 一句话概括:逻辑回归假设数据服从伯努利分布,通过极大化似然函数(损失函数)的方法,运用梯度下降或其他优化算法来求解参数,来达到将数据二分类的目的。 定义:逻辑回归(Logistic Regression)是一种用于解决二分类(0 or 1)问题的机器学习方法,用于估计某种事物的可能性(不是概率)。比如某用户...

指针OR数组?用他们来表达字符串又有何不同?

cocowy的编程之旅 在学习C语言的过程中我们经常可以看到或者听到这样一句话:数组其实等价于指针,例如: 在这里可以轻松的看出输出后他们的值相等,其实在计算机内存里面,p为本地变量,有着他自己的作用域。而指针变量q保存着这个数组的首地址,通过*号指向这个地址保存的变量值。 然而我们再看一个例子: 这个时候计算机报错,这是为什么呢? 其实原因很简单,指针说指向的这个字符串的地址是位于计算机代码段地...

广度搜索

广度搜索的基本使用方法 广度搜索不同于深度搜索,是一种一步一步进行的过程,每一个点只记录一遍。需要用到队列记录每一步可以走到的位置,找到目标位置输出步数即可。 用到的知识:结构体、队列 如图 首先我们需要定义一个结构体来存储每个遍历到的点和步数 广搜不会用到递归,所以可以直接在主函数里写,这里需要定义一个结构体队列 初始化队列并将起始点入列 遍历 完整代码...

猜你喜欢

NIO Socket 编程实现tcp通信入门(二)

1、NIO简介 NIO面向通道和缓冲区进行工作,数据总是从通道读取到缓冲区中,或者从缓冲区写入到通道中。可以双向传输数据,是同步非阻塞式IO。NIO还引入了选择器机制,从而实现了一个选择器监听多个底层通道,减少了线程并发数。用NIO实现socket的Tcp通信需要掌握下面三个知识点: Buffer 缓冲区 Channel 通道 Selector 选择器   2、java.nio.Buff...

[字节码系列]ObjectWeb ASM构建Method Monitor

      在前面的篇章中,我们看到Java Instrutment的强大能力,本篇,我们将介绍如何使用ObjectWeb ASM的字节码增强能力构建Method Monitor       1.什么是ObjectWeb ASM      ObjectWeb ...

Core Location 电子围栏:入门

原文:Geofencing with Core Location: Getting Started 作者:Andy Pereira 译者:kmyhy 更新说明:Andy Pereira 将本教程升级至 Xcode 9.3 和 Swift 4.1。 Geofencing 会在设备进入/离开指定的电子围栏时通知应用程序。它可以让你写出一些很酷的应用程序,当你从家里出来时触发通知,或者在附近出现最爱的商...

Android 圆角边框RoundRect原理

绘制圆角矩形的方法 该方法来自Canvas类,rect代表矩形,rx和ry分别代表形成圆角所需要的椭圆的x和y轴半径,那么rx和ry究竟如何形成圆角呢? 形成圆角的原理 矩形的四个圆角是分别生成的,以左上角的圆角为例: 首先通过rect绘制出矩形,然后以矩形的左上角定点为起点,分别向x和y轴平移rx和ry, 得到的点为中心,以rx和ry为x和y轴的半径绘制椭圆,椭圆的坐上半部分圆弧就是圆角了。如下...

Python 面向对像

整理至http://www.cnblogs.com/wupeiqi/p/4493506.html博客 一、创建类和对象      类就是一个模板,模板里可以包含多个函数,函数里实现一些功能 对象则是根据模板创建的实例,通过实例对象可以执行类中的函数 class是关键字,表示类 创建对象,类名称后加括号即可 ps:类中的函数第一个参数必须是self(详细见:类的三大特性...