みなさん、最近人生のパーティショニングしてますか？
こんにちは、勝利です。
今回はMySQLのパーティショニングについて紹介させていただきます。
MySQLのパーティショニングとは？
MySQL5.1ぐらいから使えるようになった、1つのテーブルを分割する機能。
テーブルを分割するので、格納できる根本的な容量の拡張や、やり方(Plunning)によっては高速処理を実現できます。
分割した際のデータ振り分け方法としては大体以下のような形があります。
RANGE ・・・ パーティションごとに範囲を指定して振り分ける
LIST ・・・ パーティションごとに格納する値で振り分ける
HASH ・・・ 1つのカラムの値を式の結果で振り分ける
KEY ・・・ １つ以上のカラムの値をMD5関数等で評価して分割する
使用するメリットとしては下記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;まとめ
というように割と簡単にできます。
うまくいかない場合はパーティションをかける前の状態に戻す事もできます。
※ただし、パーティションの削除した際のデータは戻すことが出来ないので最新の注意を払ってください。おお。
これで君もパーティショナイザーだ。LIGはWebサイト制作を支援しています。ご興味のある方は事業ぺージをぜひご覧ください。
