Mycat continuous sharding-) custom number range sharding

Mycat continuous sharding-) custom number range sharding
1. Custom number range Partition

You can customize the number range sharding and plan in advance which part of a range belongs to the sharding field. For example, you can partition the first million data on the first node, the second million data is sliced on the second node, and so on.

2. Add a configuration file

Configure in function. xml:

<code class=" hljs xml">    <!--{cke_protected}{C}%3C!%2D%2D%20%E5%AF%B9%E8%87%AA%E5%AE%9A%E4%B9%89%E6%95%B0%E5%AD%97%E5%88%86%E7%89%87%E8%A7%84%E5%88%99rang-long-tr04%E7%9A%84%E5%AE%9A%E4%B9%89%20%2D%2D%3E-->        <function name="rang-long-04" class="org.opencloudb.route.function.AutoPartitionByLong">                <property name="mapFile">autopartition-long04.txt</property>                <property name="defaultNode">0</property>                 <property name="type">0</property>        </function>        <!--{cke_protected}{C}%3C!%2D%2D%20%E5%AF%B9%E8%A1%A8%E8%BF%9B%E8%A1%8C%E5%88%86%E7%89%87%E7%9A%84%E6%8F%8F%E8%BF%B0%EF%BC%8C%E5%AD%97%E6%AE%B5%E4%BB%A5%E5%8F%8A%E5%88%86%E7%89%87%E8%A7%84%E5%88%99%20%2D%2D%3E-->        <tablerule name="rang-long-tr04">                 <rule>                         <columns>ID</columns>                          <algorithm>rang-long-04</algorithm>                </rule>        </tablerule></code>

Configure in schema. xml:

Autopartition-long04.txt file Configuration:

 [root@crm_idc_squid_1_11 conf]# more autopartition-long04.txt 0-500M=0 500M-1000M=1 1000M-1500M=2 You have mail in /var/spool/mail/root [root@crm_idc_squid_1_11 conf]# 

Note: This configuration is very simple, that is, a possible id range is pre-defined to a shard. All node configurations start from 0 and 0 represents Node 1.

3. Create a table and input data

 CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);mysql> CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()); INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW()); mysql> explain INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()); +-----------+--------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------------------------------------------------+ | dn21 | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()) | +-----------+--------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW()); Query OK, 1 row affected (0.00 sec) mysql> 

4. Analyze the insert operation process. Refer to mycat. log for a complete analysis of the routing process.

View the background mycat. log analysis as follows:

