MySQLでパーティショニングする

4539 ワード
パーティション mariadb MySQL MySQL テキストリンク

一定のルールでテーブルに格納されるデータを分割して保存するようなイメージの機能です。
巨大テーブルで走らせるDELETEはとても負荷の大きい処理ですが、パーティションごと削除することでとても高速に不要データの切り離し処理を行うことができるようになります。
ほかにも、上手にパーティションを設定してクエリをチューニングすると、インデックスを張ったときのようなパフォーマンス向上にもつながります。

特定のパーティションを対象に検索をするクエリの例です。
スキャン対象がパーティション内に限られるのでクエリの高速化が見込めます。

SELECT * FROM logs PARTITION (p2017, p2018) WHERE productid = 3;

パーティショニングの種類

いろいろなパーティショニングの種類がありますが、おおむね以下の３種類で大概のことは足るように思います。

RANGEパーティショニング

パーティショニングに使用するカラムの値の範囲を指定してパーティショニングします。
ログテーブルでデータを期間別に管理したり、データをパージすることを見込んでいる場合などに使います。

LISTパーティショニング

パーティショニングに使用するカラムの値のバリエーションでパーティショニングします。
性別、地域などのある程度とりうるバリエーションが決まっているデータを分割したい場合に使います。

HASHパーティショニング

パーティショニングに使用するカラムの数字をパーティション数で割り算したときの剰余（余り）の数字でグルーピングしてパーティショニングします。

パーティショニングの定義

以下のテーブルにパーティショニングを設定する例を考えます。
MySQL系のパーティション機能で対象にできるカラムは主キー（Primary Key）となっているものに限られるのでテーブル設計から練っていく必要があります。

CREATE TABLE logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    userid BIGINT UNSIGNED NOT NULL,
    productid BIGINT UNSIGNED NOT NULL,
    created DATETIME NOT NULL,
    amount INT UNSIGNED NOT NULL,
    PRIMARY KEY (id, userid, productid, created)
) ENGINE=InnoDB;

RANGEパーティショニング

createdを使って年ごとに分割します。

ALTER TABLE logs PARTITION BY RANGE (YEAR(created)) (
    PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
    PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
    PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

※MAXVALUEを使ったパーティションを設定すると新しいパーティションを追加することができなくなるので注意です。

LISTパーティショニング

productidを使って分割します。

ALTER TABLE logs PARTITION BY LIST (productid) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3),
    PARTITION p4 VALUES IN (4),
    PARTITION p5 VALUES IN (5)
);

HASHパーティショニング

idを使って20パーティションに分割します。

ALTER TABLE logs PARTITION BY HASH (id) PARTITIONS 20;

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

新しく登録しようとするデータが入ることができるパーティションが存在しないとエラーになりますので、MAXVALUEのないRANGEパーティションは定期実行バッチなどでパーティションを増やしていく必要があります。
また、LISTパーティションでデータのバリエーションが増えるときも同様にパーティションを増やす必要があります。

パーティションの追加

パーティションの追加はただ器を用意するだけでデータの分類を伴わないため一瞬で完了します。

ALTER TABLE logs ADD PARTITION ( PARTITION p2021 VALUES LESS THAN (2021) ENGINE = InnoDB );

パーティションの削除

パーティションを削除すると、そのパーティションに所属するデータも一緒に削除されます。

ALTER TABLE logs DROP PARTITION p2017;

パーティションの再構成

再構成といっても、既存のパーティショニングを土台から組み替えるようなことではなく、あるパーティションに入っているデータを細分化させる処理となります。

※MAXVALUEを何とかしたい場合の例

ALTER TABLE logs REORGANIZE PARTITION pmax INTO (
    PARTITION p2021 VALUES LESS THAN (2021) ENGINE=InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

※古いデータを細分化したい場合の例
p2017が一番手前のパーティションとした場合。

ALTER TABLE logs REORGANIZE PARTITION p2017 INTO (
    PARTITION p2011 VALUES LESS THAN (2015),
    PARTITION p2012 VALUES LESS THAN (2016),
    PARTITION p2013 VALUES LESS THAN (2017)
);
C++:標準ライブラリタイプstring
SQLストアド・プロシージャのエントリー・ベース(プロセス制御)について