2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Posted at

一定のルールでテーブルに格納されるデータを分割して保存するようなイメージの機能です。
巨大テーブルで走らせる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)
);
2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?