mysql
以下为分区表和非分区表参考方案,转储代表数据仍需保留,删除代表物理删除,具体可以根据各系统做修改,能做成自动化转储或者删除建议做成自动化。
产品线可根据实际情况设计自己系统的转储方案

分区表:
a.转储:把需要转储的分区数据select into到历史表,删除这些分区,注意如果转储的数据量太大,应分批insert,避免大事务对数据库造成影响。
注意需要根据分区的字段来进行查询筛选。

例如:
insert into t_dfc_waybill_op_bak select * from t_dfc_waybill_op where operate_time >='2020-03-01'
alter table t_dfc_waybill_op drop PARTITION P20200301
b.删除:直接删除不保留的分区 alter table 表名 drop PARTITION 分区名称

例如:
alter table t_dfc_waybill_op drop PARTITION P20191101
非分区表:
a.转储或者删除:如果可以改造成分区表的,建议改造成分区表,参考下面的1、2方案改造;如果无法改造成分区表,只能进行物理删除或者物理转储,
删除后可以查看表碎片情况,如果需要整理碎片释放空间,需要停机操作,如果数据库实例少的可以采用从库整理后切换主从。

1 如果允许停机,可以先建好备份表(表结构保持一致),停机后将原表数据select insert到备份表,然后rename表名即可启动应用:
新建分区表:
CREATE TABLE `test` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(200) DEFAULT NULL,
`CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`address` varchar(200) DEFAULT NULL,
PRIMARY KEY (`CREATE_TIME`,`ID`),
KEY `name` (`name`),
KEY `ID` (`ID`),
KEY `name_id` (`name`,`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2293142 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
PARTITION BY RANGE COLUMNS(CREATE_TIME)
(PARTITION P201702 VALUES LESS THAN ('2017-11-3') ENGINE = InnoDB,
PARTITION P201703 VALUES LESS THAN ('2017-11-4') ENGINE = InnoDB,
PARTITION P201704 VALUES LESS THAN ('2017-11-5') ENGINE = InnoDB,
PARTITION P201705 VALUES LESS THAN ('2017-11-6') ENGINE = InnoDB,
PARTITION P201706 VALUES LESS THAN ('2017-11-7') ENGINE = InnoDB,
PARTITION P201707 VALUES LESS THAN ('2017-11-8') ENGINE = InnoDB,
PARTITION P201708 VALUES LESS THAN ('2017-11-9') ENGINE = InnoDB,
PARTITION P201709 VALUES LESS THAN ('2017-11-10') ENGINE = InnoDB,
PARTITION P201710 VALUES LESS THAN ('2017-11-11') ENGINE = InnoDB,
PARTITION P201711 VALUES LESS THAN ('2017-11-12') ENGINE = InnoDB,
PARTITION P201712 VALUES LESS THAN ('2017-11-13') ENGINE = InnoDB,
PARTITION P201801 VALUES LESS THAN ('2017-11-17') ENGINE = INNODB) ;
停应用迁移数据:
insert into test select * from t_test where CREATE_TIME >='2020-03-01
rename表:
rename table t_test to t_test_bak;
rename table test to t_test;

2:如果表非常大,无法停机,可以采用建好分区表(表结构与原表一致),使用ndc同步工具同步(要求表有主键),同步完成后可以停应用rename表名
步骤参考上面新建分区表和rename,不同的是ndc同步,此步骤DBA实施即可。

3:查看表的碎片情况(碎片大于1G):
SELECT
concat(
'optimize local table ',
table_schema,
'.',
table_name,
';'
) as sql_name,
table_rows as TABLE_ROWS,
CONCAT(
ROUND((data_length + index_length) /(1024 * 1024 * 1024), 2),
'GB'
) as TOTAL,
CONCAT(ROUND((data_length) /(1024 * 1024 * 1024), 2), 'GB') as DATA_SIZE,
CONCAT(ROUND((index_length) /(1024 * 1024 * 1024), 2), 'GB') as INDEX_SIZE,
CONCAT(ROUND((data_free) /(1024 * 1024 * 1024), 2), 'GB') as DATA_FREE,
concat(
round(data_free /(data_length + index_length) * 100, 2),
'%'
) as DATA_FREE_PERCENT,
engine
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA not in (
'mysql',
'information_schema',
'performance_schema',
'sys'
)
and DATA_FREE >= 0
order by
DATA_FREE DESC,
DATA_FREE_PERCENT DESC
limit
10;


oracle
分区表:
目前分区表删除可以通过oracle数据库JOb去实现自动删除,如果可以定期删除可以联系DBA提供自动转储的脚本,做成自动删除。如果需要人工转储
参考以下方案:
a.转储:把需要转储的分区数据select into到历史表,删除这些分区,注意如果转储的数据量太大,应分批insert,避免大事务对数据库造成影响。
注意需要根据分区的字段来进行查询筛选。

例如:
insert into VEHICLE.T_BASE_INTERFACELOG_bak select * from VEHICLE.T_BASE_INTERFACELOG where operate_time >='2020-03-01'
alter table VEHICLE.T_BASE_INTERFACELOG drop partition SYS_P99 Update GLOBAL indexes parallel 8;
b.删除:直接删除不保留的分区 alter table 表名 drop partition 分区名称 Update GLOBAL indexes parallel 8

