パーティションの追加や削除が行われる点に関しては、レンジパーティショニングもリストパーティショニングもよく似ています。このため、これらのパーティションの管理をこのセクションで紹介します。ハッシュやキーでパーティショニングされたテーブルの使用については、項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 TABLEtbl_name
REORGANIZE PARTITIONpartition_list
INTO (partition_definitions
);
ここでは、tbl_name
はパーティショニングされたテーブルの名前で、partition_list
はカンマによって分けられた、変更するべき存在するパーティションの名前です。partition_definitions
はカンマによって分けられた、新しいパーティションの定義のリストで、CREATE
TABLE
で使用される
partition_definitions
リストと同様のルールに従います。(項12.1.8. 「CREATE TABLE
構文」
を参照してください)。REORGANIZE
PARTITION
を使用する時、複数のパーティションを1つに結合する、もしくは1つのパーティションを複数に分離することだけに制限されているわけではありません。例えば、以下の様に、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;