mycat连续分片-)自定义数字范围分片

页面导航:首页 > 数据库 > Mysql > mycat连续分片-)自定义数字范围分片

mycat连续分片-)自定义数字范围分片

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

1,自定义数字范围分片自定义数字范围分片,提前规划好分片字段某个范围属于哪个分片,比如说将第一个500W的数据分片在第一个节点上面,第二个500W的数据分片在第二个节点上,依

1,自定义数字范围分片

自定义数字范围分片,提前规划好分片字段某个范围属于哪个分片,比如说将第一个500W的数据分片在第一个节点上面,第二个500W的数据分片在第二个节点上,依次类推


2,添加配置文件

在function.xml里面进行配置:

<code class=" hljs xml">    <!--{cke_protected}{C}%3C!%2D%2D%20%E5%AF%B9%E8%87%AA%E5%AE%9A%E4%B9%89%E6%95%B0%E5%AD%97%E5%88%86%E7%89%87%E8%A7%84%E5%88%99rang-long-tr04%E7%9A%84%E5%AE%9A%E4%B9%89%20%2D%2D%3E-->
        <function name="rang-long-04" class="org.opencloudb.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long04.txt</property>
                <property name="defaultNode">0</property> 
                <property name="type">0</property>
        </function>

        <!--{cke_protected}{C}%3C!%2D%2D%20%E5%AF%B9%E8%A1%A8%E8%BF%9B%E8%A1%8C%E5%88%86%E7%89%87%E7%9A%84%E6%8F%8F%E8%BF%B0%EF%BC%8C%E5%AD%97%E6%AE%B5%E4%BB%A5%E5%8F%8A%E5%88%86%E7%89%87%E8%A7%84%E5%88%99%20%2D%2D%3E-->
        <tablerule name="rang-long-tr04">
                 <rule>
                         <columns>ID</columns>
                          <algorithm>rang-long-04</algorithm>
                </rule>
        </tablerule></code>

在schema.xml里面进行配置:

    

autopartition-long04.txt文件的配置:

    [[email protected]_idc_squid_1_11 conf]# more autopartition-long04.txt 
    0-500M=0
    500M-1000M=1
    1000M-1500M=2
    You have mail in /var/spool/mail/root
    [[email protected]_idc_squid_1_11 conf]#     

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


3,建表并且录入数据

    CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
> CREATE TABLE ORDER0401(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 ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
    INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
    mysql> explain INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
    +-----------+--------------------------------------------------------------------------------------------------------+
    | DATA_NODE | SQL                                                                                                    |
    +-----------+--------------------------------------------------------------------------------------------------------+
    | dn21      | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()) |
    +-----------+--------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

    mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
    Query OK, 1 row affected (0.00 sec)

    mysql> 

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

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

开始获取连接:
    02/12 21:17:13.614  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 ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())

开始建立缓存:
    02/12 21:17:13.615  DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
    02/12 21:17:13.616   INFO [$_NIOREACTOR-1-RW] (DefaultLayedCachePool.java:80) -create child Cache: TESTDB_ORDER0401 for layered cache TableID2DataNodeCache, size 10000, expire seconds 18000
    02/12 21:17:13.616  DEBUG [$_NIOREACTOR-1-RW] (CacheManager.java:794) -Attempting to create an existing singleton. Existing singleton returned.
    02/12 21:17:13.616  DEBUG [$_NIOREACTOR-1-RW] (Cache.java:955) -No BootstrapCacheLoaderFactory class specified. Skipping...
    02/12 21:17:13.617  DEBUG [$_NIOREACTOR-1-RW] (Cache.java:929) -CacheWriter factory not configured. Skipping...
    02/12 21:17:13.618  DEBUG [$_NIOREACTOR-1-RW] (MemoryStore.java:153) -Initialized net.sf.ehcache.store.NotifyingMemoryStore for TableID2DataNodeCache.TESTDB_ORDER0401
    02/12 21:17:13.619  DEBUG [$_NIOREACTOR-1-RW] (Cache.java:1165) -Initialised cache: TableID2DataNodeCache.TESTDB_ORDER0401

开始路由匹配:
    02/12 21:17:13.621  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 ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()), route={
       1 -> dn21{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}
    } rrs

匹配好路由后,直接往对应的dn21节点开始执行insert数据录入操作:
    02/12 21:17:13.624  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1455283033608, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=52, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, packetId=0], host=13.217.1.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

连接释放:
    02/12 21:17:13.624  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455283033608, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=52, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.217.1.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

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

mycat命令台显示结果:

    mysql> explain select * from ORDER0401 t1 where t1.ID=5000001;
    +-----------+------------------------------------------------+
    | DATA_NODE | SQL                                            |
    +-----------+------------------------------------------------+
    | dn22      | select * from ORDER0401 t1 where t1.ID=5000001 |
    +-----------+------------------------------------------------+
    1 row in set (0.08 sec)

    mysql> select * from ORDER0401 t1 where t1.ID=5000001;
    +---------+----------+-----------------------+---------------------+
    | ID      | PROVINCE | SN                    | CREATE_TIME         |
    +---------+----------+-----------------------+---------------------+
    | 5000001 | shanghai | shanghai10006_5000001 | 2016-02-12 21:18:17 |
    +---------+----------+-----------------------+---------------------+
    1 row in set (0.00 sec)

    mysql> 

查看后台mycat日志信息:

    02/12 21:44:01.116  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]select * from ORDER0401 t1 where t1.ID=5000001
    02/12 21:44:01.117  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect * from ORDER0401 t1 where t1.ID=5000001
    02/12 21:44:01.118  DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
    02/12 21:44:01.118  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401  miss cache ,key:5000001
    02/12 21:44:01.118  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]select * from ORDER0401 t1 where t1.ID=5000001, route={
       1 -> dn22{select * from ORDER0401 t1 where t1.ID=5000001}
    } rrs
    02/12 21:44:01.119  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
    02/12 21:44:01.120  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=17, lastTime=1455284641106, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=59, charset=latin1, txIsolation=3, autocommit=true, attachment=dn22{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn22{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=7], host=13.217.1.11, port=3327, [email protected]1ef03, writeQueue=0, modifiedSQLExecuted=false]
    02/12 21:44:01.121  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=17, lastTime=1455284641106, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=59, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.217.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

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

    mysql> explain select * from ORDER0401 t1 where t1.ID<50000;
    +-----------+----------------------------------------------------------+
    | DATA_NODE | SQL                                                      |
    +-----------+----------------------------------------------------------+
    | dn21      | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
    | dn22      | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
    +-----------+----------------------------------------------------------+
    2 rows in set (0.01 sec)

    mysql>  

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

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

    mysql> 
Tags:

文章评论

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

<