Start to get the connection: 02/12 21:17:13. 614 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 ORDER0401 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10001, 'shanghai', 'shanghai10006 _ 100', NOW () start to create a cache: 02/12 21:17:13. 615 DEBUG [$ _ NIOREACTOR-1-RW] (RouterUtil. java: 951)-try to find cache by primary key 02/12 21:17:13. 616 INFO [$ _ NIOREACTOR-1-RW] (DefaultLayedCachePool. java: 80)-create child Cache: TESTDB_ORDER0401 for layered cache TableID2DataNodeCache, size 10000, expire seconds 18000 02/12 21:17:13. 616 DEBUG [$ _ NIOREACTOR-1-RW] (CacheManager. java: 794)-Attempting to create an existing singleton. existing singleton returned. 02/12 21:17:13. 616 DEBUG [$ _ NIOREACTOR-1-RW] (Cache. java: 955)-No BootstrapCacheLoaderFactory class specified. skipping... 02/12 21:17:13. 617 DEBUG [$ _ NIOREACTOR-1-RW] (Cache. java: 929)-CacheWriter factory not configured. skipping... 02/12 21:17:13. 618 DEBUG [$ _ NIOREACTOR-1-RW] (MemoryStore. java: 153)-Initialized net. sf. ehcache. store. notifyingMemoryStore for TableID2DataNodeCache. TESTDB_ORDER0401 02/12 21:17:13. 619 DEBUG [$ _ NIOREACTOR-1-RW] (Cache. java: 1165)-Initialised cache: TableID2DataNodeCache. TESTDB_ORDER0401 start route match: 02/12 21:17:13. 621 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 ORDER0401 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10001, 'shanghai', 'shanghai10006 _ 100', NOW ()), route = {1-> dn21 {insert into ORDER0401 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10001, 'shanghai', 'shanghai10006 _ 100', NOW ())}} after the rrs matches the route, the insert data entry operation is directly performed on the corresponding dn21 node: 02/12 21:17:13. 624 DEBUG [$ _ NIOREACTOR-0-RW] (NonBlockingSession. java: 229)-release connection MySQLConnection [id = 8, lastTime = 1455283033608, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 52, charset = latin1, txIsolation = 3, autocommit = true, attachment = dn21 {insert into ORDER0401 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10001, 'shanghai ', 'shanghai10006 _ 10000 ', NOW ()}, respHandler = SingleNodeHandler [node = dn21 {insert into ORDER0401 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10001, 'shanghai ', 'shanghai10006 _ 000000', NOW ()}, packetId = 0], host = 13.217.1.11, port = 10000, statusSync = null, writeQueue = 0, modifiedSQLExecuted = true] connection release: 02/12 21:17:13. 624 DEBUG [$ _ NIOREACTOR-0-RW] (PhysicalDatasource. java: 403)-release channel MySQLConnection [id = 8, lastTime = 1455283033608, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 52, charset = latin1, txIsolation = 3, autocommit = true, attachment = null, respHandler = null, host = 13.217.1.11, port = 3317, statusSync = null, writeQueue = 0, modifiedSQLExecuted = false]

5. Execute a simple query. If the shard field ID field is specified, query a single shard node through the shard.

Result of mycat command:

 mysql> explain select * from ORDER0401 t1 where t1.ID=5000001; +-----------+------------------------------------------------+ | DATA_NODE | SQL | +-----------+------------------------------------------------+ | dn22 | select * from ORDER0401 t1 where t1.ID=5000001 | +-----------+------------------------------------------------+ 1 row in set (0.08 sec) mysql> select * from ORDER0401 t1 where t1.ID=5000001; +---------+----------+-----------------------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +---------+----------+-----------------------+---------------------+ | 5000001 | shanghai | shanghai10006_5000001 | 2016-02-12 21:18:17 | +---------+----------+-----------------------+---------------------+ 1 row in set (0.00 sec) mysql> 

View the background mycat log information:

 02/12 21:44:01.116 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]select * from ORDER0401 t1 where t1.ID=5000001 02/12 21:44:01.117 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from ORDER0401 t1 where t1.ID=5000001 02/12 21:44:01.118 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key 02/12 21:44:01.118 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:5000001 02/12 21:44:01.118 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]select * from ORDER0401 t1 where t1.ID=5000001, route={ 1 -> dn22{select * from ORDER0401 t1 where t1.ID=5000001} } rrs 02/12 21:44:01.119 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2 02/12 21:44:01.120 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=17, lastTime=1455284641106, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=59, charset=latin1, txIsolation=3, autocommit=true, attachment=dn22{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn22{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=7], host=13.217.1.11, port=3327, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@6d1ef03, writeQueue=0, modifiedSQLExecuted=false] 02/12 21:44:01.121 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=17, lastTime=1455284641106, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=59, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.217.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

6. If the range of shard fields is queried, all nodes are used for retrieval. Even if only one piece of data is located on one Shard, the route also uses all shards for retrieval and query.

 mysql> explain select * from ORDER0401 t1 where t1.ID<50000; +-----------+----------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------+ | dn21 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 | | dn22 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 | +-----------+----------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> 

7. If you do not need to query the shard field, even a single data, you must route all shards and query them using all shards.

 mysql> explain select * from ORDER0401 t1 where t1.SN='shanghai10006_10000'; +-----------+--------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 | | dn22 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 | +-----------+--------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>