hunkier

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

mycat 按月分表

什么是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_logdm_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_12logtime为一月份时,根据算法会将数据分配到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节点配置primaryKeyautoIncrementprimaryKey为自增主键,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.cnfmy.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

谢谢你请我喝牛奶

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