MySQLパーティショニングまとめ

参考文献

公式サイト

パーティショニングとは

大量に保存されているレコードを指定したカラムの値によって、水平分割することで大量データの中から目的のレコードを高速で検索することができようになります。トランザクションデータのような常に増えていくデータで、大量レコード（100万件以上）が保存されたテーブルを「年月」単位で分割する、などがよくある使い方ではないかと思います。

↑の図のTable Aに対してあらかじめ「どのレコードをどのパーティションに保存するか」という条件を指定しておくことで、振り分けられるイメージです。

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

RANGE パーティショニング

このタイプのパーティショニングは、指定された範囲に含まれるカラム値に基づいて、行をパーティションに割り当てます。

LIST パーティショニング

RANGE によるパーティショニングに似ていますが、別個の値のセットのいずれかに一致するカラムに基づいて、パーティションが選択されます。

HASH パーティショニング

このタイプのパーティショニングでは、テーブルに挿入される行内のカラム値を操作するユーザー定義式によって返される値に基づいて、パーティションが選択されます。関数は、負ではない整数値を返す MySQL の有効な式で構成できます。このタイプを拡張した LINEAR HASH も使用できます。

KEY パーティショニング

このタイプのパーティショニングは、HASH によるパーティショニングに似ていますが、評価される 1 つ以上のカラムのみを指定し、MySQL サーバーが独自のハッシュ関数を提供します。MySQL によって提供されるハッシュ関数ではカラムデータ型に関係なく整数結果が保証されるため、これらのカラムに整数以外の値が含まれていてもかまいません。このタイプを拡張した LINEAR KEY も使用できます。

RANGE COLUMNS パーティショニング

MySQL 5.5から1つまたは複数のカラムを指定することができ、それに応じてパーティション振り分けの判定も1つまたは複数の条件を指定することができるようになりました。
※公式ドキュメントの例を見ていただくのが早いかと思います。

指定可能なカラムのデータ型：

•  TINYINT、SMALLINT、MEDIUMINT、INT (INTEGER)、および BIGINT
• DATE および DATETIME
•  CHAR、VARCHAR、BINARY、および VARBINARY

LIST COLUMNS パーティショニング

こちらもRANGE COLUMNSと同じく、1つまたは複数カラム指定でき、振り分け条件もそれに応じて複数指定できます。また条件の判定はLISTと同じく「値のセットのいずれかに一致する」です。

パーティションの最大数

パーティションの最大数、MySQL 5.6.7より前は、NDB ストレージエンジンを使用しないテーブルで可能な最大パーティション数は 1024 でした。
MySQL 5.6.7 以降は、この制限は 8192 パーティションに増えています。

プライマリーキーの変更

MySQLのパーティショニングには前提があり
パーティショニングに使いたいカラム（今回はdateカラム）が
PRIMAY KEYに含まれていなければいけないという条件があります。

そこでPRIMARY KEYにdateを含めたいのですが
既存テーブルのidカラムにPRIMARY KEYが設定されていたので
これをいったん削除してからidとdateでPRIMRY KEYを設定しています。

ALTER TABLE sample_tables DROP PRIMARY KEY, ADD PRIMARY KEY(id, date);

パーティション設定

• 前もって何年も未来のパーティションを作っておく方法がスタンダード
• 評価式は整数を返す必要があり、TO_DAYS関数は日数を返却する関数

