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

26.3.3 交换分区和子分区与表

在 MySQL 9.0 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 交换表分区或子分区与表，其中 pt 是分区表，p 是要与非分区表 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 列都将被重置。

  • 当与 ALTER TABLE ... EXCHANGE PARTITION 一起使用时，IGNORE 关键字无效。

以下是 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 9.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");

现在我们创建 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 中，方法是选择小于 50 的 id 列值，并通过随后查询 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表（来自INFORMATION_SCHEMA）的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 9.0 发行说明
下载本手册
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
手册页 (TGZ) - 258.2Kb
手册页 (Zip) - 365.3Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb