Mycat2 BKAJOIN(in)

BKAJOIN在分庫分表中間件里又名TableLookUpJoin，它是實現(xiàn)Join的物理算子的一種，主要思想是是使用左表查詢得到的值，作為查詢右表的條件，去查詢右表的數(shù)據(jù)，然后再進(jìn)行join運(yùn)算。相比不使用右表的查詢條件，大大減少了查詢右表的數(shù)據(jù)量。

BKAJOIN的右表查詢條件為了盡量剪裁分區(qū)，一般查詢索引實現(xiàn)。但是因為Mycat2是分庫分表中間件，查詢存儲節(jié)點的“數(shù)據(jù)結(jié)構(gòu)”就是SQL，所以BKAJOIN表現(xiàn)為左表算子是任意一種算子，而右表算子是查詢SQL。該查詢SQL是根據(jù)左表的條件值動態(tài)生成。因為左表查詢的條件值可能會生成很多無效的條件，它還會進(jìn)行一些表達(dá)式化簡。因為左表的每一行數(shù)據(jù)生成一個SQL進(jìn)行查詢，IO次數(shù)比較多，所以會進(jìn)行按批次請求，把多個值組成查詢條件，在一個SQL里查詢。

形式1： IN表達(dá)式

單值

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))))))