例如:
alter table VEHICLE.T_BASE_INTERFACELOG drop partition SYS_P99 Update GLOBAL indexes parallel 8;
非分区表:
a.转储或者删除:如果可以改造成分区表的,建议改造成分区表,参考下面的1方案改造;如果无法改造成分区表,只能进行物理删除或者物理转储,
删除后可以查看表碎片情况,如果需要整理碎片释放空间,热点表需要停机操作。一般不建议这种方式。

1 如果允许停机,停机后rename原表,确认回插条数后,新建分区表同时从原表拉取需要的数据,验证条数后即可启动应用:
--表重命名
RENAME wmsserver.TRAIL_DETAIL TO wmsserver.TRAIL_DETAIL_BAK;

--插入之前验证下行数:
select count(*) from WMSSERVER.TRAIL_DETAIL_BAK d where d.created_time >= to_date('2018-08-01','yyyy-mm-dd');

--创建轨迹分区表
create table WMSSERVER.TRAIL_DETAIL
(
id ,
bill_code ,
carrier_code ,
waybill_no ,
state ,
context ,
operatedept_code ,
operatedept_name ,
operator ,
area_code ,
area_name ,
remark ,
time ,
f_time ,
creator_id ,
creator ,
created_time ,
last_operator_id ,
last_operator ,
update_time ,
singer_status ,
singer_situation ,
singer_oper_type ,
chanel_code
)
tablespace WMSSERVER_DATA
PARTITION BY RANGE (created_time) interval (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION PWMSTASK_201809 VALUES LESS THAN (TIMESTAMP'2018-09-01 00:00:00'),
PARTITION PWMSTASK_201810 VALUES LESS THAN (TIMESTAMP'2018-10-01 00:00:00')
) as select /*+parallel(d,10)*/ * from WMSSERVER.TRAIL_DETAIL_BAK d where d.created_time >= to_date('2018-08-01','yyyy-mm-dd'); ;
-- Add comments to the table
comment on table WMSSERVER.TRAIL_DETAIL
is '轨迹表';
-- Add comments to the columns
comment on column WMSSERVER.TRAIL_DETAIL.id
is 'ID';
comment on column WMSSERVER.TRAIL_DETAIL.bill_code
is '单据编号';
comment on column WMSSERVER.TRAIL_DETAIL.carrier_code
is '承运商编码';
comment on column WMSSERVER.TRAIL_DETAIL.waybill_no
is '运单号';
comment on column WMSSERVER.TRAIL_DETAIL.state
is '轨迹状态';
comment on column WMSSERVER.TRAIL_DETAIL.context
is '轨迹信息';
comment on column WMSSERVER.TRAIL_DETAIL.operatedept_code
is '操作部门';
comment on column WMSSERVER.TRAIL_DETAIL.operatedept_name
is '操作部门';
comment on column WMSSERVER.TRAIL_DETAIL.operator
is '操作人';
comment on column WMSSERVER.TRAIL_DETAIL.area_code
is '区域编码';
comment on column WMSSERVER.TRAIL_DETAIL.area_name
is '区域名称';
comment on column WMSSERVER.TRAIL_DETAIL.remark
is '备注';
comment on column WMSSERVER.TRAIL_DETAIL.time
is '操作时间';
comment on column WMSSERVER.TRAIL_DETAIL.f_time
is '格式时间';
comment on column WMSSERVER.TRAIL_DETAIL.creator_id
is '创建人ID';
comment on column WMSSERVER.TRAIL_DETAIL.creator
is '创建人';
comment on column WMSSERVER.TRAIL_DETAIL.created_time
is '创建时间';
comment on column WMSSERVER.TRAIL_DETAIL.last_operator_id
is '修改人ID';
comment on column WMSSERVER.TRAIL_DETAIL.last_operator
is '修改人';
comment on column WMSSERVER.TRAIL_DETAIL.update_time
is '最后修改时间';
comment on column WMSSERVER.TRAIL_DETAIL.singer_status
is '签收状态';
comment on column WMSSERVER.TRAIL_DETAIL.singer_situation
is '签收情况';
comment on column WMSSERVER.TRAIL_DETAIL.singer_oper_type
is '签收人类型';
comment on column WMSSERVER.TRAIL_DETAIL.chanel_code
is '渠道单号';
-- Create/Recreate indexes
create index WMSSERVER.IDX1_TRAIL_DETAIL1 on WMSSERVER.TRAIL_DETAIL (BILL_CODE)
tablespace WMSSERVER_INDEX;
create index WMSSERVER.IDX2_TRAIL_DETAIL1 on WMSSERVER.TRAIL_DETAIL (CHANEL_CODE)
tablespace WMSSERVER_INDEX;
-- Create/Recreate primary, unique and foreign key constraints
alter table WMSSERVER.TRAIL_DETAIL
add constraint PK_TRAIL_DETAIL1 primary key (ID)
using index
tablespace WMSSERVER_INDEX;

收集统计信息:
begin
dbms_stats.gather_table_stats(ownname => '用户',
tabname => '表名',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => dbms_stats.AUTO_DEGREE,
cascade => true);
end;

posted on 2021-07-08 09:28  流年公子  阅读(206)  评论(0编辑  收藏  举报