パーティショニング機能

MySQL 5.1ではパーティショニング機能が実装されているのですが、そのおさらいをするぜ。（｀・ω・´）

パーティショニング機能の利用可否確認。

[test] > show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

SQL構文。CREATE TABLEのtable optionの後につける。ALTER TABLEでもつかえる。

partition_options:
    PARTITION BY
          [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr)
    [PARTITIONS num]
    [SUBPARTITION BY
          [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES {LESS THAN (expr) | MAXVALUE | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]

こんな感じで作る。年ごとに別のパーティションにする場合。パーティションの判定に使用するカラムは主キーあるいはその一部である必要がある。

[test] > CREATE TABLE t1 (c1 INT, c2 DATE, c3 VARCHAR(255), PRIMARY KEY(c1,c2))
    ->   PARTITION BY RANGE (YEAR(c2)) (
    ->     PARTITION p2004 VALUES LESS THAN (2005),
    ->     PARTITION p2005 VALUES LESS THAN (2006),
    ->     PARTITION p2006 VALUES LESS THAN (2007),
    ->     PARTITION p2007 VALUES LESS THAN (2008)
    ->   );
Query OK, 0 rows affected (0.00 sec)

このとき、生成されるファイルは以下。

mir@t43:/usr/local/mysql/data/test$ ll
合計 32
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2004.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2004.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2005.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2005.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2006.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2006.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2007.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2007.MYI
-rw-rw---- 1 mir mir 8608 2007-02-08 14:35 t1.frm
-rw-rw---- 1 mir mir   44 2007-02-08 14:35 t1.par

それぞれのパーティションにデータが入る。パーティションの定義範囲外の値はINSERTできない。

[test] > insert into t1 values (10, "2004-12-20", "hogehoge");
Query OK, 1 row affected (0.00 sec)

[test] > insert into t1 values (14, "2006-03-21", "fugafuga");
Query OK, 1 row affected (0.01 sec)

[test] > insert into t1 values (20, "2008-02-28", "murimuri");
ERROR 1513 (HY000): Table has no partition for value 2008

ちゃんと当該パーティションだけがサイズが増えている。

mir@t43:/usr/local/mysql/data/test$ ll
合計 40
-rw-rw---- 1 mir mir   24 2007-02-08 14:38 t1#P#p2004.MYD
-rw-rw---- 1 mir mir 2048 2007-02-08 14:38 t1#P#p2004.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2005.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2005.MYI
-rw-rw---- 1 mir mir   24 2007-02-08 14:39 t1#P#p2006.MYD
-rw-rw---- 1 mir mir 2048 2007-02-08 14:39 t1#P#p2006.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2007.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2007.MYI
-rw-rw---- 1 mir mir 8608 2007-02-08 14:35 t1.frm
-rw-rw---- 1 mir mir   44 2007-02-08 14:35 t1.par

2004年のデータが不要になったのでパーティションごと消す。

[test] > ALTER TABLE t1 DROP PARTITION p2004;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

ファイルが消えている。

mir@t43:/usr/local/mysql/data/test$ ll
合計 32
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2005.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2005.MYI
-rw-rw---- 1 mir mir   24 2007-02-08 14:39 t1#P#p2006.MYD
-rw-rw---- 1 mir mir 2048 2007-02-08 14:42 t1#P#p2006.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2007.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2007.MYI
-rw-rw---- 1 mir mir 8608 2007-02-08 14:42 t1.frm
-rw-rw---- 1 mir mir   40 2007-02-08 14:42 t1.par

2008年対応をするため、パーティションを追加する。

[test] > ALTER TABLE t1 ADD PARTITION (PARTITION p2008 VALUES LESS THAN (2009));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

ファイルが増えている。

mir@t43:/usr/local/mysql/data/test$ ll
合計 36
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2005.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2005.MYI
-rw-rw---- 1 mir mir   24 2007-02-08 14:39 t1#P#p2006.MYD
-rw-rw---- 1 mir mir 2048 2007-02-08 14:42 t1#P#p2006.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:35 t1#P#p2007.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:35 t1#P#p2007.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 14:46 t1#P#p2008.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 14:46 t1#P#p2008.MYI
-rw-rw---- 1 mir mir 8608 2007-02-08 14:46 t1.frm
-rw-rw---- 1 mir mir   44 2007-02-08 14:46 t1.par

今度は2008年のデータも入る。

[test] > insert into t1 values (20, "2008-02-28", "murimuri");
Query OK, 1 row affected (0.00 sec)

ストレージエンジンをMyISAMからInnoDBへ変えることもできる。

[test] > ALTER TABLE t1 ENGINE = INNODB;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

当然、ibdata1に入るのでfrmファイルとparファイル以外は消える。

mir@t43:/usr/local/mysql/data/test$ ll
合計 16
-rw-rw---- 1 mir mir 8608 2007-02-08 14:48 t1.frm
-rw-rw---- 1 mir mir   44 2007-02-08 14:48 t1.par

innodb_file_per_tableをONにしていると、ファイルが分割される。

mir@t43:/usr/local/mysql/data/test$ ll
合計 416
-rw-rw---- 1 mir mir 98304 2007-02-08 14:55 t1#P#p2004.ibd
-rw-rw---- 1 mir mir 98304 2007-02-08 14:55 t1#P#p2005.ibd
-rw-rw---- 1 mir mir 98304 2007-02-08 14:55 t1#P#p2006.ibd
-rw-rw---- 1 mir mir 98304 2007-02-08 14:55 t1#P#p2007.ibd
-rw-rw---- 1 mir mir  8608 2007-02-08 14:55 t1.frm
-rw-rw---- 1 mir mir    44 2007-02-08 14:55 t1.par

話は戻って、myisamであれば、データファイルとインデックスファイルの場所を以下のようにあとから移動が可能。

[test] > ALTER TABLE t1 REORGANIZE PARTITION p2007 INTO 
(PARTITION p2007 VALUES LESS THAN (2008)
DATA DIRECTORY = '/tmp/mysql' INDEX DIRECTORY = '/tmp/mysql' );

もちろん、CREATE TABLE時の指定も可能。

単にmvしてln -sしただけっぽい。ということは手動symlinkもokかも。

mir@t43:/usr/local/mysql/data/test$ ll
合計 28
-rw-rw---- 1 mir mir    0 2007-02-08 15:06 t1#P#p2004.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 15:06 t1#P#p2004.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 15:06 t1#P#p2005.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 15:06 t1#P#p2005.MYI
-rw-rw---- 1 mir mir    0 2007-02-08 15:06 t1#P#p2006.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 15:06 t1#P#p2006.MYI
lrwxrwxrwx 1 mir mir   25 2007-02-08 15:06 t1#P#p2007.MYD -> /tmp/mysql/t1#P#p2007.MYD
lrwxrwxrwx 1 mir mir   25 2007-02-08 15:06 t1#P#p2007.MYI -> /tmp/mysql/t1#P#p2007.MYI
-rw-rw---- 1 mir mir 8608 2007-02-08 15:06 t1.frm
-rw-rw---- 1 mir mir   44 2007-02-08 15:06 t1.par
mir@t43:/usr/local/mysql/data/test$ ll /tmp/mysql/
合計 4
-rw-rw---- 1 mir mir    0 2007-02-08 15:06 t1#P#p2007.MYD
-rw-rw---- 1 mir mir 1024 2007-02-08 15:06 t1#P#p2007.MYI

で、そろそろ長くなってきたので最後ですが、現時点のver5.1.14-betaでの主な制約

• InnoDBの外部キーは使用できない
• MERGEは使用できない
• FULLTEXTインデックスは使用できない

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

え、え、えええぇぇぇぇ！　orz.. FULLTEXTだめっすか。しかしまあそのうち対応してくれるでせう。