优秀的优秀优化器是在实践中磨练出来的

作者：白鳝 2022-11-26 00:02:00数据库 其他数据库 Oracle DBA看到这条SQL会觉得十分不解，为啥能写出这样的化器SQL语句来呢？程序员的大脑DBA是很难理解的。就是实践不知道我们的数据库厂商懂不懂了。不过实际应用场景中我们确实经常遇到这样的中磨奇葩SQL。

​在和一些国产数据库厂商的练出朋友交流的的时候，总能听到他们说自己的优秀优化器是高手设计出来的，充分利用了现代软硬件技术，化器因此与Oracle相比只强不弱。实践我不太赞成这样的中磨说法，因为一个优秀的练出优化器设计能够做到的只是在大的框架上比较不错，针对一些常规的优秀SQL语句比较有效，而SQL语句的化器复杂性往往远远超出数据库设计人员的想象，我们的实践数据库厂商也往往低估了开发人员写SQL的能力。那些天马行空的中磨神来之笔，会让再优秀的练出优化器都感到力不从心。

前些年一个朋友在做一个数据库迁移的时候遇到一条SQL的性能问题，这条SQL在Oracle上执行的效率很不错，但是到了一个基于PG的国产数据库上，就慢得让人受不了了。我们通过一个简单的测试案例来复现这个问题。

用户现场是一个内网系统，因此我们只能采用模仿的方式来给大家复一复盘。我们用dba_objects和dba_tables两个系统视图来创建两张物理表。然后执行这条语句：

Oracle DBA看到这条SQL会觉得十分不解，为啥能写出这样的SQL语句来呢？程序员的大脑DBA是很难理解的。就是不知道我们的数据库厂商懂不懂了。不过实际应用场景中我们确实经常遇到这样的奇葩SQL。

我们在PG数据库上做一个类似的测试用例，我们使用PG_TABLES、PG_INDEXES这两个视图来创建t1/t2表。

保险起见，建完表后我们做一次vacuum analyze。然后看看这条SQL的执行计划如何：

这条SQL貌似执行速度还行，不过实际上真实环境的数据是不同的。我们从执行计划上来看看会有些什么问题。首先在T2表上是做了一个根据扫描，查到一个数组，这个被定义为SubPlan1，然后对T1表做扫描，通过SubPlan1的结果做过滤，获得最终的数据。这个执行计划的问题实际上是十分明显的，当T1/T2表很大的时候，这个查询会变得很慢。比如我们增加T2的大小到几万条记录。

可以看到，PG的执行计划变成了在T2表上通过索引扫描，这是优化器做了有效的优化。我们用同样的方法扩大T2表，到几十万条记录，看看会有什么情况。

执行计划还是如此，而执行时间已经加大到400多毫秒了。如果数据库的IO性能有点问题，并且t1表十分巨大，那么这个执行计划肯定就会有问题了。实际生产环境中就是因为数据量较大，才出现了性能问题。

我们再来看看Oracle的执行计划，可以看出这两个执行计划之间的差异是很大的。

Oracle的执行计划采用了一个Hash 半连接，通过两次索引扫描获得半连接的两个半区数据，然后用HASH UNIQUE探测内表数据。做一个10053 trace我们可以看到，Oracle在编译这条SQL的时候，做了多种FPD和转换的分析，最终才找到了这个最优解。如果对这个分析过程感兴趣的朋友可以自己做个10053看看，这里篇幅有限我就不做详细的介绍了，整个trace文件接近6000行。

这个执行计划可以说是没有太大毛病的，通过两个索引避免了两张大表的全表扫描，通过Hash半连接确保了整个JOIN的总体规模可控。

一个优秀的数据库产品，其优化器一定会随着应用规模的扩大，遇到的奇葩SQL越多而变得越来越强大的。如果我们总是告诉用户，你不应该这么写SQL，而不从优化器的角度去解决这些奇葩SQL的性能问题，那么我们的进步就会变得太慢，我们与Oracle的技术差距就会越来越大。

对于这个案例，前阵子我正好和一家国产数据库厂商做过一些交流。他们的老版本中的执行计划也不是很好。

当时我和厂商的朋友分析他们的执行计划的时候，我认为虽然在T2的TABLE SCAN上做了LIMIT(1)的过滤，但是如果符合条件的记录位于一张大表的最后几行，那么这个扫描的成本会很高。并且最致命的是Nested loop Join Cartestan这个算子，如果T1符合条件的数据比较多，那么这条SQL的执行效率将会特别低，甚至几个小时执行不出来。

最近我测试了他们的最新版本的产品，让我感到了新版本在优化器方面的能力提升还是比较大的。

当表的数据量不大的时候，执行计划通过对两个索引的扫描，然后做MERGE半连接。

数据量较大的时候，执行计划改走了Hash 右半连接，与Oracle的执行计划不同的是，对较小的表T1采用了全表扫描的模式。

虽然在这个执行计划上还有一些可以商榷的地方，不过不同的数据库因为对全表扫描的成本的计算不同，因此可能会有不同的选择。从两个版本的执行计划的优化效果上，我们也看到了国产数据库在核心能力方面的进步。这种进步恐怕只能在不断的实践中才能磨练出来。因此我们也有理由相信，随着数据库信创工作的不断深入，我们的数据库产品也会越来越好的。​

责任编辑：武晓燕 来源： 白鳝的洞穴 优化器SQL语句

(责任编辑：时尚)