从KingbaseES V9的自研优化器算子谈起

KES V8/V9两个版本都过了国测，这让电科金仓的新老用户在国产化替代工作中省了不少力气。V8老用户不必急着升级，新用户可以大胆地选择功能和性能更加优秀的V9版本。之前我听一些同学吐槽过，说因为PG内核升级了，所以KES V9的性能就比V8好了。事实是这样吗？有些东西道听途说总是不太靠谱，还是眼见为实才好。

上面的信息是D-SMART从KES V8R6中采集出来的，可以看出服务器版本是12.1。

上面是V9的信息，服务器版本并未升级。看样子V9在某些SQL上的性能提升并不是如坊间传闻的那样，是因为使用了较新版本的内核。通过对KES V9的初步分析，我个人的推测是，电科金仓在KES数据库内核可能上已经走上了自主分支的道路，不一定会紧跟PG社区内核升级了。在核心上脱离社区，构建自主的独立分支，同时关注社区的技术发展，不断把社区版本中的优秀方案搬到自主内核上。既保证了对用户需求的更好支撑，又可以不断吸取社区的先进思想，从而确保技术演进高效的前提下成本最低，这对于目前研发资金不太足够的国产数据库来说至关重要。 

目前国产化替代中，用户遇到的最主要问题有两方面，一方面是如何在最小改动的情况下将企业中原来在国外商用数据库上跑得很好的应用迁移到国产数据库上，这方面很多国产数据库做得都不错。比如达梦、电科金仓、神通这些老牌数据库厂商，经过十多年的技术积累，在Oracle、MySQL、PG、DB2、SQL SERVER等数据库的兼容性上做得都相当不错了。另外一方面是迁移过来的应用性能不能太差，起码能够接近原有数据库的水平或者相差不是太大。

第二方面的问题也是目前大多数国产数据库在用户现场遇到最多的，就是一些SQL的执行计划不如Oracle优秀，导致系统迁移后应用性能无法被用户接受。其中很重要的原因是因为国产数据库的优化器功能不足，某些Oracle支持的执行算子自身不支持。要解决这些问题，就需要数据库厂商在内核上多下点功夫，提升优化器的能力。

还有一种情况是某些用户的SQL写法并不常规，数据库产品经理没有想到会有这样的SQL存在，所以在生成执行计划时rewrite出来的等价SQL不够合理，从而导致随后生成的执行计划性能不佳。这类问题往往是因为我们的国产数据库实战的应用场景还不够丰富，因此没有发现这类问题。如果这类问题能够被发现的话，作为具有一定自主核心研发能力的数据库厂商可以很快就解决掉这些问题。

最近研究KES V9，发现内核中优化器方面的功能提升还是挺明显的，特别是自研算子和SQL REWRITE规则的丰富程度方面。举个例子，在PG数据库上遇到NOT IN子查询的语句还是挺头疼的，PG在大多数情况下会使用FILTER算子。我们来看下面的测试用例：

DROP TABLE JOIN1;

DROP TABLE JOIN2;

create table join1 (id integer,name varchar(300),k1 integer);

create table join2 (id integer,name varchar(300),score integer);

