1、应用场景

某客户应用场景下，应用有16种数据类型(数据类型为应用概览，如票务数据类型、对账数据类型等，共对应16张表)，每天增量为1050万行记录(1.73GB)，为避免长时间数据积累导致性能衰减、存储增长问题，设计数据库只保留1095天的数据；同时为了保证历史数据可追述，被删除的数据（1095天前）需要做归档保留，必要时候可恢复归档数据用于分析；归档和删除操作建议在业务空闲时定时自动执行。

注：业务空闲时间为每天凌晨2点到5点。

根据以上描述，实际需求可总结为以下几点：

所有表累计每天增量数据为1050万行记录；
当前库只保留1095天数据；
每天凌晨2点至5点定时对1095天前的数据导出归档；
完成后删除数据库中被归档的数据；

注：每天归档和删除的数据量为1050万(1.73GB)，最大表为300万(879MB)。

因为应用没有实现该功能，需要数据库端来实现。本文主要介绍在以上场景下，易鲸捷数据库QianBase TP定时归档、删除方案的具体分析与实现。

2、方案分析

为了实现上述功能，需要结合数据库定时任务、归档任务、删除任务3个模块，并且需要在数据删除后vacuum full表保证数据更好的存储利用与性能提升，下面分别介绍3个模块及vacuum full各自的方案。

2.1可能存在的方案

2.1.1定时任务

定时任务可用方案有两种，分别如下：

(1)操作系统crontab定时任务，定时调用脚本实现；

(2)QianBase TP自带插件dbms_job定时任务，定时调用存储过程实现；

2.1.2归档任务

数据归档可用方案有三种，分别如下：

(1)整库归档（qbbackrest、qbasebackup），是数据库命令，可写入脚本配合crontab定时任务使用；

(2)数据库级别/表级别的归档（qdump），是数据库命令，可写入脚本配合crontab定时任务使用；

(3)表级别/行级别的归档（copy），是数据库SQL命令，可写入脚本配合crontab定时任务使用；也可用写入存储过程配合dbms_job定时任务使用；

2.1.3删除任务

数据删除可用方案有两种，分别如下：

(1)小数据量普通表以delete语句配合where条件进行删除；

(2)大数据量分区表以天分区，先分离子分区，再drop子分区表方式删除；

2.1.4空间回收

删除任务后，DELETE的数据空间并未真正释放；空间释放需要对表执行vacuum full操作。

注：vacuum full不能在存储过程中调用，定时空间释放只能使用shell脚本配合操作系统crontab定时任务实现。

2.2方案确定

2.2.1方案选择

归档内容为行级别需求，只能选择copy方式。删除任务两种方案都可以选择，针对不同数据量的表使用不同的方案。定时任务建议选择dbms_job定时任务，数据库管理定时任务更方便。空间回收的定时执行只能选择crontab定时任务+shell脚本实现。

2.2.2最终方案

小表定时归档删除方案：表不分区，使用dbms_job定时任务+存储过程+copy+条件delete的方案；vacuum full使用crontab定时任务+shell脚本实现；

大表定时归档删除方案：表以天为单位分区，使用dbms_job定时任务+存储过程+copy+分离子分区+drop分区表的方案；

注：

①最终方案都是行级别的数据归档和删除，需要表存在日期格式的字段；

②保证delete的效率，需要在该日期字段上创建索引；

③子分区drop会自动回收空间，不需要对表执行vacuum full。

3方案实现

易鲸捷QianBase TP(简称TP)是一款传统集中性关系型数据库系统，其包含了丰富的EXTENSION功能插件，其中qb_dbms_job插件兼容了Oracle DBMS_JOB定时JOB的功能。以下介绍TP使用qb_dbms_job插件的DBMS_JOB定时任务实现定时归档、删除；使用crontab定时任务实现空间释放。