共计 1856 个字符,预计需要花费 5 分钟才能阅读完成。
这是 2016 年 8 月份上海 MOORACLE 大会上陈宏义老师 (老 K) 分享的一个案例,将一个 merge SQL,通过改写成 plsql 的方式,大大提高了执行效率。老虎刘在看到这个案例的时候,开始没有注意到执行计划里面显示的各表实际记录数,不认为 plsql 的改写方式比分析函数的写法更高效,还与陈老师有过几次邮件讨论,直到后来仔细查看了执行计划。
这个 SQL 是将用户交易明细表(t_trade)的最近的一笔消费额,更新到用户信息表(t_customer)的消费额字段,使用的是 merge 操作。
执行计划:
老虎刘注:
在没有掌握分析函数的写法前,SQL 的红色部分是 group by 后取其他字段信息的一个较为常见的写法,也是这个 SQL 执行效率差的根本原因。
原 SQL 还有一个隐患,就是如果 t_trade 的某个 cstno 对应的最大 trade_date 有重复,那么这个 SQL 会报 ORA-30926 错误无法执行。
如果不仔细看执行计划(两表的真实数据量信息),这种 SQL 的惯用优化方法是使用分析函数改写:
这种改写方法会比原 SQL 效率提高很多,而且不存在某个 cstno 对应的 max trade_date 重复时报错的问题。
但是陈老师没有使用分析函数的改写方法,而是根据两表数据量相差较大的特点,将 SQL 改写成一段更为高效的 plsql:
根据原 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 代码如下:
执行计划大致如下:
这种写法也是需要 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 执行效率越来越高。
![post-qrcode](https://www.xgss.net/wp-content/uploads/2023/07/二维码-微信公众号256x256.jpg)