Mycat continuous sharding-) sharding by date (day)

Mycat continuous sharding-) sharding by date (day)
1. sharding by date (day)

Sharding by date (day): sharding by day from the start date
For example, from, a shard is generated every 10 days.
Note: You must plan and create the shards in advance. Otherwise, the date may exceed the number of shards actually configured.

2. Add a configuration file

Configure in function. xml:

<code class=" hljs xml">    <function name="sharding-by-date" class="org.opencloudb.route.function.PartitionByDate">        <property name="dateFormat">yyyy-MM-dd</property>      <!--{cke_protected}{C}%3C!%2D%2D-%E2%80%94%E6%97%A5%E6%9C%9F%E6%A0%BC%E5%BC%8F%2D%2D%2D%2D%3E-->        <property name="sBeginDate">2016-01-01</property>            <!--{cke_protected}{C}%3C!%2D%2D%E5%BC%80%E5%A7%8B%E6%97%A5%E6%9C%9F%2D%2D%3E-->        <property name="sPartionDay">10</property>  <!--{cke_protected}{C}%3C!%2D%2D%E6%AF%8F%E5%88%86%E7%89%87%E5%A4%A9%E6%95%B0%2D%2D%3E-->    </function>              <tablerule name="sharding-by-date-tr04">                 <rule>                         <columns>CREATE_TIME</columns>                         <algorithm>sharding-by-date</algorithm>                </rule>        </tablerule></code>

Configure in schema. xml:

3. Create a table and input data

 CREATE TABLE ORDER0402(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME); mysql> CREATE TABLE ORDER0402(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 ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_2016-01-03','2016-01-03'); INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13'); mysql> CREATE TABLE ORDER0402(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME); Query OK, 0 rows affected (0.06 sec) mysql> explain INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_2016-01-03','2016-01-03'); +-----------+--------------------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------------------------------------------------------------+ | dn21 | INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_2016-01-03','2016-01-03') | +-----------+--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.11 sec) mysql> INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_2016-01-03','2016-01-03'); Query OK, 1 row affected (0.00 sec) mysql> mysql> explain INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13'); +-----------+----------------------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------------------------------+ | dn22 | INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13') | +-----------+----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13'); Query OK, 1 row affected (0.01 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 22:23:26. 386 DEBUG [$ _ NIOREACTOR-2-RW] (ServerQueryHandler. java: 56)-ServerConnection [id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] insert into ORDER0402 (ID, PROVINCE, SN, CREATE_TIME) VALUES (5000001, 'shanghai', 'shanghai10006 _ 2016 ', '2017-01-13') Start route matching: 02/12 22:23:26. 388 DEBUG [$ _ NIOREACTOR-2-RW] (NonBlockingSession. java: 113)-ServerConnection [id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] insert into ORDER0402 (ID, PROVINCE, SN, CREATE_TIME) VALUES (5000001, 'shanghai', 'shanghai10006 _ 2016 ', '2017-01-13 '), route = {1-> dn22 {insert into ORDER0402 (ID, PROVINCE, SN, CREATE_TIME) VALUES (5000001, 'shanghai', 'shanghai10006 ', '2017-01-13 ')} after the route is matched, the insert data entry operation is directly performed on the corresponding dn22 node: 2016 22:23:26. 403 DEBUG [$ _ NIOREACTOR-1-RW] (NonBlockingSession. java: 229)-release connection MySQLConnection [id = 17, lastTime = 1455287006372, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 90, charset = latin1, txIsolation = 3, autocommit = true, attachment = dn22 {insert into ORDER0402 (ID, PROVINCE, SN, CREATE_TIME) VALUES (5000001, 'shanghai ', 'shanghai10006 _ 2016-01-13 ', '2017-01-13')}, respHandler = SingleNodeHandler [node = dn22 {insert into ORDER0402 (ID, PROVINCE, SN, CREATE_TIME) VALUES (2016, 'shanghai', 'shanghai10006 _ 2016-01-13 ', '2017-01-13')}, packetId = 0], host = 13.23.1.11, port = 2016, statusSync = null, writeQueue = 0, modifiedSQLExecuted = true] after the operation is completed, the connection is released: 02/12 22:23:26. 404 DEBUG [$ _ NIOREACTOR-1-RW] (PhysicalDatasource. java: 403)-release channel MySQLConnection [id = 17, lastTime = 1455287006372, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 90, charset = latin1, txIsolation = 3, autocommit = true, attachment = null, respHandler = null, host = 13.23.1.11, port = 3327, 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 ORDER0402 t1 where t1.CREATE_TIME='2016-01-03'; +-----------+--------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER0402 t1 WHERE t1.CREATE_TIME = '2016-01-03' LIMIT 100 | +-----------+--------------------------------------------------------------------------+ 1 row in set (0.04 sec) mysql> select * from ORDER0402 t1 where t1.CREATE_TIME='2016-01-03'; +-------+----------+--------------------------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +-------+----------+--------------------------+---------------------+ | 10001 | shanghai | shanghai10006_2016-01-03 | 2016-01-03 00:00:00 | +-------+----------+--------------------------+---------------------+ 1 row in set (0.00 sec) mysql> 

View the background mycat log information:

 02/12 22:44:13.773 DEBUG [$_NIOREACTOR-2-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 ORDER0402 t1 where t1.CREATE_TIME='2016-01-03' 02/12 22:44:13.774 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from ORDER0402 t1 where t1.CREATE_TIME='2016-01-03' 02/12 22:44:13.812 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER0402 t1 where t1.CREATE_TIME='2016-01-03' value:select * from ORDER0402 t1 where t1.CREATE_TIME='2016-01-03', route={ 1 -> dn21{SELECT * FROM ORDER0402 t1 WHERE t1.CREATE_TIME = '2016-01-03' LIMIT 100} }

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 ORDER0402 t1 where t1.CREATE_TIME<'2016-01-04'; +-----------+--------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER0402 t1 WHERE t1.CREATE_TIME < '2016-01-04' LIMIT 100 | | dn22 | SELECT * FROM ORDER0402 t1 WHERE t1.CREATE_TIME < '2016-01-04' 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 ORDER0402 t1 where t1.SN='shanghai10006_2016-01-03'; +-----------+-------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+-------------------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER0402 t1 WHERE t1.SN = 'shanghai10006_2016-01-03' LIMIT 100 | | dn22 | SELECT * FROM ORDER0402 t1 WHERE t1.SN = 'shanghai10006_2016-01-03' LIMIT 100 | +-----------+-------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql>