MySQL :: MySQL 8.0 リファレンスマニュアル :: 24.3.1 RANGE および LIST パーティションの管理

MySQL 8.0 リファレンスマニュアル / ... / RANGE および LIST パーティションの管理

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

レンジパーティションとリストパーティションの追加および削除は同様の方法で処理されるため、このセクションでは両方のパーティション化の管理について説明します。ハッシュまたはキーによってパーティション化されたテーブルの管理については、セクション24.3.2「HASH および KEY パーティションの管理」を参照してください。

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),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

パーティション p2 に挿入されているはずの項目を以下のように確認できます。

mysql> SELECT * FROM tr
    ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

次に示すように、パーティション選択を使用してこの情報を取得することもできます:

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

詳しくはセクション24.5「パーティション選択」,をご覧ください。

p2 という名前のパーティションを削除するには、次のコマンドを実行します。

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

注記

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)

注記

DROP PARTITION は、ネイティブパーティション化インプレース API でサポートされており、ALGORITHM={COPY|INPLACE} で使用できます。 ALGORITHM=INPLACE を使用した DROP PARTITION は、パーティションに格納されているデータを削除し、パーティションを削除します。ただし、ALGORITHM=COPY または old_alter_table=ON を使用した DROP PARTITION では、パーティションテーブルが再構築され、削除されたパーティションから互換性のある PARTITION ... VALUES 定義を持つ別のパーティションへのデータの移動が試行されます。別のパーティションに移動できないデータは削除されます。

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

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

データを失うことなくテーブルのパーティショニングを変更する場合は、代わりに ALTER TABLE ... REORGANIZE PARTITION を使用してください。 REORGANIZE PARTITION については、後続の説明またはセクション13.1.9「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=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 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  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 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 つあります。このテーブルには、削除したパーティションを定義する値リストに含まれていた値を持つ行を挿入できなくなります。 (例については、セクション24.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 (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

さらに、メンバーの最少年齢は 16 歳であるとします。カレンダが 2015 年末に近づくにつれて、2000 年 (以降) に生まれたメンバーを入学させる準備が間もなくできていることがわかりました。次のように members テーブルを変更することで、2000 年から 2010 年までに生まれた新しいメンバーを受け入れることができます。

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

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

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
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 (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

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 (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

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

1960 年より前に生まれたメンバーを表すすべての行を別のパーティションに移動するとします。すでに説明したように、これは ALTER TABLE ... ADD PARTITION を使用して行うことはできません。ただし、ALTER TABLE への別のパーティション関連拡張を使用して、これを行うことができます。

ALTER TABLE members REORGANIZE PARTITION n0 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 リストと同じルールに従います。 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 に指定されたパーティションの組み合わせの範囲または値セット全体と同じであるべきです。

たとえば、パーティション p1 と p2 は、このセクションの例として使用される members テーブルの 1980 年から 1999 年までをカバーします。これら 2 つのパーティションの再編成では、全体的に同じ年の範囲をカバーする必要があります。
RANGE によってパーティション化されたテーブルの場合、再編成できるのは隣接するパーティションのみで、レンジパーティションはスキップできません。

たとえば、1970 年より前の年は p0 でカバーされ、1990 年から 1999 年までの年は p2 でカバーされるため、ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... で始まるステートメントを使用して members テーブルの例を再編成することはできませんでした。そのため、これらは隣接するパーティションではありません。 (この場合、パーティション p1 はスキップできません。)
REORGANIZE PARTITION を使用して、テーブルで使用されるパーティション化のタイプを変更することはできません (たとえば、RANGE パーティションを HASH パーティションに変更したり、その逆を行うことはできません)。このステートメントを使用してパーティション化式またはカラムを変更することもできません。テーブルを削除および再作成せずにこれらのタスクのいずれかを実行するには、次に示すように ALTER TABLE ... PARTITION BY ... を使用できます:
```
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```

関連キーワード: 　PARTITION, TABLE, VALUES, LESS, THAN, テーブル, InnoDB, パーティショニング, members, RANGE

PREV HOME UP NEXT