Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

by taroshin
1 / 17

参考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; で移行して下さいとのことです。


fullspeed
独自のアドテクノロジーにより、DSP、ソーシャルメディア、SEO、リスティング、アフィリエイトを展開
https://www.fullspeed.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした