有时候刚开始设计的时候没考虑到数据归档。数据归档不是简单的导出delete。因为一般我们的数据库都是delete不释放空间。而我们一般数据库遇到空间不足,就不得不面对这个问题。当然也可以做碎片整理的空间回收。我不太喜欢这样,尤其大的表的时候。MySQL一旦超过100G的,在线环境我都不敢去做。Oracle的在线回收我做过20T的表,碎片800G,回收了一次做了55个小时(不停机)
我个人觉得最好的方式是采用分区,至于是迁移分区还是归档分区都好说。一般都是截断分区这样操作,很快。但是如果一个表之前没有做分区怎么办?
我们举例
create table b (id int ,a varchar2(10),time date);普通堆表
模拟5条数据
insert into b values (1,'a',sysdate-120);
insert into b values (2,'b',sysdate-90);
insert into b values (3,'c',sysdate-60);
insert into b values (4,'d',sysdate-30);
insert into b values (5,'e',sysdate);
一般来说我们都要使用到时间。所以时间建立索引。
create index b1 on b (time);
一般表都有主键,在Oracle中应该使用业务单据号来作为主键,在MySQL中用ID自增无业务含义。在PG中按照Oracle的来就行。
Alter table b add constraint pkb primary key(id);
为B表增加主键,这里注意一定要是自己命名的而不是数据库自己命令的。因为正常主从都没有问题,但是有OGG的场景这样的话就是一个坑。后面我来讲为什么。
ALTER TABLE b MODIFY
PARTITION BY RANGE (time)
(
PARTITION b1 VALUES LESS THAN (TO_DATE ('2022-03-01', 'YYYY-MM-DD')),
PARTITION b2 VALUES LESS THAN (TO_DATE ('2022-04-01', 'YYYY-MM-DD')),
PARTITION b3 VALUES LESS THAN (TO_DATE ('2022-05-01', 'YYYY-MM-DD')),
PARTITION b4 VALUES LESS THAN (TO_DATE ('2022-06-01', 'YYYY-MM-DD')),
PARTITION b5 VALUES LESS THAN (TO_DATE ('2022-07-01', 'YYYY-MM-DD'))
) online
UPDATE INDEXES
(
pkb GLOBAL,
b1 LOCAL
);
这个就是在线把非分区表转换成分别表的命令。注意红色部分我这里指定了主键名,如果是自动的那么就是自动的名字。上面讲的坑就在这里,因为在OGG场景下,目标端也就是下游数据库的自动的名字和源端上游不一样。执行这个命令时候就会引发OGG的中断。
那么这个是数据库的问题还是OGG的问题?我觉得都不是,是熟悉产品的问题。执行完毕以后看到表已经变成分区的了。
这个时候再截断过期数据只要drop partition就可以了,当然注意一个UPDATE GLOBAL INDEXES;否则会出现数据无法写入的问题。
源码附件已经打包好上传到百度云了,大家自行下载即可~
链接: https://pan.baidu.com/s/14G-bpVthImHD4eosZUNSFA?pwd=yu27 提取码: yu27 百度云链接不稳定,随时可能会失效,大家抓紧保存哈。
如果百度云链接失效了的话,请留言告诉我,我看到后会及时更新~
开源地址 码云地址: http://github.crmeb.net/u/defu
Github 地址: http://github.crmeb.net/u/defu
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。