Cette rubrique explique comment automatiser la gestion des partitions à l'aide de tâches planifiées pour créer, supprimer et échanger régulièrement des partitions.
Contexte
Dans certains secteurs, de grandes quantités de données sont générées périodiquement. Il est également nécessaire de supprimer régulièrement les données obsolètes afin d'économiser de l'espace de stockage. Si une seule table stocke toutes les données, l'ajout et la suppression fréquents de volumes importants peuvent nuire à la continuité des activités. Comme la plupart des tables ne sont pas partitionnées, vous devez créer manuellement de nouvelles tables pour les données entrantes et supprimer celles contenant les données inutiles.
Cette approche présente plusieurs inconvénients :
Les mises à jour périodiques de grands volumes de données risquent d'affecter la continuité des activités.
La gestion manuelle des tables augmente les coûts d'exploitation et de maintenance (O&M).
Créez des tâches planifiées pour automatiser la gestion des partitions. Cela évite les impacts potentiels sur la continuité des activités et réduit les interventions manuelles.
Prérequis
Le cluster est un cluster PolarDB for MySQL.
La version du cluster est 8.0.2, avec une version de révision 8.0.2.2.0 ou ultérieure.
Pour consulter la version de votre cluster, reportez-vous à la section « Query the engine version » de la rubrique Versions du moteur.
Syntaxe et paramètres
Syntax
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
Paramètre | Obligatoire ? | Notes |
DEFINER | Non | Accorde à l'utilisateur spécifié l'autorisation d'utiliser la tâche planifiée. |
IF NOT EXISTS | Non | Vérifie si l'événement à créer existe déjà. |
event_name | Oui | Nom de l'événement. La valeur |
schedule | Oui | Heure planifiée de l'événement. Spécifiez l'heure de deux manières :
Les unités INTERVAL prises en charge sont les suivantes : |
ON COMPLETION [NOT] PRESERVE | Non | Spécifie le comportement de l'événement après son exécution unique. Valeurs possibles :
|
ENABLE, DISABLE et DISABLE ON SLAVE | Non | Spécifie une propriété de l'événement. Valeurs possibles :
|
COMMENT 'comment' | Non | Commentaire associé à l'événement. |
DO event_body | Oui | Code à exécuter lorsque l'événement est activé. Remarque
|
Exemple
Supposons que la table orders soit partitionnée par plage selon un intervalle de temps. Les données sont distribuées dans les partitions en fonction de la plage horaire correspondante. La table est créée à l'aide des instructions suivantes :
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')
);La section suivante utilise la table orders pour illustrer comment ajouter, supprimer et échanger des partitions à l'aide de tâches planifiées.
Ajouter des partitions
Créez une tâche planifiée qui ajoute régulièrement des partitions à la table 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 ;Supposons que la plage horaire maximale des partitions s'étende de 2022-05-20 00:00:00 à 2022-05-20 23:59:59. À partir de 2022-05-20 22:00:00, la tâche planifiée crée une partition chaque jour pour stocker les données du lendemain.
Si la table orders est partitionnée par intervalle de temps, vous pouvez également créer une tâche planifiée pour ajouter des partitions à l'aide de l'instruction 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));Supposons que la plage horaire maximale des partitions s'étende de 2022-05-20 00:00:00 à 2022-05-20 23:59:59. À partir de 2022-05-20 00:00:00, la tâche planifiée crée une partition chaque jour pour stocker les données du lendemain.
Supprimer des partitions
Créez une tâche planifiée qui supprime régulièrement les partitions inutiles :
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 ;Supposons que les opérations commencent quotidiennement à 02:00:00. À partir de 2022-05-21 02:00:00, la tâche planifiée supprime chaque jour la partition de la veille à 02:00:00.
Échanger des partitions
Si vous préférez conserver les données contenues dans les partitions inutiles de la table orders, créez une tâche planifiée utilisant exchange_partition pour échanger régulièrement ces partitions avec une table totalement indépendante de orders, puis décidez du sort des données :
-- 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 ;À partir de 2022-05-21 02:00:00, la tâche planifiée échange quotidiennement la partition de la veille avec une table vide, déplaçant ainsi efficacement les données de la partition vers la nouvelle table.