此规则是在运行阶段有应用自主决定路由到那个分片。
此方法为直接依据字符子串(必须是数字)计算分区号(由应用传递參数。显式指定分区号)。
在 function.xml 里面进行配置:
- <function name="sharding-by-substring-040302" class="org.opencloudb.route.function.PartitionDirectBySubString">
- <property name="startIndex">
- 0
- </property>
- <property name="size">
- 1
- </property>
- <property name="partitionCount">
- 8
- </property>
- <property name="defaultPartition">
- 0
- </property>
- </function>
- 比如id=0-100000020在此配置中代表依据id中从startIndex=0,開始,截取siz=1位数字即0,0就是获取的分区,假设没传默认分配到defaultPartition
- <tableRule name="tr-sharding-substr-mc040302">
- <rule>
- <columns>
- CODE
- </columns>
- <algorithm>
- sharding-by-substring-040302
- </algorithm>
- </rule>
- </tableRule>
在 schema.xml 里面进行配置:
- <table name="ORDER040302" primaryKey="ID" dataNode="dn21,dn22" rule="tr-sharding-substr-mc040302"
- />
- CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, CODE VARCHAR(64), SN VARCHAR(64), CREATE_TIME DATETIME);
- mysql > CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, CODE VARCHAR(64), SN VARCHAR(64), CREATE_TIME DATETIME);
- Query OK,
- 0 rows affected(0.05 sec)
- mysql > INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(1, '0 - 10000020', 'beijing_03 - 10000020', NOW());
- INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW());
- mysql > explain INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(1, '0 - 10000020', 'beijing_03 - 10000020', NOW()); + -----------+----------------------------------------------------------------------------------------------------+|DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------------+|dn21 | INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(1, '0 - 10000020', 'beijing_03 - 10000020', NOW()) | +-----------+----------------------------------------------------------------------------------------------------+1 row in set(0.00 sec)
- mysql > mysql > INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(1, '0 - 10000020', 'beijing_03 - 10000020', NOW());
- Query OK,
- 1 row affected(0.01 sec)
- mysql > mysql > explain INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW()); + -----------+-----------------------------------------------------------------------------------------------------+|DATA_NODE | SQL | +-----------+-----------------------------------------------------------------------------------------------------+|dn22 | INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW()) | +-----------+-----------------------------------------------------------------------------------------------------+1 row in set(0.00 sec)
- mysql > INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW());
- Query OK,
- 1 row affected(0.00 sec)
- mysql >
查看后台 mycat.log 分析例如以下:
- 開始获取连接:02 / 13 00 : 40 : 18.173 DEBUG[$_NIOREACTOR - 1 - RW](ServerQueryHandler.java: 56) - ServerConnection[id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW())
- 開始路由匹配:02 / 13 00 : 40 : 18.175 DEBUG[$_NIOREACTOR - 1 - RW](NonBlockingSession.java: 113) - ServerConnection[id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW()),
- route = {
- 1 - >dn22 {
- INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW())
- }
- 匹配好路由后,直接往相应的dn21节点開始运行insert数据录入操作:02 / 13 00 : 40 : 18.180 DEBUG[$_NIOREACTOR - 1 - RW](NonBlockingSession.java: 229) - release connection MySQLConnection[id = 17, lastTime = 1455295218162, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 199, charset = latin1, txIsolation = 3, autocommit = true, attachment = dn22 {
- INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW())
- },
- respHandler = SingleNodeHandler[node = dn22 {
- INSERT INTO ORDER040302(ID, CODE, SN, CREATE_TIME) VALUES(2, '1 - 10000020', 'shanghai_10 - 10000020', NOW())
- },
- packetId = 0], host = 10.254.1.11, port = 3327, statusSync = null, writeQueue = 0, modifiedSQLExecuted = true]
- 连接释放:02 / 13 00 : 40 : 18.180 DEBUG[$_NIOREACTOR - 1 - RW](PhysicalDatasource.java: 403) - release channel MySQLConnection[id = 17, lastTime = 1455295218162, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 199, charset = latin1, txIsolation = 3, autocommit = true, attachment = null, respHandler = null, host = 10.254.1.11, port = 3327, statusSync = null, writeQueue = 0, modifiedSQLExecuted = false] 02 / 13 00 : 40 : 24.390 DEBUG[Timer1](SQLJob.java: 85) - con query s
mycat 命令台显示结果:
- mysql > explain select * from ORDER040302 t1 where CODE = '0 - 10000020'; + -----------+------------------------------------------------------------------+|DATA_NODE | SQL | +-----------+------------------------------------------------------------------+|dn21 | SELECT * FROM ORDER040302 t1 WHERE CODE = '0 - 10000020'LIMIT 100 | +-----------+------------------------------------------------------------------+1 row in set(0.00 sec)
- mysql > select * from ORDER040302 t1 where CODE = '0 - 10000020'; + ----+------------+---------------------+---------------------+|ID | CODE | SN | CREATE_TIME | +----+------------+---------------------+---------------------+|1 | 0 - 10000020 | beijing_03 - 10000020 | 2016 - 02 - 13 00 : 40 : 27 | +----+------------+---------------------+---------------------+1 row in set(0.01 sec)
- mysql >
查看后台 mycat 日志信息:
- 02 / 13 00 : 44 : 46.717 DEBUG[$_NIOREACTOR - 1 - RW](ServerQueryHandler.java: 56) - ServerConnection[id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] explain select * from ORDER040302 t1 where CODE = '0 - 10000020'02 / 13 00 : 44 : 46.718 DEBUG[$_NIOREACTOR - 1 - RW](EnchachePool.java: 76) - SQLRouteCache miss cache,
- key: TESTDB select * from ORDER040302 t1 where CODE = '0 - 10000020'02 / 13 00 : 44 : 46.719 DEBUG[$_NIOREACTOR - 1 - RW](EnchachePool.java: 59) - SQLRouteCache add cache,
- key: TESTDB select * from ORDER040302 t1 where CODE = '0 - 10000020'value: select * from ORDER040302 t1 where CODE = '0 - 10000020',
- route = {
- 1 - >dn21 {
- SELECT * FROM ORDER040302 t1 WHERE CODE = '0 - 10000020'LIMIT 100
- }
- }
- mysql > explain select * from ORDER040302 t1 where CODE likt'0 - 1 % '; + -----------+---------------------------------------------------+|DATA_NODE | SQL | +-----------+---------------------------------------------------+|dn21 | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 | |dn22 | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 | +-----------+---------------------------------------------------+2 rows in set(0.00 sec)
- mysql >
- mysql > explain select * from ORDER040302 t1 where SN = 'beijing_03 - 10000020'; + -----------+-------------------------------------------------------------------------+|DATA_NODE | SQL | +-----------+-------------------------------------------------------------------------+|dn21 | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03 - 10000020'LIMIT 100 | |dn22 | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03 - 10000020'LIMIT 100 | +-----------+-------------------------------------------------------------------------+2 rows in set(0.00 sec)
- mysql >
来源: http://www.bubuko.com/infodetail-2171176.html