【MySQL】パーティショニングの基本：大規模テーブルを分割する

MySQL
2022.12.20

こんにちは、かつコーチです。

数千万件規模のログテーブルに対して、直近1週間分のデータだけを集計するクエリを投げているのに、テーブル全体をスキャンしてしまい応答が返ってこない。

インデックスを見直しても限界がある、そんな状況に心当たりはないでしょうか。

パーティショニング（1つの論理テーブルを、物理的には複数の領域に分割して管理する仕組み）は、こうした大規模テーブルの運用課題に対する、インデックスとは別のアプローチです。

この記事では、MySQL 8.0のパーティショニングの仕組み・種類・実装方法と、導入する際の判断基準を解説します。

インデックス編・EXPLAIN編を前提知識として進めるため、実行計画の読み方に不安がある場合は先にそちらを確認しておいてください。

パーティショニングとは何か

パーティショニングは、1つのテーブルを内部的に複数の物理領域（パーティション）に分割し、クエリの条件に応じて必要なパーティションだけを読みに行く仕組みです。

論理的には1つのテーブルとして扱えるため、アプリケーション側のSQLを大きく変更する必要がない点が、テーブル分割やシャーディング（複数のデータベースサーバーにデータを分散させる仕組み）と異なる利点です。

パーティションプルーニングという最適化

パーティショニングの効果を支えているのがパーティションプルーニング（クエリの条件から不要なパーティションをあらかじめ除外し、スキャン対象を絞る最適化）です。

例えば月別にパーティション分割されたログテーブルに対して「今月のログだけ」を検索するクエリを投げると、オプティマイザは他の月のパーティションを一切読みに行きません。

これにより、テーブル全体のサイズが数千万件あっても、実質的には該当パーティション分のデータ量だけを走査すればよくなります。

インデックスとの役割の違い

パーティショニングとインデックスは、どちらも検索を高速化する仕組みですが役割が異なります。

観点インデックスパーティショニング
効果の粒度行単位で絞り込むパーティション（領域）単位で絞り込む
得意なこと特定の行を素早く特定する大量データの一括処理・削除を高速化する
運用面の恩恵クエリの高速化が中心古いデータの一括削除、バックアップの分割が可能
適用コスト比較的手軽テーブル設計・運用ルールの見直しが必要

実務では「パーティショニングか、インデックスか」の二択ではなく、パーティションで大まかに絞り込み、パーティション内はインデックスで絞り込むという併用が基本的な考え方になります。

パーティションの種類と実装

RANGEパーティション（範囲分割）

最もよく使われるのが、値の範囲で分割するRANGEパーティションです。

日付やIDのような連続した値を持つカラムを基準に分割するのに向いています。

CREATE TABLE access_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    accessed_at DATETIME NOT NULL,
    url VARCHAR(255) NOT NULL,
    PRIMARY KEY (id, accessed_at)
)
PARTITION BY RANGE (YEAR(accessed_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

YEAR(accessed_at)のように、パーティション分割のキーには関数を使うこともできます。

PARTITION p_future VALUES LESS THAN MAXVALUEは、想定範囲外のデータを受け皿として吸収するためのパーティションで、これがないと範囲外のデータのINSERTがエラーになります。

LISTパーティション（値のリストによる分割)

特定の値のリストで分割したい場合はLISTパーティションを使います。

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    region_code VARCHAR(10) NOT NULL,
    ordered_at DATETIME NOT NULL,
    PRIMARY KEY (id, region_code)
)
PARTITION BY LIST COLUMNS (region_code) (
    PARTITION p_east VALUES IN ('tokyo', 'chiba', 'saitama'),
    PARTITION p_west VALUES IN ('osaka', 'kyoto', 'hyogo'),
    PARTITION p_other VALUES IN ('hokkaido', 'okinawa')
);

地域コードや店舗コードのような、あらかじめ取りうる値が決まっているカラムを分割基準にする場合に適しています。

HASHパーティション（均等分散)

明確な範囲や分類基準がなく、単純にデータを均等に分散させたい場合はHASHパーティションを使います。

