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


参考URL

https://dev.mysql.com/doc/refman/5.6/ja/partitioning.html



パーティショニングとは

Package Diagram (2).png



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


RANGE パーティショニング

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


LIST パーティショニング

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


HASH パーティショニング

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


KEY パーティショニング

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



パーティションの最大数

パーティションの最大数、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からパーティショニング機能が追加されている

  • パーティショニング操作、クエリー、および更新操作は通常、InnoDB または NDB テーブルより MyISAM テーブルで高速である傾向があります

  • パーティション化されていないテーブルと同様に、インデックスを適切に使用することで、パーティション化されたテーブルに対する照会速度が大幅に向上することがあります

  • 参照する際はパーティションを分けているカラム(パーティショニングキー)を使ってWHERE文を作るようにし、参照するパーティションを絞り込むようにする(パーティションプルーニング)

  • クエリーキャッシュはパーティション化されたテーブルではサポートされません

  • InnoDBの外部キー制約(FOREIGN KEYで定義するやつ)を使っているとパーティショニングできません(エラーでこける)

  • MyISAMの場合テーブルロックではなくパーティションロックになる

  • パーティション定義されていない未来のデータのINSERTはエラーになる



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

https://dev.mysql.com/doc/refman/5.6/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;



InnoDB Nativeパーティショニングについて (2019/01追記)

MySQL 5.7.17から the generic partitioning handler が非推奨になり、MySQL 8から使えなくなるので、(“native”) partitioning handler を使えと。

参照元:https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

InnoDB と NDB ストレージエンジンしかNativeパーティショニングをサポートしないので、例えばMyISAMを使っている場合は ALTER TABLE table_name ENGINE = INNODB; で移行して下さいとのことです。