很多人经常说,同样的SQL在Oracle中的查询性能要比在MySQL中好很多,大家有没有深究过其中的原因呢?除了MySQL 8.0之前不支持hash-join以外,还有其他原因吗?
其实很多时候,出现这种差异的原因,是Oracle有查询重写的机制,并不是Oracle本身有多快,而是Oracle聪明的优化器已经帮你改好了SQL。
首先,对比下面两张图,看看区别在哪里?
猜猜为什么会有区别?估计你们是猜不到的。。
对比两个图,能看到图2中,oracle做了view merge,将
(select distinct t2.c1,t3.c3 from t2 inner join t3 on (t2.c2 =t3.c2 )) b
打开,view中的表与其他表放在一起计算连接方式,生成一个性能较好的执行计划;图1的执行计划中,
外层循环一次,view就要执行一次,里面的谓词又不高效,因此,查询性能就表现得很差。
就不卖关子了,其实,我在第一次执行SQL前,先将参数
"_complex_view_merging"
设成了
false ,默认是true。
实验过程如下:
在oracle中,
复杂视图或嵌套视图(包含有group by 或 distinct)的视图合并,由
_complex_view_merging
隐藏参数控制,当设置为true时,优化器评估可能应用视图合并,但是当设置为false时,即使使用merge hint也不能应用视图合并。
Tips:Oracle优化器真的是很强大,在生产上,最好不要轻易关闭优化器参数。
上期分享的案例中的SQL,如果拿到Oracle中执行,因为有查询重写的机制,就不会出现执行效率差的现象。(感觉Oracle还是蛮厉害的。。
)
为了更加突出Oracle优化器的聪明,我在MySQL数据库中造了一模一样的数据,把这个sql拿到MySQL数据库中执行,看看性能如何?
sql语句:
声明一下,写这篇文章的目的,不是想说Oracle多好,MySQL多么不好,研发SQL写得好,那么在MySQL中得查询性能也是一样一样的~
select * from tt1 inner join(select distinct tt2.c1,tt3.c3 from tt2 inner join tt3 on (tt2.c2 =tt3.c2 )) b on tt1.c1 = b.c1 ;
分析执行计划:
首先执行 id=2 子查询的内容,根据条件“tt2.c2 =tt3.c2” 将表tt2和表tt3关联,需扫描行数约
100000 * 2000
;接着执行 id =1 ,将子查询的结果集和表 tt1 进行关联,需要扫描的行数约为
10 * 2000000
,那么执行这个sql一共需要扫描的行数大约是
220,000,000
行。
执行了
17分钟
,结果也没出来,没耐心的我直接control + c了。。
显然,MySQL就没那么聪明了,并没有帮我们改SQL,规规矩矩的先执行子查询,再与外表做嵌套连接。
模仿Oracle的改写方式,手动修改了sql文本:
select * from tt1 ,(select distinct c2, c1 from tt2) t2 ,
(select distinct c2,c3 from tt3) t3
where tt1.c1=t2.c1 and t2.c2=t3.c2;
执行计划发生了明显的改变,扫描的行数也大大减少。
执行一次,
2.7秒
结果就出来了,查询性能得到了显著提升。
总结:
1. 在Oracle数据库中,绝对不要根据经验,随便关闭优化器参数,存在即合理。
2. 在MySQL数据库中,对于多表关联的SQL查询,编写时一定要慎重!
声明一下,写这篇文章的目的,不是想说Oracle多好,MySQL多么不好,研发SQL写得好,那么在MySQL中得查询性能也是一样一样的~