Manutenção de partições RANGE: adicionar partições futuras, arquivar / remover as antigas

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

O que faz

Em uma tabela particionada por RANGE com base em data, adiciona a partição do próximo período, move um período antigo para uma tabela separada em milissegundos via EXCHANGE e remove a partição a um custo muito menor que DELETE.

Quando usar

Para retenção mensal e arquivamento em tabelas que crescem com o tempo, como logs, eventos e trilhas de auditoria.

Atenção

A coluna de particionamento precisa fazer parte de toda chave única (PK incluída). REORGANIZE PARTITION pmax reescreve os dados em pmax; mantenha pmax vazia e crie partições com antecedência (por exemplo, 3 meses). DROP PARTITION não pode ser desfeito e não é registrado linha a linha; nas réplicas ele roda como DDL. O destino do EXCHANGE precisa ter a mesma estrutura e não ser particionado; linhas fora do intervalo causam erro (WITHOUT VALIDATION é mais rápido, mas pode deixar entrar dados errados). DDL de partição obtém um metadata lock no nível da tabela. Para pruning, use a coluna de particionamento no WHERE sem envolvê-la em uma função.