《MyCat 学习笔记》第六篇.数据分片 之 按月数据分片
1 应用场景
Mycat 有很多数据分库规则，接下来几篇就相关觉得常用的规则进行试用与总结。
一般来说，按自然月份来进行数据分片的规则比较适用于商城订单查询，类似最近1周、2周、3个月内的数据。或是报表类应用。
这样的数据放在一个片区内省去了数据合并的时间。
当然按月数据量不要过大就OK。
2 环境说明
Windows 7
本机多数据库 Mysql 5.5.2
3306 端口下挂有4个库 ： range_db_4、range_db_5、range_db_6、range_db_7
3310 端口下挂有4个库 ： range_db_8、range_db_9、range_db_10、range_db_11
3 参数配置
3.1 数据库配置
mysql 客户端分别进入 3306 和 3310 服务，开始建立物理的schema。
CREATE SCHEMA `range_db_4` DEFAULT CHARACTER SET utf8 ;
CREATE SCHEMA `range_db_5` DEFAULT CHARACTER SET utf8 ;
CREATE SCHEMA `range_db_6` DEFAULT CHARACTER SET utf8 ;
CREATE SCHEMA `range_db_7` DEFAULT CHARACTER SET utf8 ;
...
3306 上
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat_sync_test |
| mysql |
| performance_schema |
| range_db_4 |
| range_db_5 |
| range_db_6 |
| range_db_7 |
+--------------------+
8 rows in set (0.00 sec)
3310上
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat_sync_test |
| mysql |
| performance_schema |
| range_db_10 |
| range_db_11 |
| range_db_8 |
| range_db_9 |
| traveldata_db_1 |
| traveldata_db_2 |
+--------------------+
10 rows in set (0.00 sec)
3.2 server.xml 配置
<!-- 开通test用户访问RANGEDB访问权限 RANGEDB是虚拟schema -->
<user name="test">
<property name="password">test</property>
<property name="schemas">TRDB,RANGEDB</property>
</user>
3.3 schema.xml 配置
<!-- 设定虚拟 schema RANGEDB 信息 -->
<schema name="RANGEDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- 设定虚拟表 t_range_date 对应至数据结点 dn4:dn11 一共8个数据分片，使用 sharding-by-date 分片规则 -->
<table name="t_range_date" dataNode="dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11" rule="sharding-by-date" />
</schema>
<!-- 设定数据结点dn4:dn7 对应的host为 3306服务 以及对应的物理schema -->
<dataNode name="dn4" dataHost="localhost3306" database="range_db_4" />
<dataNode name="dn5" dataHost="localhost3306" database="range_db_5" />
<dataNode name="dn6" dataHost="localhost3306" database="range_db_6" />
<dataNode name="dn7" dataHost="localhost3306" database="range_db_7" />
<!-- 设定数据结点dn8:dn11 对应的host为 3310 服务 以及对应的物理schema -->
<dataNode name="dn8" dataHost="localhost3310" database="range_db_8" />
<dataNode name="dn9" dataHost="localhost3310" database="range_db_9" />
<dataNode name="dn10" dataHost="localhost3310" database="range_db_10" />
<dataNode name="dn11" dataHost="localhost3310" database="range_db_11" />
<!-- 设定datahost 3306 目前只配了一台物理机，若要做读写分离可以参考开场第1、2篇内容进行调整 -->
<dataHost name="localhost3306" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3306" url="localhost:3306" user="root" password="root123"></writeHost>
</dataHost>
<!-- 设定datahost 3306 -->
<dataHost name="localhost3310" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3310" url="localhost:3310" user="root" password="root123"></writeHost>
</dataHost>
3.4 rule.xml 配置
<!-- 分片字段对应到date_str 分片规则为partbymonth -->
<tableRule name="sharding-by-date">
<rule>
<columns>date_str</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<!-- 分片规则 partbymonth 的配置 从 2015 -01 -01 开始分片 -->
<function name="partbymonth" class="org.opencloudb.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
3.5 mycat 重新加载配置信息
访问Mycat 9066 管理口，并重新加载所有参数配置。
D:\bin\mysql\MySQL_3307\bin>mysql -utest -ptest -P 9066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.5-beta-20160111170158 MyCat Server (monitor)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> reload @@config_all;
Query OK, 1 row affected (0.36 sec)
Reload config success
4 数据验证
4.2 Mycat 建表
进入 Mycat 8066 服务口，选用 RANGEDB 库，同步create table。
D:\bin\mysql\MySQL_3310\bin>mysql -utest -ptest -P 8066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5-beta-20160111170158 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> use RANGEDB;
Database changed
mysql> CREATE TABLE `t_range_date` ( `id` INT NOT NULL, `date` DATE NULL, `date_str` VARCHAR(45) NULL, `context` VARCHAR(45) NULL, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.09 sec)
4.5 数据插入与查询
由于只建立了8个分片，超出部分就直接抛数组越界异常了。
mysql> insert into t_range_date (id,date_str,context) values(1,‘2015-01-01‘,‘month-1-str‘);
insert into t_range_date (id,date_str,context) values(2,‘2015-02-01‘,‘month-2-str‘);
insert into t_range_date (id,date_str,context) values(3,‘2015-03-01‘,‘month-3-str‘);
insert into t_range_date (id,date_str,context) values(4,‘2015-04-01‘,‘month-4-str‘);
insert into t_range_date (id,date_str,context) values(5,‘2015-05-01‘,‘month-5-str‘);
insert into t_range_date (id,date_str,context) values(6,‘2015-06-01‘,‘month-6-str‘);
insert into t_range_date (id,date_str,context) values(7,‘2015-07-01‘,‘month-7-str‘);
insert into t_range_date (id,date_str,context) values(8,‘2015-08-01‘,‘month-8-str‘);
insert into t_range_date (id,date_str,context) values(9,‘2015-09-01‘,‘month-9-str‘);
insert into t_range_date (id,date_str,context) values(10,‘2015-10-01‘,‘month-10-str‘);
insert into t_range_date (id,date_str,context) values(11,‘2015-11-01‘,‘month-11-str‘);
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
ERROR 1064 (HY000): Index: 8, Size: 8
ERROR 1064 (HY000): Index: 9, Size: 8
ERROR 1064 (HY000): Index: 10, Size: 8
mysql> select * from t_range_date;
+----+------+------------+-------------+
| id | date | date_str | context |
+----+------+------------+-------------+
| 2 | NULL | 2015-02-01 | month-2-str |
| 4 | NULL | 2015-04-01 | month-4-str |
| 5 | NULL | 2015-05-01 | month-5-str |
| 1 | NULL | 2015-01-01 | month-1-str |
| 3 | NULL | 2015-03-01 | month-3-str |
| 6 | NULL | 2015-06-01 | month-6-str |
| 7 | NULL | 2015-07-01 | month-7-str |
| 8 | NULL | 2015-08-01 | month-8-str |
+----+------+------------+-------------+
8 rows in set (0.01 sec)
4.6 物理库查询
前 4 个月份的数据进入 3306 服务的物理库
mysql> select * from range_db_4.t_range_date;
select * from range_db_5.t_range_date;
select * from range_db_6.t_range_date;
select * from range_db_7.t_range_date;
+----+------+------------+-------------+
| id | date | date_str | context |
+----+------+------------+-------------+
| 1 | NULL | 2015-01-01 | month-1-str |
+----+------+------------+-------------+
1 row in set (0.00 sec)
+----+------+------------+-------------+
| id | date | date_str | context |
+----+------+------------+-------------+
| 2 | NULL | 2015-02-01 | month-2-str |
+----+------+------------+-------------+
1 row in set (0.00 sec)
+----+------+------------+-------------+
| id | date | date_str | context |
+----+------+------------+-------------+
| 3 | NULL | 2015-03-01 | month-3-str |
+----+------+------------+-------------+
1 row in set (0.00 sec)
+----+------+------------+-------------+
| id | date | date_str | context |
+----+------+------------+-------------+
| 4 | NULL | 2015-04-01 | month-4-str |
+----+------+------------+-------------+
1 row in set (0.00 sec)
5 优缺点分析
1.可以做简单的按月分片，如果真要商起来，可以将一个季度的数据配置到相同的datanode 里去。
2.不能按年进行循环配置，如果数据结点不足时需要提前加入，并手动清理历史数据。
本篇完