【MySQL】パーティションでテーブル分割して大量データ等の検索速度改善

Mysql-1

2012年08月30日(木)

今回はMySQLのテーブル分割機能である、パーティショニングについて実際にやってみたので紹介します。 MySQLの5.1ぐらいからパーティショニングが使えるようなったらしいです。 パーティションを区切ると、対象のカラムのデータで物理的にテーブルを分割する事と同等の効果を発揮します。 これで、大量データも気軽に扱えるようになりそうです。  


早速操作ログと共に説明したいと思います。  

まず第一条件としては、 対象カラムがプライマリーキーとなっていることが必要です。  

例えば、期間で分ける場合は、 date/datetimeカラムをプライマリーキーに設定する必要がありそうです。  

また、フラグ等で分ける場合は、 そのフラグをプライマリーキーに含める必要がありそうです。
今回はフラグでパーティションを区切る方法を試してみました。 (期間で分けるタイプは結構WEBに転がっていたので…) まず対象のテーブルを作成します。

---
対象のテーブル
---
CREATE TABLE IF NOT EXISTS `test_log_1000000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type_code` tinyint(1) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`,`type_code`),
)


idとtype_codeをプライマリーキーにします。 これでパーティションを作成できます。  
データはあらかじめ、1000000件登録しておきました。 次に先ほど作成したテーブルをパーティションで区切ります。

今回、type_codeには固定値で(1,2,3,4,5)という値がそれぞれ入ると想定しています。

---
既存テーブルにパーティション構築
---
ALTER TABLE test_log_1000000
PARTITION BY RANGE columns(type_code) (
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
);



  これでtype_codeの値に対してパーティションを区切る事が出来ました。 少し説明を入れると、

PARTITION p_type_code0 VALUES LESS THAN (1)


  これはtype_codeが1未満のデータが蓄積されるパーティションです。 つまり、type_codeが0の場合にこのパーティションに蓄積されます。

PARTITION pmax VALUES LESS THAN MAXVALUE


こちらは例外的なものですね。今回で言えば、0~5以外のデータが入ってきた場合にこのパーティションに蓄積されます。

  これでもう既存データは分割されているので、 続いて既存データ構造の確認をします。

---
パーティション確認方法
---
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 | +------------------+------------------+----------------+----------------------------+------------+ | point_cm_db_test | test_log_1000000 | p_type_code0 | 1 | 0 | | point_cm_db_test | test_log_1000000 | p_type_code1 | 2 | 6073 | | point_cm_db_test | test_log_1000000 | p_type_code2 | 3 | 4371 | | point_cm_db_test | test_log_1000000 | p_type_code3 | 4 | 1195 | | point_cm_db_test | test_log_1000000 | p_type_code4 | 5 | 2149 | | point_cm_db_test | test_log_1000000 | p_type_code5 | 6 | 995849 | | point_cm_db_test | test_log_1000000 | pmax | 7 | 0 | +------------------+------------------------+----------------+----------------------------+------------+  


どのパーティションにどれぐらいデータが入っているのか一目瞭然ですね! では既存データのチェックをしましょう。 全データ件数確認。

select count(id) from test_log_1000000;


+———–+ | count(id) | +———–+ | 1000000 | +———–+   各type_codeの各データ件数確認。

select count(id) from test_log_1000000 where type_code = 5;


+———–+ | count(id) | +———–+ | 986486 | +———–+

select count(id) from test_log_1000000 where type_code = 4;


+———–+ | count(id) | +———–+ | 2144 | +———–+

select count(id) from test_log_1000000 where type_code = 3;


+———–+ | count(id) | +———–+ | 1166 | +———–+

select count(id) from test_log_1000000 where type_code = 2;


+———–+ | count(id) | +———–+ | 4251 | +———–+

select count(id) from test_log_1000000 where type_code = 1;


+———–+ | count(id) | +———–+ | 5953 | +———–+  

現状はこのような割合でデータが入っています! ではこのtype_codeの5を削除します!  

このような場合、パーティションを分割していれば楽にできます。 また、パーティションごとにMySQLのファイルデータが作成されているので、 前述した、ibdataの増幅を抑える事もできます。

http://pk-brothers.com/?p=584 (これがなによりうれしい事だったりします!)

---
パーティションの一部を消去したい場合
---
ALTER TABLE test_log_1000000 DROP PARTITION p_type_code5;
Query OK, 0 rows affected (1.57 sec)


※対象のパーティションのデータは完全に削除されます。(MySQLのログデータも)   各type_codeのデータ件数確認。 type_code=5 のみがすべて削除されている事を確認します。

select count(id) from test_log_1000000 where type_code = 5;


+———–+ | count(id) | +———–+ | 0 | +———–+

select count(id) from test_log_1000000 where type_code = 4;


+———–+ | count(id) | +———–+ | 2144 | +———–+

select count(id) from test_log_1000000 where type_code = 3;


+———–+ | count(id) | +———–+ | 1166 | +———–+

select count(id) from test_log_1000000 where type_code = 2;


+———–+ | count(id) | +———–+ | 4251 | +———–+

select count(id) from test_log_1000000 where type_code = 1;


+———–+ | count(id) | +———–+ | 5953 | +———–+  

おぉ。 完全に対象のパーティションのデータのみが削除されている事が確認できます。 再度パーティション構成を確認。

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 | +------------------+------------------+----------------+----------------------------+------------+ | point_cm_db_test | test_log_1000000 | p_type_code0 | 1 | 0 | | point_cm_db_test | test_log_1000000 | p_type_code1 | 2 | 5717 | | point_cm_db_test | test_log_1000000 | p_type_code2 | 3 | 4089 | | point_cm_db_test | test_log_1000000 | p_type_code3 | 4 | 1275 | | point_cm_db_test | test_log_1000000 | p_type_code4 | 5 | 2149 | | point_cm_db_test | test_log_1000000 | pmax | 6 | 0 | +------------------+------------------------+----------------+----------------------------+------------+  


きちんと削除されています。 尚、このデータは復元できないので、注意してください。

ちなみに、その他の制御も一通りやったので参考までに。

 

---
パーティションの追加構築
---
ALTER TABLE test_log_1000000 REORGANIZE PARTITION pmax INTO (
PARTITION p_type_code5 VALUES LESS THAN (6),
PARTITION pmax VALUES LESS THAN MAXVALUE);


Query OK, 0 rows affected (2.79 sec)   追加されているか確認。

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 | +------------------+------------------+----------------+----------------------------+------------+ | point_cm_db_test | test_log_1000000 | p_type_code0 | 1 | 0 | | point_cm_db_test | test_log_1000000 | p_type_code1 | 2 | 6073 | | point_cm_db_test | test_log_1000000 | p_type_code2 | 3 | 4072 | | point_cm_db_test | test_log_1000000 | p_type_code3 | 4 | 1195 | | point_cm_db_test | test_log_1000000 | p_type_code4 | 5 | 2293 | | point_cm_db_test | test_log_1000000 | p_type_code5 | 6 | 0 | | point_cm_db_test | test_log_1000000 | pmax | 7 | 0 | +------------------+------------------------+----------------+----------------------------+------------+  

type_code = 5 のデータを1件INSERTして再度配置されているか確認。

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 | +------------------+------------------+----------------+----------------------------+------------+ | point_cm_db_test | test_log_1000000 | p_type_code0 | 1 | 0 | | point_cm_db_test | test_log_1000000 | p_type_code1 | 2 | 5717 | | point_cm_db_test | test_log_1000000 | p_type_code2 | 3 | 4339 | | point_cm_db_test | test_log_1000000 | p_type_code3 | 4 | 1135 | | point_cm_db_test | test_log_1000000 | p_type_code4 | 5 | 2293 | | point_cm_db_test | test_log_1000000 | p_type_code5 | 6 | 1 | | point_cm_db_test | test_log_1000000 | pmax | 7 | 0 | +------------------+------------------------+----------------+----------------------------+------------+

---
パーティションをやめて通常テーブルに戻す方法
---
ALTER TABLE test_log_1000000 REMOVE PARTITIONING;




  これで君もパーティショナーだ!!!!    

スポンサードリンク

運営サービスPR

スポンサードリンク

運営サービスPR