BKAJOIN在分库分表中间件里又名TableLookUpJoin，它是实现Join的物理算子的一种，主要思想是是使用左表查询得到的值，作为查询右表的条件，去查询右表的数据，然后再进行join运算。相比不使用右表的查询条件，大大减少了查询右表的数据量。
BKAJOIN的右表查询条件为了尽量剪裁分区，一般查询索引实现。但是因为Mycat2是分库分表中间件，查询存储节点的“数据结构”就是SQL，所以BKAJOIN表现为左表算子是任意一种算子，而右表算子是查询SQL。该查询SQL是根据左表的条件值动态生成。因为左表查询的条件值可能会生成很多无效的条件，它还会进行一些表达式化简。因为左表的每一行数据生成一个SQL进行查询，IO次数比较多，所以会进行按批次请求，把多个值组成查询条件，在一个SQL里查询。

需使用注释开启

  1. /*+ mycat:use_bka_join() */ select * from db1.sharding s inner join db1.normal e on s.id = e.id order by s.id;
  2. /*+ mycat:use_bka_join(s,e) */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

形式： 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

  1. SELECT * FROM db1.sharding s INNER JOIN db1.normal e ON s.id = e.id ORDER BY s.id
  4. plan
  5. MycatMemSort(sort0=[$0], dir0=[ASC])
  6.   MycatSQLTableLookup(condition=[=($0, $6)], joinType=[inner], type=[BACK], correlationIds=[[$cor0]], leftKeys=[[0]])
  7.     MycatView(distribution=[[db1.sharding]])
  8.     MycatView(distribution=[[db1.normal]])
  9. Each(targetName=prototype, sql=SELECT * FROM db1.normal AS `normal` WHERE ((`normal`.`id`) IN ($cor0)))
  10. Each(targetName=c0, sql=SELECT * FROM db1_0.sharding_0 AS `sharding` union all SELECT * FROM db1_0.sharding_1 AS `sharding`)
  11. Each(targetName=c1, sql=SELECT * FROM db1_1.sharding_2 AS `sharding` union all SELECT * FROM db1_1.sharding_3 AS `sharding`)
  13. [pool-2-thread-50] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  14. FROM db1.normal AS `normal`
  15. 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

  1. 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;
  4. plan
  5. MycatMemSort(sort0=[$0], dir0=[ASC])
  6.   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])
  7.     MycatSQLTableLookup(condition=[=($0, $9)], joinType=[inner], type=[BACK], correlationIds=[[$cor0]], leftKeys=[[0]])
  8.       MycatView(distribution=[[db1.global, db1.sharding]])
  9.       MycatView(distribution=[[db1.normal]])
  11. Each(targetName=prototype, sql=SELECT * FROM db1.normal AS `normal` WHERE ((`normal`.`id`) IN ($cor0)))
  12. 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`))
  13. 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`))
  15. [pool-2-thread-56] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  16. FROM db1_1.sharding_2 AS `sharding`
  17.     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT *
  18. FROM db1_1.sharding_3 AS `sharding`
  19.     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`)
  20. [pool-2-thread-55] DEBUG io.mycat.datasource.jdbc.datasource.JdbcConnectionManager - get connection:prototypeDs io.mycat.datasource.jdbc.datasource.DefaultConnection@1b0174f1
  21. [pool-2-thread-55] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  22. FROM db1.normal AS `normal`
  23. WHERE (((`normal`.`id`) = ('1')) OR (((`normal`.`id`) = ('2')) OR ((`normal`.`id`) = ('3'))))
  25. [pool-2-thread-56] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  26. FROM db1_0.sharding_0 AS `sharding`
  27.     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT *
  28. FROM db1_0.sharding_1 AS `sharding`
  29.     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`)
  30. [pool-2-thread-56] DEBUG io.mycat.datasource.jdbc.datasource.JdbcConnectionManager - get connection:ds1 io.mycat.datasource.jdbc.datasource.DefaultConnection@dd73d1b
  31. [pool-2-thread-56] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  32. FROM db1_1.sharding_2 AS `sharding`
  33.     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`) union all SELECT *
  34. FROM db1_1.sharding_3 AS `sharding`
  35.     INNER JOIN db1.global AS `global` ON (`sharding`.`id` = `global`.`id`)


ROW IN BKAJOIN

  1. 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
  3. plan
  4. MycatMemSort(sort0=[$0], dir0=[ASC])
  5.   MycatSQLTableLookup(condition=[AND(=($0, $6), =($1, $7))], joinType=[inner], type=[BACK], correlationIds=[[$cor0, $cor1]], leftKeys=[[0, 1]])
  6.     MycatView(distribution=[[db1.sharding]])
  7.     MycatView(distribution=[[db1.normal]])
  8. Each(targetName=prototype, sql=SELECT * FROM db1.normal AS `normal` WHERE ((`normal`.`id`, `normal`.`companyname`) IN ($cor0, $cor1)))
  9. Each(targetName=c0, sql=SELECT * FROM db1_0.sharding_0 AS `sharding` union all SELECT * FROM db1_0.sharding_1 AS `sharding`)
  10. Each(targetName=c1, sql=SELECT * FROM db1_1.sharding_2 AS `sharding` union all SELECT * FROM db1_1.sharding_3 AS `sharding`)
  12.  get connection:ds1 io.mycat.datasource.jdbc.datasource.DefaultConnection@4f201e61
  13. [pool-2-thread-59] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  14. FROM db1_1.sharding_2 AS `sharding` union all SELECT *
  15. FROM db1_1.sharding_3 AS `sharding`
  16. [pool-2-thread-58] DEBUG io.mycat.datasource.jdbc.datasource.JdbcConnectionManager - get connection:prototypeDs io.mycat.datasource.jdbc.datasource.DefaultConnection@4aa86a53
  17. [pool-2-thread-58] DEBUG io.mycat.vertxmycat.MycatVertxPreparedStatement - SELECT *
  18. FROM db1.normal AS `normal`
  19. 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))))))