达梦数据库系列—41.表连接方式

目录

连接方式

NEST LOOP（嵌套循环连接）

HASH JOIN（哈希连接）

MERGE JOIN（排序归并连接）

---

连接方式

创建测试表：

create table tab1(c1 int,c2 int ,c3 int);

create table tab2(c1 int,c2 int ,c3 int);

insert into tab1 select level,level,level from DUAL CONNECT by level <100000;

insert into tab2 select level,level,level from DUAL CONNECT by level <100000;

create index ind_tab1 on tab1(c1);

create index ind_tab2 on tab2(c1);

select * from user_indexes where table_name in ('TAB1','TAB2') ;

NEST LOOP（嵌套循环连接）

  优化器会选择一张代价较小的表作为驱动表，另一张表作为被驱动表，驱动表的每条记录与被驱动表进行一次连接操作。

需注意的问题：

- 选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。

- 大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。

使用场景：

- 驱动表有很好的过滤条件

- 表连接条件能使用索引

- 结果集比较小

NEST LOOP INNER JOIN2（嵌套循环内连接）

explain select tab1.c1 from tab1 ,tab2 where tab1.c1>tab2.c1;

NEST LOOP LEFT JOIN2（嵌套循环左连接）

explain select tab1.c1 from tab1 left join tab2 on tab1.c2>tab2.c2;

HASH JOIN（哈希连接）

以一张表的连接列为哈希键，构造哈希表，另张表的连接列进行哈希探测，找到满足条件的记录。

HASH JOIN的特点：

 - 一般没索引或用不上索引时会使用该连接方式

 - 选择小的表（或row source）做hash表

 - 只适用等值连接中的情形，非等值一般用NEST LOOP

Hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数：

 HJ_BUF_GLOBAL_SIZE

 HJ_BUF_SIZE

 HJ_BLK_SIZE

HASH2 INNER JOIN（HASH内连接）

explain select tab1.c1,tab2.c2 from tab1 , tab2 where tab1.c1=tab2.c1;

HASH LEFT JOIN2（HASH左外连接）

explain select tab1.c1 from tab1 left  join tab2 on tab1.c1=tab2.c1;

HASH FULL JOIN2（HASH 全外连接）

explain select tab2.c1 from tab1 full outer join tab2 on tab2.c1=tab1.c1 ;

全连接的查询结果是左外连接和右外连接查询结果的并集。

HASH LEFT SEMI MULTIPLE JOIN（多列not in）

explain select * from tab1 where (c1,c2) not in (select c1,c2 from tab2) ;

HASH LEFT SEMI JOIN2(HASH 左半连接)

子查询和非等值连接出现

explain select * from tab1 where c1 not in (select c1 from tab2) and c2 not in (select c2 from tab2);

HASH RIGHT JOIN2(HASH右外连接)

explain select t.c1 ,tab1.c3 from tab1 left join (select * from tab2 where c1=10)t on t.c1=tab1.c2 ;

MERGE JOIN（排序归并连接）

Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。

MERGE SORT的特点：

 - 无驱动表之分，随机读很少

 - 两个表都需要按照连接列排序，需要消耗大量的cpu和额外的内存

应用场景：

通常情况下，merge sort join需要消耗大量的cpu和内存，效率都不会太高。如果存在相关索引可以消除sort，那么CBO可能会考虑该连接方式。

explain select tab1.c1 from tab1,tab2 where tab1.c1=tab2.c1;

达梦技术社区：达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台