聊聊国产数据库迁移中的表连接性能问题

时间:2023-02-01 12:07:39

最近很多朋友都在搞信创数据库迁移,从Oracle迁移到国产或者开源数据库后,最大的抱怨就是性能下降太厉害,这好像和一些厂商宣称的性能不太符合。不过有时候问题也不像一些抱怨那么严重。大多数遇到性能下降的问题都是执行计划的问题,都是可以解决的,不过也有一些在Oracle上执行计划很好的SQL,迁移到了国产数据库或者开源数据库上后,怎么调整都调不好。在开始搞信创数据库后,SQL优化将成为一件大事情。

实际上SQL最容易出问题的地方还是表连接,单表的访问,哪怕走错了索引,SQL跑的慢点了,影响还没那么严重。表连接的执行计划出问题了,可能在Oracle上秒出的SQL,几个小时都跑不出来。这两天写了一些云山雾罩的文章,今天就写点轻松的,和大家聊聊表连接。表连接有很多种,内连接、左外连接、右外连接、全外连接、半连接(SEMI JOIN)、ANTI JOIN等。把这些连接搞清楚,把这些连接采用的连接模式搞准确,那么SQL跑起来也不至于那么让人沮丧了。

聊聊国产数据库迁移中的表连接性能问题

为了更好的解释今天的内容,我首先创建了两张测试表。我们今天的测试是在一个PG 12.3内核的数据库上进行。首先我们创建两张测试表,然后我们再来看看各种表连接的情况。

聊聊国产数据库迁移中的表连接性能问题

内连接是最常用的一种,是取两表的交集数据。典型的语法是:SELECT count(*) FROM join1 j1 INNER JOIN join2 j2 ON j1.id = j2.id;。

聊聊国产数据库迁移中的表连接性能问题

如果对两张表不加筛选条件,对上万条数据的JOIN,最佳的执行方式是HASH JION。从执行计划上也可以看出这一点。Inner join的等价SQL是:SELECT count(*) FROM join1 j1,join2 j2 where j1.id = j2.id;

聊聊国产数据库迁移中的表连接性能问题

如果我们把这条语句稍微改一下,改成SELECT count(*) FROM join1 j1,join2 j2 where j1.id <> j2.id;我们会发现这条语句的执行效率下降的很厉害,从8.3毫秒变为将近40秒了。从执行计划上看,没有使用HASH JOIN,而是使用了性能较差的NESTED LOOP。如果在某个国产数据库中因为这种情况存在无法使用HASH JOIN引发应用性能问题,在语义等价的情况下,看看是否能够使用NOT IN或者NOT EXISTS来改写,参考下面的例子。

聊聊国产数据库迁移中的表连接性能问题

聊聊国产数据库迁移中的表连接性能问题

左外连接返回的结果是整个左行源加上二者交集的部分。典型的SQL是SELECT count(*) FROM join1 j1 LEFT JOIN join2 j2 ON j1.id = j2.id;

聊聊国产数据库迁移中的表连接性能问题

不过我们从执行计划上看,这条SQL并没有选择left join的执行计划,而是选择了以JION2表为驱动表的右外连接。优化器认为这个执行计划效率更高。

聊聊国产数据库迁移中的表连接性能问题

右外连接和左外连接类似:SELECT count(*) FROM join2 j1 RIGHT JOIN join2 j2 ON j1.id = j2.id;

聊聊国产数据库迁移中的表连接性能问题

和上面的例子类似,优化器认为改为左外连接效果更好。

半连接SEMI JOIN和LEFT JOIN是SQL语句中两种不同的连接类型,SEMI JOIN,该语句只返回左表中包含右表中的行,并且仅返回一次。而LEFT JOIN返回左表的所有行,如果右表中没有匹配的行,则返回NULL值。其典型的SQL是:SELECT count(*) FROM join1 j1 where exists (SELECT id FROM join2 j2 WHERE j1.id = j2.id);

聊聊国产数据库迁移中的表连接性能问题

聊聊国产数据库迁移中的表连接性能问题

ANTI JOIN是排除右表的数据。典型SQL是:

SELECT count(*) FROM join1 j1 where not exists (SELECT id FROM join2 j2 WHERE j1.id = j2.id);

聊聊国产数据库迁移中的表连接性能问题

还有一些连接模式,比如全外连接等,我们今天就不做讨论了。最后再给大家介绍一个从Oracle数据库迁移到国产开源数据库上最容易出性能问题的SQL:select j1.* from join1 j1,join2 j2 where j1.id=j2.id or j1.id=100;。

聊聊国产数据库迁移中的表连接性能问题

这条SQL的特点是在条件里出现了Or条件,在Oracle中这条SQL的执行效率是没问题的,不过在很多国产、开源数据库中性能就出问题了。遇到这种情况,改写SQL是最终解决方法。如果在等价的情况下,我们可以用union来改写这条SQL。

select j1.* from join1 j1,join2 j2 where j1.id=j2.id

union

select j1.* from join1 j1 where j1.id=100;

聊聊国产数据库迁移中的表连接性能问题

今天早上写了一半有客户过来,下午又要开始出差了,今天就先写到这里吧。有些问题只是提出来了,并没有做深入的分析,只是给大家提了一个思考的方向,如果有兴趣的朋友可以再去深入分析一下。解决了这些问题,国产数据库替代的工作就会顺利很多。