今さらだけどMySQLのパーティショニング機能を試してみた

最近は花粉が飛んでて辛い季節ですがみなさまいかがお過ごしでしょうか。でももうちょっと我慢すればサクラの季節ですよ～。花見良いですよね、飲みたいだけですが。

・・さて、今回はちょっと必要になったので、MySQLのパーティショニング機能なるものを試してみました。存在は知ってたけど、実際に試してみたことは無かった…。

パーティショニングとは？

これはどういうものかと言うと、MySQL5.1から使えるようになった機能で、ひとつのテーブルのデータを条件によって複数の領域（パーティション）に振り分けて管理することができる、というものです。例えば日別にデータを別々のパーティションに振り分けたり。

パーティショニングするとデータの削除が高速だったり（通常は削除ってものすっごい遅いけど、特定のパーティションごと削除するのであればテーブル削除のように高速に行える）、特定のパーティションしか参照しないような場合であれば高速にデータが読み出せるといったメリットがあります。

データの振り分け方法としては RANGE, LIST, HASH, KEY といったいろいろなパターンがありますが、今回はRANGEパーティショニングのみを扱います。それぞれのパターンの説明は省くので気になる人はググってみてください～。

早速やってみよう

例としてこんなテーブルを考えてみましょう。

> DESC samples;

+--------------+--------------+------+-----+----------+----------------+
| Field        | Type         | Null | Key | Default  | Extra          |
+--------------+--------------+------+-----+----------+----------------+
| id           | int(11)      | NO   | PRI | NULL     | auto_increment |
| user_id      | int(11)      | NO   |     | NULL     |                |
| action       | varchar(255) | NO   |     | NULL     |                |
| created_at   | datetime     | NO   | PRI | NULL     |                |
| updated_at   | datetime     | YES  |     | NULL     |                |
+--------------+--------------+------+-----+----------+----------------+

今回はRANGEパーティショニングを使って、日付ごとに別のパーティションにデータを保存するようにしてみようかと思います。が、MySQL のパーティショニングにはまず前提があって、パーティショニングに使いたいカラム（この場合はcreated_at）が PRIMAY KEY に含まれていなければいけないという条件があります。

そこでまず PRIMARY KEY を設定します。最初、id に PRIMARY KEY が設定されていたので、これをいったん削除してから再度 id と created_at で PRIMRY KEY を設定しています。

ALTER TABLE samples DROP PRIMARY KEY, ADD PRIMARY KEY(id, created_at);

ではこの samples テーブルに対して（後から）パーティショニングしてみます。この例では created_at の値によってパーティションを振り分けるようにしてみました。もちろんテーブル作成時にパーティショニングを指定することだって可能です。

ALTER TABLE samples
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p20130315 VALUES LESS THAN (TO_DAYS('2013-03-16 00:00:00')) COMMENT = '2013-03-15',
PARTITION p20130316 VALUES LESS THAN (TO_DAYS('2013-03-17 00:00:00')) COMMENT = '2013-03-16',
PARTITION p20130317 VALUES LESS THAN (TO_DAYS('2013-03-18 00:00:00')) COMMENT = '2013-03-17',
PARTITION p20130318 VALUES LESS THAN (TO_DAYS('2013-03-19 00:00:00')) COMMENT = '2013-03-18'
);

これで、p20130315, p20130316, p20130317, p20130318 という4つのパーティションが作成されました。

RANGEパーティショニングでは評価式が必ずINT型の結果を返すようにする必要があるため、TO_DAYS(created_at) のようにTO_DAYS関数を呼んでいます。これだとあとからテーブルの情報見たときに展開された数値情報しかわからないので不便なのです（この例のようにコメントを付けておけば良いのですが）。

ちなみにMySQL5.5以上であれば、DATE型やDATETIME型をパーティションの振り分けに利用できるので、わざわざTO_DAYS関数を使ったりしなくてもよくてもっと直感的です。

ただこれだと問題が・・

ただ、これだと問題があります。というのも、今回の例で言うと created_at が 3/19 以降のデータを保存しようとするとこのようなエラーが出て失敗します。。

> INSERT INTO samples(user_id, action, created_at, updated_at) values(123, 'action', '2013-03-19 10:10:10', '2013-03-19 10:10:10');

ERROR 1526 (HY000): Table has no partition for value 735312

created_at が 3/19 以降のデータに対しては振り分けるべきパーティションが無いためです。その他はこのパーティションに入れよう、っていうパーティションを作ることはこんな感じで簡単に出来るんですが、これだと今後新たなパーティション追加するのにいちいち REORGANIZE PARTITION しないといけない。。

ALTER TABLE points ADD PARTITION (PARTITION pleftover VALUES LESS THAN (MAXVALUE));

この問題ってみんなどうやってるんですかね？定期的に ADD PARTITION してるのかなー。テーブルロック掛かったりしないのかな。。どなたか知ってたら教えてくださいm(_ _)m

>> 2013/03/22 09:30 追記ここから

REORGANIZE PARTITION するにも ADD PARTITION するにもテーブルロックが避けられないので、事前にまとめてガガッとパーティション作っちゃう場合が多いみたいですね。参考になります！

追記その1

<< 2013/03/22 09:30 追記ここまで

パーティションの振り分けを確認する

ま、こんな感じでパーティショニングすることは出来るんですが、実際の利用に際してはいくつか知っといたほうが良いコマンドがあるので紹介します。例えば、それぞれのパーティションにいったい何件のデータが振り分けられているかはこのようなコマンドで調べることが出来ます。

> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='samples';

+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| development  | samples    | p20130315      |                          1 |          8 |
| development  | samples    | p20130316      |                          2 |         13 |
| development  | samples    | p20130317      |                          3 |         12 |
| development  | samples    | p20130318      |                          4 |          6 |
+--------------+------------+----------------+----------------------------+------------+

この場合だと、p20130315 に8件、p20130316 に13件、p20130317 に12件、p20130318 に6件データが振り分けられて管理されてることがわかりますね。

どのパーティションが使われているか

あと、どのパーティションが利用されているかっていうのは実際のSQLの前に EXPLAIN PARTITIONS と付ければ知ることができます。基本的にはインデックスを貼るときなんかに使う EXPLAIN と同じですが、それに加えてパーティションの情報も出してくれるというものです。

> EXPLAIN PARTITIONS SELECT * FROM samples;

+----+-------------+---------+-----------------------------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | partitions                              | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+-----------------------------------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | samples | p20130315,p20130316,p20130317,p20130318 | ALL  | NULL          | NULL | NULL    | NULL |   39 |       |
+----+-------------+---------+-----------------------------------------+------+---------------+------+---------+------+------+-------+

この場合だと p20130315, p20130316, p20130317, p20130318 と対象になってますが、

> EXPLAIN PARTITIONS SELECT * FROM samples WHERE created_at < '2013-03-16 00:00:00';

+----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | samples | p20130315  | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+

この場合だと p20130315 のパーティションだけを対象としていることがわかります。

要らなくなったパーティションの削除

こんな感じで簡単に高速にできます。ただ、パーティションを削除するとその中にあるデータもすべて削除されるのでお気をつけ下さい。念のため。

ALTER TABLE samples DROP PARTITION p20130315;

タグ：: パーティショニング

(ﾟ∀ﾟ)o彡 sasata299's blog