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

1,自定义数字范围分片

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


2,添加配置文件

在function.xml里面进行配置:

	<!-- 对自定义数字分片规则rang-long-tr04的定义 -->
        <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>

		<!-- 对表进行分片的描述,字段以及分片规则 -->
        <tableRule name="rang-long-tr04">
                 <rule>
                         <columns>ID</columns>
                          <algorithm>rang-long-04</algorithm>
                </rule>
        </tableRule>

在schema.xml里面进行配置:

	<table name="ORDER0401" primaryKey="ID" dataNode="dn21,dn22" rule="rang-long-tr04"/>
	

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> 
<think>好的,用户想了解如何在Mycat中实现自定义分片规则,需要方法和示例。首先,我要回忆一下Mycat分片规则配置相关知识。记得Mycat主要通过rule.xml来定义分片规则,涉及分片算法和具体的表规则。 首先,需要明确自定义分片规则的步骤。根据引用中的内容,比如引用[2]提到了在rule.xml中配置function和tableRule。自定义分片规则通常需要实现一个分片算法类,然后在rule.xml中配置这个类,并关联到具体的表规则上。 接下来,用户可能需要具体的实现步骤。比如,创建一个Java类继承AbstractPartitionAlgorithm,重写calculate和init方法。然后,打包这个类到Mycat的lib目录下。之后,在rule.xml中配置这个分片函数,并定义对应的tableRule。最后,在schema.xml中应用这个分片规则。 同时,用户可能需要示例。引用[4]提到了范围分片的例子,使用autopartition-long.txt文件来配置范围。可以以此为例,说明如何自定义范围分片。例如,配置mapFile指定范围文件,defaultNode设置默认节点。此外,分片列和算法在tableRule中定义,如引用[2]中的配置。 还要注意分片规则的其他类型,比如引用[3]中的mod-long是取模分片,但用户需要的是自定义方法,所以需要强调如何扩展。可能需要提到分片算法的实现逻辑,比如根据特定字段的值计算分片节点。 另外,用户的问题涉及到配置文件的修改,所以需要详细说明每个配置步骤,确保用户能够正确修改rule.xml和schema.xml。同时,需要注意Mycat的版本,不同版本可能有差异,但根据引用中的信息,这里应该基于Mycat3的配置方法。 最后,确保回答结构清晰,分步骤说明,并给出示例代码和配置片段,如引用[2]和[4]中的例子。同时,相关问题部分需要生成与自定义分片规则相关的问题,比如性能优化、不同分片策略对比等。</think>### Mycat自定义分片规则实现方法 在Mycat中实现自定义分片规则需要修改$rule.xml$配置文件,主要分为**分片函数定义**和**分片规则关联**两个步骤。以下是具体实现方法: --- #### 一、分片函数配置 1. **编写分片算法类** 需继承$io.mycat.route.function.AbstractPartitionAlgorithm$类,实现以下核心方法: ```java public class CustomSharding extends AbstractPartitionAlgorithm { @Override public Integer calculate(String columnValue) { // 自定义分片逻辑,返回目标分片节点编号 return hash(columnValue) % partitionCount; } @Override public void init() { // 初始化配置参数 } } ``` 2. **部署算法类** 将编译后的JAR文件放入$MYCAT_HOME/lib$目录[^1] 3. **配置rule.xml** ```xml <function name="custom-shard" class="com.example.CustomSharding"> <property name="partitionCount">3</property> <!-- 分片数量 --> <property name="hashSeed">0x1234ABCD</property> <!-- 自定义参数 --> </function> ``` --- #### 二、分片规则关联 1. **定义表规则** 在$rule.xml$中绑定字段与分片函数: ```xml <tableRule name="custom_rule"> <rule> <columns>user_id</columns> <!-- 分片字段 --> <algorithm>custom-shard</algorithm> </rule> </tableRule> ``` 2. **应用分片规则** 在$schema.xml$中指定逻辑表使用该规则: ```xml <table name="user" dataNode="dn1,dn2,dn3" rule="custom_rule" /> ``` --- #### 三、示例:自定义范围分片 1. **创建分片映射文件** $autopartition-long.txt$内容: ``` 0-10000=0 10001-20000=1 20001-30000=2 ``` 2. **配置范围分片规则** ```xml <function name="rang-custom" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">autopartition-long.txt</property> <property name="defaultNode">0</property> </function> <tableRule name="auto-sharding-custom"> <rule> <columns>order_id</columns> <algorithm>rang-custom</algorithm> </rule> </tableRule> ``` 此配置会根据$order_id$的值按指定范围路由到不同分片[^4] ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值