MySQL 自动维护分区表的分区

一、需求

这是一个设备监控系统中用到的数据库。一个设备实时数据表,用于存储系统采集到的设备实时数据,如电压、电流、温度、功率等。数据采集和存储周期最快是一分钟,系统要求支持不低于10W台设备的监控,历史数据存储不低于10年。历史数据查询一般是以时间范围和设备Id作为查询条件,生成报表。

从需求上看,历史数据表的数据量将会是非常大的。没有别的办法,必须把历史数据表作分区,否则无法支撑那么大的数据量的查询。分区方案使用 range 分区,分区依据是设备时间。

经过测试,一个分区的数据量在1.2亿~1.3亿之间比较合适,太多时查询效率会有较为明显的下降,太少时查询效率没有太大的优势。

于是问题来了,一个分区中存储多大的数据量是最合适的,如何尽量地使每个分区中的数据量维护在这个合理的范围内?

二、方案选择

无论用什么方案,都应该支持使分区能够根据业务情况进行自动扩展。也就是说初始时,表的分区应该不大,但分区应该是随时间不断地增长的。如何进行分区的扩展,有两个方案。

1)固定周期性地扩展分区。即每过一段时间(一般是一个季度、半年、一年、或者几个月)创建一个新的分区。

这种方案中,思路简单,一般只要在MySQL中创建一个事件,通过调用存储过程,每天判断当前分区是否将要“到期”,如果到期,则创建新的分区。

但这种方案的不足之处在于,固定周期的分区无法使每个分区的数据量保持在一个合理范围内。系统刚上线时,由于设备数量少,这样一个分区的数据量会很少;而系统运维一段时间后,可能接入很多的设备,这样又会导致一个分区的数据量过大。

2)动态地扩展分区,当一个分区数据量达到一定量时,再创建新的分区。

这种方案比较灵活,基本能够确保每个分区的数据量保持在1.2亿~1.3亿这个合理范围之间。

本文详细描述这个方案的处理过程。

三、动态分区方案

1)首先确定表结构

Drop Table If Exists `DeviceData`;

Create Table `DeviceData` (

    `Id` Bigint Unsigned Not Null Auto_Increment Comment '主键',

    `DeviceId` Int Unsigned Not Null Comment '设备Id',

    `DeviceDateTime` Bigint Unsigned Not Null Comment '设备时间,以长整型表示,格式yyyyMMddHHmmss',

    `SignalId` Int Unsigned Not Null Comment '信号Id',

    `DataValue` Varchar(20) Not Null Comment '信号值',

    Primary Key(`Id`, `DeviceDateTime`)

) Partition By Range(DeviceDateTime / 1000000) (

    Partition pmax Values Less Than (maxvalue)

);

初始时,只有一个分区,所有数据在进行插入时,都会写到 pmax 分区中。当 pmax 分区中的数据量达到一个阈值时,再进行重组分区。

2)重组分区

我们可以直接使用重组分区命令把 pmax 分区拆分成为两个分区:

Alter Table `DeviceData` 

    Reorganize Partition pmax Into (

        Partition p20180101 Values Less Than (20180101), 

        Partition pmax Values Less Than (maxvalue)

    );

但是,当我们要进行重组分区时,pmax 分区中的数据量一定是非常大的,这条命令执行时间会非常非常非常……长,必然导致系统业务停顿,可能还会造成应用程序存储队列暴表而崩溃。

因此,直接重组分区的方案,否掉!

为了快速重组分区,可以借用一个临时表(是临时创建一个表,不是数据库概念中的临时表),使用交换分区的方式,把当前拥有上亿数据量的分区交换到临时表中,然后在正式数据表中进行拆分,由于这时正式数据表中的 pmax 分区没有数据,因此拆分数据会非常快,拆分完成后,再把临时表中的数据重新交换回来。用数据库脚本描述如下:

-- 创建临时表

Drop Table If Exists `TmpDeviceData`;

Create Table `TmpDeviceData` like `DeviceData`;

-- 用于交换的表不能是分区表,把它改为非分区表

Alter Table `TmpDeviceData` Remove Partitioning;

-- 把数据表和临时表锁住

Lock Table `DeviceData` Write, `TmpDeviceData` Write;

-- 把正式数据表中 pmax 分区交换到临时表,交换完成后,pmax 分区中将没有数据

Alter Table `DeviceData` Exchange Partition pmax With Table `TmpDeviceData`;

-- 把分区 pmax 拆分为两个分区

