Mycat discrete parts-) Enumeration parts

Mycat discrete parts-) Enumeration parts
1. Enumeration parts

Enumeration sharding: You can configure the shard by configuring the possible enumeration id in the configuration file. This rule applies to specific scenarios. For example, some businesses need to be saved by province or district/county, the province, county, and district are fixed

2. Add a configuration file

Configure in function. xml:

<code class=" hljs xml">    <!--{cke_protected}{C}%3C!%2D%2D%20%0A%20%20%20%20mapFile%E6%A0%87%E8%AF%86%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6%E5%90%8D%E7%A7%B0%0A%20%20%20%20type%E9%BB%98%E8%AE%A4%E5%80%BC%E4%B8%BA0%EF%BC%880%E8%A1%A8%E7%A4%BAInteger%EF%BC%8C%E9%9D%9E%E9%9B%B6%E8%A1%A8%E7%A4%BAString%EF%BC%89%0A%20%20%20%20%E9%BB%98%E8%AE%A4%E8%8A%82%E7%82%B9%E7%9A%84%E4%BD%9C%E7%94%A8%EF%BC%9A%E6%9E%9A%E4%B8%BE%E5%88%86%E7%89%87%E6%97%B6%EF%BC%8C%E5%A6%82%E6%9E%9C%E7%A2%B0%E5%88%B0%E4%B8%8D%E8%AF%86%E5%88%AB%E7%9A%84%E6%9E%9A%E4%B8%BE%E5%80%BC%EF%BC%8C%E5%B0%B1%E8%AE%A9%E5%AE%83%E8%B7%AF%E7%94%B1%E5%88%B0%E9%BB%98%E8%AE%A4%E8%8A%82%E7%82%B9%20%20%20%20%0A%20%20%20%20%2D%2D%3E-->        <function name="hash-int-mc040301" class="org.opencloudb.route.function.PartitionByFileMap">                <property name="mapFile">partition-hash-int.txt</property>                 <property name="defaultNode">0</property>                 <property name="type">0</property>        </function>        <tablerule name="tr-hash-int-mc040301">                 <rule>                         <columns>PROVINCE</columns>                          <algorithm>hash-int-mc040301</algorithm>                </rule>        </tablerule></code>

Configure in schema. xml:

Autopartition-long04.txt file configuration, 10000 represents the Beijing part to the first node, 10010 represents the Shanghai part to the second node

 [root@crm_1_21 conf]# more partition-hash-int04.txt 10000=0 10010=1 [root@crm_1_21 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 ORDER040301(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE INT,SN VARCHAR(64),CREATE_TIME DATETIME); mysql> CREATE TABLE ORDER040301(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE INT,SN VARCHAR(64),CREATE_TIME DATETIME); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW()); INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW()); mysql> explain INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW()); +-----------+-------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+-------------------------------------------------------------------------------------------------+ | dn21 | INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW()) | +-----------+-------------------------------------------------------------------------------------------------+ 1 row in set (0.12 sec) mysql> INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW()); Query OK, 1 row affected (0.02 sec) mysql> mysql> explain INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW()); +-----------+--------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------------------------------------+ | dn22 | INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW()) | +-----------+--------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',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 23:32:11. 323 DEBUG [$ _ NIOREACTOR-2-RW] (ServerQueryHandler. java: 56)-ServerConnection [id = 2, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] insert into ORDER040301 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10010, 10010, 'shanghai _ 100', NOW () Start route match: 02/12 23:32:11. 324 DEBUG [$ _ NIOREACTOR-2-RW] (NonBlockingSession. java: 113)-ServerConnection [id = 2, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] insert into ORDER040301 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10010, 'shanghai_100', NOW ()), route = {1-> dn22 {insert into ORDER040301 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10010, 'shanghai_100', NOW ())}} after the rrs matches the route, the insert data entry operation is directly performed on the corresponding dn21 node: 02/12 23:32:11. 327 DEBUG [$ _ NIOREACTOR-0-RW] (NonBlockingSession. java: 229)-release connection MySQLConnection [id = 16, lastTime = 1455291131321, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 153, charset = latin1, txIsolation = 3, autocommit = true, attachment = dn22 {insert into ORDER040301 (ID, PROVINCE, SN, CREATE_TIME) VALUES, 'shanghai _ 100', NOW ()}, respHandler = SingleNodeHandler [node = dn22 {insert into ORDER040301 (ID, PROVINCE, SN, CREATE_TIME) VALUES (10010, 'shanghai_000000', NOW ()}, packetId = 0], host = 13.123.1.11, port = 10010, statusSync = null, writeQueue = 0, modifiedSQLExecuted = true] connection release: 02/12 23:32:11. 328 DEBUG [$ _ NIOREACTOR-0-RW] (PhysicalDatasource. java: 403)-release channel MySQLConnection [id = 16, lastTime = 1455291131321, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 153, charset = latin1, txIsolation = 3, autocommit = true, attachment = null, respHandler = null, host = 13.123.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 ORDER040301 t1 where PROVINCE=10000; +-----------+---------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER040301 t1 WHERE PROVINCE = 10000 LIMIT 100 | +-----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from ORDER040301 t1 where PROVINCE=10000; +----+----------+---------------------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +----+----------+---------------------+---------------------+ | 1 | 10000 | beijing_10006_10000 | 2016-02-12 23:31:55 | +----+----------+---------------------+---------------------+ 1 row in set (0.01 sec) mysql> 

