既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
此分片适用于提前规划好分片字段某个范围属于哪个分片。
配置步骤:
- 修改配置文件
schema.xml
<table name="test\_range" primaryKey="id" subTables="test\_range\_$1-3" dataNode="dn1" rule="auto\_sharding\_long" />
- 修改配置文件
rule.xml
<tableRule name="auto\_sharding\_long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
+ `columns`:分片字段
+ `algorithm`:分片函数
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
+ `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
<table name="test\_month" primaryKey="id" subTables="test\_month\_2022$1-12" dataNode="dn1" rule="sharding-by-month"/>
- 修改配置文件
rule.xml
<tableRule name="sharding-by-month">
<rule>
<columns>create_date</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
+ `columns`:分片字段
+ `algorithm`:分片函数
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2022-01-01</property>
<!-- <property name="sEndDate">2022-12-31</property> -->
</function>
+ `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<SimpleDateFormat> formatter;
@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<SimpleDateFormat>() {
@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<Integer> 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
<table name="test\_month\_time" primaryKey="id" subTables="test\_month\_time\_2022$1-12" dataNode="dn1" rule="sharding-by-month-time"/>
- 修改配置文件
rule.xml
<tableRule name="sharding-by-month-time">
<rule>
<columns>create_time</columns>
<algorithm>partbymonthtime</algorithm>
</rule>
</tableRule>
<function name="partbymonthtime"
class="io.mycat.route.function.PartitionByMonthTime">
![img](https://img-blog.csdnimg.cn/img_convert/4ac7759da3e9e1b566a255d5ef8111bd.png)
![img](https://img-blog.csdnimg.cn/img_convert/c0cff6c64f498e4f4d19ee251ff6e85c.png)
**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**
<algorithm>partbymonthtime</algorithm>
</rule>
</tableRule>
<function name="partbymonthtime"
class="io.mycat.route.function.PartitionByMonthTime">
[外链图片转存中...(img-mCN8qLM8-1715612179733)]
[外链图片转存中...(img-x1vWNWnm-1715612179734)]
**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**