2023-05-29 monetdb-多表连接-分析-优快云博客

本文深入探讨MonetDB处理多表连接的性能，分析了其在不同连接操作如merge join和select join中的应用，并通过具体示例展示了执行过程，包括计划分析和trace日志解析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

摘要:

最近正在分析一个列存储引擎处理缓慢的问题，montedb作为一个查询性能顶级的数据库，有必要参考下monetdb是怎么处理的.

本文采取相同的场景, 分析monetdb的处理.

DML:

创建表结构

create table b(b1 int, b2 varchar(2)) ;
 
create table a(a1 int, a2 varchar(2)) ;
 
create table c(c1 int, c2 varchar(2)) ;

插入数据:

insert into a values(1, 'a1');
 
insert into a values(3, 'a3');
 
insert into b values(1, 'b1');
 
insert into b values(2, 'b2');
 
insert into b values(3, 'b3');
 
insert into c values(1, 'c1');
 
insert into c values(2, 'c2');
 
insert into c values(2, 'ce');
 
insert into c values(null, 'c3');

查询SQL

SELECT b.b1,x.c1 FROM b 
LEFT JOIN 
(
    SELECT c1 FROM c INNER JOIN a ON a.a1 = c.c1
) x
ON b.b1 = x.c1;

执行过程分析:

plan分析:

+---------------------------------------------+
| rel                                         |
+=============================================+
| project (                                   |
| | left outer join (                         |
| | | table("sys"."b") [ "b"."b1" ] COUNT ,   |
| | | join (                                  |
| | | | table("sys"."a") [ "a"."a1" ] COUNT , |
| | | | table("sys"."c") [ "c"."c1" ] COUNT   |
| | | ) [ "a"."a1" = "c"."c1" ]               |
| | ) [ "b"."b1" = "c"."c1" ]                 |
| ) [ "b"."b1", "c"."c1" as "x"."c1" ]        |
+---------------------------------------------+

trace分析

+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| usec | statement                                                                                                                                                                              |
+======+========================================================================================================================================================================================+
|    6 | X_0=0@0:void := querylog.define("trace\nselect b.b1,x.c1 from b \nleft join \n(\n select c1 from c inner join a on a.a1 = c.c1\n) x\non b.b1 = x.c1;":str, "default_pipe":str, 47:int) |
:      : ;                                                                                                                                                                                      :
|   29 | X_1=[0]:bat[:int] := bat.new(nil:int);                                                                                                                                                 |
|   34 | X_2=0:int := sql.mvc();                                                                                                                                                                |
|   23 | X_3=[0]:bat[:int] := bat.new(nil:int);                                                                                                                                                 |
|   38 | C_4=[3]:bat[:oid] := sql.tid(X_2=0:int, "sys":str, "b":str);                                                                                                                           |
|  206 | X_5=[2]:bat[:str] := bat.pack("b1":str, "c1":str);                                                                                                                                     |
|  159 | X_6=[2]:bat[:str] := bat.pack("int":str, "int":str);                                                                                                                                   |
|  214 | X_7=[2]:bat[:int] := bat.pack(0:int, 0:int);                                                                                                                                           |
|  299 | X_8=[2]:bat[:int] := bat.pack(32:int, 32:int);                                                                                                                                         |
|  274 | X_9=[2]:bat[:str] := bat.pack(".b":str, ".x":str);                                                                                                                                     |
|   36 | C_10=[2]:bat[:oid] := sql.tid(X_2=0:int, "sys":str, "a":str);                                                                                                                          |
|   97 | C_11=[4]:bat[:oid] := sql.tid(X_2=0:int, "sys":str, "c":str);                                                                                                                          |
|  174 | X_12=[2]:bat[:int] := sql.bind(X_2=0:int, "sys":str, "a":str, "a1":str, 0:int);                                                                                                        |
|  176 | X_13=[3]:bat[:int] := sql.bind(X_2=0:int, "sys":str, "b":str, "b1":str, 0:int);                                                                                                        |
|   20 | X_14=[2]:bat[:int] := algebra.projection(C_10=[2]:bat[:oid], X_12=[2]:bat[:int]);                                                                                                      |
|   60 | X_15=[4]:bat[:int] := sql.bind(X_2=0:int, "sys":str, "c":str, "c1":str, 0:int);                                                                                                        |
|   79 | X_16=[4]:bat[:int] := algebra.projection(C_11=[4]:bat[:oid], X_15=[4]:bat[:int]);                                                                                                      |
|   98 | X_17=[3]:bat[:int] := algebra.projection(C_4=[3]:bat[:oid], X_13=[3]:bat[:int]);                                                                                                       |
|   25 | C_18=[3]:bat[:oid] := bat.mirror(X_17=[3]:bat[:int]);                                                                                                                                  |
|  133 | X_19=[1]:bat[:oid] := algebra.join(X_16=[4]:bat[:int], X_14=[2]:bat[:int], nil:BAT, nil:BAT, false:bit, nil:lng); # hashjoin using existing hash (swapped)                             |
|   19 | X_20=[1]:bat[:int] := algebra.projection(X_19=[1]:bat[:oid], X_16=[4]:bat[:int]);                                                                                                      |
|    5 | X_21=0@0:void := language.pass(X_16=[4]:bat[:int]);                                                                                                                                    |
|   89 | (X_22=[1]:bat[:oid], X_23=[1]:bat[:oid]) := algebra.join(X_17=[3]:bat[:int], X_20=[1]:bat[:int], nil:BAT, nil:BAT, false:bit, nil:lng); # selectjoin; select: sorted                   |
|   28 | C_24=[2]:bat[:oid] := algebra.difference(C_18=[3]:bat[:oid], X_22=[1]:bat[:oid], nil:BAT, nil:BAT, false:bit, false:bit, nil:lng); # leftjoin; mergejoin_void; select: dense           |
|   28 | X_25=[1]:bat[:int] := algebra.projection(X_23=[1]:bat[:oid], X_20=[1]:bat[:int]);                                                                                                      |
|   32 | X_26=0@0:void := language.pass(X_20=[1]:bat[:int]);                                                                                                                                    |
|   48 | X_27=[2]:bat[:int] := algebra.projection(C_24=[2]:bat[:oid], X_17=[3]:bat[:int]);                                                                                                      |
|   99 | X_28=[1]:bat[:int] := algebra.projection(X_22=[1]:bat[:oid], X_17=[3]:bat[:int]);                                                                                                      |
|    7 | X_30=0@0:void := language.pass(X_22=[1]:bat[:oid]);                                                                                                                                    |
|   30 | X_29=[2]:bat[:int] := algebra.project(C_24=[2]:bat[:oid], nil:int);                                                                                                                    |
|   24 | X_32=[1]:bat[:int] := bat.append(X_1=[1]:bat[:int], X_28=[1]:bat[:int], true:bit);