MySQLのテーブルパーティショニング設定ガイド：RANGEとLISTで大規模テーブルを高速化 - ITFROMZERO - Share tobe shared! 5,000万行超の大規模テーブルでクエリ遅延やDELETE文によるDB停止に悩んでいませんか？MySQLのテーブルパーティショニング（RANGE／LIST）を活用すれば、データを適切に分離し、クエリの高速化と瞬時のディスク容量解放を実現できます。

Table of Contents

永遠の課題：データテーブルが数千万行に達したとき

紙の書類保管庫を管理している場面を想像してみてください。1,000万件もの契約書をラベルも貼らずにひとつの部屋に詰め込んでいたとします。上司から2021年の契約書を求められるたびに、棚を端から端まで探さなければなりません。しかし、部屋を年ごとの専用キャビネットに分けて整理していれば、2021年の棚へ直行するだけで、ほんの数秒で見つけ出せます。

MySQLデータベースの仕組みも、これとまったく同じです。

users テーブルが1,000万行程度であれば、B-Tree Indexの最適化によって十分対応できます。しかし、注文データ（orders）、システムログ、IoTデータなどが5,000万〜1億行に達すると、インデックスはかえって逆効果になり始めます。データとインデックスの合計サイズが InnoDB Buffer Pool（サーバー上のメモリが16GBまたは32GB程度の場合）を大幅に超過し、NVMe SSDへのI/Oが頻発して、クエリがキューで詰まるようになります。

特に深刻なのが古いデータのクリーンアップです。5,000万行のテーブルに対して DELETE FROM logs WHERE created_at < '2023-01-01' を実行すると、undo logが肥大化し、大量のレコードがロックされ、激しいI/O競合が発生します。最悪の場合、アプリケーションが数時間フリーズすることもあります。テーブルパーティショニング（Table Partitioning）は、まさにこの課題を解決するために生まれました。

テーブルパーティショニング（Table Partitioning）とは？

シンプルに言えば、テーブルパーティショニング（Table Partitioning）とは、巨大なテーブルをディスク上で複数の独立した小さな単位に分割する仕組みです（InnoDBでは各パーティションが個別の .ibd ファイルとして保存されます）。分割されていても、バックエンドアプリケーションからは従来通り単一のテーブルとして認識されます。

アプリケーションのコードを変更したり、テーブルを orders_2022、orders_2023 のように手動で分割して UNION で結合したりする必要はありません。クエリはこれまでの記述のまま利用できます。

SELECT * FROM orders WHERE order_date = '2024-05-10';

このとき、MySQLのオプティマイザは Partition Pruning（パーティションプルーニング） 機能を実行します。WHERE 句の条件を読み取り、2024年のパーティションのみをピンポイントで特定して、残りのパーティションをすべてスキャン対象から除外します。これにより、1億行全体をフルスキャンする代わりに、2024年分の600万行だけを探索すれば済むため、処理速度が飛躍的に向上します。

代表的な2つのパーティショニング方式

RANGE Partitioning（範囲分割）: 連続した値の範囲で分割します。日付・時刻（年、四半期、月）や、自動採番IDの範囲（1〜1,000,000など）での分割が一般的です。
LIST Partitioning（リスト分割）: 事前に定義した個別の値のリストに基づいて分割します。例として、地域コード（北部: 1、中部: 2、南部: 3）や注文ステータスなどがあります。

厳格な技術的制約： テーブル上のすべてのユニークキー（Primary KeyまたはUnique Key）には、パーティションキーとなるカラムを必ず含める必要があります。もし主キーが id のみのテーブルで order_date によるパーティショニングを試みると、MySQLはテーブル作成を即座に拒否します。

実践：RANGEとLISTパーティショニングの設定手順

1. 期間による分割（RANGE Partitioning）

年間約2,000万件、累計8,000万件のEC注文データを保持する orders テーブルを例に設計してみましょう。

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_amount DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (id, order_date)
)
ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

上記の設定における重要なポイントは以下の通りです。

p2022 には2022年以前のすべてのデータが格納されます。
p2023 と p2024 には、それぞれの該当する年のデータが格納されます。
MAXVALUE を指定した p_future はセーフティネットとして機能します。これがない場合、2025年の注文データが挿入された際に MySQL が “Table has no partition for value 2025” というエラーを返し、書き込みに失敗します。

EXPLAIN を実行して、Partition Pruning が正しく機能しているか確認してみましょう。

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-03-01' AND '2023-06-30';

実行結果の partitions カラムを確認すると、p2023 のみが表示されているはずです。MySQLは他の3つのパーティションを完全にスキャン対象外としています。

最大のメリット：不要データの即座なパージ。 3年が経過し、ディスク容量を30GB節約するために2022年の全データを削除したいとします。システムに高負荷をかける DELETE 文を実行する代わりに、以下のコマンドを1行実行するだけで完了します。

ALTER TABLE orders DROP PARTITION p2022;

このコマンドはディスク上の物理ファイルを直接削除します。undo logの生成や長時間のテーブルロックを伴わず、わずか0.05秒足らずで処理が完了し、ディスク領域が即座に解放されます。

2. 離散値グループによる分割（LIST Partitioning）

次に、店舗売上を管理する store_sales テーブルで、北部（地域ID: 1, 2）、中部（3, 4）、南部（5, 6）の3つの地域グループに分割するケースを考えてみましょう。

CREATE TABLE store_sales (
    id BIGINT NOT NULL AUTO_INCREMENT,
    branch_id INT NOT NULL,
    region_id INT NOT NULL,
    revenue DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, region_id)
)
ENGINE=InnoDB
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1, 2),
    PARTITION p_central VALUES IN (3, 4),
    PARTITION p_south VALUES IN (5, 6)
);

経理部門が北部地域の店舗データを集計・抽出する場合：

EXPLAIN SELECT * FROM store_sales WHERE region_id IN (1, 2);

MySQLはクエリを p_north のファイルのみに誘導するため、スキャン対象となるデータ量を即座に約66%削減できます。

将来的に事業が拡大し、新地域（コード 7, 8）の支店を追加する場合でも、新しいパーティションを簡単に追加できます。

ALTER TABLE store_sales ADD PARTITION (
    PARTITION p_highlands VALUES IN (7, 8)
);

テーブルパーティショニングの導入判断基準：使うべきケースと避けるべきケース

パーティショニングは大容量テーブルの課題に対して非常に有効ですが、決して万能薬ではありません。適用すべきなのは、以下のようなケースに限られます。

テーブル規模が十分に大きい場合： レコード数が2,000万行を超えるか、データファイルのサイズがデータベースサーバーのRAM容量を上回っている。
定期的なデータパージが必要な場合： ログデータ、監査証跡、過去の注文履歴など、月次や年次で定期的に一括削除する運用がある。
クエリが常にパーティションキーで絞り込まれる場合： 頻繁に実行される重要なクエリの大部分で、WHERE 句にパーティションキーが含まれている。

一方で、500万行未満の比較的小さなテーブルにはパーティショニングを適用すべきではありません。MySQLのメタデータ管理オーバーヘッドにより、通常のテーブルよりもクエリ性能が低下する恐れがあります。

特に注意すべきリスクとして、WHERE 句にパーティションキーを含まないクエリを実行すると、MySQLはすべてのパーティションを順にスキャン（scatter-gather query）しなければならなくなります。この処理は莫大なCPUリソースとI/Oを消費し、設計時の高速化の恩恵が完全に失われてしまいます。