MySQL 8.4 参考手册  /  ...  /  交换分区和子分区与表

26.3.3 交换分区和子分区与表

在 MySQL 8.4 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 将表分区或子分区与表交换，其中 pt 是分区表，p 是 pt 要与非分区表 nt 交换的分区或子分区，前提是以下语句为真

  1. 表 nt 本身不是分区表。

  2. 表 nt 不是临时表。

  3. 表 pt 和 nt 的结构在其他方面相同。

  4. 表 nt 不包含任何外键引用，并且没有其他表包含任何引用 nt 的外键。

  5. 表 nt 中没有行位于 p 的分区定义边界之外。如果使用 WITHOUT VALIDATION，则此条件不适用。

  6. 两个表都必须使用相同的字符集和排序规则。

  7. 对于 InnoDB 表，两个表都必须使用相同的行格式。要确定 InnoDB 表的行格式，请查询 INFORMATION_SCHEMA.INNODB_TABLES。

  8. 任何 p 的分区级 MAX_ROWS 设置必须与为 nt 设置的表级 MAX_ROWS 值相同。任何 p 的分区级 MIN_ROWS 设置也必须与为 nt 设置的任何表级 MIN_ROWS 值相同。

    无论 pt 是否有生效的显式表级 MAX_ROWS 或 MIN_ROWS 选项，情况都是如此。

  9. 两个表 pt 和 nt 之间的 AVG_ROW_LENGTH 不能不同。

  10. INDEX DIRECTORY 在表和要与其交换的分区之间不能不同。

  11. 在任何表中都不能使用任何表或分区 TABLESPACE 选项。

除了通常需要用于 ALTER TABLE 语句的 ALTER、INSERT 和 CREATE 权限外，您必须具有 DROP 权限才能执行 ALTER TABLE ... EXCHANGE PARTITION。

您还应该注意 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 语句中，只能将一个分区或子分区与一个非分区表交换。要交换多个分区或子分区，请使用多个 ALTER TABLE EXCHANGE PARTITION 语句。 EXCHANGE PARTITION 不能与其他 ALTER TABLE 选项组合使用。分区表使用的分区和（如果适用）子分区可以是 MySQL 8.4 中支持的任何类型或类型。

将分区与非分区表交换

假设已经使用以下 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");

现在我们创建一个 e 的非分区副本，名为 e2。这可以使用 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

您可以通过查询信息模式 PARTITIONS 表来查看表 e 中哪些分区包含行，如下所示

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 表，信息模式 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)

更准确地说，刚刚发出的语句会将分区中找到的任何行与表中找到的任何行进行交换。您可以通过像以前一样查询信息模式 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 中插入一行，确保此行存储在分区 p0 中，方法是选择一个 id 列值，该值小于 50，并在之后通过查询 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语句之前，在非分区表中找到的任何行都必须满足存储在目标分区中所需的条件；否则，该语句将失败。要了解此过程，请先将一行插入到e2中，该行位于表e的分区p0的分区定义边界之外。例如，插入一行，其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执行。

在没有逐行验证的情况下交换分区

为了避免在将分区与包含许多行的表交换时进行耗时的验证，可以通过将WITHOUT VALIDATION附加到ALTER TABLE ... EXCHANGE PARTITION语句来跳过逐行验证步骤。

以下示例比较了在将分区与非分区表交换时，有验证和无验证的执行时间差异。分区表（表e）包含两个分区，每个分区包含 100 万行。表 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)

当将分区与包含与分区定义不匹配的行进行交换时，数据库管理员有责任修复不匹配的行，这可以使用REPAIR TABLE或ALTER TABLE ... REPAIR PARTITION执行。

将子分区与非分区表交换

您还可以使用ALTER TABLE ... EXCHANGE PARTITION语句将子分区表的子分区（请参阅第 26.2.6 节，“子分区”）与非分区表交换。在以下示例中，我们首先创建一个表es，该表按RANGE分区，并按KEY子分区，像我们对表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时没有明确命名任何子分区，但我们可以通过在从该表进行选择时包含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 COLLATE=utf8mb4_0900_ai_ci
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

此示例中的ALTER TABLE ... ENGINE ...语句有效，因为先前的ALTER TABLE语句已从表es3中删除了分区。


PREV   HOME   UP   NEXT
相关文档
MySQL 8.4 发布说明
下载此手册
PDF (US Ltr) - 39.9Mb
PDF (A4) - 40.0Mb
手册页 (TGZ) - 258.5Kb
手册页 (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb