MySQL :: MySQL 8.0 リファレンスマニュアル :: 24.3.3 パーティションとサブパーティションをテーブルと交換する

MySQL 8.0 リファレンスマニュアル / ... / パーティションとサブパーティションをテーブルと交換する

24.3.3 パーティションとサブパーティションをテーブルと交換する

MySQL 8.0 では、ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt を使用して、テーブルパーティションまたはサブパーティションをテーブルと交換できます。ここで、pt はパーティション化されたテーブル、p はパーティション化されていないテーブル nt と交換する pt のパーティションまたはサブパーティションです (次の記述が true である場合)。

テーブル nt 自体はパーティション化されていない。
テーブル nt は一時テーブルではない。
テーブル pt および nt の構造はそれ以外の点で同じである。
テーブル nt は外部キー参照を含まず、ほかのどのテーブルも nt を参照する外部キーを持たない。
nt 内に p のパーティション定義の境界の外に存在する行がない。この条件は、WITHOUT VALIDATION が使用されている場合は適用されません。
InnoDB テーブルの場合、両方のテーブルで同じ行形式が使用されます。 InnoDB テーブルの行形式を確認するには、INFORMATION_SCHEMA.INNODB_TABLES をクエリーします。
nt には、DATA DIRECTORY オプションを使用するパーティションはありません。この制限は、MySQL 8.0.14 以降の InnoDB テーブルではなくなりました。

ALTER TABLE ステートメントに通常必要な ALTER、INSERT、および CREATE 権限に加えて、ALTER TABLE ... EXCHANGE PARTITION を実行するための DROP 権限が必要です。

ALTER TABLE ... EXCHANGE PARTITION の次の影響も考慮してください。

ALTER TABLE ... EXCHANGE PARTITION を実行しても、パーティション化されたテーブルまたは交換されるテーブルに対するトリガーは呼び出されません。
交換されるテーブル内の AUTO_INCREMENT カラムがリセットされます。
IGNORE キーワードは、ALTER TABLE ... EXCHANGE PARTITION と一緒に使用された場合、効果を持つません。

ALTER TABLE ... EXCHANGE PARTITION の構文を次に示します。ここで、pt はパーティションテーブル、p は交換されるパーティション (またはサブパーティション)、nt は p と交換される非パーティションテーブルです:

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

オプションで、WITH VALIDATION または WITHOUT VALIDATION を追加できます。 WITHOUT VALIDATION が指定されている場合、ALTER TABLE ... EXCHANGE PARTITION 操作では、パーティションを非パーティションテーブルと交換するときに行ごとの検証は実行されず、データベース管理者は行がパーティション定義の境界内にあることを確認する責任を負うことができます。 WITH VALIDATION がデフォルトです。

単一 ALTER TABLE EXCHANGE PARTITION ステートメントでは、1 つのパーティションまたはサブパーティションのみを 1 つのパーティション化されていないテーブルのみと交換できます。複数のパーティションまたはサブパーティションを交換するには、複数の ALTER TABLE EXCHANGE PARTITION ステートメントを使用してください。 EXCHANGE PARTITION は、ほかの ALTER TABLE オプションと組み合わせることはできません。パーティション化されたテーブルによって使用されるパーティショニングおよび (該当する場合) サブパーティショニングには、MySQL 8.0 でサポートされる任意のタイプを選択できます。

パーティションをパーティション化されていないテーブルと交換する

次の SQL ステートメントを使用して、パーティション化されたテーブル e が作成および移入されているとします。

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

ここで、e2 という名前の、e のパーティション化されていないコピーを作成します。これは、mysql クライアントを使用して次のように行うことができます。

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

テーブル e のどのパーティションに行が含まれるかは、次のように INFORMATION_SCHEMA.PARTITIONS テーブルを照会することで確認できます。

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

注記

パーティション化された InnoDB テーブルの場合、INFORMATION_SCHEMA.PARTITIONS テーブルの TABLE_ROWS カラムに示される行数は、SQL 最適化で使用される見積もり値であり、常に正確とはかぎりません。

テーブル e のパーティション p0 をテーブル e2 と交換するには、次に示すように ALTER TABLE を使用できます:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)

より正確に言うと、ここで発行したステートメントによって、パーティションで見つかる行がテーブルで見つかるものと交換されます。これがどのように行われたかは、前のように INFORMATION_SCHEMA.PARTITIONS テーブルを照会することで観察できます。パーティション p0 で以前は見つかったテーブル行が存在しなくなっています。

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

