PG数据库中的表连接方式及应用场景

2023-05-18 17:51:33

关系型数据库的表连接常用的有三种方式，NESTED LOOP，HASH JOIN和MERGE JOIN(SORT MERGE JOIN,SMJ)。在Oracle数据库中，DBA也经常被这三种JOIN给折腾的够呛，一旦优化器因为统计信息的不准确出现了JOIN类型选择错误，那么对系统性能来说，就没法看了。前阵子有朋友问我这三种JOIN在什么场合使用。实际上PG数据库对这三种JOIN的支持都还不错，今天早上事情比较多，我就写个简单点的文章，利用一个INNER JOIN的SQL来给大家解释一下这三种JOIN模式，并且大致分析一下其应用场景吧。

DROP TABLE JOIN1;

DROP TABLE JOIN2;

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

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

insert into join1 values

( generate_series(1,10000),

'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA');

insert into join1 values

( generate_series(50001,51000),

'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAASSSSSAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA');

insert into join2 values

( generate_series(1,10000),'

aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',1);

insert into join2 values

( generate_series(1,10000),

'aaaaaaaaaaaaaaaaAAAAAAABBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',2);

insert into join2 values

( generate_series(20001,22000),

'aaaaaaaaaaaaaaaaAACCCCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',3);

VACUUM ANALYZE JOIN1;

VACUUM ANALYZE JOIN2;

先准备两张测试表JOIN1/JOIN2。先看看如果没有索引情况下是使用什么样的执行计划的。

可以看出，这时候使用了HASH JOIN，而且选择了行数较大的表JOIN2来做驱动表。我们可以继续验证一下是不是总是选用数据量较大的表。

可以很明显的看到，HASH JOIN总是选择较小的数据集来构建HASH表，然后用较大的数据集去做探测。这个也很容易理解，这是HASH JOIN的算法决定的，这样的开销小。

我们在JOIN2上创建了一个ID字段的索引，并且在JOIN1上设置了一个筛选条件，只选取10条记录，可以看到，执行计划不再使用HASH JOIN而选择NESTED LOOP了。因为较少的循环，并且通过索引可以很快速的完成JOIN。

我们再来看一个MERGE JOIN的问题，什么时候会走MERGE JOIN呢？我们修改一下对JOIN1和JOIN2的数据筛选条件，让二者的数据集规模相对接近，这时候我们看到使用了MERGE JOIN。因为MERGE JOIN是把两个行源的数据分别先排序，然后将两个排过序的行源做JOIN，因此当二者体量比较接近的时候，使用MERGE JOIN是比较合适的。