Mantenimiento de particiones RANGE: añadir particiones futuras, archivar / eliminar las antiguas

Comando

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

Qué hace

En una tabla particionada por RANGE basada en fechas, añade la partición del siguiente periodo, mueve un periodo antiguo a una tabla aparte en milisegundos mediante EXCHANGE y elimina la partición con un costo mucho menor que DELETE.

Cuándo usarlo

Para la retención mensual y el archivado en tablas que crecen con el tiempo, como logs, eventos y pistas de auditoría.

Precaución

La columna de particionamiento debe formar parte de todas las claves únicas (incluida la PK). REORGANIZE PARTITION pmax reescribe los datos de pmax; mantenga pmax vacía y cree las particiones con antelación (p. ej. 3 meses). DROP PARTITION no se puede deshacer y no se registra fila por fila; en las réplicas se ejecuta como DDL. El destino del EXCHANGE debe tener la misma estructura y no estar particionado; las filas fuera del rango provocan un error (WITHOUT VALIDATION es más rápido pero puede dejar entrar datos erróneos). El DDL de particiones toma un metadata lock a nivel de tabla. Para el pruning, use la columna de particionamiento en el WHERE sin envolverla en una función.