MySQL のパーティショニング運用 - 大規模テーブルの分割管理

テーブルの行数が数千万件を超えてくると、クエリの実行時間やメンテナンス操作の所要時間が急激に増加します。パーティショニングは、こうした大規模テーブルを論理的に分割し、運用負荷を軽減するための仕組みです。

パーティショニングとは

パーティショニングは、1 つのテーブルを内部的に複数の領域（パーティション）に分割する機能です。アプリケーションからは通常のテーブルと同じようにアクセスできるため、既存のクエリを変更する必要がありません。

パーティショニングなし

テーブル全体をスキャンする必要があり、データ量の増加に比例してクエリが遅くなる。

パーティショニングあり

条件に合致するパーティションだけをスキャンするため、検索対象のデータ量を大幅に削減できる。

この「必要なパーティションだけにアクセスする」動作をパーティションプルーニングと呼びます。プルーニングが効くかどうかが、パーティショニングの効果を左右する最大の要因です。

パーティションの種類

MySQL では 4 種類のパーティショニング方式が利用できます。それぞれ適用に向いたデータの特性が異なるため、テーブルの性質に合わせた選択が必要です。

RANGE パーティショニング

連続する値の範囲でデータを分割する。日付ベースのログテーブルや時系列データに最も適しており、実務で最も多く使われる方式。

LIST パーティショニング

離散的な値のリストでデータを分割する。地域コードやカテゴリ ID など、取りうる値が限定されている場合に有効。

HASH パーティショニング

ハッシュ関数の結果でデータを均等に分散する。特定の分割基準がなく、単純にデータを均等に分けたい場合に使う。

KEY パーティショニング

MySQL の内部ハッシュ関数を使って分散する。HASH と似ているが、複数カラムやカラム型の制約が緩い点が異なる。

RANGE パーティショニングの実装

最も利用頻度が高い RANGE パーティショニングの具体的な実装を見ていきます。ここではアクセスログのテーブルを月単位で分割する例を示します。

CREATE TABLE access_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p202404 VALUES LESS THAN (202405),
    PARTITION p202405 VALUES LESS THAN (202406),
    PARTITION p202406 VALUES LESS THAN (202407),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

ここで重要なのは、パーティションキーとなるカラム（created_at）が主キーに含まれていなければならないという制約です。MySQL のパーティションテーブルでは、ユニークインデックスや主キーのすべてにパーティションキーのカラムが含まれている必要があります。

パーティションの追加と削除

運用中にパーティションを追加したり、古いデータを含むパーティションを削除したりする操作は頻繁に発生します。

-- 新しいパーティションの追加
ALTER TABLE access_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p202407 VALUES LESS THAN (202408),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 古いパーティションの削除（データも消える）
ALTER TABLE access_logs DROP PARTITION p202401;

パーティションの削除は DELETE 文で大量行を消すよりも圧倒的に高速です。テーブルロックの時間も短く、運用への影響が小さいのが大きなメリットになります。

DELETE で古いデータを削除

数百万行の削除に長時間かかり、ロックが発生する

DROP PARTITION なら瞬時に完了し、負荷もほぼゼロ

この性質を活かして、ログテーブルのデータ保持期間管理にパーティショニングを採用するケースは非常に多く見られます。

パーティションプルーニングの確認

パーティショニングの効果を得るには、クエリがプルーニングを正しく実行しているかを確認する必要があります。EXPLAIN の出力にある partitions 列を見ることで、アクセスされるパーティションを特定できます。

EXPLAIN SELECT * FROM access_logs
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

この結果で partitions 列に p202403 のみが表示されていれば、プルーニングが正しく機能しています。すべてのパーティションが表示されている場合は、WHERE 句の条件がパーティションキーと合致していない可能性があるため、クエリの見直しが必要です。

パーティショニングの制約と注意点

パーティショニングには複数の制約があり、導入前に把握しておくことが重要です。

パーティションキーはすべてのユニークインデックスに含める必要がある

外部キー制約はパーティションテーブルで使用できない

パーティション数の上限は 8192 個

全文検索インデックスはサポートされない

空間データ型のカラムをパーティションキーにできない

特に外部キー制約が使えない点は、リレーショナルデータベースの設計に大きな影響を与えます。パーティショニングの対象は、外部キーを必要としないログ系テーブルやイベントテーブルに限定するのが実践的な判断です。

運用の自動化

パーティションの追加・削除を手動で行うのは運用ミスの温床になります。cron やイベントスケジューラを使って自動化するのが望ましい運用方法です。

-- MySQL イベントスケジューラの有効化
SET GLOBAL event_scheduler = ON;

-- 月初に新パーティションを追加するイベント
CREATE EVENT add_partition
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-07-01 00:00:00'
DO
  ALTER TABLE access_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p_new VALUES LESS THAN (
      YEAR(CURDATE()) * 100 + MONTH(CURDATE()) + 1
    ),
    PARTITION p_future VALUES LESS THAN MAXVALUE
  );

ただし、イベントスケジューラで DDL 操作を行う場合は、実行時のテーブルロックやレプリケーションへの影響を考慮する必要があります。大規模な本番環境では、外部のジョブスケジューラ（cron や Airflow など）からスクリプト経由で実行するほうが制御しやすいでしょう。

パーティショニングを導入すべきか

パーティショニングはすべてのテーブルに適用すべきものではありません。行数が少ないテーブルではオーバーヘッドのほうが大きくなり、かえってパフォーマンスが悪化する可能性もあります。

導入が適しているケース

数千万行以上のテーブル、時系列データの定期削除が必要なケース、特定の範囲クエリが中心のワークロード。

導入が不適切なケース

行数が数百万以下のテーブル、外部キーが必要な設計、全パーティションをまたぐクエリが多いワークロード。

導入を検討する際には、まずスロークエリログやクエリの実行計画を分析し、パーティショニングによってプルーニングの恩恵を受けられるクエリがどれだけあるかを見極めることが大切です。闇雲に適用するのではなく、データの特性とアクセスパターンに基づいた判断が求められます。