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 dobegin
  call sp_order_his_rotate();end;
//

delimiter ;