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

​在和一些国产数据库厂商的朋友交流的的时候，总能听到他们说自己的优化器是高手设计出来的，充分利用了现代软硬件技术，因此与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采用了全表扫描的模式。

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