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

最新推荐文章于 2023-09-30 15:32:53 发布

Han H

最新推荐文章于 2023-09-30 15:32:53 发布

阅读量106

点赞数

文章标签： mysql定义范围

本文链接：https://blog.csdn.net/weixin_42565652/article/details/114345809

版权

1，自定义数字范围分片

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

2，添加配置文件

在function.xml里面进行配置：

autopartition-long04.txt

rang-long-04

在schema.xml里面进行配置：

autopartition-long04.txt文件的配置：

[root@crm_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

[root@crm_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);

mysql> 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, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@6d1ef03, 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>

Han H

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql定义范围_mycat连续分片-)自定义数字范围分片

1，自定义数字范围分片自定义数字范围分片，提前规划好分片字段某个范围属于哪个分片，比如说将第一个500W的数据分片在第一个节点上面，第二个500W的数据分片在第二个节点上，依次类推2，添加配置文件在function.xml里面进行配置：autopartition-long04.txt00IDrang-long-04在schema.xml里面进行配置：autopartition-long04.txt...
复制链接

扫一扫

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

“相关推荐”对你有帮助么？