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 のドキュメントはここらへんが参考になりました。

• 15.3. パーティショニング管理
• 15.3.1. RANGE と LIST パーティションの管理

数カ月前にをPHPで予約システム制作する機会があったのですが、予約枠どうしが重複しないかチェックしなければならない何度か場面がありました。

よくあるホテルの部屋の予約システムや、座席の予約、病院の予約などのあれです。

例えば新規で新たに予約する際など、予約時間帯が既存の予約枠と衝突しないかをチェックする必要がありました。でも二つの時間がかぶるかどうかを判定するのってどうやるんだと思って結構悩みました…。

恐らく愚直に判定しようとすると && や || を複数個並べた判定になると思うのですが、最近とても簡潔に書けることを知りました／(^o^)＼

実際のコードサンプルは以下です。引数は全て整数のUNIXタイムスタンプを想定しています。

/**
 * ２つの時間帯が重複しているかをチェックする
 *
 * @param int $startTime1 一つ目の時間範囲の開始時間
 * @param int $endTime1 一つ目の時間範囲の終了時間
 * @param int $startTime2 二つ目の時間範囲の開始時間
 * @param int $endTime2 二つ目の時間範囲の終了時間
 * @return bool 重複している場合はtrueを返す
 */
function isTimeDuplication($startTime1, $endTime1, $startTime2, $endTime2) {
	return ($startTime1 < $endTime2 && $startTime2 < $endTime1);
}

$startTime1 = strtotime('2011/11/25 10:00:00');
$endTime1 = strtotime('2011/11/25 11:00:00');
$startTime2 = strtotime('2011/11/25 10:30:00');
$endTime2 = strtotime('2011/11/25 11:30:00');
$isDuplicate = isTimeDuplication($startTime1, $endTime1, $startTime2, $endTime2); //重複しているので真が返る