MySQL 日付別パーティショニングの運用

MySQL :: MySQL 5.1 リファレンスマニュアル :: 15 パーティショニング
http://dev.mysql.com/doc/refman/5.1/ja/partitioning.html

実験的にやってみただけでノウハウとして固まってはいないのですが、現状の知識をまとめてみたいと思います。

前提

MySQL 5.1以降
- 検証した環境は5.1.47
日付ごとにパーティショニング
カラムは一意な"id"と作成日付の"created"があるとする

準備

idとcreatedを複合でプライマリキーにする
- パーティショニングの条件はプライマリキーに含める必要がある
- idがAUTO_INCREMENTであればPRIMARY KEY (id, created)の順のみ
createdをTO_DAYS()してパーティショニングする
- 大枠と個別のパーティションの両方でTO_DAYS()する
- UNIX_TIMESTAMP()とどちらがいいのかは未検証
- 5.5からはDATETIME型のままできるとのこと
TO_DAYS()した値は認識しづらいので、COMMENTで年月日がわかるようにしておく
運用開始時の取りうる日付から数日間先まで追加しておく
- 週単位/月単位/年単位のどれが適しているかは未検証
バッファとしての VALUES LESS THAN MAXVALUE は使わない
- 新たなパーティションの追加ができなくなるため
- REORGANIZE PARTITIONで再配置できるが時間がかかる

SQL例

-- 大枠追加
ALTER TABLE table PARTITION BY RANGE (TO_DAYS(created))
	(PARTITION p20110327 VALUES LESS THAN (TO_DAYS('2011-03-28 00:00:00')) COMMENT = '2011-03-28 00:00:00');
-- 値追加
ALTER TABLE table PARTITION ADD PARTITION
	(PARTITION p20110328 VALUES LESS THAN (TO_DAYS('2011-03-29 00:00:00')) COMMENT = '2011-03-29 00:00:00');

スキーマ例

-- SHOW CREATE TABLEの結果
CREATE TABLE `game_histories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `score` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(created))
 PARTITION p20110327 VALUES LESS THAN (734589) COMMENT = '2011-03-28 00:00:00' ENGINE = InnoDB,
 PARTITION p20110328 VALUES LESS THAN (734590) COMMENT = '2011-03-29 00:00:00' ENGINE = InnoDB,
 PARTITION p20110329 VALUES LESS THAN (734591) COMMENT = '2011-03-30 00:00:00' ENGINE = InnoDB,
 PARTITION p20110330 VALUES LESS THAN (734592) COMMENT = '2011-03-31 00:00:00' ENGINE = InnoDB,
 PARTITION p20110331 VALUES LESS THAN (734593) COMMENT = '2011-04-01 00:00:00' ENGINE = InnoDB */

運用

パーティションの削除
- 不要になったパーティションに対してDROP PARTITION
パーティションの追加
- 先回りして追加しておく必要がある
- 対象となるパーティションが存在しない場合INSERTに失敗する
cron自動実行
- n日おきにn個追加/削除というようなジョブを登録して実行
- 問題なく追加/削除できているかを確認する仕組みを作る
パーティション自体が不要になったらREMOVE PARTITIONING

SQL例

-- 値削除
ALTER TABLE table DROP PARTITION p20110327;
-- 大枠削除
ALTER TABLE table REMOVE PPARTITIONING;

-- データ分布確認
SELECT table_schema, table_name, partition_name, partition_ordinal_position, table_rows FROM information_schema.partitions WHERE table_name = 'game_histories';
-- 刈り込み(pruning)確認
EXPLAIN PARTITIONS SELECT * FROM game_histories WHERE created < '2011-03-28 00:00:00';