Mycat之——取模分片
来源：互联网 发布：胡适道德与规则 知乎 编辑：程序博客网 时间：2024/06/05 19:12
转载请注明出处：http://blog.csdn.net/l1028386804/article/details/77150670
一、简单描述
取模分片，就是根据数据表的某一个字段，通常是某一个整数型的字段，对其进行十进制的求模运算，将运算结果作为Mycat的路由结果，具体规则如下：
此分片算法根据id进行十进制求模运算，相比固定的分片hash，这种分片算法在批量插入时会增加事务一致性的难度。
二、实现取模分片
1、配置rule.xml
在rule.xml中添加如下配置
<tableRule name="mod-long"><rule><columns>id</columns><algorithm>mod-long</algorithm></rule></tableRule><function name="mod-long" class="org.opencloudb.route.function.PartitionByMod"><!-- how many data nodes --><property name="count">3</property></function>
2、配置schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://org.opencloudb/" ><schema name="lyzdb" checkSQLschema="false" sqlMaxLimit="100"><!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --><table name="t_user" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/></schema><!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --><dataNode name="dn1" dataHost="localhost1" database="db1" /><dataNode name="dn2" dataHost="localhost1" database="db2" /><dataNode name="dn3" dataHost="localhost1" database="db3" /><!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" /> <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> <dataNodename="jdbc_dn2" dataHost="jdbchost" database="db2" /> <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> --><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="192.168.209.137:3306" user="root" password="root"></writeHost><!--<writeHost host="hostS1" url="localhost:3316" user="root"--><!--password="123456" />--><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --></dataHost></mycat:schema>至此，Mycat的配置工作就算完成了，下面，我们一起来测试下Mycat的路由结果。
三、测试路由结果
1、创建数据表
mysql> explain CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME);+-----------+------------------------------------------------------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+------------------------------------------------------------------------------------------------------------------------+| dn1 | CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME) || dn2 | CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME) || dn3 | CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME) |+-----------+------------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)mysql> CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME);Query OK, 0 rows affected (0.21 sec)Mycat日志如下：
08/13 21:44:36.871 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME), route={ 1 -> dn1{CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME)} 2 -> dn2{CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME)} 3 -> dn3{CREATE TABLE t_user (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64),sex VARCHAR(2), CREATE_TIME DATETIME)}} rrs由此可见，当创建数据表时，Mycat将SQL路由到所有的数据节点。2、录入数据
mysql> explain insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW());+-----------+-----------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+-----------------------------------------------------------------------------+| dn2 | insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW()) |+-----------+-----------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>mysql>mysql> insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW());Query OK, 1 row affected (0.10 sec)mysql> explain insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW());+-----------+-----------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+-----------------------------------------------------------------------------+| dn3 | insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW()) |+-----------+-----------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW());Query OK, 1 row affected (0.04 sec)mysql> explain insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW());+-----------+-----------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+-----------------------------------------------------------------------------+| dn1 | insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW()) |+-----------+-----------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW());Query OK, 1 row affected (0.01 sec)Mycat日志如下：
08/13 21:51:45.221 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW()), route={ 1 -> dn2{insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW())}} rrs insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW());08/13 21:52:53.005 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW()), route={ 1 -> dn3{insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW())}} rrs insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW());08/13 21:54:00.546 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW()), route={ 1 -> dn1{insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW())}} rrs由此可见，录入数据时，Mycat根据id进行十进制求模运算，将运算结果作为路由结果，将SQL路由到指定的数据分片节点，符合我们配置的分片规则。3、指定数据分片字段查询
08/13 21:51:45.221 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW()), route={ 1 -> dn2{insert into t_user(id,name, sex, CREATE_TIME) values(1,'lyz01', 'n', NOW())}} rrs insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW());08/13 21:52:53.005 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW()), route={ 1 -> dn3{insert into t_user(id,name, sex, CREATE_TIME) values(2,'lyz02', 'n', NOW())}} rrs insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW());08/13 21:54:00.546 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW()), route={ 1 -> dn1{insert into t_user(id,name, sex, CREATE_TIME) values(3,'lyz03', 'n', NOW())}} rrsMycat日志如下：08/13 21:55:57.257 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]select * from t_user where id = 1, route={ 1 -> dn2{select * from t_user where id = 1}} rr由此可见，执行简单的查询，如果指定分片字段，则走分片查询单个分片节点。4、按照分片字段范围查询
mysql> explain select * from t_user where id >=3;+-----------+----------------------------------------------+| DATA_NODE | SQL |+-----------+----------------------------------------------+| dn1 | SELECT * FROM t_user WHERE id >= 3 LIMIT 100 || dn2 | SELECT * FROM t_user WHERE id >= 3 LIMIT 100 || dn3 | SELECT * FROM t_user WHERE id >= 3 LIMIT 100 |+-----------+----------------------------------------------+3 rows in set (0.02 sec)mysql> select * from t_user where id >=3;+----+-------+------+---------------------+| ID | name | sex | CREATE_TIME |+----+-------+------+---------------------+| 3 | lyz03 | n | 2017-08-13 21:54:00 |+----+-------+------+---------------------+1 row in set (0.04 sec)Mycat日志如下：
08/13 21:58:08.598 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]select * from t_user where id >=3, route={ 1 -> dn1{SELECT *FROM t_userWHERE id >= 3LIMIT 100} 2 -> dn2{SELECT *FROM t_userWHERE id >= 3LIMIT 100} 3 -> dn3{SELECT *FROM t_userWHERE id >= 3LIMIT 100}} rrs 由此可见，如果分片字段范围的查询，则走所有节点去检索，哪怕只有一条数据在一个分片上，route路由也是走所有的分片进行检索查询。5、按照非分片字段查询
mysql> explain select * from t_user where name = 'lyz01';+-----------+-----------------------------------------------------+| DATA_NODE | SQL |+-----------+-----------------------------------------------------+| dn1 | SELECT * FROM t_user WHERE name = 'lyz01' LIMIT 100 || dn2 | SELECT * FROM t_user WHERE name = 'lyz01' LIMIT 100 || dn3 | SELECT * FROM t_user WHERE name = 'lyz01' LIMIT 100 |+-----------+-----------------------------------------------------+3 rows in set (0.00 sec)mysql>mysql> select * from t_user where name = 'lyz01';+----+-------+------+---------------------+| ID | name | sex | CREATE_TIME |+----+-------+------+---------------------+| 1 | lyz01 | n | 2017-08-13 21:51:45 |+----+-------+------+---------------------+1 row in set (0.01 sec)Mycat日志如下：
08/13 21:59:01.166 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]select * from t_user where name = 'lyz01', route={ 1 -> dn1{SELECT *FROM t_userWHERE name = 'lyz01'LIMIT 100} 2 -> dn2{SELECT *FROM t_userWHERE name = 'lyz01'LIMIT 100} 3 -> dn3{SELECT *FROM t_userWHERE name = 'lyz01'LIMIT 100}} rrs 由此可见，如果不走分片字段的查询，即使是单个数据，也要route路由所有的分片，走所有的分片进行查询。阅读全文
1 0
- Mycat之——取模分片
- mycat 取模分片，ER分片
- Mycat之——枚举分片
- mycat ER关系 取模分片试验
- Mycat之——数据分片入门实战
- Mycat之——自定义数字范围分片
- Mycat之——按日期(天)分片
- Mycat之——程序指定分区分片
- Mycat 数据分片--取模函数源码阅读
- Mycat系列—Mysql分片的配置
- Mycat系列—常用的分片规则
- MyCAT常用分片规则之分片枚举
- MyCAT常用分片规则之分片枚举
- mycat之按日期分片
- Mycat之——读写分离+垂直切分+水平切分+er分片+全局表 测试
- Mycat范围求模分片
- mycat分片
- MySQL之Mycat月分片方法
- c语言初步经典题15--另类求和程序填空
- c++经验总结(1):linux c 编译时出现warning: assignment makes pointer from integer without a cast原因
- EA画时序图
- 《C Primer Plus》
- 你还在这样做：浏览器“记住用户名密码”？
- Mycat之——取模分片
- python之struct
- 最短路算法M程序
- HDU 2037 今年暑假不AC
- NB-IoT之BC95调试记录
- ETL利器Kettle实战应用解析系列一【Kettle使用介绍】
- 【模板】高斯消元
- 数据库主键设计之思考[转]
- 【运维】安装群晖NAS
