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 dobegin call sp_order_his_rotate();end; //
delimiter ;
转载于:https://blog.51cto.com/266898/1952285