从MYSQL 数据库归档到归档设计

2020-02-11 10:43:05

到数据归档，很多人的个概念就是，不就是无用的数据，换个地方放吗，直接拷贝，删除不就得了，有那么麻烦。

我见到过的，听到过的数据库归档的方法有以下几种

1 数据通过人工的手段来进行清理，直接将表换名字，然后在重建一个新的表，承接数据。

首先这样的做法一个字，快，这是这样做法的好处所在，但另一方面要考虑的问题就是，业务要不要停，涉及的人有多少，如果光是IT 的还好说，但恰恰这样做，不会光光牵扯 IT，业务的人一定是要牵扯进来，然后就是各种流程和通知，要在几点几点，某个业务，甚至整体业务暂时停止。

2 数据通过MYSQL dump 或者其他的备份方式，将数据备份出来，在将数据恢复到数据归档库中，然后将备份的数据直接手动清理掉，这样的做法速度也很快，对业务的影响也比较小，基本上可以算是透明的方式了，但还是避免不了人工的介入，并且也不可能是天天这样做。

3 数据通过工具的方式来进行处理，例如pt-archiver 的方式来进行数据的归档和清理，但这个工具貌似bug不少，pt-1126

4 自己设计数据归档

自己设计数据归档的面就广了，有使用程序来做的，例如JAVA ，Python等等，也有使用存储过程来进行的。

下面就是一个MYSQL 针对一个数据库表归档的案例（这个案例也是有缺陷的，但目前是秉承着够用就好，以及时间成本的原则）

首先设计一个归档要考虑的问题如下

1 归档表的大小，以及每日大，或小的归档数据量，或者数据过期时间

同时归档表是否必须是全量的数据归档，还是可以抛弃一些数据，例如有一些日志的归档中可能存在一些无用的数据，是否还必须全量的归档等等都是要考虑的问题，归档数据并不一定是原封不动的归档，有的逻辑上，只归档一些数据关键点也是可以的。

2 归档的数据量，数据归档一般根据上面的东西，归档有一次性归档，和规律有固定日期的归档，一次性的归档一般归档的数据量比较大，而有规律的归档则归档的数据量并不大，对比两者的方式，其实定期归档（有规律）的要有优势一些，主要是数据是不断灌入的，而数据的归档如果也是不断输出的，这样整体这个表的数据量就会有一个平衡，不会一下子少了很多，要不就是在清理的前一天，数据量已经大到一定的水平，有可能影响性能。

3 归档的方法，自己定义数据的归档方面，可以每次归档将数据灌入一个表，也可以定期的将数据写入不同的归档表，例如已归档日期和后缀的方式来将每次写入的数据进行分割，或者建立分区表的方式来进行归档。

4 归档的方式是否灵活，有的归档的方法仅仅针对一个表来进行归档，有的方法是可以灵活配置，可以任意扩展。那就都任意扩展，灵活配置不就好了，其实随着能任意扩展或者灵活配置，则工作量就会变大，这也要考虑一个性价比，具体要考虑表的数量以及归档的方式。

下面就是一个简单的例子，需求是一张表每天数据量在40- 50 万，主要都是来自于客户的短信以及消息发送的内容。表中的数据要保留半年之内的，其余的数据可以移走。

以下以简单的自动化的方案来讲

下图是基于案例来讲的

因为数据库是MYSQL 所以考虑了归档一次是多大的批量，避免归档数据量过大的时候将生产库hang 死，另外配置表主要的功能是有两个 1 限制一次拷贝和清理的数据量，2 控制拷贝过期数据的日期限制

下面是这段代码，如果看的不方便，下面有截图

DELIMITER $$

DROP PROCEDURE IF EXISTS archive_data;

create PROCEDURE archive_data()

BEGIN

declare row_s int; #大执行多少次每次1000条

declare save_month tinyint; #保留多少月之前的数据

declare times int; #执行次数记录

declare min_row_s int; # 当前数据库小的tid

declare archive_date datetime;

select @times := 1; #设置每天初始清理次数初始值

select @row_s := max_row_clean from db_archive.db_config order by id limit 1; #获取当前配置库数据

select @save_month := archive_save_date from db_archive.db_config order by id limit 1;

select @min_row_s := min(tid) from msgcdb.t_sms_message; #获取当前系统小的TID号

select @max_row_s := max(tid) from msgcdb.t_sms_message; #获取当前系统大的TID号

select @archive_date := DATE_SUB(CURDATE(), INTERVAL @save_month MONTH);

select @row_s, @save_month,@archive_date,@min_row_s,@max_row_s;

if @min_row_s = @max_row_s then

set @times = @row_s + 1;

elseif @min_row_s is null then

set @times = @row_s + 1;

end if;

insert into db_archive.archive_log (save_month,times,min_row_s,max_row_s,archive_date,row_s,insert_time,delete_time,type_s) values (@save_month,@times,@min_row_s,@max_row_s,@archive_date,@row_s,sysdate(),sysdate(),'initial');

select @times, @min_row_s;

while @times < @row_s DO

begin

insert into db_archive.t_sms_message (tid,summary_id,uid,code,channel,batch_id,done_time,phone,sms_content,create_time,send_time,storage_time,status,estimatedTime,operate_type,origin,creator_id ,dept_id,del_flag,priority,template_id,repetitions_num)

select tid,summary_id,uid,code,channel,batch_id,done_time,phone,sms_content,create_time,send_time,storage_time,status,estimatedTime,operate_type,origin,creator_id ,dept_id,del_flag,priority,template_id,repetitions_num

from msgcdb.t_sms_message

where tid >= @min_row_s and tid < @min_row_s + 1000 and status <> 0 and storage_time < @archive_date;

set @times = @times + 1;

delete from msgcdb.t_sms_message where tid >= @min_row_s and tid < @min_row_s + 1000 and status <> 0 and storage_time < @archive_date;

select @min_row_s,@max_row_s;

select @min_row_s := min(tid) from msgcdb.t_sms_message;

select @max_row_s := max(tid) from msgcdb.t_sms_message;

select @min_row_s,@max_row_s;

if @min_row_s = @max_row_s then

set @times = @row_s + 1;

elseif @min_row_s is null then

set @times = @row_s + 1;

end if;

end;

END WHILE;

END$$

DELIMITER ;

配置表

归档日志表

为什么要这么设计，其实寻根溯源有两点

1 简单有效，够用原则

2 设计配置表的主要原因是对于非IT 人员，例如project manager 或者其他的人员，也可以调整归档的时间，例如 archive_save_date 的数字就是保留多少月的数据，max_row_clean，就是当前的数字 *1000 就是每天大的归档数据量。通过这两个参数双重限制每天的归档的数据量，避免归档的时间太长，影响了备份，或其他操作。而日志表本身就是一个查看归档成功失败的东西，其中的type_s 就是表现数据归档操作状态的东西，通过日志表可以反映归档多少数据，每次操作消耗的时间，以及当前操作获取的系统变量是什么，方便出现故障时，查看到底归档的数据少不少，或者大致可能出现问题。

下面是这两个表的结构