hunkier

学习笔记，开源代码，技术分享

mycat 按月分表

发表于 更新于 分类于 ， ， ， Disqus：

什么是MYCAT
• 一个彻底开源的，面向企业应用开发的大数据库集群
• 支持事务、ACID、可以替代MySQL的加强版数据库
• 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
• 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
• 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
• 一个新颖的数据库中间件产品

Mycat关键特性
• 支持SQL92标准
• 支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法
• 遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。
• 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群。
• 支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster
• 基于Nio实现，有效管理线程，解决高并发问题。
• 支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数,支持跨库分页。
• 支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join。
• 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。
• 支持多租户方案。
• 支持分布式事务（弱xa）。
• 支持XA分布式事务（1.6.5）。
• 支持全局序列号，解决分布式下的主键生成问题。
• 分片规则丰富，插件化开发，易于扩展。
• 强大的web，命令行监控。
• 支持前端作为MySQL通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉。
• 支持密码加密
• 支持服务降级
• 支持IP白名单
• 支持SQL黑名单、sql注入攻击拦截
• 支持prepare预编译指令（1.6）
• 支持非堆内存(Direct Memory)聚合计算（1.6）
• 支持PostgreSQL的native协议（1.6）
• 支持MySQL和Oracle存储过程，out参数、多结果集返回（1.6）
• 支持zookeeper协调主从切换、zk序列、配置zk化（1.6）
• 支持库内分表（1.6）
• 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）。
一、分表规则
dm_log和dm_opendoor_record表每年数据量两千万左右，平均每个月两百万左右，mysql单表数据量达到800万时性能出现明显下降，此时需要考虑优化。优先方案考虑优化程序以及sql语句，再优化表结构，最后才是分库分表。
自然月分表，每个月分一张表，分表规则定义在MYCAT_HOME/conf/rule.xml中具体如下：

1
2
3
4
5
6
7
8
9
10
11
12
<tableRule name="dm_log_sharding-by-date">
	<rule>
		<columns>logtime</columns>
		<algorithm>partbymonth</algorithm>
	</rule>
</tableRule>
<tableRule name="dm_opendoor_record_sharding-by-date">
	<rule>
		<columns>opertime</columns>
		<algorithm>partbymonth</algorithm>
	</rule>
</tableRule>
1
2
3
4
5
6
<function name="partbymonth"
	class="io.mycat.route.function.PartitionByMonth">
	<property name="dateFormat">yyyy-MM-dd HH:mm:ss</property>
	<property name="sBeginDate">2014-01-01 00:00:00</property>
	<property name="sEndDate">2014-12-31 00:00:00</property>
</function>

dm_log表按logtime字段根据partbymonth算法分表，
dm_opendoor_record表按opertime字段根据partbymonth算法分表，
算法partbymonth中的dateFormat定义时间格式，在sql语句增删改查中时间需要按照定义格式传入，才能正常执行分表算法。
分表名称定义在MYCAT_HOME/conf/schema.xml

1
2
3
4
5
6
<schema name="mt_pm" checkSQLschema="false" sqlMaxLimit="100" dataNode="mt_pm_dn">
      <table name="dm_log" primaryKey="logid" autoIncrement="true" subTables="dm_log_$1-12" dataNode="mt_pm_dn"
             rule="dm_log_sharding-by-month"/>
      <table name="dm_opendoor_record" primaryKey="id" autoIncrement="true" subTables="dm_opendoor_record_$1-12"
             dataNode="mt_pm_dn" rule="dm_opendoor_record_sharding-by-month"/>
     </schema>

