对于复杂的SQL， Oracle是怎么做的？

扫地僧的故事 2020-09-05

2243

很多人经常说，同样的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语句：

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中得查询性能也是一样一样的～

oracle

文章转载自扫地僧的故事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

对于复杂的SQL， Oracle是怎么做的？

评论

相关阅读