森の夢

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス
前回の記事では、mysqlのパーティショニングを使うための、mysql本体のセッティングについて述べたが、
今回はパーティショニングの種類について記述していく。

サンプルテーブル

まず、説明を始める前に今回はサンプルとして、郵政省の郵便番号データを扱う以下のテーブルを用意した。
いろいろカラムが分かれているが、code(郵便番号)にたいして、フラグやら県名を保持しているテーブルだ。

CREATE TABLE　`postal` (
  `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`)
)
DEFAULT CHARSET=utf8
ENGINE=InnoDB;

パーティショニングの種類

さて、本題に入る。
mysqlのパーティショニングには以下の４つのタイプが存在する。

• RANGEパーティショニング
• LISTパーティショニング
• [LINEAR] HASHパーティショニング
• [LINEAR] KEYパーティショニング

RANGEパーティショニング

LANGEパーティショニングとは、値の範囲を絞って、パーティショニングする方法である。
あくまでも、パーティショニングする値がある程度決まっている場合などに使用できる。
今回は郵便番号は12万レコード前後で推移すると考え、ＩＤを30000毎に区切ってみる。
（サンプルでは、日付で絞る例がおおい）
「ENGINE=InnoDB」の後ろに以下のように書き加える。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (30000),
PARTITION p2 VALUES LESS THAN (60000),
PARTITION p3 VALUES LESS THAN (90000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

LISTパーティショニング

LISTパーティショニングは、あるカラムの値の候補が決まっている場合に使われる。
例えば、性別、誕生月、などであろうか・・・。
今回はいい例ではないかもしれないが、flg1を1,0に分けてみる。
「ENGINE=InnoDB」の後ろに以下のように書き加える。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LIST (flg1) (
PARTITION p1 VALUES IN (0),
PARTITION p2 VALUES IN (1)
);

しかし、これはエラーになる。

 A PRIMARY KEY must include all columns in the table's partitioning function

プライマリーはパーティショニングの定義に入れなければならない・・・とのこと。なんやねん。
といわけで、MySQLのパーティショニングで必要そうな工夫を元に以下みたいな書き方をしてみる。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LIST (( id * 0 ) + flg1) (
PARTITION p1 VALUES IN (0),
PARTITION p2 VALUES IN (1)
);

うーん・・・いけない・・・。
ちなみに、15.5. パーティショニングの制約と制限によれば、

とのこと。是非、取り除いてほしいですね。
どうやらlistを使用するにはprimaryを外さないと使えなさそうだが、いけてなさすぎるので今回はつかわない。

[LINEAR] HASHパーティショニング

HASHパーティショニングとは、行が格納されるパーティションを算出するのにMOD（剰余）を利用するものである。
RANGEやLISTと違い、あらかじめ値の範囲や候補をしらなくても動的にパーティショニングできる。
また、各パーティショニングのかたよりも無いと思われる。

また、LINER HASHというものがあるが、これは
15.2.3.1. LINEAR HASH パーティショニングによれば

とのことらしい、だが計算が難しくてよくわからないが漢(オトコ)のコンピュータ道によれば、

ということらしい。
なので、HASHを使う場合は「ENGINE=InnoDB」の後ろに以下のように書き加えるとよいだろう。
下記の例だと、idをキーにして1024パーティショニングすることになる。
１パーティショニングあたり、120くらい、めっちゃはやそう。
＊1024がパーティショニングの限界値

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LINEAR HASH (id) PARTITIONS 1024;

[LINEAR] KEYパーティショニング

KEYパーティショニングはPASSWORD()関数を使ってハッシュ値を算出する。PASSWORD関数を利用するので、文字列に対しても利用することが出来る。が、ここでもPrimaryの制約があり使用できそうにない（ためしていない）。
もし、教科書的に書くなら「ENGINE=InnoDB」の後ろに以下のように書き加える感じだと思う。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LINEAR KEY(ken) PARTITIONS 1024
);

パーティショニング確認

以上がパーティショニングの説明となる。
これらのテーブルのパーティショニングの状態をチェックするには、

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=postal

というＳＱＬを投げれば確認できる。
では、次回はパーティショニングのパフォーマンスについて述べていこうと思う。

関連の記事

• 2009 年 12 月 8 日 -- mysqlパーティショニングのまとめ③- パフォーマンス
• 2009 年 12 月 8 日 -- mysqlパーティショニングのまとめ① – 設定・再コンパイル
• 2010 年 1 月 17 日 -- 2010年の目標！！
• 2009 年 11 月 29 日 -- phpMyAdminでエラーはないがログインできない現象について
• 2009 年 11 月 28 日 -- symfonyでまずはプロジェクト作成

written by YSU \\ tags: mysql, まとめ

Trackback URL