RDS for MySQL Archives historical data by partition
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