Mycat2 路由控制注释

本文介绍路由控制注释

Mycat2里对SQL处理可以分为两类

1. 路由逻辑
2. SQL查询器逻辑

1.19实现

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

/*+ 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优化器处理)

当语句是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;