insert into join1 values ( generate_series(1,20000),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',10);

insert into join1 values ( generate_series(1,20000),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',10);

insert into join1 values ( generate_series(1,20000),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',10);

insert into join1 values ( generate_series(50201,50300),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAASSSSSAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',10);

insert into join1 values ( generate_series(50201,50300),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAASSSSSAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',10);

insert into join1 values ( generate_series(150201,1350300),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAASSSSSAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',10);

insert into join2 values ( generate_series(1,40000),'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',1);

insert into join2 values ( generate_series(1,40000),'aaaaaaaaaaaaaaaaAAAAAAABBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',2);

insert into join2 values ( generate_series(20001,22000),'aaaaaaaaaaaaaaaaAACCCCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',3);

insert into join2 values ( generate_series(150201,950300),'aaaaaaaaaaaaaaaaAACCCCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',3);

create index idx_j1 on join1(id);

create index idx_j2 on join2(id);

VACUUM ANALYZE JOIN1;

VACUUM ANALYZE JOIN2;    

首先我们在一套PG 14上测试一下下面的一个带有NOT IN子查询的SQL：

这是PG典型的过滤器算子。子查询扫描出来的数据做HASH，然后对外表的每行计算HASH值，进行否定过滤。这种执行计划与HASH ANTI JION相比存在一定的缺陷，无法更好选择左表，而且当子计划返回的数据超过WORK_MEM限制的时候，无法使用HASH表，会极大影响SQL的执行效率。以前在优化PG数据库上的应用时，遇到此类情况，只能改写SQL了。

我们再来看一下KES V9，它使用了Hash Anti LSNA Jion算子，效率也高了不少。Oracle、SQL SERVER等数据库都支持Hash Anti Jion算子，这对于NOT IN等类型的SQL消除子查询是十分有效的，特别对于数据量很大的情况。KES在算子方面从O记借鉴了很多，对于HASH ANTI JOIN，设计了NA ，LSNA，RSNA等多种算子，分别针对不同的场景。

上面的例子中，PG数据库做Filter的subplan返回的数据集还不算很大，我们设置32M的WORK_MEM还能够放得下整个HASH表，PG可以采用Hash算法来做Filter，此时的性能与HASH ANTI JOIN差别还不算大。如果返回的数据集比较大，PG的执行计划就会恶化。通过一个简单的测试，把T2的数据加大，再做一次测试看看。

上面是KES V9的执行计划，可以看出KES依然使用了Hash Anti Jion，因为我去掉了子查询中的>条件，返回的结果集可能带有空值，所以无法使用更加高效的LSNA算子，使用了NA算子。从响应时间上看是可以接受的，644毫秒相对数据量的增长还算线性。接下来再来看看PG 14的执行情况。

因为WORK_MEM不足，因此按照PG优化器的限制无法使用HASH，改为使用Materialize，所以这条SQL的执行时间恶化到75146毫秒。   

当然我们也可以通过设置更大的WORK_MEM来优化这条SQL，上面是我们把WORK_MEM加大到64M后的执行效果。不过能够在不需要调整WORK_MEM的情况下，通过优化器去解决这些问题，是不是对用户更加友好呢？而实际生产环境中，很多情况下，子查询的结果集可能会更大，我们也不能总是通过加大WORK_MEM来解决问题吧。

对于此类查询，Hash Anti Jion算子并不一定是最优的选择，如果子查询能够等价转换为JOIN，那么在不同的情况下，可能需要使用其他的算子来解决问题。修改一下查询条件，让外表扫描返回的数据量更少，在这个案例里KES V9优化器认为走Nested Loop Anti Jion最佳，看上图的结果，确实如此，执行时间降低到50毫秒。除此之外，适当调整数据量，我们还能看到这条SQL使用了MERGE ANTI JOIN算子，这些算子都是KES为了提升此类表连接的性能自研的。

PG 14则还是使用祖传的Filter: (NOT (hashed SubPlan 1))算子，执行时间的差距拉得更大了。

实际上目前数据库国产化替代工作中遇到的最麻烦的事情就是替换后很多执行计划变差，而且无法优化，只能通过修改SQL来解决问题，这给数据库国产化替代工作带来了额外的成本。

KES V9版本里，多了很多面向用户应用场景的优化器功能增强，比如参数kdb_rbo.enable_push_joininfo_to_union可以控制优化器的行为，让一个带有UNION操作的子查询参与连接操作，该特性可以将连接的条件下推到UNION连接的各子查询中，从而优化nested loop算子，从而提高SQL的性能。    

另外一个例子是针对大表做count distinct这个算子的优化 ，在数据重复度比较高的情况下，KES通过等价变换逻辑变换，将select count(distinct name) from t1; 转换成select count(name) from (select name from t1 group by name);的形式，可以大大提高SQL的效率。当然这种优化和数据的分布关系很大，因此并不是通用性的，通过调整kdb_rbo.attribute_distinct_value_threshold参数，用户可以根据自己应用的数据分布特点，在普通情况下使用传统的方式去处理，而达到参数规定的阈值后，自动启用SQL改写，从而能够更加灵活地解决SQL的性能问题。

其实DB2、Oracle的优化器中就有大量的这样的开关，这些开关，都是不断地在解决用户的实际问题的时候不断积累出来的。听电科金仓的同学说，目前他们正针对数百个客户现场遇到的与执行计划相关的性能问题，设计了大量的优化补丁 ，正在一个一个地投入研发解决。这些针对优化器的PATCH将会在未来的V9版本中陆续发布。

对于电科金仓的用户来说，这是个福音，这比简单地通过升级数据库内核获得某些方面的性能和功能的提升有价值得多。其实企业应用系统所需要的数据库功能与并发处理能力，目前的绝大多数数据库都已经够用了。用户最急迫需要的是无论自己的应用写得多烂，数据库厂商都能够通过对优化器的改进让用户的应用能够跑起来。在这方面，电科金仓的KES做得确实不错。