テーブル e2 を照会すると、「見つからない」行がそこで見つかります。

mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

パーティションと交換されるテーブルは、必ずしも空である必要はありません。これを示すために、まずテーブル e に新しい行を挿入し、50 未満の id カラム値を選択してこの行がパーティション p0 に格納されていることを確認し、PARTITIONS テーブルをクエリーしてこれを後で検証します:

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';            
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

ここで、前と同じ ALTER TABLE ステートメントを使用して、ふたたびパーティション p0 をテーブル e2 と交換します。

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

次のクエリーの出力は、ALTER TABLE ステートメントを発行する前に、パーティション p0 に格納されていたテーブル行およびテーブル e2 に格納されていたテーブル行の配置が切り替わったことを示しています。

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

一致しない行

ALTER TABLE ... EXCHANGE PARTITION ステートメントを発行する前にパーティション化されていないテーブルで見つかる行は、それらがターゲットパーティションに格納されるために必要な条件を満たしている必要があり、そうでない場合はステートメントが失敗することを覚えておいてください。これがどのように発生するかを確認するために、まずテーブルe のパーティション p0 のパーティション定義の境界外の行を、e2 に挿入します。たとえば、id カラム値が大きすぎる行を挿入してから、テーブルをパーティションとふたたび交換してみてください。

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

WITHOUT VALIDATION オプションのみがこの操作の成功を許可します:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

パーティション定義と一致しない行を含むテーブルとパーティションを交換する場合、データベース管理者は、REPAIR TABLE または ALTER TABLE ... REPAIR PARTITION を使用して実行できる不一致の行を修正する必要があります。

行ごとの検証なしでのパーティションの交換

多数の行を含むテーブルとパーティションを交換するときに時間のかかる検証を回避するために、ALTER TABLE ... EXCHANGE PARTITION ステートメントに WITHOUT VALIDATION を追加することで、行ごとの検証ステップをスキップできます。

次の例では、パーティションを非パーティションテーブルと交換する際の実行時間の違いを、検証ありおよび検証なしで比較します。パーティションテーブル (テーブル e) には、それぞれ 100 万行の 2 つのパーティションが含まれます。テーブル e の p0 の行は削除され、p0 は 100 万行のパーティション化されていないテーブルと交換されます。 WITH VALIDATION 操作には 0.74 秒かかります。比較すると、WITHOUT VALIDATION 操作には 0.01 秒かかります。

# Create a partitioned table with 1 million rows in each partition

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

# View the rows in each partition

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

# Create a nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

# Create another nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)

# Confirm that the partition was exchanged with table e2

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Once again, drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

# Confirm that the partition was exchanged with table e3

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

サブパーティションをパーティション化されていないテーブルと交換する

ALTER TABLE ... EXCHANGE PARTITION ステートメントを使用して、サブパーティション化されたテーブルのサブパーティション (セクション24.2.6「サブパーティショニング」を参照してください) をパーティション化されていないテーブルと交換することもできます。次の例では、まず RANGE によってパーティション化され、KEY によってサブパーティション化されたテーブル es を作成し、テーブル e と同様にこのテーブルに移入してから、このテーブルの空のパーティション化されていないコピー es2 を作成します。

mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

テーブル es の作成時にサブパーティションに明示的に名前を付けませんでしたが、次に示すように、INFORMATION_SCHEMA から PARTITIONS テーブルの SUBPARTITION_NAME カラムを含めて、生成された名前を取得できます:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

次の ALTER TABLE ステートメントは、テーブル es のサブパーティション p3sp0 をパーティション化されていないテーブル es2 と交換します:

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

次のクエリーを発行することで、それらの行が交換されたことを確認できます。

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

テーブルがサブパーティション化されている場合、次に示すように、パーティション化されていないテーブルと交換できるのは、テーブルのパーティション全体ではなくサブパーティションのみです。

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

テーブル構造は厳密に比較されます。パーティションテーブルと非パーティションテーブルのカラムおよびインデックスの数、順序、名前およびタイプは正確に一致する必要があります。また、両方のテーブルが同じストレージエンジンを使用している必要があります。

mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)

mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

関連キーワード: 　TABLE, テーブル, PARTITION, NAME, rows, 交換, ROWS, FROM, Query, INFORMATION

PREV HOME UP NEXT