MySQLのデータ分割。パーティション（Partition）とは

最近、DBのパーティションという言葉をよく聞くようになった。
DBのパーティションとは、「データの分割」。

自分が扱うアプリケーションはまだこれを扱うべき状況にはなっていないが、いまいちどういう物なのか分かっていなかったので調べてみた。

主にMySQLについて調べたので、他のDBミドルウェアとマッチしない部分はあるかも。
※MySQL5.1からパーティションを利用可
主に、http://dev.mysql.com/doc/refman/5.1/ja/partitioning-overview.html
を参考にして自分なりに噛み砕いた。

メリット

そもそも、なぜデータ分割をするのか。
データ分割する事で、分割方法の定義、その管理、それを扱うアプリ側の実装、調査のコスト等いろいろ増えそうなのに。
メリットを洗い出してみた。こんな感じかな。

データの大量格納
- 1つのテーブルを分割できるので、1つのディスク/ファイルシステムでを超える量を格納できる

負荷の軽減
- テーブル内のデータを分割で減らせるので、クエリに探索されるデータが減る。
- そのマシンに保持される分割データを漁るクエリを受ける確立が高くなるので、クエリキャッシュのヒット率も高くなる（特定のデータしか持っていないので、クエリキャッシュをもつメモリも有効に使える）
- 集約関数(SUM/COUNT)を並列化させられる（速度向上？)

逆にデメリットは、分割方法の定義、その管理、それを扱うアプリ側の実装、調査のコスト等いろいろ増えそう等だろうか。
パーティションは進んで手を出していくものでなく、チューニングの一環だと言えそう。
また、my.cnfの設定変更といった一度行えば終るようなものでなく、その後の運用にも関わってくるものとなるので簡単に手を出すべきものでも無さそう。

パーティションの種類

パーティションのデータ分割方法には、「RANGE、LIST、HASH、KEY」がある。

■RANGE(範囲）
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-range.html

とある値（関数適用した値でも良い）の範囲でパーティションを作る方法。
下記は、YEAR(created_at)の値がそれぞれ、1970〜/1980〜/1990〜の範囲でパーティションを設定する例。

PARTITION BY RANGE( YEAR(created_at) ) (
    PARTITION p0 VALUES LESS THAN (1970),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990)
);

○用途
数あるパーティションの分割方法でこれが一番使い易そう
・視聴データ、発言履歴等、時間指定で取得される事が考えられる物。
　WHERE区が特定のパーティションのみで取得（刈り込み発動）されるすると尚良さそう
　- 刈り込み
　http://dev.mysql.com/doc/refman/5.1/ja/partitioning-pruning.html

■LIST(規則）
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-list.html
とある値（関数適用した値でも良い）の、指定リストでパーティションを作る方法。
下記は、値が[5,10,15]の場合、[6,12,18]でパーティションを分けている。

PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

○用途
すぐにこれだ！というものが思いつかなかったが、こんな感じだろうか。
・特定のグループ、属性等で分割する時
　たとえば、?？グループに紐づくデータ、オプション番号？？に紐づくデータ等

■HASH(範囲）
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-hash.html
事前に決めたパーティションで、均等にデータを割り振る方法。
先のRANGE、LISTと比べて大きく概念が違う。
RANGE、LIST = ユーザがデータの分割方法を設定する
HASH = 「分割に使う値」と分割数だけ設定し、実際の分割方法はMySQLに任せる。
分割方法はMOD(HASH設定した値,パーティション数）で算出する。

下記の例は、YEAR(hired)を「分割に使う値」

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

この指定方法の場合、データの分割先の決定方法は

MOD(YEAR(hired),4)

となり、もしhiredが「'2005-09-01'」であれば

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

とパーティションの1となる。

○用途
LISTやRANGEと違って、単純にデータを分割しただけの時に使うようなものっぽい。
（MODの結果によって、どうパーティショニングされるかを予測してアプリを組むとか特種な事はしなそうなので）

■KEY(範囲）
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-key.html
「分割に使う値」と分割数だけ設定し、実際の分割方法はMySQLに任せるとう点でHASHと似ている。
・HASHと違う点
「分割に使う値」は、プライマリキー(またはユニークキー）に対しサーバ側で決めたアルゴリズム（MySQL クラスタ:MD5 / 他のストレージエンジン / PASSWORD())。

CREATE TABLE user (   
    id INT NOT NULL PRIMARY KEY,   
    name VARCHAR(20) 
) 
PARTITION BY KEY() 
PARTITIONS 2;

上記の場合、「id」がPRIMARY KEYとなる。じゃあ、PRIMARY KEYがない場合は？

CREATE TABLE user (   
    id INT NOT NULL,   
    name VARCHAR(20),
    UNIQUE KEY (id) 
) 
PARTITION BY KEY() 
PARTITIONS 2;

UNIQUE KEYの値が対象となる。
つまり、PRIMARY/UNIQUEキーが無いといけない。
また、UNIQUEキーはNOT NULLじゃないといけない（NULL値じゃMD5/PASSWORD関数を適用できない）

○用途
　HASHと同じ。