MySQL のパーティショニングでクエリを高速化する

テーブルの行数が数千万〜数億を超えると、インデックスを適切に設計してもクエリの応答時間が悪化し始める。パーティショニングはテーブルを物理的に分割し、クエリが必要なパーティションだけを走査する仕組みだ。適切に設計すれば、大規模テーブルのクエリ性能と運用効率を同時に改善できる。

パーティショニングの基本的な考え方

パーティショニングとは、1 つの論理テーブルを複数の物理的な領域に分割して格納する手法を指す。アプリケーションからは 1 つのテーブルとして見えるが、内部的には条件に応じて異なるパーティションにデータが振り分けられている。

クエリの WHERE 句にパーティションキーが含まれていれば、MySQL は該当するパーティションだけを読み取る。これを「パーティションプルーニング」と呼び、フルテーブルスキャンの対象を劇的に減らせる仕組みだ。

パーティションの種類

MySQL が対応しているパーティション方式は 4 種類ある。データの特性とクエリパターンに応じて使い分ける。

実務で最もよく使われるのは RANGE パーティションだ。特に日付ベースの分割はログテーブルや注文テーブルで広く採用されている。

RANGE パーティションの実装

注文テーブルを月ごとにパーティション分割する例を見てみよう。

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    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
);

ここで重要なのは主キーの定義だ。InnoDB のパーティションテーブルでは、パーティションキーがすべてのユニークインデックス（主キーを含む）に含まれている必要がある。そのため PRIMARY KEY (id, order_date) のように order_date を主キーに加えている。

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

パーティションが実際に効いているかどうかは EXPLAIN で確認できる。partitions 列にアクセスされるパーティションが表示される。

EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';

結果の partitions 列に p202403 だけが表示されれば、プルーニングが正しく機能している証拠だ。逆に全パーティションが列挙されている場合は、WHERE 句の条件がパーティションキーと合致していない可能性がある。

プルーニングが効くためには、WHERE 句の条件がパーティション定義の式と一致する必要がある。たとえば YEAR(order_date) * 100 + MONTH(order_date) で定義したなら、WHERE 句でも同じ形式で条件を書くか、MySQL が推論できる範囲条件を使うことが求められる。

LIST パーティションの活用

地域ごとにデータを分割したい場合は LIST パーティションが適している。

CREATE TABLE sales (
    id BIGINT NOT NULL AUTO_INCREMENT,
    region_code INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, region_code)
) PARTITION BY LIST (region_code) (
    PARTITION p_east VALUES IN (1, 2, 3),
    PARTITION p_west VALUES IN (4, 5, 6),
    PARTITION p_south VALUES IN (7, 8, 9),
    PARTITION p_north VALUES IN (10, 11, 12)
);

LIST パーティションでは、定義されていない値を挿入しようとするとエラーになる。新しい地域コードを追加する際は、事前にパーティション定義を更新しておく必要がある点に注意しよう。

パーティション管理の運用

時系列の RANGE パーティションでは、定期的なパーティションの追加と古いパーティションの削除が運用の中心になる。

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

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

DROP PARTITION は DELETE 文とは異なり、パーティション単位で瞬時にデータを削除できる。数億行のデータでも一瞬で完了するため、大量データの定期削除に極めて有効だ。これは DELETE + OPTIMIZE TABLE の組み合わせよりもはるかに高速で、ディスク I/O への負荷も最小限に抑えられる。

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

パーティショニングは万能ではなく、いくつかの制約を理解しておく必要がある。

特にパーティションキーとクエリパターンの不一致は深刻な問題になる。WHERE 句でパーティションキーを指定しないクエリが多い場合、すべてのパーティションを走査するためパーティションなしよりも遅くなるケースもある。

パーティショニングが有効な場面

パーティショニングの導入を検討すべき典型的なシナリオをまとめておこう。

導入前にまずインデックスの最適化で対応できないかを検討し、それでも限界がある場合にパーティショニングを選択肢に入れるのが堅実なアプローチだ。EXPLAIN でプルーニングの効果を必ず確認し、想定どおりにパーティションが絞り込まれることを検証してから本番に適用しよう。