mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス
さて、前回まではパーティショニングの設定やパーティショニングのタイプについて述べてきたが、
ここからはパフォーマンスの話をしていく。
サンプルテーブル
まず、以下のサンプルテーブルを用意した。
それぞれのテーブルに2倍にあたる245746件の郵政省のデータをいれてある。
・postal_normal
CREATE TABLE postal_normal (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
`code` int(7) NOT NULL,
ken_kana varchar(255) DEFAULT NULL,
ctiy_kana varchar(255) DEFAULT NULL,
town_kana varchar(255) DEFAULT NULL,
ken varchar(255) DEFAULT NULL,
citiy varchar(255) DEFAULT NULL,
town varchar(255) DEFAULT NULL,
flg1 tinyint(1) NOT NULL,
flg2 tinyint(1) NOT NULL,
flg3 tinyint(1) NOT NULL,
flg4 tinyint(1) NOT NULL,
flg5 tinyint(1) NOT NULL,
flg6 tinyint(1) NOT NULL,
entry timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY town (town),
KEY flg1 (flg1),
KEY entry (entry),
KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
・postal_hash16
CREATE TABLE postal_hash16 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
`code` int(7) NOT NULL,
ken_kana varchar(255) DEFAULT NULL,
ctiy_kana varchar(255) DEFAULT NULL,
town_kana varchar(255) DEFAULT NULL,
ken varchar(255) DEFAULT NULL,
citiy varchar(255) DEFAULT NULL,
town varchar(255) DEFAULT NULL,
flg1 tinyint(1) NOT NULL,
flg2 tinyint(1) NOT NULL,
flg3 tinyint(1) NOT NULL,
flg4 tinyint(1) NOT NULL,
flg5 tinyint(1) NOT NULL,
flg6 tinyint(1) NOT NULL,
entry timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY town (town),
KEY flg1 (flg1),
KEY entry (entry),
KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LINEAR HASH (id) PARTITIONS 16;
テストケース
上記のデータに対して、以下のテストを行う。
- id=240000で検索
- id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007)で検索
- code=1000000で検索
- ken LIKE '%北海道%'
- １レコード追加
id=240000で検索
・パーティショニング
select * from postal_hash16 where id = 240000;
1 row in set (0.02 sec)
・ノーマル
select * from postal_normal where id = 240000;
1 row in set (0.01 sec)
さすがに、パーティションしていないとはいえインデックスの効いたたかが24万件なので差はあまり無いと思われる。
ただ、パーティションしてあるほうはたかだか1万ちょっとからの検索になるはずなので早いはずだが・・・
id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007)で検索
・パーティショニング
select * from postal_hash16 where id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007);
8 rows in set (0.00 sec);
・ノーマル
select * from postal_normal where id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007);
8 rows in set (0.00 sec)
うーん・・・検索件数を増やしてもスピードは変わらず・・・
やはり24万件程度では差がでないということか。
code=1000000で検索
・パーティショニング
select * from postal_hash16 where code = 1000000;
2 rows in set (0.26 sec)
・ノーマル
select * from postal_normal where code = 1000000;
2 rows in set (0.03 sec)
これは違いがはっきり出た。
やはりパーティションしているほうが遅いということである。この場合インデックスは効いていないのかも・・・。
ken LIKE '%北海道%'で検索
・パーティショニング
select * from postal_hash16 where ken LIKE '%北海道%'
16454 rows in set (2.34 sec)
・ノーマル
select * from postal_normal where ken LIKE '%北海道%'
16454 rows in set (1.74 sec)
なるほど、インデックスの効いていないカラムに対しては、
ノーマルもやはり遅いが、やはりパーティショニングしている方が遅い結果になった。
１レコード追加
・パーティショニング
INSERT INTO `test2`.`postal_hash16` (`code`, `ken_kana`, `ctiy_kana`, `town_kana`, `ken`, `citiy`, `town`, `flg1`, `flg2`, `flg3`, `flg4`, `flg5`, `flg6`, `entry`) VALUES ( "9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0,NOW() );
Query OK, 1 row affected (0.04 sec)
・ノーマル
INSERT INTO `test2`.`postal_normal` (`code`, `ken_kana`, `ctiy_kana`, `town_kana`, `ken`, `citiy`, `town`, `flg1`, `flg2`, `flg3`, `flg4`, `flg5`, `flg6`, `entry`) VALUES ( "9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0,NOW() );
Query OK, 1 row affected (0.02 sec)
パーティショニングを計算してインサートする分どうしても遅くなってしまう。
まとめ
今回の実験結果では、パーティショニングのキーとなったプライマリキーの検索ではパフォーマンスは同じであったが、
それ以外のカラムの検索ではパフォーマンスが大きく低下してしまう結果になった。
ただし、24万件ではなく、100万件、1000万件レベルのテストになるとパーティショニングした場合のプライマリキーでの検索に効果が見られる可能性はある。
現時点では他のカラムの検索や、インサートなどのパフォーマンスが劇的に落ちてしまうため、24万レコードレベルのテーブルではパーティショニングは導入すべきでないと考える。
もし、使用するとなれば、アクセスログやクエリーログをテーブル化し、データ量がそれこそ何千万件となったときに効果を発揮するのかもしれない。
是非、大容量のデータでテストをしてみたいが、なんせインサートが遅いのとマシンのスペックが追いつかないため今回は見送らせていただく。
実は仕事でパーティショニングを使ってみたかったが、今回のパフォーマンスではなかなか難しそうだなぁ～
written by YSU
\\ tags: mysql, まとめ
