Mycat之——按日期(天)分片
来源:互联网 发布:常见毒药 知乎 编辑:程序博客网 时间:2024/05/17 09:19
转载请注明出处:http://blog.csdn.net/l1028386804/article/details/77018426
一、简单描述
这篇文章就给大家介绍如何用Mycat实现按日期(天)来进行数据分片操作,
即满足如下要求:
按日期(天)分片:从开始日期算起,按照天数来分片
例如,从2017-01-01,每10天一个分片
注意事项:需要提前将分片规划好,建好,否则有可能日期超出实际配置分片数
二、实现数据分片
1、配置rule.xml
在rule.xml中添加如下配置:
<tableRule name="sharding-by-date-customer"> <rule> <columns>create_time</columns> <algorithm>sharding-by-date</algorithm> </rule></tableRule><function name="sharding-by-date" class="org.opencloudb.route.function.PartitionByDate"> <property name="dateFormat">yyyy-MM-dd</property> <!--日期格式--> <property name="sBeginDate">2017-01-01</property> <!--开始日期--> <property name="sPartionDay">10</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_order" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-date-customer"/></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="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_order (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);+-----------+----------------------------------------------------------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+----------------------------------------------------------------------------------------------------------------------------+| dn1 | CREATE TABLE t_order (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) || dn2 | CREATE TABLE t_order (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |+-----------+----------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.01 sec)mysql>mysql>mysql> CREATE TABLE t_order (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);Query OK, 0 rows affected (0.27 sec)可以看出,建表SQL路由到了所有的数据节点上。
接下来我们执行数据录入操作
mysql> explain INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09');+-----------+-----------------------------------------------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+-----------------------------------------------------------------------------------------------------------------+| dn1 | INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09') |+-----------+-----------------------------------------------------------------------------------------------------------------+1 row in set (0.05 sec)mysql> INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09');Query OK, 1 row affected (0.05 sec)mysql> explain INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11');+-----------+-----------------------------------------------------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+-----------------------------------------------------------------------------------------------------------------+| dn2 | INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11') |+-----------+-----------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>mysql> INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11');Query OK, 1 row affected (0.01 sec)我们可以看出,日期为2017-01-09的数据SQL路由到了dn1节点,日期为2017-01-11的数据路由到了dn2节点,符合我们配置的Mycat路由规则。
2、录入数据的Mycat日志
我们打开Mycat的日志,观察如下:
08/09 23:23:03.272 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]explain INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09')08/09 23:23:07.718 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09')08/09 23:23:07.719 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_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09'), route={ 1 -> dn1{INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10001_2017-01-09','2017-01-09')}} rrs 08/09 23:23:59.154 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]explain INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11')08/09 23:23:59.341 DEBUG [Timer0] (SQLJob.java:85) -con query sql:select user() to con:MySQLConnection [id=3, lastTime=1502292239341, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=49, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.209.137, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]08/09 23:23:59.342 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=3, lastTime=1502292239329, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=49, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.209.137, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]08/09 23:24:02.624 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11')08/09 23:24:02.625 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_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11'), route={ 1 -> dn2{INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(20002,'beijing','beijing20002_2017-01-11','2017-01-11')}} rrs从日志我们也可以分析:出日期为2017-01-09的数据SQL路由到了dn1节点,日期为2017-01-11的数据路由到了dn2节点,符合我们配置的Mycat路由规则。
3、指定分片字段查询
mysql> explain select * from t_order where create_time = '2017-01-09';+-----------+------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+------------------------------------------------------------------+| dn1 | SELECT * FROM t_order WHERE create_time = '2017-01-09' LIMIT 100 |+-----------+------------------------------------------------------------------+1 row in set (0.21 sec)可以看出: 执行简单的查询,如果指定分片字段ID字段,则走分片查询单个分片节点
查看后台mycat日志信息:
08/09 23:28:34.883 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=lyzdb, host=192.168.209.1, user=lyz,txIsolation=3, autocommit=true, schema=lyzdb]explain select * from t_order where create_time = '2017-01-09'08/09 23:28:34.909 DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:lyzdb select * from t_order where create_time = '2017-01-09'08/09 23:28:35.094 DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:lyzdb select * from t_order where create_time = '2017-01-09' value:select * from t_order where create_time = '2017-01-09', route={ 1 -> dn1{SELECT *FROM t_orderWHERE create_time = '2017-01-09'LIMIT 100}
4、按照分片字段范围查询
mysql> explain select * from t_order where create_time < '2017-01-10';+-----------+------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+------------------------------------------------------------------+| dn1 | SELECT * FROM t_order WHERE create_time < '2017-01-10' LIMIT 100 || dn2 | SELECT * FROM t_order WHERE create_time < '2017-01-10' LIMIT 100 |+-----------+------------------------------------------------------------------+2 rows in set (0.00 sec)可以得出:如果分片字段范围的查询,则走所有节点去检索,哪怕只有一条数据在一个分片上,route路由也是走所有的分片进行检索查询
5、按照非分片字段查询
mysql> explain select * from t_order where sn = 'beijing10001_2017-01-09';+-----------+----------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+----------------------------------------------------------------------+| dn1 | SELECT * FROM t_order WHERE sn = 'beijing10001_2017-01-09' LIMIT 100 || dn2 | SELECT * FROM t_order WHERE sn = 'beijing10001_2017-01-09' LIMIT 100 |+-----------+----------------------------------------------------------------------+2 rows in set (0.00 sec)可以得出:如果不走分片字段的查询,即使是单个数据,也要route路由所有的分片,走所有的分片进行查询
阅读全文
1 0
- Mycat之——按日期(天)分片
- mycat之按日期分片
- mycat 连续分片 -> 按日期(天)分片
- mycat 连续分片(按日期(天)分片)
- Mycat之——枚举分片
- Mycat之——取模分片
- Mycat之——数据分片入门实战
- Mycat之——自定义数字范围分片
- Mycat之——程序指定分区分片
- Mycat系列—Mysql分片的配置
- Mycat系列—常用的分片规则
- MyCAT常用分片规则之分片枚举
- MyCAT常用分片规则之分片枚举
- Mycat之——读写分离+垂直切分+水平切分+er分片+全局表 测试
- Nginx之——日志按日期分割的实现(基于CentOS操作系统)
- mycat分片
- MySQL之Mycat月分片方法
- MYSQL之表分区----按日期分区
- Android 增量更新技术实现
- Java:身份证号的校验与生成
- C++知识总结(12)
- Ubuntu 16.04安装Eclipse并创建桌面快捷方式
- 基本的二分查找
- Mycat之——按日期(天)分片
- 从策划到运营,做一款地方棋牌要解决哪些问题
- C++知识总结(13)
- 7. Reverse Integer
- 《笨办法学python》加分习题5——我的答案
- 大型网站技术架构
- qml在stackView的情况下处理Android back键(返回键)全局双击back键退出程序
- DNS高速缓存以及DDNS动态域名服务的配置
- C++知识总结(14)