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

16814 ワード
MySQL MySQL テキストリンク

参考URL

パーティショニングとは

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

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はエラーになる

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

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

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

おまけ

  • パーティションを指定して参照 
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; で移行して下さいとのことです。

LeetCode 387. First Unique Character in a String(文字列の最初の一意文字)--c言語
秩序あるkey-valueの値と保存の実装