RDS for MySQL 通过分区自动归档历史数据-阿里云开发者社区

开发者社区> 阿里云支持与服务> 正文
登录阅读全文

RDS for MySQL 通过分区自动归档历史数据

简介: RDS for MySQL 通过分区归档历史数据 原始表 分区 用于分区维护的存储过程 每月调用存储过程的事件 随着数据的积累,数据量的增加,越来越多的表体积变的庞大,不但影响查询的执行时间,而且使得管理工作(比如添加删除索引)变的越发复杂和困难。本文介绍一个通过分区滑动来归档历史

RDS for MySQL 通过分区归档历史数据



随着数据的积累,数据量的增加,越来越多的表体积变的庞大,不但影响查询的执行时间,而且使得管理工作(比如添加删除索引)变的越发复杂和困难。

本文介绍一个通过分区滑动来归档历史数据(以便可以有效控制表的尺寸)的方法,仅供大家参考。


1. 原始表

未分区的原始表:order_history。

create table order_history
( 
	id              bigint unsigned auto_increment primary key,
	order_id        bigint unsigned not null,
	customer_id     bigint unsigned not null,
	goods_id        bigint unsigned not null,
	payment_id      bigint unsigned not null,
	details         text,
	comments        text,
	order_date      datetime not null,
	status          tinyint
);

 

2. 分区

MySQL 分区要求分区字段必须是主键或者唯一键的一部分,因此需要修改主键定义。

alter table order_history algorithm=inplace, lock=none, drop primary key, add primary key (id, order_date);

-- algorithm=inplace, lock=none 是 RDS for MySQL 5.6 支持的 online DDL 特性。
-- 如果要修改主键,删除主键和添加主键建议放在一条语句中,以便充分利用 5.6 版本的 Online DDL 特性。

alter table order_history partition by range columns (order_date) (
	partition p1606 values less than ('2016-07-01'),
	partition p1607 values less than ('2016-08-01'),
	partition p1608 values less than ('2016-09-01'),
	partition p1609 values less than ('2016-10-01'),
	partition p1610 values less than ('2016-11-01'),
	partition p1611 values less than ('2016-12-01'),
	partition p1612 values less than ('2017-01-01'),
	partition p0 values less than maxvalue
);

-- 将数据按月份分区

 3. 用于分区维护的存储过程

删除最久的一个月份分区,新建下个月份分区,总体保留最近 6 个月份的数据。

delimiter //
drop procedure sp_order_his_rotate//
create procedure sp_order_his_rotate ()
begin
       declare todrop_par varchar(10) default null;
       declare toadd_par varchar(10) default null;
       declare toadd_day varchar(10) default null;
       declare last_par varchar(10) default null;
       declare new_par varchar(10) default null;
       declare v_sql varchar(200) default null;
	   
       select date_format(date_sub(curdate(), interval 6 month), 'p%y%m') into todrop_par;
       select date_format(date_add(curdate(), interval 1 month), 'p%y%m') into toadd_par;
       select date_format(date_add(curdate()-day(curdate())+1,interval 2 month), '%Y-%m-%d') into toadd_day;
       select partition_name into last_par from information_schema.partitions 
	 where table_name = 'order_history' 
	 and table_schema = 'db_name' 
	 and partition_description != 'maxvalue' 
	 order by partition_description asc limit 1;
       if todrop_par = last_par then
       select concat('alter table order_history drop partition ',todrop_par,';') into v_sql;
	 set @v_sql=v_sql;
	 prepare stmt from @v_sql;
	 execute stmt;
	 deallocate prepare stmt;
       end if;
       select partition_name into new_par from information_schema.partitions 
         where table_name = 'order_history' 
         and table_schema = 'db_name' 
         and partition_description != 'maxvalue' 
	 order by partition_description desc limit 1;	
       if toadd_par != new_par then
       select concat('alter table order_history reorganize partition p0 into (partition ', toadd_par, ' values less than (''', toadd_day,'''), partition p0 values less than (maxvalue));') into v_sql;
	 set @v_sql=v_sql;
	 prepare stmt from @v_sql;
	 execute stmt;
	 deallocate prepare stmt;
       end if;
end;
//
delimiter ;

 

4. 每月调用存储过程的事件

每月月末 28 日调用分区管理存储过程,来自动维护分区。

drop event if exists order_his_rotate;
delimiter //
create event order_his_rotate
  on schedule every 1 month starts '2016-12-28 02:00:00'
  on completion preserve enable do
begin
  call sp_order_his_rotate();
end;
//
delimiter ;

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
文档