MySQLのパーティショニング

技術

パーティショニングはMySQL 5.1で追加された新機能です。

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

種類 内容
RANGE パーティションごとに範囲を指定して振り分けます。
LIST パーティションごとに格納する値で振り分けます。
HASH 1つのカラムの値を式の結果で振り分けます。式を指定しないとMOD関数を使用します。また、大量のデータにはLINEAR HASHが用意されています。
KEY 1つ以上のカラムの値をMD5関数やPassword関数で評価して分割します。

パーティショニングの制約

  • パーティショニング表現に含まれるカラムは、そのテーブル内に存在するすべてのプライマリキーとユニークキーに含まれていなければなりません。
    • ただしプライマリキー、またはユニークキーが存在しない場合は例外です。
  • パーティショニング表現は連続した整数値をとるものでなければなりません。
    • たとえば日付を利用する場合、TO_DAYS()関数などを使って整数値に直す必要があります。
  • 作成できるパーティションの上限は1テーブルにつき1024個までです。

パーティショニングがサポートされているかの確認

mysql> SHOW VARIABLES LIKE '%PARTITION%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.01 sec)

パーティショニングは、コンパイル時に--with-partitionを指定してバイナリレベルで組み込む必要があるので、個人でコンパイルする場合は注意が必要です。

新しいパーティションドテーブルを作成する

CREATE TABLE logs (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(256) NOT NULL,
  data VARCHAR(256) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY(id, logged_at),
  INDEX(name)
)
PARTITION BY RANGE(TO_DAYS(created_at)) (
  PARTITION p201101 VALUES LESS THAN (TO_DAYS('2011-02-01')),
  PARTITION p201102 VALUES LESS THAN (TO_DAYS('2011-03-01')),
  PARTITION p201103 VALUES LESS THAN (TO_DAYS('2011-04-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

パーティションドテーブルの作成は、通常のCREATE TABLE文の後にパーティション設定ルールの記述を追加するだけです。このとき、パーティショニング表現に含まれるカラムはそのテーブル内に存在するプライマリキー、ユニークキー、またはその一部でなければなりません。

パーティション設定ルールの最後の行にある"VALUES LESS THAN MAXVALUE"はキャッチオール節と呼ばれています。MAXVALUEは表現可能な最大の整数を表し、created_at≧'2011-04-01'のデータをpmaxに格納します。
この行がなかった場合、created_at≧'2011-04-01'のデータを格納する場合にエラーが発生します。

既存のテーブルをパーティションに分割する

LISTパーティショニング、作成年で分割する
ALTER TABLE logs PARTITION BY LIST (YEAR(created_at)) (
  PARTITION p2011 VALUES IN (2011),
  PARTITION p2012 VALUES IN (2012)
);
RANGEパーティショニング、作成月で分割する
ALTER TABLE logs PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p201101 VALUES LESS THAN (TO_DAYS('2011-02-01')),
  PARTITION p201102 VALUES LESS THAN (TO_DAYS('2011-03-01')),
  PARTITION p201103 VALUES LESS THAN (TO_DAYS('2011-04-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

ADD PARTITIONによるパーティションの追加

ALTER TABLE logs ADD PARTITION (
  PARTITION p201104 VALUES LESS THAN (TO_DAYS('2011-05-01'))
);

既存のパーティションの後ろに新しいパーティションを追加します。

RANGEパーティショニングされているテーブルにおいて、ADD PARTITIONは既存のパーティションの「後」にしかパーティションを追加できません。そのため、キャッチオール節（MAXVALUE）が既に存在する場合は（MAXVALUEより後の値は存在し得ないために）、この方法でパーティションを追加することができません。

REORGANIZE PARTITIONによるパーティションの再分割

ALTER TABLE logs REORGANIZE PARTITION pmax INTO (
  PARTITION p201104 VALUES LESS THAN (TO_DAYS('2010-05-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

既存のパーティションを再分割します。既存のデータが大量にある場合、REORGANIZE PARTITIONによる再振り分け処理にはそれなりの時間がかかります。

REORGANIZE PARTITIONによるパーティションの結合

ALTER TABLE logs REORGANIZE PARTITION p201101, p201102 INTO (
  PARTITION p201101_02 VALUES LESS THAN (TO_DAYS('2011-03-01'))
);

既存のパーティションを結合します。

REORGANIZE PARTITIONによるパーティションの再構成

ALTER TABLE logs REORGANIZE PARTITION p201101, p201102, p201103, pmax INTO (
    PARTITION p201101_02 VALUES LESS THAN (TO_DAYS('2011-03-01')),
    PARTITION p201103_04 VALUES LESS THAN (TO_DAYS('2011-05-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

既存のパーティションを複数まとめて、新しく複数のパーティションに再構成することもできます。

パーティションの削除

ALTER TABLE logs DROP PARTITION p201101;

パーティションとそこに含まれるデータをまとめて削除します。パーティションの削除は内部的にはテーブルのDROPとほぼ同じであるため、ストレージエンジンがMyISAMやInnoDBであれば、格納されているデータ件数によらず一瞬で完了します。

パーティショニングの解除

ALTER TABLE logs REMOVE PARTITIONING;

パーティショニングを解除して、そこに含まれるデータをひとつのテーブルにまとめます。既存のデータが大量にある場合、REMOVE PARTITIONINGによる結合処理にはそれなりの時間がかかります。