用表交换分区和子分区
在MySQL 8.0中,可以使用来与表交换表分区或子分区,其中是分区表,是要与未分区表交换的分区或子分区,前提是以下语句为真:ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt
pt
p
pt
nt
- 表
nt
本身未分区。 - 表
nt
不是临时表。 - 表的结构
pt
与nt
其他结构相同。 - 该表不
nt
包含任何外键引用,并且其他任何表都没有任何引用的外键nt
。 - 中没有行
nt
位于的分区定义的边界之外p
。如果WITHOUT VALIDATION
使用,则此条件不适用。 - 对于
InnoDB
表,两个表使用相同的行格式。要确定InnoDB
表的行格式,请查询INFORMATION_SCHEMA.INNODB_TABLES
。 nt
没有使用该DATA DIRECTORY
选项的任何分区。对于InnoDB
MySQL 8.0.14及更高版本中的表,取消了此限制。
除了ALTER
,INSERT
和CREATE
通常需要的权限ALTER TABLE
声明,你必须有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 ptEXCHANGE PARTITION pWITH 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.0支持的任何类型。
用未分区表交换分区
假设e
已使用以下SQL语句创建并填充了分区表:
CREATE TABLE e ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) )PARTITION BY RANGE (id) (PARTITION p0VALUES LESS THAN (50),PARTITION p1VALUES LESS THAN (100),PARTITION p2VALUES LESS THAN (150),PARTITION p3VALUES LESS THAN (MAXVALUE ) );INSERT INTO eVALUES (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 e2REMOVE PARTITIONING ; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
您可以e
通过查询INFORMATION_SCHEMA.PARTITIONS
表来参见表中哪些分区包含行,如下所示:
mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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
表,在给定的行数TABLE_ROWS
的列INFORMATION_SCHEMA.PARTITIONS
表仅仅是一个估计值在SQL优化使用,并不总是准确的。
要将p0
table中的分区e
与table 交换e2
,可以使用ALTER TABLE
,如下所示:
mysql>ALTER TABLE eEXCHANGE PARTITION p0WITH TABLE e2; Query OK, 0 rows affected (0.04 sec)
更准确地说,刚发出的语句使分区中找到的任何行都与表中找到的行交换。您可以INFORMATION_SCHEMA.PARTITIONS
像以前一样通过查询表来观察这种情况。以前在分区中找到的表行p0
不再存在:
mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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)
如果查询table e2
,您会发现现在可以在此处找到“ missing ”行:
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 eVALUES (41, "Michael", "Green");Query OK, 1row affected (0.05 sec) mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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)
现在,我们再次使用与前面相同的语句p0
与表交换分区:e2
ALTER TABLE
mysql>ALTER TABLE eEXCHANGE PARTITION p0WITH TABLE e2; Query OK, 0 rows affected (0.28 sec)
以下查询的输出显示,在发出该语句之前,存储在partition中p0
的表行和存储在table中的表行现在已切换位置:e2
ALTER 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_ROWSFROM 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
语句之前在未分区表中找到的任何行都必须满足将它们存储在目标分区中所需的条件;否则,该语句将失败。要参见这种情况是如何发生的,请首先在table e2
的partition的分区定义的边界之外的行中插入一行。例如,插入一行的列值太大;然后,尝试再次与分区交换表:p0
e
id
mysql>INSERT INTO e2VALUES (51, "Ellen", "McDonald"); Query OK, 1 row affected (0.08 sec) mysql>ALTER TABLE eEXCHANGE PARTITION p0WITH TABLE e2; ERROR 1707 (HY000): Found row that does not match the partition
只有该WITHOUT VALIDATION
选项才能使此操作成功:
mysql>ALTER TABLE eEXCHANGE PARTITION p0WITH TABLE e2WITHOUT VALIDATION ; Query OK, 0 rows affected (0.02 sec)
当一个分区与一个表中包含的行与该分区定义不匹配时,数据库管理员有责任修复不匹配的行,这可以使用REPAIR TABLE
或来执行ALTER TABLE ... REPAIR PARTITION
。
没有行逐行验证的分区交换
为避免在将分区与具有很多行的表交换分区时花费大量时间进行验证,可以通过WITHOUT VALIDATION
在ALTER TABLE ... EXCHANGE PARTITION
语句后附加跳过逐行验证步骤。
以下示例比较了通过和不通过验证交换具有非分区表的分区时执行时间之间的差异。分区表(table e
)包含两个分区,每个分区有100万行。表e中p0中的行被删除,并且p0与一百万行的未分区表交换。该WITH VALIDATION
操作需要0.74秒。相比之下,该WITHOUT VALIDATION
操作需要0.01秒。
# Create a partitioned table with 1 million rows in each partitionCREATE TABLE e ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) )PARTITION BY RANGE (id) (PARTITION p0VALUES LESS THAN (1000001),PARTITION p1VALUES LESS THAN (2000001), ); mysql>SELECT COUNT(*)FROM e; | COUNT(*) | +---------- + | 2000000 | +---------- + 1 row in set (0.27 sec) # View the rows in each partitionSELECT PARTITION_NAME, TABLE_ROWSFROM 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 rowsCREATE 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 rowsCREATE 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 eWHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) # Confirm that there are no rows in partition p0 mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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 eEXCHANGE PARTITION p0WITH TABLE e2WITH VALIDATION ; Query OK, 0 rows affected (0.74 sec) # Confirm that the partition was exchanged with table e2 mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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 eWHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) # Confirm that there are no rows in partition p0 mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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 eEXCHANGE PARTITION p0WITH TABLE e3WITHOUT VALIDATION ; Query OK, 0 rows affected (0.01 sec) # Confirm that the partition was exchanged with table e3 mysql>SELECT PARTITION_NAME, TABLE_ROWSFROM 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
。在下面的示例中,我们首先创建一个表es
,该表按RANGE
和进行分区,然后KEY
像表table一样填充该表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 p0VALUES LESS THAN (50), ->PARTITION p1VALUES LESS THAN (100), ->PARTITION p2VALUES LESS THAN (150), ->PARTITION p3VALUES LESS THAN (MAXVALUE ) -> ); Query OK, 0 rows affected (2.76 sec) mysql>INSERT INTO esVALUES -> (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 es2REMOVE PARTITIONING ; Query OK, 0 rows affected (0.70 sec) Records: 0 Duplicates: 0 Warnings: 0
尽管在创建表时未明确命名任何子分区es
,但可以通过在从该表中进行选择时包括表的SUBPARTITION_NAME
列来获取这些子分区的生成名称,如下所示:PARTITIONS
INFORMATION_SCHEMA
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
语句将p3sp0
表es
中的子分区与未分区的表交换es2
:
mysql>ALTER TABLE esEXCHANGE PARTITION p3sp0WITH 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 esEXCHANGE PARTITION p3WITH 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 es3REMOVE 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 es3ENGINE = MyISAM;Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql>ALTER TABLE esEXCHANGE PARTITION p3sp0WITH TABLE es3;ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL