上次分析聊天業務按照月進行拆。
具體拆分方案已經有了：
![\](https://www.aspphp.online/shujuku/UploadFiles_3118/201701/2017012012032690.gif "\")
但是在操作的時候還是有點小問題，但基本上是按照這個設計實現的。
首先是mycat的，mycat正式版本是1.3.0.3-release，但是這個不包括PartitionByMonth這個類，其次PartitionByMonth 這個類的輸入參數是日期也不好按月進行分表。
還好這類可以轉換月，不用修改代碼，也可以將就著用。
打包PartitionByMonth這個類生成一個jar。這個類在1.4-rc包裡面有。將新jar放到lib目錄下面。
#打包類io.mycat.route.function.PartitionByMonth。
jar -cvf Mycat-server-PartitionByMonth.jar *
PartitionByMonth這個類非常簡單，對比下日期然後返回分區的序號。
如果業務復雜不是一個月一個月的分區可以直接寫死邏輯然後打包使用，比如按季度分區，半個月一分區，或者在2015-06月以前是一個表以後是按月分區等等。
public class PartitionByMonth {
private String sBeginDate;
private String dateFormat;
private Calendar beginDate;
public void init() {
try {
beginDate = Calendar.getInstance();
beginDate.setTime(new SimpleDateFormat(dateFormat)
.parse(sBeginDate));
} catch (ParseException e) {
throw new java.lang.IllegalArgumentException(e);
}
}
//通過時間計算返回分區號 0-n
public Integer calculate(String columnValue) {
try {
Calendar curTime = Calendar.getInstance();
curTime.setTime(new SimpleDateFormat(dateFormat).parse(columnValue));
return (curTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
* 12 + curTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH);
} catch (ParseException e) {
throw new java.lang.IllegalArgumentException(e);
}
}
2，mycat 配置首先創建數據庫，默認分4個表，所有創建4個數據庫，同理可以直接創建好一年的12個表，這裡只是舉例子。
CREATE DATABASE msg_201501 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE msg_201502 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE msg_201503 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE msg_201504 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
在這4個數據庫中創建表，表做10個分區（具體分區數可根據業務量劃定，每個月的mysql分區可以不一樣），按照gid做分區。
CREATE TABLE `msg` (
`id` bigint(20) NOT NULL,
`gid` bigint(20) DEFAULT NULL COMMENT '群id，mysql分區字段',
`content` varchar(4000),
`create_time` datetime DEFAULT NULL COMMENT '創建時間',
`create_month` int(6) DEFAULT NULL COMMENT '按月分表字段，如201501，不能為空。',
PRIMARY KEY (`id`,`gid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY KEY(`gid`)
PARTITIONS 10;
配置mycat 的rule.xml，這裡用到了一個小技巧。month的格式化是
create_month
sharding-by-month
yyyyMM
201501
schema.xml配置：
select 1
server.xml配置：
druidparser
msg
msg
3，mysql 客戶端測試
如果mycat啟動正常，查看logs/wrapper.log沒有異常，且數據庫連接已經創建。
# mysql -umsg -pmsg -P8066 -h 127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.3 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, 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 msg;
mysql>
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'java',now(),201501);
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(2,1,'oracle',now(),201501);
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,2,'ibm',now(),201501);
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(2,2,'mysql',now(),201501);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'zhangsan',now(),201502);
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'lisi',now(),201503);
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'wangwu',now(),201504);
Query OK, 1 row affected (0.00 sec)
mysql> select * from msg where gid = 1 and create_month = 201501;
+----+-----+---------+---------------------+--------------+
| id | gid | content | create_time | create_month |
+----+-----+---------+---------------------+--------------+
| 1 | 1 | java | 2015-07-24 13:21:41 | 201501 |
| 2 | 1 | oracle | 2015-07-24 13:21:41 | 201501 |
+----+-----+---------+---------------------+--------------+
2 rows in set (0.19 sec)
mysql> select * from msg where gid = 1 and create_month = 201502;
+----+-----+----------+---------------------+--------------+
| id | gid | content | create_time | create_month |
+----+-----+----------+---------------------+--------------+
| 1 | 1 | zhangsan | 2015-07-24 13:21:42 | 201502 |
+----+-----+----------+---------------------+--------------+
1 row in set (0.00 sec)
mysql> select * from msg where gid = 1 and create_month = 201503;
+----+-----+---------+---------------------+--------------+
| id | gid | content | create_time | create_month |
+----+-----+---------+---------------------+--------------+
| 1 | 1 | lisi | 2015-07-24 13:21:42 | 201503 |
+----+-----+---------+---------------------+--------------+
1 row in set (0.01 sec)
mysql> select * from msg where gid = 1 and create_month = 201504;
+----+-----+---------+---------------------+--------------+
| id | gid | content | create_time | create_month |
+----+-----+---------+---------------------+--------------+
| 1 | 1 | wangwu | 2015-07-24 13:21:43 | 201504 |
+----+-----+---------+---------------------+--------------+
1 row in set (0.13 sec)
mysql> select * from msg where gid = 2 and create_month = 201501;
+----+-----+---------+---------------------+--------------+
| id | gid | content | create_time | create_month |
+----+-----+---------+---------------------+--------------+
| 1 | 2 | ibm | 2015-07-24 13:21:41 | 201501 |
| 2 | 2 | mysql | 2015-07-24 13:21:41 | 201501 |
+----+-----+---------+---------------------+--------------+
2 rows in set (0.01 sec)
4，java客戶端調用測試
import java.sql.*;
import java.sql.Date;
import java.util.*;
public class MycatTest {
private static Connection connect = null;
private static Statement statement = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void init() {
try {
Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager
.getConnection("jdbc:mysql://192.168.100.1:8066/msg", "msg", "msg");
statement = connect.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close() {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (Exception e) {
}
try {
if (statement != null) {
statement.close();
}
} catch (Exception e) {
}
try {
if (connect != null) {
connect.close();
}
} catch (Exception e) {
}
}
public static void testInsert() {
//實際當中i為gid的自增id。跨按月分區自增。
for (int i = 1; i < 100; i++) {
try {
//特意設置28循環周期。
int j = (i / 28) + 1;
preparedStatement = connect
.prepareStatement("insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(?,?,?,?,?)");
//錄入參數。
preparedStatement.setInt(1, i);
preparedStatement.setInt(2, 99);
preparedStatement.setString(3, "test content " + i);
//插入j時間
preparedStatement.setDate(4, new java.sql.Date(2015, j - 1, i));
//設置按月分區。
preparedStatement.setInt(5, 201500 + j);
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
}
static class Msg {
private int id;
private int gid;
private String content;
private java.util.Date createTime;
private int createMonth;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getGid() {
return gid;
}
public void setGid(int gid) {
this.gid = gid;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public java.util.Date getCreateTime() {
return createTime;
}
public void setCreateTime(java.util.Date createTime) {
this.createTime = createTime;
}
public int getCreateMonth() {
return createMonth;
}
public void setCreateMonth(int createMonth) {
this.createMonth = createMonth;
}
@Override
public String toString() {
return "Msg{" +
"id=" + id +
", gid=" + gid +
", content='" + content + '\'' +
", createTime=" + createTime +
", createMonth=" + createMonth +
'}';
}
}
public static List selectByGidMonth(int gid, int month, int id, int limit) {
List list = new ArrayList();
try {
//如果id == 0就是按照id倒敘查詢。
if (id == 0) {
String sql = "select `id`,`gid`,`content`,`create_time`,`create_month` from msg where gid = ? and create_month = ? order by id desc limit ? ";
preparedStatement = connect
.prepareStatement(sql);
preparedStatement.setInt(1, gid);
preparedStatement.setInt(2, month);
preparedStatement.setInt(3, limit);
} else {//
String sql = "select `id`,`gid`,`content`,`create_time`,`create_month` from msg where gid = ? and create_month = ? and id < ? order by id desc limit ? ";
preparedStatement = connect
.prepareStatement(sql);
preparedStatement.setInt(1, gid);
preparedStatement.setInt(2, month);
preparedStatement.setInt(3, id);
preparedStatement.setInt(4, limit);
}
resultSet = preparedStatement.executeQuery();
int lastId = id;
while (resultSet.next()) {
int id2 = resultSet.getInt("id");
//設置最後查詢id。
lastId = id2;
int gid2 = resultSet.getInt("gid");
String content = resultSet.getString("content");
java.util.Date create_time = resultSet.getDate("create_time");
int create_month = resultSet.getInt("create_month");
Msg msg = new Msg();
msg.setId(id2);
msg.setGid(gid2);
msg.setContent(content);
msg.setCreateTime(create_time);
msg.setCreateMonth(create_month);
//增加數據到list。
list.add(msg);
}
//非常重要的，如果id > 1，且當月沒有查詢到數據，查詢前一個月的數據，直到id = 1 為止。
if (lastId > 1 && list.size() < limit && month >= 201501) {
//剩余數據
int remainSize = limit - list.size();
//使用遞歸進行查詢。month-1 是簡單操作，實際應該用Date返回前一個月。
List remainList = selectByGidMonth(gid, month - 1, lastId, remainSize);
list.addAll(remainList);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
private static void testSelect() {
//假設分頁是20 條記錄。
int page = 20;
int lastId = 0;
List list = selectByGidMonth(99, 201504, lastId, page);
for (Msg msg : list) {
System.out.println(msg);
lastId = msg.getId();
}
System.out.println("###########################");
list = selectByGidMonth(99, 201503, lastId, page);
for (Msg msg : list) {
System.out.println(msg);
lastId = msg.getId();
}
System.out.println("###########################");
list = selectByGidMonth(99, 201503, lastId, page);
for (Msg msg : list) {
System.out.println(msg);
lastId = msg.getId();
}
System.out.println("###########################");
list = selectByGidMonth(99, 201502, lastId, page);
for (Msg msg : list) {
System.out.println(msg);
lastId = msg.getId();
}
System.out.println("###########################");
list = selectByGidMonth(99, 201501, lastId, page);
for (Msg msg : list) {
System.out.println(msg);
lastId = msg.getId();
}
}
public static void main(String[] args) {
init();
//testInsert();
testSelect();
close();
}
}
java客戶端調用說明，首先msg表的id是按照gid連續自增的，如果id > 1，且當月沒有查詢到數據，查詢前一個月的數據，直到id = 1 為止。
if (lastId > 1 && list.size() < limit && month >= 201501) {
//剩余數據
int remainSize = limit - list.size();
//使用遞歸進行查詢。month-1 是簡單操作，實際應該用Date返回前一個月。
List remainList = selectByGidMonth(gid, month - 1, lastId, remainSize);
list.addAll(remainList);
}
使用遞歸函數往前一個月一個月查詢數據，直到查詢到id = 1 為止。查詢結果如下，每次顯示20條數據，插入的100 條 % 28 分別插入4個月數據。
查詢結果可以跨月查詢:
Msg{id=99, gid=99, content='test content 99', createTime=3915-07-08, createMonth=201504}
Msg{id=98, gid=99, content='test content 98', createTime=3915-07-07, createMonth=201504}
Msg{id=97, gid=99, content='test content 97', createTime=3915-07-06, createMonth=201504}
Msg{id=96, gid=99, content='test content 96', createTime=3915-07-05, createMonth=201504}
Msg{id=95, gid=99, content='test content 95', createTime=3915-07-04, createMonth=201504}
Msg{id=94, gid=99, content='test content 94', createTime=3915-07-03, createMonth=201504}
Msg{id=93, gid=99, content='test content 93', createTime=3915-07-02, createMonth=201504}
Msg{id=92, gid=99, content='test content 92', createTime=3915-07-01, createMonth=201504}
Msg{id=91, gid=99, content='test content 91', createTime=3915-06-30, createMonth=201504}
Msg{id=90, gid=99, content='test content 90', createTime=3915-06-29, createMonth=201504}
Msg{id=89, gid=99, content='test content 89', createTime=3915-06-28, createMonth=201504}
Msg{id=88, gid=99, content='test content 88', createTime=3915-06-27, createMonth=201504}
Msg{id=87, gid=99, content='test content 87', createTime=3915-06-26, createMonth=201504}
Msg{id=86, gid=99, content='test content 86', createTime=3915-06-25, createMonth=201504}
Msg{id=85, gid=99, content='test content 85', createTime=3915-06-24, createMonth=201504}
Msg{id=84, gid=99, content='test content 84', createTime=3915-06-23, createMonth=201504}
Msg{id=83, gid=99, content='test content 83', createTime=3915-05-22, createMonth=201503}
Msg{id=82, gid=99, content='test content 82', createTime=3915-05-21, createMonth=201503}
Msg{id=81, gid=99, content='test content 81', createTime=3915-05-20, createMonth=201503}
Msg{id=80, gid=99, content='test content 80', createTime=3915-05-19, createMonth=201503}
###########################
Msg{id=79, gid=99, content='test content 79', createTime=3915-05-18, createMonth=201503}
Msg{id=78, gid=99, content='test content 78', createTime=3915-05-17, createMonth=201503}
Msg{id=77, gid=99, content='test content 77', createTime=3915-05-16, createMonth=201503}
Msg{id=76, gid=99, content='test content 76', createTime=3915-05-15, createMonth=201503}
Msg{id=75, gid=99, content='test content 75', createTime=3915-05-14, createMonth=201503}
Msg{id=74, gid=99, content='test content 74', createTime=3915-05-13, createMonth=201503}
Msg{id=73, gid=99, content='test content 73', createTime=3915-05-12, createMonth=201503}
Msg{id=72, gid=99, content='test content 72', createTime=3915-05-11, createMonth=201503}
Msg{id=71, gid=99, content='test content 71', createTime=3915-05-10, createMonth=201503}
Msg{id=70, gid=99, content='test content 70', createTime=3915-05-09, createMonth=201503}
Msg{id=69, gid=99, content='test content 69', createTime=3915-05-08, createMonth=201503}
Msg{id=68, gid=99, content='test content 68', createTime=3915-05-07, createMonth=201503}
Msg{id=67, gid=99, content='test content 67', createTime=3915-05-06, createMonth=201503}
Msg{id=66, gid=99, content='test content 66', createTime=3915-05-05, createMonth=201503}
Msg{id=65, gid=99, content='test content 65', createTime=3915-05-04, createMonth=201503}
Msg{id=64, gid=99, content='test content 64', createTime=3915-05-03, createMonth=201503}
Msg{id=63, gid=99, content='test content 63', createTime=3915-05-02, createMonth=201503}
Msg{id=62, gid=99, content='test content 62', createTime=3915-05-01, createMonth=201503}
Msg{id=61, gid=99, content='test content 61', createTime=3915-04-30, createMonth=201503}
Msg{id=60, gid=99, content='test content 60', createTime=3915-04-29, createMonth=201503}
###########################
Msg{id=59, gid=99, content='test content 59', createTime=3915-04-28, createMonth=201503}
Msg{id=58, gid=99, content='test content 58', createTime=3915-04-27, createMonth=201503}
Msg{id=57, gid=99, content='test content 57', createTime=3915-04-26, createMonth=201503}
Msg{id=56, gid=99, content='test content 56', createTime=3915-04-25, createMonth=201503}
Msg{id=55, gid=99, content='test content 55', createTime=3915-03-27, createMonth=201502}
Msg{id=54, gid=99, content='test content 54', createTime=3915-03-26, createMonth=201502}
Msg{id=53, gid=99, content='test content 53', createTime=3915-03-25, createMonth=201502}
Msg{id=52, gid=99, content='test content 52', createTime=3915-03-24, createMonth=201502}
Msg{id=51, gid=99, content='test content 51', createTime=3915-03-23, createMonth=201502}
Msg{id=50, gid=99, content='test content 50', createTime=3915-03-22, createMonth=201502}
Msg{id=49, gid=99, content='test content 49', createTime=3915-03-21, createMonth=201502}
Msg{id=48, gid=99, content='test content 48', createTime=3915-03-20, createMonth=201502}
Msg{id=47, gid=99, content='test content 47', createTime=3915-03-19, createMonth=201502}
Msg{id=46, gid=99, content='test content 46', createTime=3915-03-18, createMonth=201502}
Msg{id=45, gid=99, content='test content 45', createTime=3915-03-17, createMonth=201502}
Msg{id=44, gid=99, content='test content 44', createTime=3915-03-16, createMonth=201502}
Msg{id=43, gid=99, content='test content 43', createTime=3915-03-15, createMonth=201502}
Msg{id=42, gid=99, content='test content 42', createTime=3915-03-14, createMonth=201502}
Msg{id=41, gid=99, content='test content 41', createTime=3915-03-13, createMonth=201502}
Msg{id=40, gid=99, content='test content 40', createTime=3915-03-12, createMonth=201502}
###########################
Msg{id=39, gid=99, content='test content 39', createTime=3915-03-11, createMonth=201502}
Msg{id=38, gid=99, content='test content 38', createTime=3915-03-10, createMonth=201502}
Msg{id=37, gid=99, content='test content 37', createTime=3915-03-09, createMonth=201502}
Msg{id=36, gid=99, content='test content 36', createTime=3915-03-08, createMonth=201502}
Msg{id=35, gid=99, content='test content 35', createTime=3915-03-07, createMonth=201502}
Msg{id=34, gid=99, content='test content 34', createTime=3915-03-06, createMonth=201502}
Msg{id=33, gid=99, content='test content 33', createTime=3915-03-05, createMonth=201502}
Msg{id=32, gid=99, content='test content 32', createTime=3915-03-04, createMonth=201502}
Msg{id=31, gid=99, content='test content 31', createTime=3915-03-03, createMonth=201502}
Msg{id=30, gid=99, content='test content 30', createTime=3915-03-02, createMonth=201502}
Msg{id=29, gid=99, content='test content 29', createTime=3915-03-01, createMonth=201502}
Msg{id=28, gid=99, content='test content 28', createTime=3915-02-28, createMonth=201502}
Msg{id=27, gid=99, content='test content 27', createTime=3915-01-27, createMonth=201501}
Msg{id=26, gid=99, content='test content 26', createTime=3915-01-26, createMonth=201501}
Msg{id=25, gid=99, content='test content 25', createTime=3915-01-25, createMonth=201501}
Msg{id=24, gid=99, content='test content 24', createTime=3915-01-24, createMonth=201501}
Msg{id=23, gid=99, content='test content 23', createTime=3915-01-23, createMonth=201501}
Msg{id=22, gid=99, content='test content 22', createTime=3915-01-22, createMonth=201501}
Msg{id=21, gid=99, content='test content 21', createTime=3915-01-21, createMonth=201501}
Msg{id=20, gid=99, content='test content 20', createTime=3915-01-20, createMonth=201501}
###########################
Msg{id=19, gid=99, content='test content 19', createTime=3915-01-19, createMonth=201501}
Msg{id=18, gid=99, content='test content 18', createTime=3915-01-18, createMonth=201501}
Msg{id=17, gid=99, content='test content 17', createTime=3915-01-17, createMonth=201501}
Msg{id=16, gid=99, content='test content 16', createTime=3915-01-16, createMonth=201501}
Msg{id=15, gid=99, content='test content 15', createTime=3915-01-15, createMonth=201501}
Msg{id=14, gid=99, content='test content 14', createTime=3915-01-14, createMonth=201501}
Msg{id=13, gid=99, content='test content 13', createTime=3915-01-13, createMonth=201501}
Msg{id=12, gid=99, content='test content 12', createTime=3915-01-12, createMonth=201501}
Msg{id=11, gid=99, content='test content 11', createTime=3915-01-11, createMonth=201501}
Msg{id=10, gid=99, content='test content 10', createTime=3915-01-10, createMonth=201501}
Msg{id=9, gid=99, content='test content 9', createTime=3915-01-09, createMonth=201501}
Msg{id=8, gid=99, content='test content 8', createTime=3915-01-08, createMonth=201501}
Msg{id=7, gid=99, content='test content 7', createTime=3915-01-07, createMonth=201501}
Msg{id=6, gid=99, content='test content 6', createTime=3915-01-06, createMonth=201501}
Msg{id=5, gid=99, content='test content 5', createTime=3915-01-05, createMonth=201501}
Msg{id=4, gid=99, content='test content 4', createTime=3915-01-04, createMonth=201501}
Msg{id=3, gid=99, content='test content 3', createTime=3915-01-03, createMonth=201501}
Msg{id=2, gid=99, content='test content 2', createTime=3915-01-02, createMonth=201501}
Msg{id=1, gid=99, content='test content 1', createTime=3915-01-01, createMonth=201501}
5，總結
mycat可以支持按月插入數據，但是查詢起來要自己做好分月查詢方案。
由於用戶插入的數據有可能分散在多個月的數據表中，查詢的時候需倒序一個月一個月的查詢。
數據的存儲可以按照年，500G數據放到一個磁盤，一年增加一個磁盤，新數據都寫到新磁盤上面，保證數據隨著時間增長只需要新增加數據庫和磁盤即可，不需要進行數據遷移。
