22.3.3 用 table 交换分区和子分区

在 MySQL 5.7 中，可以使用ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt与 table 交换 table 分区或子分区，其中* pt 是分区 table， p 是 pt 的分区或子分区与未分区 table nt *交换，前提是以下 Statements 正确：

table* nt *本身未分区。
table* nt *不是临时 table。
table* pt 和 nt *的结构在其他方面相同。
tablent不包含外键引用，其他 table 也没有任何引用nt的外键。
- nt 中没有任何行位于 p *的分区定义的边界之外。如果使用WITHOUT VALIDATION选项，则此条件不适用。 [{WITH|WITHOUT} VALIDATION]选项是在 MySQL 5.7.5 中添加的。
对于InnoDBtable，两个 table 都使用相同的行格式。要确定InnoDBtable 的行格式，请查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。
nt没有任何使用DATA DIRECTORY选项的分区。对于 MySQL 5.7.25 及更高版本中的InnoDBtable，取消了此限制。

除了ALTER TABLE语句通常需要的ALTER，INSERT和CREATE特权外，您还必须具有DROP特权才能执行ALTER TABLE ...交换分区。

您还应该注意ALTER TABLE ...交换分区的以下影响：

执行ALTER TABLE ...交换分区不会在分区 table 或要交换的 table 上调用任何触发器。
交换 table 中的所有AUTO_INCREMENT列都将重置。
与ALTER TABLE ... EXCHANGE PARTITION一起使用时，IGNORE关键字无效。

此处显示ALTER TABLE ...交换分区语句的语法，其中* pt 是分区 table， p 是要交换的分区或子分区，而 nt 是要与 p *交换的未分区 table：

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

(可选)您可以附加WITH VALIDATION或WITHOUT VALIDATION子句。如果指定WITHOUT VALIDATION，则在交换未分区 table 的分区时，ALTER TABLE ...交换分区操作不会执行逐行验证，从而使数据库 Management 员可以负责确保行在分区定义的边界内。 WITH VALIDATION是默认行为，无需明确指定。 [{WITH|WITHOUT} VALIDATION]选项是在 MySQL 5.7.5 中添加的。

在一个更改 table 交换分区语句中，可以与一个且只有一个未分区 table 交换一个且只有一个分区或子分区。要交换多个分区或子分区，请使用多个更改 table 交换分区语句。 EXCHANGE PARTITION不能与其他ALTER TABLE选项结合使用。分区 table 使用的分区和(如果适用)子分区可以是 MySQL 5.7 支持的任何类型。

用未分区 table 交换分区

假设已使用以下 SQL 语句创建并填充了分区 tablee：

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的非分区副本。可以使用mysqlClient 端完成此操作，如下所示：

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

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

您可以通过查询INFORMATION_SCHEMA.PARTITIONStable 来查看 tablee中的哪些分区包含行，如下所示：

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)

Note

对于分区的InnoDBtable，在INFORMATION_SCHEMA.PARTITIONStable 的TABLE_ROWS列中给出的行数只是 SQL 优化中使用的估计值，并不总是精确的。

要将 tablee中的分区p0与 tablee2交换，可以使用此处显示的ALTER TABLE语句：

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

更准确地说，刚发出的语句使分区中找到的任何行都与 table 中找到的行交换。您可以像以前一样通过查询INFORMATION_SCHEMA.PARTITIONStable 来观察这种情况。先前在分区p0中找到的 table 行不再存在：

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)

如果查询 tablee2，则可以看到现在在此处找到“缺失”行：

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

与分区交换的 table 不一定必须为空。为了证明这一点，我们首先在 tablee中插入新行，通过选择小于 50 的id列值并通过查询PARTITIONStable 来验证此行，确保将该行存储在分区p0中：

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与 tablee2交换：

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

以下查询的输出显示，发出ALTER TABLE语句之前，存储在分区p0中的 table 行和存储在 tablee2中的 table 行现在已切换位置：

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)

Nonmatching Rows

您应该记住，在发出ALTER TABLE ...交换分区语句之前在未分区 table 中找到的任何行都必须满足将它们存储在目标分区中的条件；否则，该语句将失败。若要了解这种情况的发生，请首先在 tablee的分区p0的分区定义的边界之外的e2中插入一行。例如，插入行值id太大的行；然后，尝试再次与分区交换 table：

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)

当分区与包含不匹配分区定义的行的 table 交换时，数据库 Management 员有责任修复不匹配的行，这可以使用REPAIR TABLE或更改 table...修复分区执行。

交换分区而不按行验证

为了避免在将分区与具有很多行的 table 交换分区时花费大量时间进行验证，可以通过将WITHOUT VALIDATION附加到ALTER TABLE ...交换分区语句来跳过逐行验证步骤。

以下示例比较了通过和不通过验证交换具有非分区 table 的分区时执行时间之间的差异。分区 table(tablee)包含两个分区，每个分区有 100 万行。删除 tablee 中 p0 中的行，并将 p0 与 100 万行的未分区 table 交换。 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),
);

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)

如果用 table 交换包含分区定义不匹配的行的分区，则数据库 Management 员有责任修复不匹配的行，可以使用REPAIR TABLE或更改 table...修复分区来执行。

用未分区 table 交换子分区

您还可以使用ALTER TABLE ...交换分区语句将子分区 table 的子分区(请参见第 22.2.6 节“子分区”)与未分区 table 交换。在以下示例中，我们首先创建一个 tablees，该 table 由RANGE分区，并由KEY子分区，像处理 tablee一样填充该 table，然后创建该 table 的空的，未分区的副本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

尽管在创建 tablees时我们没有明确命名任何子分区，但是当从该 table 中进行选择时，我们可以通过从INFORMATION_SCHEMA中包含PARTITIONStable 的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语句将子分区p3sp0tablees与未分区 tablees2交换：

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)

如果 table 是子分区的，则只能与未分区的 table 交换 table 的子分区，而不是整个分区，如下所示：

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

MySQL 使用的 table 结构的比较非常严格。分区 table 和未分区 table 的数目，Sequences，名称以及列和索引的类型必须完全匹配。此外，两个 table 必须使用相同的存储引擎：

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=latin1
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

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35