Alter Table `DeviceData` Reorganize Partition pmax Into (
    Partition p20180601 Values Less Than (20180601), 
    Partition pmax Values Less Than (Maxvalue)

);

-- 把数据从临时表中交换回来,交换回来的数据将存储到 p20180601 分区

Alter Table `DeviceData` Exchange Partition p20180601 With Table `TmpDeviceData` Without Validation;

-- 把表解锁

Unlock Tables;

以上方案在本人虚拟机上试验通过

另外,判断一个分区的数据量的办法有两个:

直接在分区中通过 Select Count(Id) From `DeviceData` 查询,但这样会对整个分区的数据进行一次计算,虽然很准确,但是占用数据库服务器的资源较多

还有一种办法是,在 information_schema.Partitions 表中,有个 Table_Rows 字段,它指示一个分区当前存储的数据行数,能够在0.01秒内得到结果。不过这个值是个估计值,会有大约3%的误差。但我们没必要计较那么精确,可以直接用它查询。

3)意外

按理说,到这里已经变得很简单了:

写个存储过程,判断一下 pmax 分区的数据量,如果达到一个阈值,则执行上面的脚本。当然分区的时间范围要使用系统当前时间(使用 Now函数取得),这可能要用到动态 sql 执行。然后再写个事件,每天调用这个存储过程。

但笔者在写存储过程时,系统提示:在存储过程中不允许出现锁表语句

4)意外的解决办法

没办法了,只能通过应用程序来传递 sql 了

我的系统是 CentOS + Java + MySQL

只写一个简单的 java 程序,通过java连接到数据库,然后动态执行上面的语句。然后在CentOS中利用 crontab 周期性地启动这个Java应用程序了。

Java程序会很长,这里就不帖代码了,基本上,是通过执行 

Select Table_Rows from information_schema.`PARTITIONS`

where TABLE_SCHEMA = database() and TABLE_NAME = 'DeviceData' and PARTITION_NAME = 'pmax';

得到分区中的数据量,当值大于1.2亿时,根据上面的脚本进行重新划分

Java程序编译成功后,通过linux的crontab命令加到crond服务中:

在命令行中执行 crontab -e,然后在编辑界面输入以下内容:

1 1 * * * java -cp $CLASSPATH:/home/zhimin/temp/crontab-java:/home/zhimin/temp/crontab-java/log4j-1.2.17.jar:/home/zhimin/temp/crontab-java/mysql-connector-java-5.1.25-bin.jar test.DataCheck

以上内容表示,在每天的1点1分执行后面的命令

有关crontab 的知识,网上搜索一大把,这里不再废话了。

  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MySQL 中,可以使用分区表来提高查询性能和管理数据的效率。如果你需要按月自动创建分区,可以使用 MySQL 事件调度器(Event Scheduler)和存储过程(Stored Procedure)来实现。 以下是一个示例存储过程,用于创建按月分区的表: ``` DELIMITER $$ CREATE PROCEDURE create_monthly_partitions() BEGIN DECLARE start_date DATE; DECLARE end_date DATE; SET start_date = '2022-01-01'; -- 分区开始日期 SET end_date = DATE_ADD(CURDATE(), INTERVAL 1 MONTH); -- 下一个月的第一天 WHILE start_date < end_date DO SET @sql = CONCAT('ALTER TABLE my_table ADD PARTITION (PARTITION p', DATE_FORMAT(start_date, '%Y%m'), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(DATE_ADD(start_date, INTERVAL 1 MONTH), '%Y-%m-%d'), '\')))'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET start_date = DATE_ADD(start_date, INTERVAL 1 MONTH); END WHILE; END$$ DELIMITER ; ``` 这个存储过程会创建从当前月份开始到下一个月份的所有分区,每个分区以年份和月份为名称,例如 p202201、p202202、p202203 等等。 然后,你可以使用事件调度器来定期运行这个存储过程,以便自动创建新的分区。以下是一个示例事件,每月的第一天凌晨 1 点运行: ``` CREATE EVENT create_monthly_partitions_event ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(CURDATE(), INTERVAL 1 DAY) ON COMPLETION PRESERVE DO CALL create_monthly_partitions(); ``` 这个事件会在每月的第一天凌晨 1 点运行,调用上面创建的存储过程来创建新的分区。 请注意,分区表需要使用特定的语法来查询和管理数据,因此在使用分区表时,请确保你已经理解了它们的概念和使用方法。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值