Mycat2路由控制注释

本文介绍路由控制注释
Mycat2里对SQL处理可以分为两类
1.路由逻辑
2.SQL查询器逻辑

需要注意的是注释中的condition属性只能写简单条件

/*+ MYCAT:scan(table='t,c',condition='t.id = 2 and c.id = 3',target='c0') */
不支持or表达式,只支持and 与分片键相关的字段

v1.20-2021-11-21
实现mycat:schema注释

/*+mycat:schema=ds2*/是语句作用的注释,实现不通过use schema临时修改当前的schema
select DATABASE();//mysql库
/*+mycat:schema=ds2*/select DATABASE()");//ds2
select DATABASE();//恢复到mysql库

多种注释可以组合使用,但有冲突的项只有一个生效

/*+ MYCAT:EXECUTE_TIMEOUT(time) MASTER() */  select * from db1.travelrecord;

执行超时强杀执行SQL任务

/*+ MYCAT:EXECUTE_TIMEOUT(time) */ select sleep(5)

time 单位:毫秒 MILLISECONDS

/*+MYCAT:EXECUTE_TIMEOUT(1)*/ select sleep(100)
java.sql.SQLException:  HY000java.util.concurrent.TimeoutException: The source did not signal an event for 1 milliseconds and has been terminated.

负载均衡至主节点

/*+ MYCAT:MASTER() */ select * from db1.travelrecord;

负载均衡至从节点

/*+ MYCAT:SLAVE() */ select * from db1.travelrecord;

透传SQL下发(不经过SQL优化器处理)

/*+ MYCAT:TARGET(c0) */ select * from db1.travelrecord;
/*+ MYCAT:TARGET(c1,c2) */ select * from db1.travelrecord;//自动结果集合拼(union all)

参数为集群名字或数据源名字,支持所有sql语句

分布式SQL路由(经过SQL优化器处理)

支持select语句,当语句是UPDATE,DELETE而且涉及分片表的时候,暂不支持,期待高手实现

/*+ MYCAT:scan(table='travelrecord2',condition='id = 2') */ UPDATE  `travelrecord2` SET user_id = 1 where id = 1;
//根据travelrecord2.id = 2这个条件路由此SQL
/*+ MYCAT:scan(table='travelrecord2',datanode='c0_db1_travelrecord3') */ UPDATE  `travelrecord2` SET user_id = 1 where id = 1;
//把逻辑表修改为db1.travelrecord3并路由至c0
/*+ MYCAT:scan(table='travelrecord2',condition='id = 2',target='c0') */ UPDATE  `travelrecord2` SET user_id = 1 where id = 1;
//根据travelrecord2.id = 2,计算target,并保留c0,去掉不是c0的target

当语句是SELECT而且涉及分片表的时候(支持)

全表扫描

explain select /*+MYCAT:scan()*/  * from db1.travelrecord
plan
MycatView(distribution=[[db1.travelrecord]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 union all SELECT * FROM db1_0.travelrecord_1)
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_2 union all SELECT * FROM db1_1.travelrecord_3)

仅一个目标

explain select /*+MYCAT:scan(TARGET='c0')*/  * from db1.travelrecord
plan
MycatView(distribution=[[db1.travelrecord]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 union all SELECT * FROM db1_0.travelrecord_1)

多个目标

plan
MycatView(distribution=[[db1.travelrecord]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 union all SELECT * FROM db1_0.travelrecord_1)
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_2 union all SELECT * FROM db1_1.travelrecord_3)

仅一个分片表使用条件路由

explain select /*+MYCAT:scan(TABLE='t1', condition='t1.id = 2')*/  * from db1.travelrecord t1

plan
MycatView(distribution=[[db1.travelrecord]])
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_2)

仅一个分片表指定映射的物理表

explain select /*+MYCAT:scan(TABLE='t1', PARTITION=('c0_db1_travelrecord6'))*/  * from db1.travelrecord t1

plan
MycatView(distribution=[[db1.travelrecord]])
Each(targetName=c0, sql=SELECT * FROM db1.travelrecord6)

多个分片表使用条件

explain select /*+MYCAT:scan(TABLE='t1,t2', condition='t1.id = 2 and t2.id = 2')*/  * from db1.travelrecord t1 join db1.travelrecord2 t2 on t1.id = t2.id


plan
MycatView(distribution=[[db1.travelrecord, db1.travelrecord2]])
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_2     INNER JOIN db1_1.travelrecord2_2 ON (`db1_travelrecord`.`id` = `db1_travelrecord2`.`id`) union all SELECT * FROM db1_1.travelrecord_2     INNER JOIN db1_1.travelrecord2_2 ON (`db1_travelrecord`.`id` = `db1_travelrecord2`.`id`))

多个分片表指定映射的物理表

explain select /*+MYCAT:scan(TABLE='t1,t2', PARTITION=('c0_db1_travelrecord,c0_db1_travelrecord_5','c0_db1_travelrecord2,c0_db1_travelrecord2_0')) */  * from db1.travelrecord t1 join db1.travelrecord2 t2 on t1.id = t2.id


plan
MycatView(distribution=[[db1.travelrecord, db1.travelrecord2]])
Each(targetName=c0, sql=SELECT * FROM db1.travelrecord     INNER JOIN db1.travelrecord2 ON (`db1_travelrecord`.`id` = `db1_travelrecord2`.`id`) union all SELECT * FROM db1.travelrecord_5     INNER JOIN db1.travelrecord2_0 ON (`db1_travelrecord`.`id` = `db1_travelrecord2`.`id`))

MySQL Index索引,涉及的数据源必须是mysql类型(实验)
FORCE INDEX只能写在表名后面,不能写在语句尾部

explain select * from db1.travelrecord  FORCE INDEX(haha)

plan
MycatView(distribution=[[db1.travelrecord]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 USE INDEX(haha)...

设计稿

/*+ MYCAT:scan(table='travelrecord2',condition='id = 2') */select * from db1.travelrecord;
//根据travelrecord2.id = 2这个条件路由此SQL

/*+ MYCAT:scan(table='travelrecord2',condition='id = 2',target='c0') */select * from db1.travelrecord;
//根据travelrecord2.id = 2,计算target,并保留c0,去掉不是c0的target

/*+ MYCAT:scan(table='t,c',condition='t.id = 2 and c.id = 3',target='c0') */select * from db1.travelrecord t,db1.company c;
//根据travelrecord2.id = 2 and db1.company = 3,计算target,并保留c0,去掉不是c0的target


待完成工作,基于全局物理表下表的路由,而非基于TARGET或者基于条件

样例:
/*+ MYCAT:scan(partitionIndex='0') */select * from db1.travelrecord;
=>
select * from db1_0.travelrecord_0;

/*+ MYCAT:scan(partitionIndex='1') */select * from db1.travelrecord;
=>
select * from db1_0.travelrecord_1;