WEB

高速処理化!MySQLのパーティショニング機能を使ってみよう

高速処理化!MySQLのパーティショニング機能を使ってみよう

みなさん、最近人生のパーティショニングしてますか?

こんにちは、勝利です。
今回はMySQLのパーティショニングについて紹介させていただきます。

 

MySQLのパーティショニングとは?

MySQL5.1ぐらいから使えるようになった、1つのテーブルを分割する機能。
テーブルを分割するので、格納できる根本的な容量の拡張や、やり方(Plunning)によっては高速処理を実現できます。

分割した際のデータ振り分け方法としては大体以下のような形があります。

RANGE ・・・ パーティションごとに範囲を指定して振り分ける
LIST ・・・ パーティションごとに格納する値で振り分ける
HASH ・・・ 1つのカラムの値を式の結果で振り分ける
KEY ・・・ 1つ以上のカラムの値をMD5関数等で評価して分割する

 

使用するメリットとしては下記2点のようなことが考えられます。

メリット1 [高速化が見込める]

・インデックスを張りたいカラム等のデータの種類が少ない場合

たとえば、「手」というテーブルがあるとします。

その中のカラムに「指の種類」みたいなカラムがあったとしたら、この「指の種類」の数は固定的で最大でも5種類?になり、種類が少ないですね。

データ参照時にオプティマイザっていう新たしい仮面ライダーみたいなものが、
対象のパーティションのみを参照するらしいので、対象範囲が限定されるため、高速化が見込めます。
わかりやすいよう、普通のテーブルとパーティションされた状態を比較できるような図を描きました。

メリット2 [データ管理が楽になる]

・限定的な期間だけ必要なデータを蓄えておくような場合

例えば、ログデータなどがそれに含められるんじゃないかと思います。
これっていらなくなったデータも溜まっていっちゃいますよね?パーティションを効率的に使用していると不要データの削除が楽になります。

 

Plunningで高速化も見込めると思いますが、一番は不要データの破棄がとても高速にできるから。

 

なぜ高速なんだろう?

->それはね、パーティションの削除は内部的にはテーブルの削除(DROP)と同じような処理らしいから。
しかもMySQLのログデータも残らないで、通常のDELETEのようにDELETEしたのにHDDの空きが減ったとかはないです。
削除されるイメージです。なんか削除が簡単そうですね。

 

使用する上での制約

パーティショニングの制約と制限
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-limitations.html

 

僕なりに忘れないでほしい箇所は以下になります。(僕がつまずきました。)
パーティショニングされたテーブルのパーティショニング表現に使用される全てのカラムはテーブル内に存在する全てのユニークキーの一部でなければいけない。

要は、テーブルのユニークなキーを含めていなければいけないということです。
後述するテーブルCreate文を確認すれば理解できると思います。

 

 

では、実際の操作をしてみましょうか

今回はtypeの種類が7種類(0,1,2,3,4,5,6)を想定、
そしてパーティショニングの種類は「RANGE」で実施します。

 

 

まず、テーブル作成。

CREATE TABLE IF NOT EXISTS `test_log_1000000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` tinyint(1) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`,`type_code`),
)

 

次に上記のテーブルにさっそくではありますが、パーティションで区切ります。
※事前にこのテーブルに1000000件のデータをINSERTしました。

 

 

パーティションの作成方法です。

ALTER TABLE test_log_1000000
PARTITION BY RANGE columns(type) (
PARTITION p_type_code0 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p_type_code1 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p_type_code2 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION p_type_code3 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION p_type_code4 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p_type_code5 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);

 

僕> これで、実はもうできてるんだぜ。

 

僕> え?うそ??どうやって確認するんだよ!

 

 

そこでパーティションの確認方法です。

select
    TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
from INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='test_log_1000000’;

 

+------------------+------------------+----------------+----------------------------+------------+
| TABLE_SCHEMA     | TABLE_NAME       | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+------------------+------------------+----------------+----------------------------+------------+
| db_test          | test_log_1000000 | p_type_code0   |                          1 |          0 |
| db_test          | test_log_1000000 | p_type_code1   |                          2 |       6073 |
| db_test          | test_log_1000000 | p_type_code2   |                          3 |       4371 |
| db_test          | test_log_1000000 | p_type_code3   |                          4 |       1195 |
| db_test          | test_log_1000000 | p_type_code4   |                          5 |       2149 |
| db_test          | test_log_1000000 | p_type_code5   |                          6 |     995849 |
| db_test          | test_log_1000000 | pmax           |                          7 |          0 |
+------------------+------------------+----------------+----------------------------+------------+

 

てなもんです。
あらかじめ登録したデータが悪かったようで、あからさまに隔たりがある別れ方をしてしまいましたね。。。
まぁどのパーティションにどのくらいデータが入っているのか一目瞭然で良いでしょう。

※ROWの数の合計が1,000,000件じゃないじゃんと思った方、正解です。ただ、気にしないでください。
各パーティションの件数を確認したところきちんと分かれていました。

俺> ・・・

僕> なんか、だんだん「パーティション:p_type_code5」が消したくなってきたでしょう?

 

僕> では消しましょう。
僕> そうです。
僕> 簡単です。

 

 

対象のパーティションの削除方法です。

ALTER TABLE test_log_1000000 DROP PARTITION p_type_code5;

 

※対象のパーティションのデータは完全に削除されます。(MySQLのログデータも!!うれしいね!容量が空くね!!)

 

僕> てか、このパーティションやっぱあった方がよくね??

 

僕> そうです。あった方がいいんです。
僕> そうです。
僕> パーティションを追加しましょう。(※以前のデータは復元できません。)

 

 

パーティションの追加方法です。

ALTER TABLE test_log_1000000 REORGANIZE PARTITION pmax INTO (
PARTITION p_type_code5 VALUES LESS THAN (6),
PARTITION pmax VALUES LESS THAN MAXVALUE);

これで、パーティションを再配置できました。
※初期のパーティションの構成に戻りました。(データはもどりませんから!!)

 

僕> というか、パーティションとかいらなくね??

 

僕> そうです。いらないんです。
僕> では通常テーブルに戻しましょう。

 

 

パーティションをやめて通常テーブルに戻す方法です。

ALTER TABLE test_log_1000000 REMOVE PARTITIONING;

まとめ

というように割と簡単にできます。
うまくいかない場合はパーティションをかける前の状態に戻す事もできます。
※ただし、パーティションの削除した際のデータは戻すことが出来ないので最新の注意を払ってください。

おお。
これで君もパーティショナイザーだ。

この記事を書いた人

勝利
勝利 TRIPチーム 2012年入社
青森最強は伊達じゃない。女を泣かす奴には容赦ない。殴った相手はマブダチだ。シャバいダサ坊かかってこいや。俺の名は勝利。仁義を通して不義理を許さず。プログラム?できるよ。発一だそんなもん。