View the background mycat log information:

 02/12 23:36:08.302 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER040301 t1 where PROVINCE=10000 02/12 23:36:08.303 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from ORDER040301 t1 where PROVINCE=10000 02/12 23:36:08.304 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * from ORDER040301 t1 where PROVINCE=10000 value:select * from ORDER040301 t1 where PROVINCE=10000, route={ 1 -> dn21{SELECT * FROM ORDER040301 t1 WHERE PROVINCE = 10000 LIMIT 100} } 02/12 23:36:08.305 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER040301 t1 where PROVINCE=10000, route={ 1 -> dn21{SELECT * FROM ORDER040301 t1 WHERE PROVINCE = 10000 LIMIT 100} } rrs 02/12 23:36:08.305 DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1 02/12 23:36:08.307 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=5, lastTime=1455291368304, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=161, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{SELECT * FROM ORDER040301 t1 WHERE PROVINCE = 10000 LIMIT 100}, respHandler=SingleNodeHandler [node=dn21{SELECT * FROM ORDER040301 t1 WHERE PROVINCE = 10000 LIMIT 100}, packetId=7], host=13.123.1.11, port=3317, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@1cfa73be, writeQueue=0, modifiedSQLExecuted=false] 02/12 23:36:08.308 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=5, lastTime=1455291368304, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=161, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.123.1.11, port=3317, 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 ORDER040301 t1 where SN='beijing_10006_10000'; +-----------+-------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+-------------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER040301 t1 WHERE SN = 'beijing_10006_10000' LIMIT 100 | | dn22 | SELECT * FROM ORDER040301 t1 WHERE SN = 'beijing_10006_10000' LIMIT 100 | +-----------+-------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> select * from ORDER040301 t1 where SN='beijing_10006_10000'; +----+----------+---------------------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +----+----------+---------------------+---------------------+ | 1 | 10000 | beijing_10006_10000 | 2016-02-12 23:31:55 | +----+----------+---------------------+---------------------+ 1 row 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 ORDER040301 t1 where PROVINCE < 10010; +-----------+---------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER040301 t1 WHERE PROVINCE < 10010 LIMIT 100 | | dn22 | SELECT * FROM ORDER040301 t1 WHERE PROVINCE < 10010 LIMIT 100 | +-----------+---------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from ORDER040301 t1 where PROVINCE < 10010; +----+----------+---------------------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +----+----------+---------------------+---------------------+ | 1 | 10000 | beijing_10006_10000 | 2016-02-12 23:31:55 | +----+----------+---------------------+---------------------+ 1 row in set (0.01 sec) mysql>