mycat离散分片-)枚举分片

页面导航:首页 > 数据库 > Mysql > mycat离散分片-)枚举分片

mycat离散分片-)枚举分片

来源: 作者: 时间:2016-02-18 10:28 【

1,枚举分片枚举分片:通过在配置文件中配置可能的枚举id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的2,添加配置

1,枚举分片

枚举分片:通过在配置文件中配置可能的枚举id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的


2,添加配置文件

在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>

在schema.xml里面进行配置:

    

autopartition-long04.txt文件的配置, 10000代表北京分片到第一个节点,10010代表上海分片到第二个节点

    [[email protected]_1_21 conf]# more partition-hash-int04.txt 
    10000=0 
    10010=1 

    [[email protected]_1_21 conf]# 

注意: 此配置非常简单,即预先制定可能的id范围到某个分片,所有的节点配置都是从0开始,及0代表节点1


3,建表并且录入数据

    CREATE TABLE ORDER040301(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE INT,SN VARCHAR(64),CREATE_TIME DATETIME);
    > 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,对insert操作流程进行分析,参考mycat.log对路由过程做完整的分析

查看后台mycat.log分析如下:

开始获取连接:
    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(2,10010,'shanghai_10010',NOW())

开始路由匹配:
    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(2,10010,'shanghai_10010',NOW()), route={
       1 -> dn22{INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW())}
    } rrs

匹配好路由后,直接往对应的dn21节点开始执行insert数据录入操作:
    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(2,10010,'shanghai_10010',NOW())}, respHandler=SingleNodeHandler [node=dn22{INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW())}, packetId=0], host=13.123.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

连接释放:
    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,执行简单的查询,如果指定分片字段ID字段,则走分片查询单个分片节点

mycat命令台显示结果:

    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> 

查看后台mycat日志信息:

    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, [email protected]fa73be, 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,如果分片字段范围的查询,则走所有节点去检索,哪怕只有一条数据在一个分片上,route路由也是走所有的分片进行检索查询

    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,如果不走分片字段的查询,即使是单个数据,也要route路由所有的分片,走所有的分片进行查询

    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> 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<