MySQL5.5でパーティショニング使って時系列のデータを分散する

はい、乙カレーさまです。寒い日が続きますね。
そしてMySQLも続きそうな私です。

前回はトリガをやってみましたが、今度はパーティショニングをしてみます。

パーティショニングとは

パーティショニングは、特定のカラム情報を使って、テーブルを論理的／物理的に自動で分ける事で管理を簡単にしたり、パフォーマンスを確保する機能のことです。例えば今回は、更新日時でパーティショニングを行うことで、特定期間のデータを削除する等の運用が簡単になります。

パーテションの設定

プライマリキーの設定

まず既存のテーブルの場合は最初にパーテションを行うカラムがプライマリキーが含まれていないといけないので貼り直します。

mysql> ALTER TABLE usermaster_cs DROP PRIMARY KEY, ADD PRIMARY KEY(user_id, upd_datetime);

新規テーブルの場合

これから作るテーブルをパーティショニング対応するならCREATE TABLE文に含めることが出来ます。
この場合はupd_datetimeカラムを使用して、2013年1月からの月ごとのデータが入る様にパーティショニングを設定します。

mysql> CREATE TABLE `usermaster_cs` (
	  `user_name` varchar(255) NOT NULL,
	  `user_id` int(10) NOT NULL,
	  `type` int(10) DEFAULT NULL,
	  `status` int(10) DEFAULT NULL,
	  `upd_datetime` datetime NOT NULL,
	  PRIMARY KEY (`user_id`,`upd_datetime`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(upd_datetime)
(PARTITION p201301 VALUES LESS THAN ('2013-02-01 00:00:00') COMMENT = '2013-01',
 PARTITION p201302 VALUES LESS THAN ('2013-03-01 00:00:00') COMMENT = '2013-02' ,
 PARTITION p201303 VALUES LESS THAN ('2013-04-01 00:00:00') COMMENT = '2013-03' ,
 PARTITION p201304 VALUES LESS THAN ('2013-05-01 00:00:00') COMMENT = '2013-04' ,
 PARTITION p201305 VALUES LESS THAN ('2013-06-01 00:00:00') COMMENT = '2013-05' ,
 PARTITION p201306 VALUES LESS THAN ('2013-07-01 00:00:00') COMMENT = '2013-06' ,
 PARTITION p201307 VALUES LESS THAN ('2013-08-01 00:00:00') COMMENT = '2013-07' ,
 PARTITION p201308 VALUES LESS THAN ('2013-09-01 00:00:00') COMMENT = '2013-08' ,
 PARTITION p201309 VALUES LESS THAN ('2013-10-01 00:00:00') COMMENT = '2013-09' ,
 PARTITION p201310 VALUES LESS THAN ('2013-11-01 00:00:00') COMMENT = '2013-10' ,
 PARTITION p201311 VALUES LESS THAN ('2013-12-01 00:00:00') COMMENT = '2013-11' ,
 PARTITION p201312 VALUES LESS THAN ('2014-01-01 00:00:00') COMMENT = '2013-12' ,
 PARTITION p201401 VALUES LESS THAN ('2014-02-01 00:00:00') COMMENT = '2014-01' ,
);

既存テーブルの場合

既存のテーブルに設定するならALTER TABLE を使いましょう。

mysql> ALTER TABLE usermaster_cs
PARTITION BY RANGE COLUMNS(upd_datetime) (
PARTITION p201301 VALUES LESS THAN ('2013-02-01 00:00:00') COMMENT = '2013-01',
 PARTITION p201302 VALUES LESS THAN ('2013-03-01 00:00:00') COMMENT = '2013-02' ,
 PARTITION p201303 VALUES LESS THAN ('2013-04-01 00:00:00') COMMENT = '2013-03' ,
 PARTITION p201304 VALUES LESS THAN ('2013-05-01 00:00:00') COMMENT = '2013-04' ,
 PARTITION p201305 VALUES LESS THAN ('2013-06-01 00:00:00') COMMENT = '2013-05' ,
 PARTITION p201306 VALUES LESS THAN ('2013-07-01 00:00:00') COMMENT = '2013-06' ,
 PARTITION p201307 VALUES LESS THAN ('2013-08-01 00:00:00') COMMENT = '2013-07' ,
 PARTITION p201308 VALUES LESS THAN ('2013-09-01 00:00:00') COMMENT = '2013-08' ,
 PARTITION p201309 VALUES LESS THAN ('2013-10-01 00:00:00') COMMENT = '2013-09' ,
 PARTITION p201310 VALUES LESS THAN ('2013-11-01 00:00:00') COMMENT = '2013-10' ,
 PARTITION p201311 VALUES LESS THAN ('2013-12-01 00:00:00') COMMENT = '2013-11' ,
 PARTITION p201312 VALUES LESS THAN ('2014-01-01 00:00:00') COMMENT = '2013-12' ,
 PARTITION p201401 VALUES LESS THAN ('2014-02-01 00:00:00') COMMENT = '2014-01' ,
);

稼働中ならPercona-Toolkitで提供しているpt-online-schema-changeで無停止で行うことが出来ます。ありがたやありがたや。

pt-online-schema-change --charset="utf8" --set-vars="sql_log_bin=OFF" --execute --alter="
PARTITION BY RANGE COLUMNS(upd_datetime) (
PARTITION p201301 VALUES LESS THAN ('2013-02-01 00:00:00') COMMENT = '2013-01',
(snip)
 PARTITION p201401 VALUES LESS THAN ('2014-02-01 00:00:00') COMMENT = '2014-01' ,
);" h=localhost,D=database,t=usermaster_cs,u=dbuser

パーテション追加

もし、2014年2月分のレンジ追加を行う場合は以下のSQLで可能です。

mysql> ALTER TABLE usermaster_cs
ADD PARTITION (
PARTITION p201412 VALUES LESS THAN ('2014-03-01 00:00:00') COMMENT = '2014-02' ENGINE = InnoDB,
);

実行時はロックがかかります。データが無ければ一瞬で終わりますが、気になる場合は前述のpt-online-schema-changeを使いましょう。

レンジを都度追加していく場合はもしもレンジの追加を忘れていた場合等に、その存在しないパーテションレンジへデータの追加しようとした場合にはデータ追加が出来ない、と言う事になります。

その場合はMAXVALUEを指定して、このレンジよりも最新のデータはこのパーテションレンジで救う事が可能となります。素晴らしい。

mysql> ALTER TABLE usermaster_cs ADD PARTITION (PARTITION pmaxvalue VALUES LESS THAN (MAXVALUE));

その代わり2月分をまとめたい、と言う話になった場合はREORGANIZE PARTITIONでパーテションレンジを分離する必要が出てきます。
どっちが楽かみたいな話はありますが、自動でADDするような仕組みを作るならMAXVALUEは設定しなくてもいいかもしれないですね。

mysql> ALTER TABLE usermaster_cs REORGANIZE PARTITION pmaxvalue INTO (
    PARTITION p201412 VALUES LESS THAN ('2014-03-01 00:00:00') COMMENT = '2014-02',
    PARTITION pmaxvalue VALUES LESS THAN (MAXVALUE)
);

パーテション削除

（2014/2/8：追記　削除完全に書き忘れてたｗ）
2013年1月のデータが要らなくなったんで削除したい、と言う場合はDROP PARTITIONで該当のパーテションを削除したらOKです。
これもそんなに時間かからないですが、ロックかかるので気になる環境であればpt-online-schema-change使いましょう。
当たり前ですけどデータも消えちゃうので注意。

mysql> ALTER TABLE usermaster_cs DROP PARTITION p201301;

データ入れ込み

じゃあデータを入れてみませう。（実際はトリガでデータを入れ込む予定）

mysql> insert into usermaster_cs select * from copy_source_table;

設定の確認

SHOW CREATE TABLE

SHOW CREATE TABLE を使用すると、パーティショニングされたテーブルの作成に使用されたPARTITION句をみると大体わかります。

mysql> SHOW CREATE TABLE usermaster_cs;
(snip)

SHOW TABLE STATUS

SHOW TABLE STATUS を使用するとテーブルがパーティショニングされているかを判定することができます。

mysql> SHOW TABLE STATUS \G
(snip)
 Create_options: partitioned
(snip)

INFORMATION_SCHEMAで確認

下記クエリでusermaster_csにあるパーテションを確認できます。

mysql> SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name='usermaster_cs' \G

EXPLAIN PARTITIONS SELECT

EXPLAIN PARTITIONS SELECT ステートメントを使用するとパーティショニング環境のクエリのEXPLAINが行うことができます。
どのパーティションが SELECT で使用されているか判別できます。

条件を入れない場合は全てのパーテションに対してアクセスが行くのに対して、

mysql> EXPLAIN PARTITIONS SELECT * FROM usermaster_cs \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: usermaster_cs
   partitions: p201301,p201302,p201303,p201304,p201305,p201306,p201307,p201308,p201309,p201310,p201311,p201312,p201401,pmaxvalue
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 68886025
        Extra:
1 row in set (0.00 sec)

条件を入れた場合には該当パーテションにだけアクセスが行くことがわかります。

mysql> EXPLAIN PARTITIONS SELECT * FROM usermaster_cs WHERE upd_datetime='2013-10-30 21:04:06' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: usermaster_cs
   partitions: p201310
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2247981
        Extra: Using where
1 row in set (0.00 sec)

この前のトリガと組み合わせると特定テーブルのデータを別テーブルで期間保存とか出来たりして便利かもしれないですね。
ではでは三ʅ(◔౪◔ʅ)三(ʃ◔౪◔)ʃ