[mysql]パーティショニングの使いどころ

mysqlにはパーティショニングという大変便利な機能がある。
これは、あるカラムの条件をキーにしてテーブルを分割して、検索効率を上げる便利な機能である。

パーティショニングとこんなに早くなる！というメリットを書いてあるブログはたくさんあるのですが、デメリットがあげられているところがあまりないので、今回はデメリットを紹介したいとおもいます。

まず、こんな感じのテーブルがあったとします。

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pre_id` int(11) NOT NULL COMMENT '都道府県コード',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

table1.id と　table2.id は1対１の関係とします。
ここでパーティショニングを使いたい場合どうしたらいいでしょうか？

まず、”都道府県ごとの一覧”を出したいという要求に基づいてパーティショニングする場合
とうぜん　table2.pre_id　をキーにしてパーティショニングを行うはずです。
これで、テーブルが47に分割されるので都道府県で絞っての検索が非常に早くなるはずです。

ではtable2.pre_idでパーティショニングしてある状態で、table1とtable2をjoinしてid昇順に検索した場合どうなるでしょうか？
table1にtable2をjoinするときにtable2のすべての４７個のパーティションテーブルすべてからidを検索して探さなければなりません。

つまり、”パーティショニングのキーをまたいだ（もしくは検索条件に入ってない）”検索の場合、パーティショニングを使うと逆に劇重になってしまうのです。

ではどうするのか？
1.例で示した構造とすればtable2からtable1をjoinして、あとでidでソートをかける。
2.joinの条件にパーティショニングのキーをつける。
3.パーティショニングしていないテーブルを別途用意して使い分ける。

といったことが考えられますが、１，２は場合によっては使えません(or 効果がない)。
例の構造だと１は件数が膨大な場合ソートが不可能です。
２はそもそもtable1にpre_idがありません。４７回検索するという方法はありますが、これは重いですし、結局ソートの問題があります。
結局のところ、すべてのパターンで使える方法は３しかないのですが、正規化の原則を逸脱しているのでDBの管理が煩雑になります。

MySQL徹底入門第4版 MySQL 8.0対応

タグ