下载地址: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.gz
- Win下载地址: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
customer_id mod-long - columns:分片字段
- algorithm:分片函数
mod-long规则为配置文件自带的,修改count属性。代表意思为,根据count数取模
10 -
需单独在数据节点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
id rang-long - columns:分片字段
- algorithm:分片函数
autopartition-long.txt 0 - 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
create_date partbymonth - columns:分片字段
- algorithm:分片函数
yyyy-MM-dd 2022-01-01 - 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方式,如果需要按照时间戳来进行分表,则需要更改源码,增加分表规则。
下载Mycat源码GitHub源码地址(目前最新版本为1.6.7.6-release):
https://github.com/MyCATApache/Mycat-Server.git
可使用git clone 下载源码
切换分支、tag命令:
git checkout 分支/tag名称增加适配时间戳方式
-
在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.*; 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 ThreadLocalformatter; @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); } }; } private int reCalculatePartition(int targetPartition) { // 没有指定end_date,不是循环使用的情况,直接返回对应的targetPartition if (nPartition == -1) { return targetPartition; } 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)); 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 list = new ArrayList<>(); 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
create_time partbymonthtime yyyy-MM-dd 1640966400000 - 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查看数据分布情况
-