subTables定义了分表的名称：
​ dm_log表定义了分表名为dm_log_$1-12，表示分为12张表，表名分别为：dm_log_1、dm_log_2、dm_log_3、dm_log_4、dm_log_5、dm_log_6、dm_log_7、dm_log_8、dm_log_9、dm_log_10、dm_log_11、dm_log_12。logtime为一月份时，根据算法会将数据分配到dm_log_1表，二月份数据插入表dm_log_2，以此类推，到十二月份后的一月份数据又循环插入dm_log_1表。
​ dm_opendoor_record表定义了分表名为dm_opendoor_record _$1-12，表示分为12张表，表名分别为：dm_opendoor_record _1、dm_opendoor_record _2、dm_opendoor_record _3、dm_opendoor_record _4、dm_opendoor_record _5、dm_opendoor_record _6、dm_opendoor_record_7、dm_opendoor_record_8、dm_opendoor_record_9、dm_opendoor_record _10、dm_opendoor_record _11、dm_opendoor_record _12。opertime为一月份时，根据算法会将数据分配到dm_opendoor_record _1表，二月份数据插入表dm_opendoor_record_2，以此类推，到十二月份后的一月份数据又循环插入dm_opendoor_record _1表。

二、自增主键
原理:
​ 在数据库中建立一张表，存放 sequence 名称(name)，sequence 当前值(current_value)，步长(increment) int 类型每次读取多少个 sequence，假设为 K)等信息；
Sequence 获取步骤：
​ 1).当初次使用该 sequence 时，根据传入的 sequence 名称，从数据库这张表中读取 current_value，和 increment 到 MyCat 中，并将数据库中的 current_value 设置为原 current_value 值+increment 值；
​ 2).MyCat 将读取到 current_value+increment 作为本次要使用的 sequence 值，下次使用时，自动加 1，当使用 increment 次后，执行步骤 1)相同的操作.
MyCat 负责维护这张表，用到哪些 sequence，只需要在这张表中插入一条记录即可。若某次读取的sequence 没有用完，系统就停掉了，则这次读取的 sequence 剩余值不会再使用。
配置方式：
MYCAT_HOME/conf/server.xml 配置：

1
<system><property name="sequnceHandlerType">1</property></system>

注：sequnceHandlerType 需要配置为 1，表示使用数据库方式生成 sequence.
MYCAT_HOME/conf/schema.xml配置：

1
2
3
4
5
6
<schema name="mt_pm" checkSQLschema="false" sqlMaxLimit="100" dataNode="mt_pm_dn">
      <table name="dm_log" primaryKey="logid" autoIncrement="true" subTables="dm_log_$1-12" dataNode="mt_pm_dn"
             rule="dm_log_sharding-by-month"/>
      <table name="dm_opendoor_record" primaryKey="id" autoIncrement="true" subTables="dm_opendoor_record_$1-12"
             dataNode="mt_pm_dn" rule="dm_opendoor_record_sharding-by-month"/>
     </schema>

在table节点配置primaryKey和autoIncrement，primaryKey为自增主键，autoIncrement值为ture。

数据库配置：

  1. 创建 MYCAT_SEQUENCE 表
    – 创建存放 sequence 的表
1
2
DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name)) ENGINE=InnoDB;

  1. name sequence 名称

  2. current_value 当前 value

  3. increment 增长步长，可理解为 mycat 在数据库中一次读取多少个 sequence， 当这些用完后, 下次再从数据库中读取。

– 插入sequence

1
2
INSERT INTO `mycat_sequence` (`name`, `current_value`, `increment`) VALUES ('dm_log', '152509809922444', '1000');
INSERT INTO `mycat_sequence` (`name`, `current_value`, `increment`) VALUES ('dm_opendoor_record', '58733280', '1000');
  1. 创建相关 function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
–- 获取当前 sequence 的值 (返回当前值,增量)
DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf-8
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval=“-999999999,null” ;
SELECT concat(CAST(current_value AS CHAR),“,” ,CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END
DELIMITER;
–- 设置 sequence 值
DROP FUNCTION IF EXISTS mycat_seq_setval;
DELIMITER
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64)
CHARSET utf-8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER;
–- 获取下一个 sequence 值
DROP FUNCTION IF EXISTS mycat_seq_nextval;
DELIMITER
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf-8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER;
  1. sequence_db_conf.properties 相关配置,指定 sequence 相关配置在哪个节点上：
    例如：
1
2
DM_LOG=mt_pm_dn
DM_OPENDOOR_RECORD=mt_pm_dn

注意：MYCAT_SEQUENCE 表和以上的 3 个 function，需要放在同一个节点上。 function 请直接在具体节
点的数据库上执行，如果执行的时候报：
you might want to use the less safe log_bin_trust_function_creators variable
需要对数据库做如下设置：
windows 下 my.ini[mysqld]加上 log_bin_trust_function_creators=1
linux 下/etc/my.cnf 下 my.ini[mysqld]加上 log_bin_trust_function_creators=1
修改完后，即可在 mysql 数据库中执行上面的函数.
使用示例：

1
INSERT INTO `mt_pm`.`dm_log` (`logid`, `deviceid`, `logtype`, `logtime`, `content`, `picurl`, `id`, `positionid`, `position`, `comid`, `launchposition`, `status`) VALUES (next value for MYCATSEQ_DM_LOG, 'af0f69db62-4d31-d4e2-c041-793ed33931', 'unlock', '2018-01-01 08:01:39', '3|0e3f6740|1', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

或者不带主键

1
INSERT INTO `mt_pm`.`dm_log` (`deviceid`, `logtype`, `logtime`, `content`, `picurl`, `id`, `positionid`, `position`, `comid`, `launchposition`, `status`) VALUES ('af0f69db62-4d31-d4e2-c041-793ed33931', 'unlock', '2018-01-01 08:01:39', '3|0e3f6740|1', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

三、定期备份表数据
​ 分为12张表后，每年循环插入数据，最终数据量越来越大，需要定期将历史数据备份或迁移。这里采用修改表名，再新建和原表名结构一样的新表。例如：

1
2
ALTER  TABLE dm_log_1 RENAME TO dm_log201801;
CREATE TABLE IF NOT EXISTS dm_log_1 LIKE dm_log201801;

​ 备份dm_log_1的数据，假设dm_log_1存的都是2018年1月份数据，则把表名修改为dm_log201801，然后再创建一张表名为dm_log_1的表。
​ 上述操作在数据库中写成存储过程，通过数据库定时事件调用执行，详细请看附件中相关sql语句。

定时事件调用策略：

    每月1日的凌晨3:30执行备份历史数据，例如:2018年7月1日凌晨3:30把表dm_log_1改为dm_log201801并新建表dm_log_1，2018年8月1日凌晨3:30把表dm_log_2改为dm_log201802并新建表dm_log_2，以此类推，保留最近半年的数据供物管系统页面查询，超过半年数据则需通过数据库查询历史数据。
    rename_dm_log_history为备份dm_log表数据的存储过程，Event_Rename_dm_log_history为定时事件每月调用rename_dm_log_history备份数据。

1
2
   每月1日的凌晨3:30执行备份历史数据，例如:2018年7月1日凌晨3:30把表`dm_opendoor_record_1`改为dm_opendoor_record201801并新建表dm_opendoor_record_1，2018年8月1日凌晨3:30把表dm_opendoor_record_2改为dm_opendoor_record201802并新建表dm_opendoor_record_2，以此类推，保留最近半年的数据供物管系统页面查询，超过半年数据则需通过数据库查询历史数据。
rename_dm_opendoor_record_history为备份dm_opendoor_record表数据的存储过程，Event_Rename_dm_opendoor_record_history为定时事件每月调用rename_dm_log_history备份数据。

四、附件
1.相关sql语句

2.mycat
​
五、参考资料
Mycat官网：http://www.mycat.io/
Mycat权威指南：http://www.mycat.io/document/Mycat_V1.6.0.pdf

谢谢你请我喝牛奶

欢迎关注我的其它发布渠道