作者:微枫Micromaple
主页:欢迎关注Micromaple
简介:Java程序员、后端全栈工程师
点赞➕收藏⭐➕留言 您的支持就是我前进的动力
目录
前言一、前期准备二、注意事项三、取模分表四、范围约定分表五、按月分表六、按月分表-适配时间戳6.1、下载Mycat源码6.2、增加适配时间戳方式
前言
大家好,我是微枫Micromaple,下面是我的Mycat系列专栏(持续更新ing),有喜欢的小伙伴欢迎订阅学习呀
深入学习Mycat
✨ Mycat系列专栏:点我学习
Mycat主从搭建、读写分离:点我学习
Mycat实现分库分表:点我学习
Mycat实现单库水平分表、按月分表:点我学习
Mycat高可用方案-HAProxy+Keepalived:点我学习
一、前期准备
下载地址:http://dl.mycat.org.cn/。选择版本进行下载即可。
本文使用的是1.6.7.6 Linux版本。
Linux下载地址:http://dl.mycat.org.cn/1.6.7.6/20220524101549/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gzWin下载地址:http://dl.mycat.org.cn/1.6.7.6/20220524101549/Mycat-server-1.6.7.6-release-20220524173810-win.tar.gz
解压即可使用。
二、注意事项
Mycat单库分表需要准备空白表,提前制定好分表规则。
分表的时候使用到了该字段作为分表字段,数据会按照制定好的分表规则存入不同的数据库或表里面。Mycat中是不允许修改作为分表依据的列的,所以更新是需要去掉此列。
三、取模分表
对分片字段求摸运算。也是水平分表最常用规则。
配置步骤:
修改配置文件schema.xml
修改配置文件rule.xml
columns:分片字段algorithm:分片函数 mod-long规则为配置文件自带的,修改count属性。代表意思为,根据count数取模
需单独在数据节点dn1创建test_table_1至test_table_10表 建表语句: CREATE TABLE `test_table_1`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_2`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_3`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_4`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_5`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_6`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_7`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_8`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_9`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_table_10`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
重启Mycat
连接Mycat 插入数据 INSERT INTO test_table(id, NAME) VALUES(1, '张三1');
INSERT INTO test_table(id, NAME) VALUES(2, '张三2');
INSERT INTO test_table(id, NAME) VALUES(3, '张三3');
INSERT INTO test_table(id, NAME) VALUES(4, '张三4');
INSERT INTO test_table(id, NAME) VALUES(5, '张三5');
INSERT INTO test_table(id, NAME) VALUES(6, '张三6');
INSERT INTO test_table(id, NAME) VALUES(7, '张三7');
INSERT INTO test_table(id, NAME) VALUES(8, '张三8');
INSERT INTO test_table(id, NAME) VALUES(9, '张三9');
INSERT INTO test_table(id, NAME) VALUES(10, '张三10');
登录数据库dn1查看数据分布情况
四、范围约定分表
此分片适用于提前规划好分片字段某个范围属于哪个分片。
配置步骤:
修改配置文件schema.xml
修改配置文件rule.xml
columns:分片字段algorithm:分片函数
mapFile:标识配置文件名称defaultNode:默认节点。小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点,设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错。 修改配置文件autopartition-long.txt 0-99=0
100-199=1
200-300=2
需单独在数据节点dn1创建test_range_1至test_range_3表 建表语句: CREATE TABLE `test_range_1`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_range_2`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_range_3`(
`id` BIGINT,
`name` VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
重启Mycat
连接Mycat 插入数据 INSERT INTO test_range(id, NAME) VALUES(1, '1');
INSERT INTO test_range(id, NAME) VALUES(100, '100');
INSERT INTO test_range(id, NAME) VALUES(200, '200');
登录数据库dn1查看数据分布情况
五、按月分表
此规则为按天分片。设定时间格式、范围。
配置步骤:
修改配置文件schema.xml
修改配置文件rule.xml
columns:分片字段algorithm:分片函数 class="io.mycat.route.function.PartitionByMonth">
dateFormat :日期格式。
字段类型为date使用yyyy-MM-dd字段类型为datetime使用yyyy-MM-dd HH:mm:ss sBeginDate :开始日期sEndDate:结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入 需单独在数据节点dn1创建test_month_20221至test_month_202212表 建表语句: CREATE TABLE `test_month_20221`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20222`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20223`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20224`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20225`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20226`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20227`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20228`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_20229`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_202210`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_202211`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_202212`(
`id` BIGINT,
`name` VARCHAR(255),
`create_date` DATE
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
重启Mycat
连接Mycat 插入数据 INSERT INTO test_month(id,NAME,create_date) VALUES (1,'2022-01-01','2022-01-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (2,'2022-02-01','2022-02-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (3,'2022-03-01','2022-03-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (4,'2022-04-01','2022-04-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (5,'2022-05-01','2022-05-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (6,'2022-06-01','2022-06-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (7,'2022-07-01','2022-07-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (8,'2022-08-01','2022-08-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (9,'2022-09-01','2022-09-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (10,'2022-10-01','2022-10-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (11,'2022-11-01','2022-11-01');
INSERT INTO test_month(id,NAME,create_date) VALUES (12,'2022-12-01','2022-12-01');
登录数据库dn1查看数据分布情况
六、按月分表-适配时间戳
该方式针对于表设计时,将日期字段类型设置为bigint,存储时间戳方式。Mycat按月分表仅支持date、datetime方式,如果需要按照时间戳来进行分表,则需要更改源码,增加分表规则。
6.1、下载Mycat源码
GitHub源码地址(目前最新版本为1.6.7.6-release):
https://github.com/MyCATApache/Mycat-Server.git
可使用git clone 下载源码
切换分支、tag命令:
git checkout 分支/tag名称
6.2、增加适配时间戳方式
在src/main/java源码目录下的io.mycat.route.function包下创建类PartitionByMonthTime。代码如下: package io.mycat.route.function;
import io.mycat.config.model.rule.RuleAlgorithm;
import org.apache.log4j.Logger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 例子 通过时间戳按月份列分区 ,每个自然月一个分片,格式 between操作解析的范例
*
* @author Micromaple
*/
public class PartitionByMonthTime extends AbstractPartitionAlgorithm implements
RuleAlgorithm {
private static final Logger LOGGER = Logger.getLogger(PartitionByMonthTime.class);
/** 开始时间戳 */
private String lBeginDate;
/** 默认格式 */
private String dateFormat = "yyyy-MM-dd";
/** 场景 */
private int scene = -1;
/** 结束时间戳 */
private String lEndDate;
private Calendar beginDate;
private Calendar endDate;
private int nPartition;
private ThreadLocal
@Override
public void init() {
if (lBeginDate == null && lEndDate == null) {
nPartition = 12;
scene = 1;
initFormatter();
beginDate = Calendar.getInstance();
beginDate.set(Calendar.MONTH, 0);
endDate = Calendar.getInstance();
endDate.set(Calendar.MONTH, 11);
return;
}
beginDate = Calendar.getInstance();
beginDate.setTime(new Date(Long.parseLong(lBeginDate)));
initFormatter();
if (lEndDate != null) {
endDate = Calendar.getInstance();
endDate.setTime(new Date(Long.parseLong(lEndDate)));
nPartition = ((endDate.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR)) * 12
+ endDate.get(Calendar.MONTH) - beginDate.get(Calendar.MONTH)) + 1;
if (nPartition <= 0) {
throw new IllegalArgumentException("Incorrect time range for month partitioning!");
}
} else {
nPartition = -1;
}
}
private void initFormatter() {
formatter = new ThreadLocal
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat(dateFormat);
}
};
}
/**
* For circulatory partition, calculated value of target partition needs to be
* rotated to fit the partition range
*/
private int reCalculatePartition(int targetPartition) {
// 没有指定end_date,不是循环使用的情况,直接返回对应的targetPartition
if (nPartition == -1) {
return targetPartition;
}
/**
* If target date is previous of start time of partition setting, shift
* the delta range between target and start date to be positive value
*/
if (targetPartition < 0) {
targetPartition = nPartition - (-targetPartition) % nPartition;
}
if (targetPartition >= nPartition) {
targetPartition = targetPartition % nPartition;
}
return targetPartition;
}
@Override
public Integer calculate(String columnValue) {
try {
if (scene == 1) {
Calendar curTime = Calendar.getInstance();
curTime.setTime(new Date(Long.parseLong(columnValue)));
return curTime.get(Calendar.MONTH);
}
int targetPartition;
Calendar curTime = Calendar.getInstance();
curTime.setTime(new Date(Long.parseLong(columnValue)));
targetPartition = ((curTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
* 12 + curTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH));
/**
* For circulatory partition, calculated value of target partition needs to be
* rotated to fit the partition range
*/
if (nPartition > 0) {
targetPartition = reCalculatePartition(targetPartition);
}
// 防止越界的情况
if (targetPartition < 0) {
targetPartition = 0;
}
return targetPartition;
} catch (Exception e) {
throw new IllegalArgumentException(new StringBuilder().append("columnValue:").append(columnValue)
.append(" Please check if the format satisfied.").toString(), e);
}
}
@Override
public Integer[] calculateRange(String beginValue, String endValue) {
try {
return doCalculateRange(beginValue, endValue, beginDate);
} catch (ParseException e) {
LOGGER.error("error", e);
return new Integer[0];
}
}
private Integer[] doCalculateRange(String beginValue, String endValue, Calendar beginDate) throws ParseException {
int startPartition, endPartition;
Calendar partitionTime = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat(dateFormat);
partitionTime.setTime(new Date(Long.parseLong(beginValue)));
startPartition = ((partitionTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
* 12 + partitionTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH));
partitionTime.setTime(new Date(Long.parseLong(endValue)));
endPartition = ((partitionTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
* 12 + partitionTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH));
List
while (startPartition <= endPartition) {
Integer nodeValue = reCalculatePartition(startPartition);
if (nodeValue < 0) {
nodeValue = 0;
}
if (Collections.frequency(list, nodeValue) < 1) {
list.add(nodeValue);
}
startPartition++;
}
int size = list.size();
// 当在场景1: "2015-01-01", "2014-04-03" 范围出现的时候
// 是应该返回null 还是返回 [] ?
return (list.toArray(new Integer[size]));
}
@Override
public int getPartitionNum() {
int nPartition = this.nPartition;
return nPartition;
}
public void setlBeginDate(String lBeginDate) {
this.lBeginDate = lBeginDate;
}
public void setDateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}
public void setlEndDate(String lEndDate) {
this.lEndDate = lEndDate;
}
}
执行打包命令mvn clean install -Dmaven.test.skip=true 找到target/classes/io/mycat/route/function目录下PartitionByMonthTime类编译后的class 在Mycat中间件服务的根目录找到lib文件夹进入,找到Mycat-server-1.6.7.6-release.jar 使用解压软件打开Mycat-server-1.6.7.6-release.jar 进入io/mycat/route/function目录,将PartitionByMonthTime类编译后的class放至该目录下 修改配置文件schema.xml
修改配置文件rule.xml
class="io.mycat.route.function.PartitionByMonthTime">
dateFormat :日期格式lBeginDate :开始日期lEndDate:结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入 需单独在数据节点dn1创建test_month_time_20221至test_month_time_202212表 建表语句: CREATE TABLE `test_month_time_20221`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20222`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20223`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20224`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20225`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20226`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20227`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20228`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_20229`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_202210`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_202211`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_month_time_202212`(
`id` BIGINT,
`name` VARCHAR(255),
`create_time` bigint
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
重启Mycat
连接Mycat 插入数据 INSERT INTO test_month_time(id,NAME,create_time) VALUES (1,'2022-01-01','1640966400000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (2,'2022-02-01','1643644800000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (3,'2022-03-01','1646064000000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (4,'2022-04-01','1648742400000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (5,'2022-05-01','1651334400000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (6,'2022-06-01','1654012800000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (7,'2022-07-01','1656604800000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (8,'2022-08-01','1659283200000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (9,'2022-09-01','1661961600000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (10,'2022-10-01','1664553600000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (11,'2022-11-01','1667232000000');
INSERT INTO test_month_time(id,NAME,create_time) VALUES (12,'2022-12-01','1669824000000');
登录数据库dn1查看数据分布情况
推荐阅读
发表评论