一、需求背景
对于单库单表数据超过800万行，并且数据库访问频繁，达到数据访问阈值，需对单表数据局进行分表。
二、适用场景
- 项目已上线，用户量较小，部分表数据量偏大，可进行分表操作
- 项目初期分析研判，用户量较小，并发量低，且热点数据较少，可按照自定分表规则，只查询热点数据（例如：订单表按照月度、季度分表）
- 硬件资源紧张，无法提供额外资源进行分库分表，可按照单库分表操作
三、环境准备
| 序号 | 系统 | 内存（g) | CPU | IP | 环境 |
|---|---|---|---|---|---|
| 1 | CentOS 7.5 | 4 | 4X | 192.168.61.63 | JDK1.7+ |
| 2 | CentOS 7.5 | 4 | 4X | 192.168.61.64 | MySQL 5.7+ |
四、Mycat安装教程
安装教程：https://blog.csdn.net/weixin_41668084/article/details/112797427
五、Mycat配置修改
-
server.xml
<!--下面是设置mycat的用户名/密码和权限控制,和mysql的用户名密码无关 -->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">mydb</property>
</user>-
schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- name: 逻辑数据库名 -->
<schema name="mydb" checkSQLschema="false" sqlMaxLimit="100">
<!-- name: 表名，分表主键，子表，数据节点，分表规则 -->
<table name="t_city" primaryKey="id" autoIncrement="true" subTables="t_city_2018,t_city_2019,t_city_2020,t_city_2021,t_city_2022" dataNode="dn1" rule="sharding-by-year"/>
</schema>
<!-- database:物理数据库名 -->
<dataNode name="dn1" dataHost="localhost1" database="mytest"/>
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.61.63:3306" user="root" password="root">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.61.63:3306" user="root" password="root"/>
</writeHost>
</dataHost>
</mycat:schema>建表语句：
-- mytest.t_city_2018 definition
CREATE TABLE `t_city_2018` (
`id` int(11) NOT NULL,
`code` varchar(10) DEFAULT NULL COMMENT '城市编码',
`name` varchar(100) DEFAULT NULL COMMENT '城市名称',
`year` varchar(100) NOT NULL,
`create_time` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- mytest.t_city_2019 definition
CREATE TABLE `t_city_2019` (
`id` int(11) NOT NULL,
`code` varchar(10) DEFAULT NULL COMMENT '城市编码',
`name` varchar(100) DEFAULT NULL COMMENT '城市名称',
`year` varchar(100) NOT NULL,
`create_time` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- mytest.t_city_2020 definition
CREATE TABLE `t_city_2020` (
`id` int(11) NOT NULL,
`code` varchar(10) DEFAULT NULL COMMENT '城市编码',
`name` varchar(100) DEFAULT NULL COMMENT '城市名称',
`year` varchar(100) NOT NULL,
`create_time` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- mytest.t_city_2021 definition
CREATE TABLE `t_city_2021` (
`id` int(11) NOT NULL,
`code` varchar(10) DEFAULT NULL COMMENT '城市编码',
`name` varchar(100) DEFAULT NULL COMMENT '城市名称',
`year` varchar(100) NOT NULL,
`create_time` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- mytest.t_city_2022 definition
CREATE TABLE `t_city_2022` (
`id` int(11) NOT NULL,
`code` varchar(10) DEFAULT NULL COMMENT '城市编码',
`name` varchar(100) DEFAULT NULL COMMENT '城市名称',
`year` varchar(100) NOT NULL,
`create_time` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-
rule.xml
<!--新增自定义规则-->
<tableRule name="sharding-by-year">
<rule>
<columns>create_time</columns>
<algorithm>partbyyear</algorithm>
</rule>
</tableRule>
<!-- 自定义按年分区 -->
<function name="partbyyear" class="io.mycat.route.function.PartitionByYear">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2018-01-01</property>
</function>源码中并无按年分配的规则，可按照：
- hash-int方法进行按年分配（需特殊创建分表字段，如year）
- 改写源码，自定义按年分配方法(无需特殊创建分表字段，直接使用create_time字段)
package io.mycat.route.function;
import io.mycat.config.model.rule.RuleAlgorithm;
import io.mycat.util.StringUtil;
import org.apache.log4j.Logger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.List;
/**
* 例子 按月份列分区 ，每个自然月一个分片，格式 between操作解析的范例
*
* @author wzh
*/
public class PartitionByYear extends AbstractPartitionAlgorithm implements RuleAlgorithm {
private static final Logger LOGGER = Logger.getLogger(PartitionByYear.class);
private String sBeginDate;
private String sEndDate;
private String dateFormat = "yyyy-MM-dd";
private Calendar beginDate;
private Calendar endDate;
private ThreadLocal<SimpleDateFormat> formatter;
@Override
public Integer calculate(String columnValue) {
try {
if (columnValue == null || columnValue.trim().equals("")) {
LOGGER.error("分区字段不能为空！");
throw new IllegalArgumentException("分区字段不能为空！");
}
// 初始化
initFormatter();
// 当前时间
Calendar curTime = Calendar.getInstance();
curTime.setTime(formatter.get().parse(columnValue));
// 开始时间
beginDate = Calendar.getInstance();
beginDate.setTime(new SimpleDateFormat(dateFormat).parse(sBeginDate));
// 获取所在年月
int currentYear = curTime.get(Calendar.YEAR);
int startYear = beginDate.get(Calendar.YEAR);
// 获得分区
int targetPartition = currentYear - startYear;
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);
}
}
private void initFormatter() {
formatter = new ThreadLocal<SimpleDateFormat>() {
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat(dateFormat);
}
};
}
public void setsBeginDate(String sBeginDate) {
this.sBeginDate = sBeginDate;
}
public void setsEndDate(String sEndDate) {
this.sEndDate = sEndDate;
}
public void setDateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}
}
六、测试验证
[root@localhost ~]# mysql -uroot -p123456 -h192.168.61.64 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.4-test-20210107100701 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2020, 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> show databases;
+----------+
| DATABASE |
+----------+
| mydb |
+----------+
1 row in set (0.00 sec)
mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables in mydb |
+----------------+
| t_city |
+----------------+
1 row in set (0.00 sec)
mysql> select count(*) from t_city;
+---------+
| COUNT0 |
+---------+
| 1000000 |
+---------+
1 row in set (0.06 sec)
mysql> select * from t_city a order by a.id limit 5;
+----+------+------------------+------+-------------+
| id | code | name | year | create_time |
+----+------+------------------+------+-------------+
| 1 | 001 | 2021年1月13日 | 2022 | 2022-01-01 |
| 2 | 001 | test-2 | 2020 | 2020-01-01 |
| 3 | 001 | test-2 | 2020 | 2020-01-01 |
| 4 | 001 | test-2 | 2018 | 2018-01-01 |
| 5 | 001 | test-2 | 2022 | 2022-01-01 |
+----+------+------------------+------+-------------+
5 rows in set (0.00 sec)
mysql>
七、主流分表中间件
| 中间件 | 优势 | 劣势 | 推荐星 |
|---|---|---|---|
| Cobar | 阿里最早起的数据库中间件，现无人维护 | 无人维护导致使用成本高，难度较大 | 2 |
| MyCat | 基于Cobar 进行二次开发，采用代理模式来进行数据库分库分表，也是目前国内较为受欢迎的中间件，低侵入性，高性能 | 对于复杂的SQL不支持，只支持基本函数，不支持复杂join等，具体可参考官方文档。http://www.mycat.io/document/mycat-definitive-guide.pdf | 5 |
| ShardingJDBC | Sharding-Sphere 1.X，是由当当的应用框架dd-frame中的dd-rdb模块演进而来，最终抽离并开源，是完全由当当孵化的开源产品，采用Apache 2.0协议，著作版权归 http://dangdang.com 所有。侵入性较高，可进行灵活的配置 | 使用难度系数大，如果是分布式系统需在每个服务中进行配置，配置难度高 | 3 |
| ShardingProxy | Sharding-Sphere 2.X，原理与MyCat 一样，进过使用测试，在单库分表能力上比MyCat优秀，社区也更活跃。 | 对一些MySQL类型不支持，如bit（可用int替代）；存在乱码问题，但通过源码编译使用，也可得到解决，一般公司使用都会使用源码编译方式，便于修改问题。详细请参考：https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/，本文不做介绍。 | 6 |
| Sharding-Sidecar | Sharding-Sphere 3.X，目前仍然在规划中，据官方介绍，定位为Kubernetes或Mesos的云原生数据库代理，以DaemonSet的形式代理所有对数据库的访问，功能十分强大。 | 全新的数据库中间件概念，面临着诸多挑战，需要大量的完善维护，不稳定。应用面不适合小打小闹的项目 | 6 |
| OneProxy | 前支付宝数据库团队领导楼总开发，C语言开发，支持连接池、故障切换、读写分离、分库分表、SQL防火墙、SQL监控等多项实用功能，主打高性能和稳定性。 | 是商业化中间件，收费。 |
5 |
以上，请参考！
参考链接：
1013
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
