MySQL

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

More than 1 year has passed since last update.

参考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';

パーティションの削除

※データも消えます
sql
ALTER TABLE sample_tables DROP PARTITION p201501;


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

※こちらはデータは消えません
sql
ALTER TABLE sample_tables REMOVE PARTITIONING;


パーティションの追加

※追加は基本的に今あるパーティションの後ろにしか出来ない
sql
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

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

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


おまけ

  • パーティションを指定して参照
    sql
    SELECT * FROM sample_tables PARTITION (p201501, p201502) WHERE c < 5;

  • 同じ構造を持つテーブル間でパーティションの移動
    sql
    ALTER TABLE sample_tables EXCHANGE PARTITION p201501 WITH TABLE t;