Mycat2 BKAJOIN(in)

BKAJOIN在分库分表中间件里又名TableLookUpJoin，它是实现Join的物理算子的一种，主要思想是是使用左表查询得到的值，作为查询右表的条件，去查询右表的数据，然后再进行join运算。相比不使用右表的查询条件，大大减少了查询右表的数据量。

BKAJOIN的右表查询条件为了尽量剪裁分区，一般查询索引实现。但是因为Mycat2是分库分表中间件，查询存储节点的“数据结构”就是SQL，所以BKAJOIN表现为左表算子是任意一种算子，而右表算子是查询SQL。该查询SQL是根据左表的条件值动态生成。因为左表查询的条件值可能会生成很多无效的条件，它还会进行一些表达式化简。因为左表的每一行数据生成一个SQL进行查询，IO次数比较多，所以会进行按批次请求，把多个值组成查询条件，在一个SQL里查询。

形式1： IN表达式

单值

SELECT * FROM table WHERE (column = (1))

批量

SELECT * FROM table WHERE (column IN (1,2,3,4))

ROW IN

SELECT * FROM table WHERE ((column1,column2) IN ((1,2),(3,4)))

样例：

BKAJOIN

SELECT * FROM db1.sharding s INNER JOIN db1.normal e ON s.id = e.id ORDER BY s.id


plan
MycatMemSort(sort0=[$0], dir0=[ASC])
  MycatSQLTableLookup(condition=[=($0, $6)], joinType=[inner], type=[BACK], correlationIds=[[$cor0]], leftKeys=[[0]])
    MycatView(distribution=[[db1.sharding]])
    MycatView(distribution=[[db1.normal]])
Each(targetName=prototype, sql=SELECT * FROM db1.normal AS `normal` WHERE ((`normal`.`id`) IN ($cor0)))
Each(targetName=c0, sql=SELECT * FROM db1_0.sharding_0 AS `sharding` union all SELECT * FROM db1_0.sharding_1 AS `sharding`)
Each(targetName=c1, sql=SELECT * FROM db1_1.sharding_2 AS `sharding` union all SELECT * FROM db1_1.sharding_3 AS `sharding`)


[pool-2-thread-50] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1.normal AS `normal`
WHERE (((((`normal`.`id`) = ('4')) OR ((`normal`.`id`) = ('8'))) OR (((`normal`.`id`) = ('1')) OR ((`normal`.`id`) = ('5')))) OR ((((`normal`.`id`) = ('9')) OR ((`normal`.`id`) = ('2'))) OR (((`normal`.`id`) = ('6')) OR (((`normal`.`id`) = ('3')) OR ((`normal`.`id`) = ('7'))))))

JoinClustering+BKAJOIN

SELECT * FROM db1.sharding s INNER JOIN db1.normal e ON s.id = e.id INNER JOIN db1.global g ON s.id = g.id ORDER BY s.id;

 
plan
MycatMemSort(sort0=[$0], dir0=[ASC])
  MycatProject(id=[$0], user_id=[$1], traveldate=[$2], fee=[$3], days=[$4], blob=[$5], id0=[$9], companyname=[$10], addressid=[$11], id1=[$6], companyname0=[$7], addressid0=[$8])
    MycatSQLTableLookup(condition=[=($0, $9)], joinType=[inner], type=[BACK], correlationIds=[[$cor0]], leftKeys=[[0]])
      MycatView(distribution=[[db1.global, db1.sharding]])
      MycatView(distribution=[[db1.normal]])


Each(targetName=prototype, sql=SELECT * FROM db1.normal AS `normal` WHERE ((`normal`.`id`) IN ($cor0)))
Each(targetName=c0, sql=SELECT * FROM db1_0.sharding_0 AS `sharding`     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT * FROM db1_0.sharding_1 AS `sharding`     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`))
Each(targetName=c1, sql=SELECT * FROM db1_1.sharding_2 AS `sharding`     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT * FROM db1_1.sharding_3 AS `sharding`     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`))


[pool-2-thread-56] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1_1.sharding_2 AS `sharding`
    INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT *
FROM db1_1.sharding_3 AS `sharding`
    INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`)
[pool-2-thread-55] DEBUG io.mycat.datasource.jdbc.datasource.JdbcConnectionManager - get connection:prototypeDs io.mycat.datasource.jdbc.datasource.DefaultConnection@1b0174f1
[pool-2-thread-55] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1.normal AS `normal`
WHERE (((`normal`.`id`) = ('1')) OR (((`normal`.`id`) = ('2')) OR ((`normal`.`id`) = ('3'))))


[pool-2-thread-56] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1_0.sharding_0 AS `sharding`
    INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT *
FROM db1_0.sharding_1 AS `sharding`
    INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`)
[pool-2-thread-56] DEBUG io.mycat.datasource.jdbc.datasource.JdbcConnectionManager - get connection:ds1 io.mycat.datasource.jdbc.datasource.DefaultConnection@dd73d1b
[pool-2-thread-56] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1_1.sharding_2 AS `sharding`
    INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT *
FROM db1_1.sharding_3 AS `sharding`
    INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`)

ROW IN BKAJOIN

SELECT * FROM db1.sharding s INNER JOIN db1.normal e ON s.id = e.id AND s.user_id = e.companyname  ORDER BY s.id


plan
MycatMemSort(sort0=[$0], dir0=[ASC])
  MycatSQLTableLookup(condition=[AND(=($0, $6), =($1, $7))], joinType=[inner], type=[BACK], correlationIds=[[$cor0, $cor1]], leftKeys=[[0, 1]])
    MycatView(distribution=[[db1.sharding]])
    MycatView(distribution=[[db1.normal]])
Each(targetName=prototype, sql=SELECT * FROM db1.normal AS `normal` WHERE ((`normal`.`id`, `normal`.`companyname`) IN ($cor0, $cor1)))
Each(targetName=c0, sql=SELECT * FROM db1_0.sharding_0 AS `sharding` union all SELECT * FROM db1_0.sharding_1 AS `sharding`)
Each(targetName=c1, sql=SELECT * FROM db1_1.sharding_2 AS `sharding` union all SELECT * FROM db1_1.sharding_3 AS `sharding`)


 get connection:ds1 io.mycat.datasource.jdbc.datasource.DefaultConnection@4f201e61
[pool-2-thread-59] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1_1.sharding_2 AS `sharding` union all SELECT *
FROM db1_1.sharding_3 AS `sharding`
[pool-2-thread-58] DEBUG io.mycat.datasource.jdbc.datasource.JdbcConnectionManager - get connection:prototypeDs io.mycat.datasource.jdbc.datasource.DefaultConnection@4aa86a53
[pool-2-thread-58] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
FROM db1.normal AS `normal`
WHERE (((((`normal`.`id`, `normal`.`companyname`) = ('4', NULL)) OR ((`normal`.`id`, `normal`.`companyname`) = ('8', NULL))) OR (((`normal`.`id`, `normal`.`companyname`) = ('1', NULL)) OR ((`normal`.`id`, `normal`.`companyname`) = ('5', NULL)))) OR ((((`normal`.`id`, `normal`.`companyname`) = ('9', NULL)) OR ((`normal`.`id`, `normal`.`companyname`) = ('2', NULL))) OR (((`normal`.`id`, `normal`.`companyname`) = ('6', NULL)) OR (((`normal`.`id`, `normal`.`companyname`) = ('3', NULL)) OR ((`normal`.`id`, `normal`.`companyname`) = ('7', NULL))))))