如何创建定时任务来创建/删除/交换分区 - 云原生数据库 PolarDB

您可以通过创建定时任务的方式来创建、删除或交换分区，以实现分区自动化管理。

背景信息

在某些行业中，固定周期内可能会产生大量的数据，同时也会通过删除大量数据的方式来节省存储空间。如果新产生的数据和需要删除的数据保存在同一张表中，周期性的大批量数据更新，极有可能影响业务的连续性。且在分区表使用不够普遍的情况下，通常的做法是，由DBA在运维时间内定期手动创建新表来承载新的数据，并且需要删除无用数据所在的表。

这种场景存在一系列痛点：

周期性的大批量更新表，可能会对业务的连续性有影响。
DBA在运维时间内手动更新表数据，会增加运维成本。

通过创建定时任务的方式来实现自动化管理分区，既可以避免影响业务连续性，也可以减少不必要的人工操作。

前提条件

集群版本需为PolarDB MySQL版8.0.2版本且Revision version为8.0.2.2.0及以上。您可以通过查询版本号确认集群版本。

定时任务语法

语法

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

参数说明

参数	是否必选	说明
DEFINER	否	给指定用户添加该定时任务的使用权限。
IF NOT EXISTS	否	判断需要创建的事件是否已存在。
event_name	是	指定事件名称。`event_name`最大长度为64个字符。
schedule	是	指定该事件的调度时间。调度时间语法支持以下两种： AT `AT timestamp [+ INTERVAL interval] ...` EVERY `EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...]` 其中，`INTERVAL`包含的时间单位如下： `{YEAR \| QUARTER \| MONTH \| DAY \| HOUR \| MINUTE \| WEEK \| SECOND \| YEAR_MONTH \| DAY_HOUR \| DAY_MINUTE \| DAY_SECOND \| HOUR_MINUTE \| HOUR_SECOND \| MINUTE_SECOND}`
ON COMPLETION [NOT] PRESERVE	否	指定事件执行完一次后的处理方式。如果为`ON COMPLETION PRESERVE`，当`event`执行完成后，该`event`会被关闭，但`event`还是会存在。如果为`ON COMPLETION NOT PRESERVE`，当`event`执行完成后，该`event`会被自动删除。
ENABLE、DISABLE、DISABLE ON SLAVE	否	指定事件的一种属性。取值如下： ENABLE（默认）：表示该事件是开启的，即调度器检查事件是否必需调用。 DISABLE：表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该被调用。 DISABLE ON SLAVE：表示该事件在从节点中是关闭的。
COMMENT ‘comment’	否	指定事件的注释。
DO event_body	是	指定事件启动时需要执行的代码。说明可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN…END复合结构。

示例

假设数据库中存在一张INTERVAL RANGE分区表orders，该表以时间列作为分区键，可以将不同时间范围内的数据划分到不同的分区中。建表语句如下所示：

CREATE TABLE orders(
  id int,
  ordertime datetime
)
PARTITION BY RANGE COLUMNS(ordertime) INTERVAL(DAY, 1)
(
  PARTITION p20220520 VALUES LESS THAN('2022-05-20'),
  PARTITION p20220521 VALUES LESS THAN('2022-05-21')
);

基于orders表来介绍定时新增分区、定时删除分区和定时转换分区的使用方法。

定时新增分区

如果您需要为orders分区表定时新增分区，可以通过创建定时任务的方式定时触发新增分区，示例如下：

DELIMITER ||
CREATE EVENT IF NOT EXISTS add_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-20 22:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
set @pname = concat("alter table orders add partition (partition p",date_format(date_add(curdate(), interval 2 day), '%Y%m%d'), " values less than('", date_add(curdate(), interval 2 day), "'))");
prepare stmt_add_partition from @pname;
execute stmt_add_partition;
deallocate prepare stmt_add_partition;
END ||
DELIMITER ;

假设当前已经存在的最大的分区范围为：2022-05-20 00:00:00~2022-05-20 23:59:59，该定时任务可以从2022-05-20 22:00:00开始，每天创建一个新的分区，来保存第二天的数据。

如果分区表orders为INTERVAL RANGE分区，也可以通过INSERT方式定时触发新增分区，示例如下：

CREATE EVENT IF NOT EXISTS add_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-20 00:00:00'
ON COMPLETION PRESERVE
DO INSERT INTO orders VALUES(id, DATE_ADD(NOW(), INTERVAL 1 DAY));

假设当前最大的分区范围是2022-05-20 00:00:00~2022-05-20 23:59:59，定时任务会从2022-05-20 00:00:00开始执行，每天新增一个新分区，而且是提前一天新增下一天的分区。

定时删除分区

如果业务上需要定期清理orders表中无用的数据，可以创建一个定时任务删除对应的分区。示例如下：

DELIMITER ||
CREATE EVENT IF NOT EXISTS drop_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-21 02:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
set @pname = concat('alter table orders drop partition p', date_format(curdate(), '%Y%m%d'));
prepare stmt_drop_partition from @pname;
execute stmt_drop_partition;
deallocate prepare stmt_drop_partition;
END ||
DELIMITER ;

假设运维时间从02:00开始，该定时任务会从2022-05-21 02:00:00开始，在每天的02:00删除前一天创建的分区。

定时转换分区

如果您不想直接删除orders表中的分区，也可以通过exchange_partition将不再需要的分区转成一张表，这张表与orders分区表完全独立，您可以自行决定如何处理这张表中的数据。示例如下：

-- 创建一个与分区表相同表结构的非分区表来做exchange, DDL结束后分区数据会被交换出去，原来的分区会变为空分区。
DELIMITER ||
CREATE EVENT IF NOT EXISTS exchange_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-21 02:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
set @pname = concat('create table orders_', date_format(curdate(), '%Y%m%d'), '(id int, ordertime datetime)');
prepare stmt_create_table from @pname;
execute stmt_create_table;
deallocate prepare stmt_create_table;

set @pname = concat('alter table orders exchange partition p', date_format(curdate(), '%Y%m%d'), ' with table orders_', date_format(curdate(), '%Y%m%d'));
prepare stmt_exchange_partition from @pname;
execute stmt_exchange_partition;
deallocate prepare stmt_exchange_partition;
END ||
DELIMITER ;

该任务会从2022-05-21 02:00:00开始，在每天的02:00，将前一天创建的分区与一个空表进行交换，分区中原有的数据都会保存在被交换的表中。

云原生数据库 PolarDB：自动化管理分区