MySQL 历史数据表迁移方法

历史数据迁移分三个步骤进行:

  1. 在线库与历史库把需要迁移(或转入)的表都改成分区表

  2. 在线库上的历史数据迁移到历史库

  3. 在线库上删除已备份的历史数据

其中步骤1只执行一次,步骤2、3可以每月执行一次,即每月清理一次历史数据。

下面是每一步的方案比较:

一. 在线库与历史库需要迁移的表都改成分区表

把需要迁移(或迁入)的表改成分区表,每月一个分区,改成分区表不需要对目前前的程序进行修改,对之前的应用是透明的。

改成分区表后,在做历史数据迁移时,可以做到只读取一个分区的数据,读取速度快;删除历史数据时,可以删除一个分区。

改为分区表的缺点是需要通过job定期新增分区和删除已备份分区,但这两个操作都是ddl操作,可在秒级完成。

下面是建立分区表的三种方案:

方案1. 根据原表建立分区表:

create table sys_visit_log_his_new partition by range(log_date)(

partition sale_2012_01 values less than(to_date('2012-02-01','YYYY-MM-DD')),

partition sale_2012_02 values less than(to_date('2012-03-01','YYYY-MM-DD')),

partition sale_2012_03 values less than(to_date('2012-04-01','YYYY-MM-DD')),

partition sale_2012_04 values less than(to_date('2012-05-01','YYYY-MM-DD')),

partition sale_2012_05 values less than(to_date('2012-06-01','YYYY-MM-DD')),

partition sale_2012_06 values less than(to_date('2012-07-01','YYYY-MM-DD')),

partition sale_2012_07 values less than(to_date('2012-08-01','YYYY-MM-DD')),

partition sale_2012_08 values less than(to_date('2012-09-01','YYYY-MM-DD')))

as select * from sys_visit_log_his;

drop table sys_visit_log_his;

rename sys_visit_log_his_new to sys_visit_log_his;

这种方案只能在业务空闲时实施。

方案2. 在线重定义的方式建立分区表

可以在线对库直接修改,对业务无影响,但实施步骤比较复杂。

方案3. 将原表导出,根据原表建立空分区表,删除原表,然后导入

这种方案也是只能在业务空闲时实施。

经测试,以上三种方案效率:1>2>3

对1千万行的数据进行测试,方案1用时7分多钟,方案2用时14分钟,方案3用时15分钟。

推荐:方案1

二. 在线库上的历史数据迁移到历史库

方案1. 历史库通过dblink直接插入

方案2. 历史库通过dblink直接插入的同时,每1000行一提交

方案3. exp或expdp加where条件,导出一个月的历史数据,传输到历史库,imp或impdp导入到历史库

方案4. 表空间传递+分区交换,每个分区一个表空间,导出分区的表空间,拷到历史库导入。

这四种方案,效率由高到低是:4>3>1>2

方案4虽然最快,但会造成库中出现太多的表空间,管理不便,操作也最复杂。

另外三种方案,把1千万行数据从在线库导入到历史库的测试,方案3用时10分钟,方案2用时18分钟左右,方案1比方案2稍快一点。

推荐:方案3

三. 在线库上删除已备份的历史数据

方案1. 直接delete 已备份的数据 对海量数据来说,delete操作是漫长的等待,删完后,还要处理表碎片,花费时间也很长。

方案2. 把未备份的数据重建一张表,删除原表,重建索引。

方案3. 删除已备份的分区,秒级完成,这也是为什么增加了步骤一,建立分区表的原因。

这三种方案效率由高到低是:3>2>1

推荐:方案3

为者常成,行者常至