パーティションを作成、削除、または交換するためのスケジュールタスクを作成する方法 - PolarDB

このトピックでは、定期的なパーティションの作成、削除、および交換にスケジュールされたタスクを使用して、パーティション管理を自動化する方法について説明します。

背景情報

一部の業界では、定期的に大量のデータが生成されます。ストレージスペースを節約するために、不要なデータも定期的に削除する必要があります。データを保持するテーブルが1つだけの場合、テーブル内の大量のデータを定期的に追加および削除すると、ビジネスの継続性に影響を与える可能性があります。ほとんどのテーブルはパーティション分割されていないため、新しいデータのテーブルを手動で作成し、不要なデータを格納するテーブルを定期的に削除する必要があります。

この方法には欠点があります。

大量のデータを定期的に更新すると、ビジネスの継続性に影響があります。
手動テーブル管理により、O&Mコストが増加します。

スケジュールされたタスクを作成してパーティション管理を自動化し、ビジネスの継続性への影響を回避し、手動操作を削減できます。

前提条件

クラスターはPolarDB for MySQLクラスターです。
クラスターバージョンは8.0.2で、リビジョンバージョンは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;

Parameters

パラメーター	必須ですか?	ノート
DEFINER	No	指定されたユーザーにスケジュール済みタスクの使用権限を付与します。
存在しない場合	No	作成するイベントが既に存在するかどうかをチェックします。
event_name	Yes	イベント名。 `event_name`値の長さは最大64文字です。
schedule	Yes	イベントの予定時刻。次の方法で時刻を指定できます。 AT `AT timestamp [+ INTERVAL interval] ...` すべて `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	No	イベントが1回実行された後の対処方法を指定します。有効な値： `ON COMPLETION PRESERVE`: `イベント`は閉じられ、保持されます。 `ON COMPLETION NOT PRESERVE`: `イベント`は自動的に削除されます。
ENABLE, DISABLE, and DISABLE ON SLAVE	No	イベントのプロパティを指定します。有効な値： ENABLE (デフォルト): イベントは有効です。スケジューラは、イベントをスケジュールする必要があるかどうかをチェックします。 DISABLE: イベントは無効です。イベント宣言はディレクトリに格納されますが、スケジューラはイベントをスケジュールする必要があるかどうかを確認しません。 DISABLE ON SLAVE: 読み取り専用ノードでイベントが無効になっています。
COMMENT 'comment'	No	イベントのコメント。
DO event_body	Yes	イベントが有効になったときに実行されるコード。説明有効なSQLステートメント、ストアドプロシージャ、またはスケジュールされた実行イベントです。複数のステートメントが含まれている場合は、BEGIN…END構文を使用できます。

例

注文テーブルが時間間隔で範囲分割されているとします。データは、該当する時間範囲に基づいてパーティションに分散されます。テーブルは次のステートメントを使用して作成されます。

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')
);

次のセクションでは、順序表を使用して、スケジュール済みタスクを使用してパーティションを追加、削除、および交換する方法を説明します。

パーティションの追加

注文テーブルに定期的にパーティションを追加するスケジュール済みタスクを作成できます。

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から、スケジュールされたタスクは、翌日のデータを格納するために毎日パーティションを作成します。

注文テーブルが時間間隔でパーティション分割されている場合は、INSERT INTOを使用してパーティションを追加するスケジュールタスクを作成することもできます。

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から、スケジュールされたタスクは、翌日のデータを格納するために毎日パーティションを作成します。

パーティションの削除

不要なパーティションを定期的に削除するスケジュール済みタスクを作成できます。

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 ;

O&Mが毎日02:00:00に開始すると仮定する。 2022-05-21 02:00:00から開始すると、スケジュールされたタスクは毎日02:00に前日のパーティションをドロップします。

Exchangeパーティション

ordersテーブルの不要なパーティション内にデータを保持したい場合は、exchange_partitionを使用してスケジュールタスクを作成し、これらのパーティションをordersとは完全に独立したテーブルと定期的に交換してから、データの処理方法を決定できます。

-- Create a non-partitioned table with the same structure as the partitioned table to receive data transferred from partitions. 
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から開始して、スケジュールされたタスクは毎日、前日のパーティションを空のテーブルと交換し、データをパーティションから新しいテーブルに効果的に移動します。

PolarDB:パーティションの自動管理