CREATE TABLE sessions (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 8;

user_idのハッシュ値をもとに8つのパーティションへ均等に振り分けます。

RANGEやLISTと違い、ビジネス上の意味を持たない分割になるため、「古いパーティションだけ削除する」といった運用には向きませんが、書き込み負荷の分散には有効です。

つまずきやすいポイント・制約事項

主キー・一意キーにパーティションキーを含める必要がある

パーティショニングを設計する際、最も多くの人がつまずくのがこの制約です。

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

これは、主キー（またはすべての一意キー）に、パーティションキーとして使うカラムを含めなければならないというMySQLの制約に違反した際に出るエラーです。

-- ❌ Before：主キーにパーティションキー（accessed_at）が含まれていない
CREATE TABLE access_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    accessed_at DATETIME NOT NULL,
    PRIMARY KEY (id)
)
PARTITION BY RANGE (YEAR(accessed_at)) (
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- ✅ After：主キーにパーティションキーを含める（複合主キーにする）
CREATE TABLE access_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    accessed_at DATETIME NOT NULL,
    PRIMARY KEY (id, accessed_at)
)
PARTITION BY RANGE (YEAR(accessed_at)) (
    PARTITION p2025 VALUES LESS THAN (2026)
);

この制約があるため、既存の設計がそのままではパーティショニングを導入できず、主キー設計から見直しが必要になるケースが少なくありません。

導入前に、この制約が既存のアプリケーションロジック（id単体でのユニーク性に依存した処理がないか等）に影響しないか、十分な確認が必要です。

外部キー制約が使えない

パーティション化されたテーブルは、InnoDBであっても外部キー制約を設定できません。

ERROR 1506 (HY000): Foreign key clause is not yet supported in conjunction with partitioning

外部キーによる参照整合性を重視する設計では、パーティショニングの対象テーブルをよく検討する必要があります。

ログテーブルや履歴テーブルのように、他テーブルから参照される側になりにくいテーブルがパーティショニングの良い候補になります。

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

導入に向いているケース

  • 数千万件〜億件規模で、日付など明確な分割軸がある
  • 古いデータを定期的に一括削除する運用がある（DROP PARTITIONはDELETEより大幅に高速)
  • クエリの多くが特定の範囲（直近1ヶ月など）に絞られている

特に「古いデータの一括削除」は、パーティショニングの隠れた主要メリットです。

-- 通常のDELETEは全行スキャン＋ロックのコストが大きい
DELETE FROM access_logs WHERE accessed_at < '2024-01-01';

-- パーティションの切り離しなら一瞬で完了する
ALTER TABLE access_logs DROP PARTITION p2023;

ログの保持期間ポリシー（例：直近2年分のみ保持）を運用している場合、DROP PARTITIONはDELETEと比べて処理時間・ロック範囲の両面で圧倒的に有利です。

導入を見送るべきケース

  • テーブルサイズが数百万件以下（インデックス最適化で十分対応できることが多い）
  • クエリのアクセスパターンがバラバラで、明確な分割軸がない
  • 外部キー制約や複合主キーの変更が既存設計に大きな影響を与える

パーティショニングは強力な機能ですが、導入コスト（設計変更・制約への対応）も相応にかかります。

まずはEXPLAIN・スロークエリログでボトルネックを特定し、インデックスの見直しで解決できないかを先に検討するのが定石です。

まとめ

この記事のポイント

  • パーティショニングは1つの論理テーブルを複数の物理領域に分割し、パーティションプルーニングで検索対象を絞る仕組み
  • RANGE（範囲）・LIST（値のリスト）・HASH（均等分散）の3種類があり、用途に応じて使い分ける
  • 主キーにパーティションキーを含める必要があり、外部キー制約は使えないという制約に注意する
  • 「古いデータの一括削除」をDROP PARTITIONで高速化できる点が実務上の大きなメリット
  • 数百万件規模以下ならまずインデックス・EXPLAINでの見直しを優先する

次に読むべき記事

  • EXPLAINでMySQLの実行計画を読む
  • スロークエリログの見方と改善の第一歩
  • MySQLとPostgreSQL、実務でどちらを選ぶべきか比較検証

タグ: MySQL, 上級者向け, 設計

タイトルとURLをコピーしました