MySQL 5.5 から強化されたパーティショニング機能を試してみたのですが、パーティションに関する制約が多くて苦戦したので、やり方をメモしておきます。
今回やりたいこと – ログデータの肥大化を防ぐ（ログローテート）
日々溜まる膨大なログテーブルのレコードをパーティショニングして、古くなったログを削除してみます。
レコードの削除は DELETE FROM でも出来ますが、パーティショニングを活用すると高速にレコードを削除できます！内部的には DROP TABLE と似たような動作で高速にレコードを削除しているようです。
まずはパーティションの追加から削除まで、ひと通り試してみます。
パーティション確認用のテーブルを生成
まずはログを保存する logs というテーブルを作成します。
CREATE TABLE `logs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `log` varchar(255) NOT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`,`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
このとき、パーティショニングのキーになる項目は全てユニークキーの一部になっている必要があります。そうしておかないとパーティションを作れません。
なので今回は id と created カラムを主キーにしました。
created カラムがレコード追加日を格納するカラムになります。
このカラムのデータを使ってパーティショニングしていきます。
まずは初期パーティションを作成
pmax という名前のパーティションを作ります。
ALTER TABLE logs PARTITION BY RANGE COLUMNS (`created`) ( PARTITION pmax VALUES LESS THAN MAXVALUE );
パーティション振り分け条件として LESS THAN MAXVALUE を指定していますので、このパーティションに全てのレコードが追加されることになります。これだけだと意味がないですが、何かしら一つパーティションを作っておかないとパーティションの追加が出来ませんので何も考えずに作っておきます。
パーティションを追加
パーティションを再編成する REORGANIZE PARTITION 文を使ってパーティションを追加します。
先ほど作成した pmax パーティションを、 p20131114 と pmax パーティションの二つに分割してパーティションを追加します。
ALTER TABLE logs REORGANIZE PARTITION pmax INTO (
PARTITION p20131114 VALUES LESS THAN ('2013-11-14') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);
パーティション分割なんかせずに ADD PARTITION でパーティションを追加すればいいじゃんと思うかもしれないですが、既存のパーティションの後ろにしか追加できない仕様になってるので pmax パーティションがある以上は追加できないです。
なので、pmax パーティションを分割する形でパーティションを追加していきます。
試しにログレコードを挿入してパーティションごとに振り分けられるか確認してみる
この状態で試しにログデータを三件追加します。
INSERT INTO `logs` (`id`, `log`, `created`) VALUES(null, 'sample log message', '2013-11-13 00:00:00'); INSERT INTO `logs` (`id`, `log`, `created`) VALUES(null, 'sample log message', '2013-11-14 00:00:00'); INSERT INTO `logs` (`id`, `log`, `created`) VALUES(null, 'sample log message', '2013-11-15 00:00:00');
データを挿入したら各パーティションに何件のデータが格納されているか確認します。確認したいテーブル名が logs の場合 WHERE TABLE_NAME = ‘logs’ とします。
SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'logs';
上記 SQL の結果です。
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
|---|---|---|---|---|
| test_partition | logs | p20131114 | 1 | 1 |
| test_partition | logs | pmax | 2 | 2 |
p20131114 パーティションに 1レコード（2013-11-13 のレコード）、
pmax パーティションに 2レコード（2013-11-14 と 2013-11-15のレコード）が属していることが分かりました。
パーティション操作をしていて状態が分からなくなったりしたら見てみるといいと思います。
パーティションとそれに属するレコードを削除
ALTER TABLE logs DROP PARTITION p20131114;
これで created が 2013-11-14 のレコードが削除されます。
MySQL パーティショニングのハマりどころ
- パーティショニングキーのカラムはユニークキーの一部になっている必要がある
- パーティションに属するレコードしか存在できない（INSERT できない）
- パーティションを追加する場合、既にあるパーティションの後ろにしか追加できない
- パーティションが一つもない場合 ADD PARTITION できない
主な制約としてこのような制約があります。知らない苦戦すると思います（しましたｗ）。なのでパーティショニングを使った運用する場合はテーブル設計の段階からよく考えて行うべきだと思います。
参考ドキュメント
MySQL のドキュメントはここらへんが参考になりました。