ALTER TABLE sample_tables
PARTITION BY RANGE (TO_DAYS(date)) (
    PARTITION p201601 VALUES LESS THAN (TO_DAYS('2016/02/01 00:00:00')),
    PARTITION p201602 VALUES LESS THAN (TO_DAYS('2016/03/01 00:00:00')),
    PARTITION p201603 VALUES LESS THAN (TO_DAYS('2016/04/01 00:00:00')),
    PARTITION p201604 VALUES LESS THAN (TO_DAYS('2016/05/01 00:00:00')),
    PARTITION p201605 VALUES LESS THAN (TO_DAYS('2016/06/01 00:00:00')),
    PARTITION p201606 VALUES LESS THAN (TO_DAYS('2016/07/01 00:00:00')),
    PARTITION p201607 VALUES LESS THAN (TO_DAYS('2016/08/01 00:00:00')),
    PARTITION p201608 VALUES LESS THAN (TO_DAYS('2016/09/01 00:00:00')),
    PARTITION p201609 VALUES LESS THAN (TO_DAYS('2016/10/01 00:00:00')),
    PARTITION p201610 VALUES LESS THAN (TO_DAYS('2016/11/01 00:00:00')),
    PARTITION p201611 VALUES LESS THAN (TO_DAYS('2016/12/01 00:00:00')),
    PARTITION p201612 VALUES LESS THAN (TO_DAYS('2017/01/01 00:00:00')),
    PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017/02/01 00:00:00')),
    PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017/03/01 00:00:00')),
    PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017/04/01 00:00:00')),
    PARTITION p201704 VALUES LESS THAN (TO_DAYS('2017/05/01 00:00:00')),
    PARTITION p201705 VALUES LESS THAN (TO_DAYS('2017/06/01 00:00:00')),
    PARTITION p201706 VALUES LESS THAN (TO_DAYS('2017/07/01 00:00:00')),
    PARTITION p201707 VALUES LESS THAN (TO_DAYS('2017/08/01 00:00:00')),
    PARTITION p201708 VALUES LESS THAN (TO_DAYS('2017/09/01 00:00:00')),
    PARTITION p201709 VALUES LESS THAN (TO_DAYS('2017/10/01 00:00:00')),
    PARTITION p201710 VALUES LESS THAN (TO_DAYS('2017/11/01 00:00:00')),
    PARTITION p201711 VALUES LESS THAN (TO_DAYS('2017/12/01 00:00:00')),
    PARTITION p201712 VALUES LESS THAN (TO_DAYS('2018/01/01 00:00:00'))
);

MySQL 5.5からはRANGE COLUMNSが使える

つまりTO_DAYを使う必要がなくなった

ALTER TABLE sample_tables
PARTITION BY RANGE COLUMNS(date) (
    PARTITION p201303 VALUES LESS THAN ('2013-04-01 00:00:00'),
    PARTITION p201304 VALUES LESS THAN ('2013-05-01 00:00:00'),
    PARTITION p201305 VALUES LESS THAN ('2013-06-01 00:00:00'),
情報をあ    PARTITION p201306 VALUES LESS THAN ('2013-07-01 00:00:00')
);

パーティション設定の確認

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'sample_tables';

パーティションの削除

※データも消えます

ALTER TABLE sample_tables DROP PARTITION p201501;

非パーティションのテーブルに戻す

※こちらはデータは消えません

ALTER TABLE sample_tables REMOVE PARTITIONING;

パーティションの追加

※追加は基本的に今あるパーティションの後ろにしか出来ない

ALTER TABLE sample_tables ADD PARTITION (
    PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022/02/01 00:00:00'))
    );

注意点

• MySQL5.1からパーティショニング機能が追加されている
• MySQL8.0では InnoDB および NDB ではパーティショニングがサポートされているが、MyISAMではサポートしていない
• パーティション化されていないテーブルと同様に、インデックスを適切に使用することで、パーティション化されたテーブルに対する照会速度が大幅に向上することがあります
• 参照する際はパーティションを分けているカラム（パーティショニングキー）を使ってWHERE文を作るようにし、参照するパーティションを絞り込むようにする（パーティションプルーニング）
• クエリーキャッシュはパーティション化されたテーブルではサポートされません
• InnoDBの外部キー制約を使っているテーブルはパーティショニングできません
• パーティション定義されていない未来のデータのINSERTはエラーになる

パーティショニングキーについて

パーティション化されたテーブルのパーティショニング式で使用されるすべてのカラムは、
テーブルが持つことができるすべての一意キーの一部である必要があります。
つまり、テーブルのすべての一意キーは、
テーブルのパーティショニング式内のすべてのカラムを使用する必要があります(これには、テーブルの主キーも含まれます。

https://dev.mysql.com/doc/refman/8.0/ja/partitioning-limitations-partitioning-keys-unique-keys.html

つまり、パーティショニングのキーにしたいカラムを、プライマリーキーに含める必要があります。

おまけ

• パーティションを指定して参照

SELECT * FROM sample_tables PARTITION (p201501, p201502) WHERE c < 5;

• 同じ構造を持つテーブル間でパーティションの移動

ALTER TABLE sample_tables EXCHANGE PARTITION p201501 WITH TABLE t;