MySQLのパーティショニングをやってみました

スマートフォン向けのアドネットワークサービス『nend』の開発とか雑用を担当しているudaと申します。

MySQLのパーティショニングを使う機会があったので、検証した結果を簡単にまとめてみました。 今回検証したMySQLのバージョンは5.5.21です。

パーティショニングを使った経緯としては、大量のレコードをdeleteした後にinsertするケースがあり、より早く処理を行うための手段として検証を行いました。 本来であれば大量レコードのdeleteは好ましくなく、truncateやDROP TABLE等で対応可能な場合はそちらの方が断然いいのですが、今回のケースだと仕様上困難だったため、パーティショニングを利用して高速化することとしました。 なお、今回の記事はRANGE パーティショニングを対象としています。 公式マニュアル： http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

• テーブル定義

まずテーブル定義ですが、パーティショニングの効果を比較するために、通常のテーブルとパーティショニングのテーブルを作成します。 test:通常テーブル test_partition:パーティショニングを利用したテーブル

[sql] CREATE TABLE test ( id int(11) NOT NULL auto_increment, date date NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE test_partition LIKE test; [/sql]

• インデックス作成

次に、パーティショニング用のインデックスを作成します。 テーブルにPRIMARY KEYが存在する場合、パーティションで指定するカラムはPRIMARY KEYに含まれている必要がありますが、PRIMARY KEYが存在しないテーブルであれば、どのカラムでも指定可能です。 なお、今回の検証は前者で行います。 [sql] ALTER TABLE test_partition ADD INDEX id_idx(id); ALTER TABLE test_partition DROP PRIMARY KEY; ALTER TABLE test_partition ADD PRIMARY KEY(id,date); ALTER TABLE test_partition DROP INDEX id_idx;

mysql> show index from test_partition; +------------------+------------+-----------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------------+------------+-----------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+ | test_partition | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | test_partition | 0 | PRIMARY | 2 | date | A | 0 | NULL | NULL | | BTREE | | +------------------+------------+-----------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+ [/sql]

• パーティション定義

インデックスを作成後に、パーティションの定義を行います。 今回は2013/01/01から2013/01/15のダミーデータを利用して検証を行います。

[sql] ALTER TABLE test_partition PARTITION BY RANGE(TO_DAYS(date)) ( PARTITION p_20130101 VALUES LESS THAN (TO_DAYS('2013-01-02')), PARTITION p_20130102 VALUES LESS THAN (TO_DAYS('2013-01-03')), PARTITION p_20130103 VALUES LESS THAN (TO_DAYS('2013-01-04')), PARTITION p_20130104 VALUES LESS THAN (TO_DAYS('2013-01-05')), PARTITION p_20130105 VALUES LESS THAN (TO_DAYS('2013-01-06')), PARTITION p_20130106 VALUES LESS THAN (TO_DAYS('2013-01-07')), PARTITION p_20130107 VALUES LESS THAN (TO_DAYS('2013-01-08')), PARTITION p_20130108 VALUES LESS THAN (TO_DAYS('2013-01-09')), PARTITION p_20130109 VALUES LESS THAN (TO_DAYS('2013-01-10')), PARTITION p_20130110 VALUES LESS THAN (TO_DAYS('2013-01-11')), PARTITION p_20130111 VALUES LESS THAN (TO_DAYS('2013-01-12')), PARTITION p_20130112 VALUES LESS THAN (TO_DAYS('2013-01-13')), PARTITION p_20130113 VALUES LESS THAN (TO_DAYS('2013-01-14')), PARTITION p_20130114 VALUES LESS THAN (TO_DAYS('2013-01-15')), PARTITION p_20130115 VALUES LESS THAN (TO_DAYS('2013-01-16')), PARTITION p_future VALUES LESS THAN MAXVALUE ); [/sql]

• テストデータ登録

テストデータの件数が中途半端なのは気にしないでください・・・

[sql] mysql> insert into test(date) select date from dummy_data where date <= '2013-01-15'; Query OK, 14590988 rows affected (39 min 14.67 sec) Records: 14590988 Duplicates: 0 Warnings: 0

mysql> insert into test_partition(date) select date from dummy_data where date <= '2013-01-15'; Query OK, 14590988 rows affected (33 min 17.02 sec) Records: 14590988 Duplicates: 0 Warnings: 0 [/sql] データの登録に関しては誤差の範囲内かなと思われます。 そもそも全パーティションへの登録なので差が出ても困りますが。

• 一日分のデータ削除

単純なdelete文で2013/01/15のデータを削除します。

[sql] delete from test where date = '2013-01-15'; Query OK, 1032650 rows affected (2 min 42.77 sec)

delete from test_partition where date = '2013-01-15'; Query OK, 1032650 rows affected (53.94 sec) [/sql] パーティショニングの効果が出ました。 約7割の時間が短縮できています。

• 削除してデータの再登録

次に、消した2013/01/15分のデータを再度登録してみます。

[sql] mysql> insert into test(date) select date from dummy_data where date = '2013-01-15'; Query OK, 1032650 rows affected (1 min 24.41 sec) Records: 1032650 Duplicates: 0 Warnings: 0

mysql> insert into test_partition(date) select date from dummy_data where date = '2013-01-15'; Query OK, 1032650 rows affected (55.98 sec) Records: 1032650 Duplicates: 0 Warnings: 0 [/sql] 単一パーティションへのデータ登録なので、パーティショニングの方が早くなります。

• 再度データを登録し直してパーティションごと削除

次に、パーティションの再作成と削除を行います。 2013/01/15のパーティションを削除して、作り直しです。 データ的には2013/01/15のデータをdeleteするのと同じとなります。

[sql] alter table test_partition drop partition p_20130115; Query OK, 0 rows affected (1.28 sec) Records: 0 Duplicates: 0 Warnings: 0 [/sql]

• パーティション再作成

2013/01/15のデータがパーティションごと消えたため、消したパーティションを再作成します。

[sql] alter table test_partition reorganize partition p_future into ( PARTITION p_20120615 VALUES LESS THAN (TO_DAYS('2012-06-16')), PARTITION p_future VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.59 sec) Records: 0 Duplicates: 0 Warnings: 0 [/sql] こうしてみると、通常テーブルのdeleteで２分42秒かかっていたのが、 わずか2秒ほどでdeleteと同じことができてしまいました。 パーティショニングすごい！

ただ、この方法では遅くなってしまうケースも存在します。

• 過去の日付のパーティション削除

2013/01/10のパーティションを削除してみます。

[sql] alter table test_partition drop partition p_20130110; Query OK, 0 rows affected (38.29 sec) Records: 0 Duplicates: 0 Warnings: 0 [/sql] 遅い・・・ 先ほど1秒程度で終わっていた処理が38秒もかかっています。

• 過去分のパーティション再作成

消した日のパーティションを再作成します。

[sql] alter table test_partition reorganize partition p_20120611 into ( PARTITION p_20120610 VALUES LESS THAN (TO_DAYS('2012-06-11')), PARTITION p_20120611 VALUES LESS THAN (TO_DAYS('2012-06-12')) ); Query OK, 1279340 rows affected (3 min 28.04 sec) Records: 1279340 Duplicates: 0 Warnings: 0 [/sql]

更に遅い・・・ パーティションの再作成だけで4分以上かかってしまいました。

まとめ ・単純なdeleteではパーティショニングを行った方が高速 ・insertもパーティションごとに行えば速度的に向上する ・パーティションごと消して再作成すると非常に高速。ただし、削除対象のパーティションの前後のパーティションにデータが存在する場合は、逆にdeleteより遅くなるケースもある。 ・今回の検証結果には含めていませんが、selectも条件次第で高速になる。

最後に レコード件数が多くなればなるほどパーティショニングの恩恵は受けられるので、テーブルが肥大化して困っている場合には是非利用してみてください。 ただ、今回の検証内容に載せたパーティショニングの再作成は基本的に利用しないことをおすすめします。 まずは、大量のデータをdeleteしなくて済む方法や、truncate等で対応することを検討し、もし利用することとなった場合はよく検証してから利用してください。