一定のルールでテーブルに格納されるデータを分割して保存するようなイメージの機能です。
巨大テーブルで走らせるDELETEはとても負荷の大きい処理ですが、パーティションごと削除することでとても高速に不要データの切り離し処理を行うことができるようになります。
ほかにも、上手にパーティションを設定してクエリをチューニングすると、インデックスを張ったときのようなパフォーマンス向上にもつながります。
特定のパーティションを対象に検索をするクエリの例です。
スキャン対象がパーティション内に限られるのでクエリの高速化が見込めます。
SELECT * FROM logs PARTITION (p2017, p2018) WHERE productid = 3;
##パーティショニングの種類
いろいろなパーティショニングの種類がありますが、おおむね以下の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)
);