MySQLのパーティショニングのハマリ所

mysql memo

　今までマニュアルを斜め読みした程度で「MySQL 5.1 から使えるようになったパーティショニング。便利そうだな」などと思っていたのですが、このたび実際に使いたいシーンが出てきたので、利用を前提に調べてみました。
　そしたら、ハマることハマること。やりたいことは、日付カラムで１日ごとのパーティションにしたいだけだったのですが（向こう２年分くらいパーティション作っておいて、運用で「古いパーティション削除→新しいのを追加」でいいかなと考えていました）、これができない。


　ハマりの原因は「パーティショニングの条件は、プライマリーキーの一部でなければならない」という制約。
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-limitations.html


　今回使用を検討したテーブルはプライマリーキーが重要だったので、

CREATE TABLE pt(
  id   int      not null, 
  dt   datetime not null, 
  col3 varchar(128),
  col4 int,PRIMARY KEY (id) 
) ENGINE=InnoDB
 PARTITION BY RANGE (TO_DAYS(dt))(
   PARTITION p1 VALUES LESS THAN ( TO_DAYS("2010-05-22" )),
   PARTITION p2 VALUES LESS THAN ( TO_DAYS("2010-05-23" )),
   PARTITION p9 VALUES LESS THAN (MAXVALUE)
 )

　これは、プライマリーキー id をパーティションの条件に含めていないのでエラーになります。

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function


　かといって、パーティション条件（TO_DAYS(dt)) の中に id を絡めても、「PKを為すカラムの部分集合」ではないので、無効です(エラーのままです)。


　で、どうするか。


　　「おもいきって、dt カラムをプライマリーキーにいれちゃう！」


　という手を考えました。

CREATE TABLE pt(
  id   int      not null, 
  dt   datetime not null, 
  col3 varchar(128),
  col4 int,
  PRIMARY KEY (id,dt) 
) ENGINE=InnoDB
 PARTITION BY RANGE (TO_DAYS(dt))(
   PARTITION p1 VALUES LESS THAN ( TO_DAYS("2010-05-22" )),
   PARTITION p2 VALUES LESS THAN ( TO_DAYS("2010-05-23" )),
   PARTITION p9 VALUES LESS THAN (MAXVALUE)
 );
Query OK, 0 rows affected (0.09 sec)

　ううん・・・・嬉しくない。嬉しくないけどやむを得ないのかなぁ。。。


　ちなみに、ストレージの占有量はともかく、idカラムのユニーク性は（dtカラムによらず）確保したいと思って UNIQUE 指定を加えると、この UNIQUE 指定には、パーティションで指定されている dt カラムが入っていないということでエラーになります。

CREATE TABLE pt(
  id   int      not null, 
  dt   datetime not null, 
  col3 varchar(128),
  col4 int,
  PRIMARY KEY (id,dt),
  UNIQUE KEY (id)
) ENGINE=InnoDB
 PARTITION BY RANGE (TO_DAYS(dt)+id*0)(
   PARTITION p1 VALUES LESS THAN ( TO_DAYS("2010-05-22" )),
   PARTITION p2 VALUES LESS THAN ( TO_DAYS("2010-05-23" )),
   PARTITION p9 VALUES LESS THAN (MAXVALUE)
 )
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

　意外と取り扱いが難しいパーティショニングでした。。


　もういっこのアイデアとしては、もう「日付単位できれいに」を諦めて、たとえばid100万件ごとに次のパーティションに行くような分割をしちゃおうか、などとも考えてみています。
　カラになったパーティションって、INFORMATION_SCHEMA.PARTITIONS のTABLE_ROWS 値を信じてもいいのかな。
古いデータの入ったパーティションをなくして新しいパーティションを作る部分の運用がまだ見えていません。



　まとまっていませんが、あれこれアタマの中が混乱したものの吐き出しとして、このメモをしたためます。



追記(2010/05/26):
　 id:sh2 さんにブックマークコメントをいただきました
　　「「MySQL 5.1のパーティショニング機能を使うときはDATETIME列を主キーに入れて、TO_DAYS(dt)をパーティション式にしましょう。それ以外の使い方は当面しないでください」っておととし社内向けの設計ガイドに書いた」　
　とのことで、とにかく「おととし」というところに私は感動したわけですが、それ以外の点として、
(id, dt) の組で、かつ id のみでの検索がそれなりに行われることがあるのだと、検索効率が(ページに入るローの数が減ってしまうので）悪化するのでは？と思いました。　まぁパーティションによってそれを上回る成果を得られるようにも思いますが。。　あと、実は id 指定の検索よりも dt（の範囲）指定の検索のほうが多いのですが、その場合 (id, dt) よりも (dt, id) でPK張った方が良さそうな気が（妄想）するのですが、どうなんでしょうね。



.