15.3.1 RANGE と LIST パーティションの管理|無料CGI PHPサイト(新着情報、営業日カレンダーなど)

15.3.1. `RANGE` と `LIST` パーティションの管理

パーティションの追加や削除が行われる点に関しては、レンジパーティショニングもリストパーティショニングもよく似ています。このため、これらのパーティションの管理をこのセクションで紹介します。ハッシュやキーでパーティショニングされたテーブルの使用については、項15.3.2. 「HASH や KEY パーティションの管理」を参照してください。RANGE や LIST パーティションの削除は追加よりも単純なので、先にこちらを紹介します。

RANGE か LIST によってパーティショニングされたテーブルからパーティションを削除するには、ALTER TABLE ステートメントを DROP PARTITION 節と使用することで達成できます。ここに単純な例を記します。すでにレンジで作成され、CREATE TABLE と INSERT ステートメントを使用して10のレコードで実装されたテーブルがあるとします。

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005)
    ->     );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'CD player', '1993-11-05'),
    ->     (3, 'TV set', '1996-03-10'),
    ->     (4, 'bookcase', '1982-01-10'),
    ->     (5, 'exercise bike', '2004-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'popcorn maker', '2001-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '1984-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)

どのアイテムが p2 パーティションに挿入されるべきかは、以下で確認できます。

mysql> SELECT * FROM tr
    -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id   | name      | purchased  |
+------+-----------+------------+
|    3 | TV set    | 1996-03-10 |
|   10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)

p2 と名づけられたパーティションを削除するには、以下のコマンドを実行してください。

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

注:MySQL 5.1 では、NDBCLUSTER ストレージエンジンは ALTER TABLE ... DROP PARTITION をサポートしません。ただし、この章で紹介される他の ALTER TABLE パーティショニングに関連する拡張はサポートされます。

パーティションを削除する時、そのパーティション内で記憶されていたデータも全て削除される ことに注意してください。以前の SELECT クエリを再起動させることでこれを確認できます。

mysql> SELECT * FROM tr WHERE purchased 
    -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

これにより、MySQL 5.1.10 では ALTER TABLE ...DROP PARTITION を実行する前にテーブルの DROP 権限があります。

テーブルの定義とパーティショニングスキーマを保持したままパーティションからデータを削除したい場合、TRUNCATE TABLE コマンドを実行してください。（詳しくは項12.2.9. 「TRUNCATE 構文」をご確認ください。）

データを 失わずに、テーブルのパーティションを変更する場合、ALTER TABLE ...REORGANIZE PARTITION を代わりに使用してください。REORGANIZE PARTITION に関する情報については、以下か項12.1.2. 「ALTER TABLE 構文」を参照してください。

これで SHOW CREATE TABLE コマンドを実行すれば、テーブルのパーティショニングの構造がどう変更したか確認できます。

mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL,
  `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE ( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)

'1995-01-01' と '2004-12-31' 間の purchased カラム値を変更されたテーブルに行挿入する時、p3 パーティションに記憶されます。以下の様に証明できます。

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased 
    -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|   11 | pencil holder  | 1995-07-12 |
|    1 | desk organiser | 2003-10-15 |
|    5 | exercise bike  | 2004-05-09 |
|    7 | popcorn maker  | 2001-11-22 |
+------+----------------+------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased 
    -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

ALTER TABLE ... DROP PARTITION の結果テーブルから削除された行の数は、同等の DELETE クエリによって処理された場合と違い、サーバによって報告されません。

LIST パーティショニングの削除は、RANGE パーティショニングの削除とと同様の ALTER TABLE ... DROP PARTITION 構文を使用します。ただし、テーブルの使用に対して、1つ重大な違いがあります。テーブルに、削除されたパーティションを定義する値リストに含まれていた値を、行挿入することができません。(項15.2.2. 「LIST パーティショニング」で例を参照してください。)

以前にパーティショニングされたテーブルに新しいレンジ、もしくはリストパーティションを追加する場合は、ALTER TABLE ... ADD PARTITION ステートメントを使用してください。RANGE によってパーティショニングされているテーブルには、これをすることによって存在するパーティションのリストに新しい絵レンジを追加できます。例えば、ユーザの所属する機関のメンバーデータを含むパーティショニングされたテーブルが、以下のようにあるとします。

CREATE TABLE members (
    id INT, 
    fname VARCHAR(25),
    lname VARCHAR(25), 
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1970),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990)
);

例えば、さらに、メンバーの最低年齢が16とします。2005年に近づくと、1990年に生まれたメンバー（そしてその年以降）を受け付けていることに気づき始めるでしょう。members テーブルを改良して、1990-1999に生まれた新メンバーを表すことができます。

ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

重要レンジによりパーティショニングされたテーブルでは、パーティションのリストに新しいパーティションを追加するため、ADD PARTITION を使用することができます。このようにして、存在するパーティションのまえ、もしくは間に新しいパーティションを追加すると、以下のようなエラー表示となります。

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION p3 VALUES LESS THAN (1960));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly ≫
   increasing for each partition

同じように、LIST によってパーティショニングされたテーブルに新しいパーティションを追加することができます。例えば、以下の様に定義されたテーブルでは：

CREATE TABLE tt (
    id INT, 
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

data カラム値 7、14、そして 21 含む行を記憶する新しいパーティションを追加することができます。

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

存在するパーティションの値リストに含まれる値を包含する新しい LIST パーティションを追加することは できません。試みると、以下のエラーが発生します。

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant ≫
                    in list partitioning

data カラム値 12 を含む行はすでにパーティション p1 に割り振られているため、テーブル tt に 12 を値リストに含む新しいパーティションを作成することはできません。これを達成するには、p1 を削除し、np を追加してから、改良された定義の新しい p1 を作成する必要があります。ただし、以前述べたとおり、p1 に記憶れた全てのデータの損失につながります。? 大抵、ユーザの意思とはかけ離れた結果となります。また、別の解決法で、新しいパーティションを含んだテーブルのコピーを作成し、かつデータを CREATE TABLE ...SELECT ... を使用して書き込み、古いテーブルを削除し新しいテーブルの名前をつけなおすことができますが、量の多いデータを取り扱っている時など、非常に多くの時間を要することがあります。加えて、これは高い有効性が求められている状況では、あまり推奨できる手段ではありません。

MySQL 5.1.6二始まり、以下の様に1つの ALTER TABLE ... ADD PARTITION ステートメントに複数のパーティションを追加することができます。

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010), 
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

幸い、MySQLのパーティショニング実装はデータを損失することなくパーティショニングを再定義する方法を提供しています。では、RANGE パーティショニングの例をいくつか見てみましょう。以下のように定義されている、members テーブルを思い出してください。

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) default NULL,
  `fname` varchar(25) default NULL,
  `lname` varchar(25) default NULL,
  `dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE ( YEAR(dob) ) (
  PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM, 
  PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
  PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)

例えば、1960年以前に生まれたメンバーを示す行を別のパーティションに移動させたいとします。すでに見たよう、ALTER TABLE ... ADD PARTITION を使用して行うのは不可能です。ただし、別の ALTER TABLE のパーティショニング関連の拡張をすることでこれを達成できます。

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

実質的に、このコマンドはパーティション p0 を2つの新しいパーティション s0 と s1 に分けます。p0 に記憶されていたデータを2つの PARTITION ... VALUES ... 節で記されるルールによってデータが移動させられます。これにより、s0 は YEAR(dob) が1960未満の、そして s1 は YEAR(dob) 1960 以上、1970 未満のレコードを含む行が存在します。

REORGANIZE PARTITION 節を使用して隣接するパーティションを結合することができます。以下の様に members テーブルを以前のパーティションに戻すことができます。

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

REORGANIZE PARTITION を使用してパーティションを分離、結合することでデータが失われることはありません。上記のステートメントを実行する際、MySQLは s0 と s1 に記憶されていたパーティションを p0 に移動させます。

REORGANIZE PARTITION の一般的な構文は

ALTER TABLE tbl_name 
    REORGANIZE PARTITION partition_list 
    INTO (partition_definitions);

ここでは、tbl_name はパーティショニングされたテーブルの名前で、partition_list はカンマによって分けられた、変更するべき存在するパーティションの名前です。partition_definitions はカンマによって分けられた、新しいパーティションの定義のリストで、CREATE TABLE で使用される partition_definitions リストと同様のルールに従います。(項12.1.8. 「CREATE TABLE 構文」を参照してください)。REORGANIZE PARTITION を使用する時、複数のパーティションを１つに結合する、もしくは１つのパーティションを複数に分離することだけに制限されているわけではありません。例えば、以下の様に、members テーブル内の4つのパーティションを2つに再編成することができます。

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

REORGANIZE PARTITION を LIST によってパーティショニングされたテーブルと使用することもできます。それでは、リストによってパーティショニングされたテーブル tt に新しいパーティションを追加する問題に戻ってみましょう。この問題は、新しいパーティションに、すでに存在するパーティションの値リストに含まれる値が新しいパーティションにも含まれていたため、失敗に終わりました。これは、衝突しない値を含むパーティションを追加することで対処し、新しいパーティションを存在するパーティションを再編成する際に、存在するパーティション内に含まれていた値が新しいパーティションに移動することで、解決できます。

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

RANGE や LIST を使用してパーティショニングされたテーブルを再度パーティショニングする際に使用される ALTER TABLE ... REORGANIZE PARTITION に関する重要なポイントは、以下のとおりです。

新しいパーティショニングスキーマを決定するのに使用される PARTITION 節は CREATE TABLE ステートメントで使用されているものに対して同じルールが適用されます。

さらに重要なのは、新しいパーティショニングスキーマには重複するレンジや値のセットがあってはならないことです(RANGE や LIST によってパーティショニングされたテーブルを再編成する際に適用する)。

注:MySQL 5.1.4以前では、INTO 節内で存在するパーティションの名前を、それらのパーティションが再定義、もしくは削除されていても再利用することはできませんでした。詳細については、項C.1.13. 「Changes in release 5.1.4 (21 December 2005)」を参照してください。
partition_definitions リストに含まれるパーティションのコンビネーションは、partition_list で名づけられている結合されたパーティションと同じレンジ、値のセットになります。

たとえば、このセクションで例として使用されている members テーブルでは、パーティション p1 と p2 は合わせて1980から1999の期間をカバーしているということになります。よって、これらパーティションのどの再編成も、最終的には同じ期間をカバーすることになります。
RANGE によりパーティショニングされたテーブルに関しては、隣接するパーティションのみ再編成することができます。レンジパーティションを飛び越すことはできません。

たとえば、このセクションで使用されている members テーブルを ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... で始まるステートメントに再編成することはできません。なぜなら、p0 1970 以前の年度をカバーしており、p2 は1990から1999をカバーするため、この二つは隣接するパーティションにはなりえません。
REORGANIZE PARTITION を使用してテーブルのパーティショニングのタイプを変更することはできません。それは、RANGE パーティショニングを HASH パーティショニングや、その逆 vice versa もまた不可能ということになります。また、このコマンドを使用してパーティショニング表現やカラムを変更することができます。両方のタスクを、テーブルを削除もしくは再作成せずに行う場合、ALTER TABLE ...PARTITION BY ... を使用することができます。例:
```
ALTER TABLE members 
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```

無料PHPプログラム

15.3.1. RANGE と LIST パーティションの管理

15.3.1. `RANGE` と `LIST` パーティションの管理