mycat离散分片-)程序指定分区的分片

页面导航:首页 > 数据库 > Mysql > mycat离散分片-)程序指定分区的分片

mycat离散分片-)程序指定分区的分片

来源: 作者: 时间:2016-02-20 09:26 【

1,程序指定分区的分片此规则是在运行阶段有应用自主决定路由到那个分片。 此方法为直接根据字符子串(必须是数字)计算分区号(由应用传递参数,显式指定分区号)。2,添加配

1,程序指定分区的分片

此规则是在运行阶段有应用自主决定路由到那个分片。
此方法为直接根据字符子串(必须是数字)计算分区号(由应用传递参数,显式指定分区号)。


2,添加配置文件

在function.xml里面进行配置:

<code class=" hljs xml">        <function name="sharding-by-substring-040302" class="org.opencloudb.route.function.PartitionDirectBySubString">
                <property name="startIndex">0</property>
                <property name="size">1</property>
                <property name="partitionCount">8</property>
                <property name="defaultPartition">0</property>
        </function>

    例如id=0-100000020在此配置中代表根据id中从startIndex=0,开始,截取siz=1位数字即0,0就是获取的分区,如果没传默认分配到defaultPartition





        <tablerule name="tr-sharding-substr-mc040302">
                 <rule>
                         <columns>CODE</columns>
                          <algorithm>sharding-by-substring-040302</algorithm>
                </rule>
        </tablerule></code>

在schema.xml里面进行配置:

    

3,建表并且录入数据

    CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME);
    > CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME);
    Query OK, 0 rows affected (0.05 sec)

    mysql> 
    INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());
    INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());
    mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());
    +-----------+----------------------------------------------------------------------------------------------------+
    | DATA_NODE | SQL                                                                                                |
    +-----------+----------------------------------------------------------------------------------------------------+
    | dn21      | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()) |
    +-----------+----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> 
    mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> 
    mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());
    +-----------+-----------------------------------------------------------------------------------------------------+
    | DATA_NODE | SQL                                                                                                 |
    +-----------+-----------------------------------------------------------------------------------------------------+
    | dn22      | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()) |
    +-----------+-----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());
    Query OK, 1 row affected (0.00 sec)

    mysql> 

4,对insert操作流程进行分析,参考mycat.log对路由过程做完整的分析

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

开始获取连接:
    02/13 00:40:18.173  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 ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())

开始路由匹配:
    02/13 00:40:18.175  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 ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()), route={
       1 -> dn22{INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())}

匹配好路由后,直接往对应的dn21节点开始执行insert数据录入操作:
    02/13 00:40:18.180  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=17, lastTime=1455295218162, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=199, charset=latin1, txIsolation=3, autocommit=true, attachment=dn22{INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())}, respHandler=SingleNodeHandler [node=dn22{INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW())}, packetId=0], host=10.254.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

连接释放:
    02/13 00:40:18.180  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=17, lastTime=1455295218162, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=199, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=10.254.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
    02/13 00:40:24.390  DEBUG [Timer1] (SQLJob.java:85) -con query s

5,执行简单的查询,如果指定分片字段ID字段,则走分片查询单个分片节点

mycat命令台显示结果:

    mysql> explain select * from ORDER040302 t1 where CODE= '0-10000020';
    +-----------+------------------------------------------------------------------+
    | DATA_NODE | SQL                                                              |
    +-----------+------------------------------------------------------------------+
    | dn21      | SELECT * FROM ORDER040302 t1 WHERE CODE = '0-10000020' LIMIT 100 |
    +-----------+------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> select * from ORDER040302 t1 where CODE= '0-10000020';
    +----+------------+---------------------+---------------------+
    | ID | CODE       | SN                  | CREATE_TIME         |
    +----+------------+---------------------+---------------------+
    |  1 | 0-10000020 | beijing_03-10000020 | 2016-02-13 00:40:27 |
    +----+------------+---------------------+---------------------+
    1 row in set (0.01 sec)

    mysql>

查看后台mycat日志信息:

    02/13 00:44:46.717  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]explain select * from ORDER040302 t1 where CODE= '0-10000020'
    02/13 00:44:46.718  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDB select * from ORDER040302 t1 where CODE= '0-10000020'
    02/13 00:44:46.719  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER040302 t1 where CODE= '0-10000020' value:select * from ORDER040302 t1 where CODE= '0-10000020', route={
       1 -> dn21{SELECT *
    FROM ORDER040302 t1
    WHERE CODE = '0-10000020'
    LIMIT 100}
    }

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

    mysql> explain select * from ORDER040302 t1 where CODE likt '0-1%';
    +-----------+---------------------------------------------------+
    | DATA_NODE | SQL                                               |
    +-----------+---------------------------------------------------+
    | dn21      | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 |
    | dn22      | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 |
    +-----------+---------------------------------------------------+
    2 rows in set (0.00 sec)

    mysql> 

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

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

    mysql> 
Tags:

文章评论

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

<