如何建立定時任務來建立/刪除/交換分區 - 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：自動化管理分區