MySQL分区表——交换分区

扫地僧的故事 2020-08-23

4711

从MySQL 5.6开始，支持分区交换。就是将一个分区表中的一个分区和一个普通表中的数据互换。

一、实现交换分区的基本语法：

ALTER TABLE pt
EXCHANGE PARTITION p
WITH TABLE nt

二、实现分区交换需满足以下前提条件：

1. 表nt不能为分区表，也不能是临时表

2. 表nt的表结构必须于分区表pt一致

3. 表nt不包含外键引用，其他表也没有任何外键引用表nt

4. 表nt中所有行都必须在p分区范围内，否则交换分区失败。MySQL 5.7.5开始，可使用WITHOUT VALIDATION选项，强制交换。

5. 对于InnoDB表，pt和nt两个表必须使用相同的行格式。

三、使用交换分区的注意事项：

1. 使用该语句时，不会触发交换表和被交换表上的触发器

2. auto_increment列将被重置

3. IGNORE关键字在alter table ... exchange partition中不起作用

四、案例模拟：

根据官网提供的案例，在自己的机器上简单的实现下：

1. 新建分区表e，并插入4行记录

2. 创建与表e结构一致的表e2，并移除分区。

remove partitioning这个语法真的第一次见，神奇。。简单测了一下，不管表中有没有数据都是可以remove的。

3. 查看表e的分区中各有多少行

4. 将表e的分区p0 交换到表e2中

5.再观察下表e和表2的数据

数据交换成功～

当然，要与分区表交换的普通表，不一定必须是空的。继续实验,在表e的p0分区插入一行数据

将表e的分区p0 交换到表e2中，然后检查数据

数据也交换成功～

如果表e2中的数据不在表e的p0分区范围内，那还能交换吗？

前面使用交换分区必须满足的前提条件中的第4点，其实已经讲到了这个问题。可以通过使用WITHOUT VALIDATION选项，不需要逐行验证，能够强制交换。当表e2中有很多数据需要交换时，加上这个参数可以避免耗时的验证，节省大量时间。

现在 (51, "Ellen", "McDonald") 这行记录落在p0分区了。

可以通过REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION来修复分区。

以上就是简单的交换分区的方法，MySQL还提供了自分区的交换功能，我就不再细说了，有需要可以去官方文档上看下语法。

总结：

1. 可以将分区表的某个分区和一个空表进行分区交换，已达到将指定分区的数据迁移出去的目的。MySQL中应该没有单独对分区备份的功能，通过交换分区的方法，能达到快速清理分区及备份的效果。

2. 可以将分区表的某个分区和一个非空表进行数据交换，可以把数据快速迁移到指定分区中。

每天学习一点点点点，大家周末愉快～

文章转载自扫地僧的故事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。