19.3.1 RANGE および LIST パーティションの管理

RANGE および LIST パーティションは、パーティションの追加と削除がどのように処理されるかに関してはよく似ています。このため、このセクションでは両方のタイプのパーティショニングの管理について説明します。ハッシュまたはキーによってパーティション化されたテーブルの管理については、セクション19.3.2「HASH および KEY パーティションの管理」を参照してください。RANGE または LIST パーティションの削除は追加よりも単純なので、これを最初に説明します。

RANGE または LIST によってパーティション化されたテーブルからパーティションを削除する操作は、DROP PARTITION 句付きで ALTER TABLE ステートメントを使用することで実行できます。非常に基本的な例ですが、次の 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)

注記

NDB ストレージエンジンは 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)

このため、テーブルに対して ALTER TABLE ... DROP PARTITION を実行するには、そのテーブルの DROP 権限が必要です。

テーブル定義およびそのパーティショニングスキームを保持したまま、すべてのパーティションからすべてのデータを削除する場合は、TRUNCATE TABLE ステートメントを使用してください。(セクション13.1.33「TRUNCATE TABLE 構文」を参照してください)。

データを失うことなくテーブルのパーティショニングを変更する場合は、代わりに ALTER TABLE ... REORGANIZE PARTITION を使用してください。REORGANIZE PARTITION については、後続の説明またはセクション13.1.7「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)

purchased カラム値が '1995-01-01' から '2004-12-31' まで (両端を含む) の新しい行を変更済みテーブルに挿入すると、それらの行はパーティション 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 つあります。このテーブルには、削除したパーティションを定義する値リストに含まれていた値を持つ行を挿入できなくなります。(例については、セクション19.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 members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

範囲によってパーティション化されたテーブルで ADD PARTITION を使用するときは、パーティションリストの上端にのみ新しいパーティションを追加できます。この方法で新しいパーティションを既存のパーティションの間または前に追加しようとすると、次のようにエラーになります。

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

この問題は、次のように最初のパーティションを 2 つに再編成し、それらの間の範囲を分割することで回避できます。

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

SHOW CREATE TABLE を使用することで、ALTER TABLE ステートメントによって意図した効果が得られたことを確認できます。

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=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1960) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2000) ENGINE = InnoDB) */
1 row in set (0.00 sec)

セクション13.1.7.1「ALTER TABLE パーティション操作」も参照してください。

ALTER TABLE ... ADD PARTITION を使用して、LIST によってパーティション化されたテーブルに新しいパーティションを追加することもできます。次の CREATE TABLE ステートメントを使用してテーブル tt が定義されているとします。

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 にすでに割り当てられているため、値リストに 12 が含まれる新しいパーティションをテーブル tt に作成することはできません。これを実現するために、p1 を削除し、np を追加してから、定義を変更した新しい p1 を追加できます。ただし、すでに説明したように、これによって p1 に格納されていたすべてのデータが失われるので、これが実際にやりたいことでないことが多いです。別の解決策になる可能性があるのが、CREATE TABLE ... SELECT ... を使用して、新しいパーティショニング付きでテーブルのコピーを作成し、データをそこにコピーしてから、古いテーブルを削除して新しいテーブルを名前変更することですが、これは大量のデータを扱うときに非常に時間がかかる可能性があります。高可用性が要求される状況では実行できない可能性もあります。

次のように単一 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 は変更する 1 つ以上の既存のパーティションの名前のカンマ区切りのリストです。partition_definitions は、CREATE TABLE で使用される partition_definitions リスト (セクション13.1.17「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)
);

LIST によってパーティション化されたテーブルで REORGANIZE PARTITION を使用することもできます。リストによってパーティション化された 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 によってパーティション化されたテーブルを再編成するとき) を指定できないことを覚えておくべきです。

• partition_definitions リストのパーティションの組み合わせは、partition_list に指定されたパーティションの組み合わせの範囲または値セット全体と同じであるべきです。

  たとえば、このセクションの例として使用されている members テーブルでは、パーティション p1 および p2 が 1980 年から 1999 年までを範囲としています。このため、これら 2 つのパーティションを再編成する場合は、全体として同じ年範囲を含むべきです。

• RANGE によってパーティション化されたテーブルの場合は、隣接するパーティションのみを再編成できます。範囲パーティションを飛ばすことはできません。

  たとえば、このセクションの例として使用されている members テーブルを、ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... で始まるステートメントを使用して再編成することはできません。p0 は 1970 年より前の年を範囲とし、p2 は 1990 年から 1999 年まで (両端を含む) を範囲としていて、この 2 つは隣接するパーティションではないためです。

• REORGANIZE PARTITION を使用して、テーブルのパーティショニングタイプを変更することはできません。つまり、たとえば、RANGE パーティションを HASH パーティションに変更することはできません (逆も不可)。このコマンドを使用してパーティショニング式またはカラムを変更することもできません。これらの作業をテーブルを削除および再作成することなく実現するために、ALTER TABLE ... PARTITION BY ... を使用できます。例:

  ALTER TABLE members
      PARTITION BY HASH( YEAR(dob) )
      PARTITIONS 8;