Maintenance des partitions RANGE : ajouter les partitions futures, archiver / supprimer les anciennes

Commande

-- Current partitions
SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'events_log' ORDER BY PARTITION_ORDINAL_POSITION;

-- Add a new month by splitting the empty pmax
ALTER TABLE shop.events_log REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE));

-- Move the old month into an archive table (instant), then drop the empty partition
CREATE TABLE shop.events_log_2026_08 LIKE shop.events_log;
ALTER TABLE shop.events_log_2026_08 REMOVE PARTITIONING;
ALTER TABLE shop.events_log EXCHANGE PARTITION p2026_08 WITH TABLE shop.events_log_2026_08;
ALTER TABLE shop.events_log DROP PARTITION p2026_08;

-- Pruning check: the partitions column should list a single partition
EXPLAIN SELECT * FROM shop.events_log WHERE created >= '2026-10-01' AND created < '2026-11-01';

Ce que fait la commande

Sur une table partitionnée par RANGE sur une date, ajoute la partition de la période suivante, déplace une ancienne période dans une table séparée en quelques millisecondes via EXCHANGE, et supprime la partition bien plus économiquement qu'un DELETE.

Quand l'utiliser

Pour la rétention mensuelle et l'archivage des tables qui grossissent avec le temps, comme les logs, les événements et les pistes d'audit.

Attention

La colonne de partitionnement doit faire partie de chaque clé unique (PK comprise). REORGANIZE PARTITION pmax réécrit les données de pmax ; gardez pmax vide et créez les partitions à l'avance (par ex. 3 mois). DROP PARTITION est irréversible et n'est pas journalisé ligne par ligne ; sur les réplicas, il s'exécute comme un DDL. La cible d'EXCHANGE doit avoir la même structure et aucun partitionnement ; les lignes hors plage provoquent une erreur (WITHOUT VALIDATION est plus rapide mais peut laisser entrer des données erronées). Le DDL de partition prend un metadata lock au niveau de la table. Pour le pruning, utilisez la colonne de partitionnement dans le WHERE sans l'envelopper dans une fonction.