Mycat2 路由控制注釋

本文介紹路由控制注釋

Mycat2里對SQL處理可以分為兩類

1. 路由邏輯
2. SQL查詢器邏輯

1.19實現(xiàn)

多種注釋可以組合使用,但有沖突的項只有一個生效

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

執(zhí)行超時強殺執(zhí)行SQL任務(wù)

/*+ 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.

負(fù)載均衡至主節(jié)點

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

負(fù)載均衡至主從節(jié)點

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

透傳SQL下發(fā)(不經(jīng)過SQL優(yōu)化器處理)

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

參數(shù)為集群名字或數(shù)據(jù)源名字

分布式SQL路由(經(jīng)過SQL優(yōu)化器處理)

當(dāng)語句是UPDATE,DELETE而且涉及分片表的時候(暫不支持,期待高手實現(xiàn))

/*+ MYCAT:scan(table='travelrecord2',condition='id = 2') */ UPDATE  `travelrecord2` SET user_id = 1 where id = 1;
//根據(jù)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;
//根據(jù)travelrecord2.id = 2,計算target,并保留c0,去掉不是c0的target

當(dāng)語句是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)

僅一個目標(biāo)

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)

多個目標(biāo)

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索引,涉及的數(shù)據(jù)源必須是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)...

設(shè)計稿

/*+ MYCAT:scan(table='travelrecord2',condition='id = 2') */select * from db1.travelrecord;
//根據(jù)travelrecord2.id = 2這個條件路由此SQL


/*+ MYCAT:scan(table='travelrecord2',condition='id = 2',target='c0') */select * from db1.travelrecord;
//根據(jù)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;
//根據(jù)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;