mycat连续分片-)按日期(天)分片

页面导航:首页 > 数据库 > Mysql > mycat连续分片-)按日期(天)分片

mycat连续分片-)按日期(天)分片

来源: 作者: 时间:2016-02-21 09:46 【

1,按日期(天)分片按日期(天)分片:从开始日期算起,按照天数来分片 例如,从2016-01-01,每10天一个分片 注意事项:需要提前将分片规划好,建好,否则有可能日期超出实际配置

1,按日期(天)分片

按日期(天)分片:从开始日期算起,按照天数来分片
例如,从2016-01-01,每10天一个分片
注意事项:需要提前将分片规划好,建好,否则有可能日期超出实际配置分片数


2,添加配置文件

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

在schema.xml里面进行配置:

    

3,建表并且录入数据

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

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

开始获取连接:
    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-01-13','2016-01-13')

开始路由匹配:
    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-01-13','2016-01-13'), route={
       1 -> dn22{INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13')}

匹配好路由后,直接往对应的dn22节点开始执行insert数据录入操作:
    02/12 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','2016-01-13')}, respHandler=SingleNodeHandler [node=dn22{INSERT INTO ORDER0402(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_2016-01-13','2016-01-13')}, packetId=0], host=13.23.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

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

mycat命令台显示结果:

    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> 

查看后台mycat日志信息:

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

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

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

文章评论

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

<