RANGE パーティションのメンテナンス: 将来のパーティションを追加し、古いものをアーカイブ / 削除する

コマンド

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

概要

日付ベースで RANGE パーティション化されたテーブルで、次の期間のパーティションを追加し、EXCHANGE により古い期間をミリ秒単位で別テーブルに移動し、DELETE よりはるかに低コストでパーティションを削除する。

使用する場面

ログ、イベント、監査証跡など時間とともに増大するテーブルの月次保持とアーカイブに。

注意

パーティション列は、すべての一意キー (PK を含む) に含まれている必要がある。REORGANIZE PARTITION pmax は pmax 内のデータを書き換えるため、pmax は空に保ち、パーティションを事前に作成しておく (例: 3 か月分)。DROP PARTITION は元に戻せず、行単位でログに記録されない。レプリカでは DDL として実行される。EXCHANGE の対象は同じ構造でパーティション化されていない必要がある。範囲外の行はエラーになる (WITHOUT VALIDATION は高速だが誤ったデータが入りうる)。パーティション DDL はテーブルレベルのメタデータロックを取得する。プルーニングのためには、WHERE でパーティション列を関数で囲まずに使用する。