MySQLのパーティショニングで必要そうな工夫

MySQL 5.1のパーティショニングを試してみた。マニュアルはMySQL :: MySQL 5.1 リファレンスマニュアル :: 15 パーティショニングを参照のこと。試してみた環境は、MacのParallels Desktop上のCentOS 5。
まずはMySQL 5.1をソースからインストール。マニュアルには次のように書かれている。

ソースからコンパイルする場合には、--with-ndbcluster、--with-partitionオプションとともにconfigureを実行して下さい。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 15 パーティショニング

この通りにすると、ここの記載のように非推奨オプションだと言われてしまうので、--with-pluginsを使って指定するようにした。今回の味見configureオプションは次の通り。

./configure --prefix=/usr/local/mysql \
--with-charset=utf8 \
--with-extra-charsets=complex \
--enable-thread-safe-client \
--enable-local-infile \
--enable-assembler \
--disable-shared \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--with-unix-socket-path=/tmp/mysql.sock \
--with-plugins=myisam,innobase,ndbcluster,partition

インストールやGRANTなどは省略。次のように表示されれば万事準備OK。

mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   | 
+-------------------+-------+
1 row in set (0.01 sec)

CREATE TABLEするときにどのようなパーティションを作成するか決める。マニュアルのパーティショニングのタイプに書かれている。
紹介されているサンプルをみると、日付などの大小比較で分割するならRANGE、大小の順番にはならないけれど分割定義を予め羅列するならLIST、特定カラムを基準に分割するならHASH、キーに対して自動的に分割するならKEYという感じ。
単純な掲示板を想定してみる。掲示板IDごとに書き込みを分割できれば、掲示板IDで絞り込んだ場合に該当書き込みが単一のパーティションから取得できるので、レスポンスの向上が期待できる。そこで次のようなCREATE TABLEを書いてみた。

CREATE TABLE entries (
    id INT NOT NULL AUTO_INCREMENT,
    thread_id INT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (id)
)
PARTITION BY HASH (thread_id)
PARTITIONS 2;

これはエラーになる。エラー内容は「A PRIMARY KEY must include all columns in the table's partitioning function」でプライマリキーは必ずパーティション定義に入れないといけない。プライマリキーでレコードを取得するときに、そのレコードがどのパーティションに存在するかわからないと全てのパーティションを調べなければならなくなってしまう、ということかなと想像（インデックス側で工夫してくれるかと思ったけれど）。
そこで次のようにするとCREATE TABLEは通るのだけど、これはこれで意図と違う気がする。

CREATE TABLE entries (
    id INT NOT NULL AUTO_INCREMENT,
    thread_id INT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (id, thread_id)
)
PARTITION BY HASH (id + thread_id)
PARTITIONS 2;

プライマリキーで単一レコードを取得するのであれば速くなるだろうけれども、掲示板IDを基準に考えると複数のパーティションに分散してしまう。無理矢理だけど回避しようとするとこんな感じか（CREATE TABLEは成功するしパーティションのデータサイズをみると意図したように分割されている様子）。

CREATE TABLE entries (
    id INT NOT NULL AUTO_INCREMENT,
    thread_id INT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (id, thread_id)
)
PARTITION BY HASH ((id * 0) + thread_id)
PARTITIONS 2;

プライマリキーがAUTO_INCREMENTなテーブルはMySQLだとよくあるパターンじゃないかと思うけれども、パーティショニングを考えるとあまり良くないかもしれない。AUTO_INCREMENTに頼ることはできなくなるけれど、次のようにした方がすっきりする。

CREATE TABLE entries (
    thread_id INT NOT NULL,
    entry_no INT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (thread_id, entry_no)
)
PARTITION BY HASH (thread_id + (entry_no * 0))
PARTITIONS 2;

日付ごとにRANGEでパーティショニングする場合もプライマリキーを指定する必要がある。

CREATE TABLE entries (
    id INT NOT NULL,
    value TEXT NOT NULL,
    separated DATE NOT NULL DEFAULT '9999-12-31',
    PRIMARY KEY (id)
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (2009),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN (2011),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

これはやはりエラーになるので、separatedをプライマリキーに含めるとか、idのプライマリキーの指定をやめるとかしないといけない。
このプライマリキーの扱いについては、マニュアルのパーティショニングの制約と制限に書いてある。「将来的に、この制限をMySQLから取り除く方向で開発を進めています。」と書かれているので何かしら解消するのかもしれないけれども。
プライマリキー（ユニークキーも同じ）を付けたい理由の一つにレコードの確実な絞り込みがあると思う。このレコードを削除したいと考えたときにユニークな印がないと困る。ログのように編集することも消すこともないようなデータであれば、日付などを基準にスムーズにパーティショニングできそうだけれども、データの操作が必要なテーブルだとパーティショニングのために色々と工夫が必要だなと思った。