RDS for MySQL Archives historical data by partition

RDS for MySQL Archives historical data by partition

• Original table

• Partition

• Stored procedures for partition maintenance

• Events that call a stored procedure every month

with the accumulation of data and the increase of data volume, more and more table volume becomes large, not only affect the execution time of query, but also make management work (such as adding and deleting index) become more complex and difficult.

This article describes a way to archive historical data (so that you can effectively control the size of the table) by sliding the partition, for your reference only.

1. Original tableThe original table is not partitioned: 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. PartitioningThe MySQL partition requires that the partition field be part of the primary key or unique key, so you need to modify the primary key definition.

Alter table order_history algorithm=inplace, lock=none, drop primary key,  add primary key  (id, order_date);-- algorithm=inplace, lock=none  is  RDS for MySQL 5.6  supported  online DDL  features. --  If you want to modify the primary key, delete the primary key and add the primary key is recommended in a statement to take advantage of the  5.6  version of  Online DDL  features. 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);--  to partition data by month 

3. Stored procedures for partition maintenanceDelete the longest month partition, create a new partition for the next month, and keep the data for the last 6 months in total.

Delimiter //drop procedure sp_order_his_rotate//create procedure sp_order_his_rotate   () Begin       declare todrop_par varchar ( default)  null;       declare toadd_par varchar (Ten)  default  null;       declare toadd_day varchar (Ten)  default  null;       declare last_par varchar (Ten)  default  Null;       declare new_par varchar (Ten)  default null;        declare v_sql varchar ( 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. Monthly events that call stored proceduresMonthly end of month 28th calls the partition management stored procedure to automatically maintain the partition.

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;  

RDS for MySQL Archives historical data by partition