本文介绍了在大数据环境下，使用Oracle数据库的分区交换技术优化历史消息表的数据迁移过程。通过创建分区表和临时表，实现了msg_message_list表中过期数据高效地转移到msg_message_history表，确保系统的高性能和数据管理的合理性。详细步骤包括查询分区、交换数据、删除源分区，并涉及存储过程的编写和异常处理。
背景
之前公司做的一个消息中心功能优化的需求，我负责了其中一小部分。大致情况是这样的。
有一张历史消息表msg_message_list，存放近一年所有用户的消息记录，数据量是千万级的。因此设计的是一张按月的分区表。时间再往前，超过一年的历史消息则结转到另外一张msg_message_history表。
我负责的是历史数据的搬运，即把符合时间条件的数据从msg_message_list搬运到msg_message_history。采用的分区交换方式实现
整体实现步骤如下
- 首先对外提供了一个http的接口用于实现该功能，数据层调用数据库的存储过程。
- 在公司现有的平台上配置了一个定时调用该接口的任务。每天定时触发。
数据库的具体实现
因为一开始，同事就给我指明了方向，使用分区交换技术。所以我也没怎么纠结，就是看存储过程具体如何写了。
表和DDL
需要用到以下三张表，缺一不可
| 序号 | 表名 | 是否分区表 | 备注 |
|---|---|---|---|
| 1 | MSG_MESSAGE_LIST | 是 | 过期数据的来源 |
| – | – | – | – |
| 2 | MSG_MESSAGE_HISTORY | 是 | 过期数据的目的地 |
| – | – | – | – |
| 3 | MSG_MESSAGE_TEMP | 否 | 分区交换时必须用到的临时表 |
表格中的三张表结构一样，只是除temp表外其他两张是分区表。ddl如下（部分字段省略）：
CREATE TABLE "USERDB"."MSG_MESSAGE_LIST"
("MSG_ID" VARCHAR2(36) NOT NULL ENABLE,
"MSG_TITLE" VARCHAR2(128) NOT NULL ENABLE,
"MSG_BODY" VARCHAR2(2000) NOT NULL ENABLE,
"ACTION_URL" VARCHAR2(512),
"MSG_TIME" DATE NOT NULL ENABLE,
"OWNER_ID" VARCHAR2(50)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("MSG_TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" )
通过上面的ddl可以看出，分区表是根据msg_time字段分区的。且在建表时直接创建了一个2017-01-01的分区 。
分区交换存储过程
分区交换的存储过程分为如下步骤
–1.查询msg_message_list表的分区数量
–2.当msg_message_list表的分区数量不止一个时，查询最早的分区信息
–3.查询分区最大时间是否在一年前（和入参upperDate有关），满足条件继续进行下面的步骤
–4.交换分区数据
– 4.1交换msg_message_list表分区数据至msg_message_temp临时表
– 4.2历史表msg_message_history中插入一条数据，产生分区，然后回滚数据
– 4.3交换单表msg_message_temp数据到msg_message_history历史分区表
– 5 删除MSG_MESSAGE_LIST表分区
CREATE OR REPLACE PROCEDURE "USERDB"."PROC_DROP_PARTITION_MSG_LIST"
(upperDate IN date, code OUT NUMBER, remark OUT VARCHAR2)
Authid Current_User as
-- 将msg_message_list表历史数据迁移到msg_message_history
dropPartitionName varchar2(200); -- msg_message_list表最早分区的PARTITION_NAME
dropPartitionHighValue varchar2(200); -- msg_message_list表最早分区的HIGH_VALUE
maxPartitionDate date; -- msg_message_list表最早分区内数据的最大时间
v_date varchar2(200); -- msg_message_list表最早分区内数据的最大时间
v_sql_maxdate_partition varchar2(500); -- sql，查询分区内数据的最大时间
v_sql_exchange_partition1 varchar2(500); -- sql，交换分区list->temp
v_sql_exchange_partition2 varchar2(500); -- sql，交换分区temp->history
v_sql_drop_partition varchar2(500); -- sql，删除msg_message_list的分区
v_sql_create_partition varchar2(500); -- sql, 将msg_message_temp的一条数据插入历史表msg_message_history
partionTotalNum number; -- msg_message_list表的分区数量
err_msg varchar2(2000);
err_code number;
begin
--1.查询msg_message_list表的分区数量
SELECT count(*) into partionTotalNum
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME = 'MSG_MESSAGE_LIST';
IF partionTotalNum <=1 then
code := 0;
remark := '分区数量不足,无需删除,';
return;
END IF;
--2.当msg_message_list表的分区数量不止一个时，查询最早的分区信息
SELECT T.PARTITION_NAME, T.HIGH_VALUE into dropPartitionName, dropPartitionHighValue
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME = 'MSG_MESSAGE_LIST' and T.PARTITION_POSITION = 2;
--3.查询分区最大时间是否在一年前
v_sql_maxdate_partition := 'SELECT MAX(MSG_TIME) FROM MSG_MESSAGE_LIST PARTITION (' ||
dropPartitionName || ')';
execute immediate v_sql_maxdate_partition into maxPartitionDate;
v_date := to_char(maxPartitionDate, 'YYYY-MM-DD');
dbms_output.put_line(dropPartitionName ||'分区内数据最大日期为' || v_date);
IF maxPartitionDate IS NOT null and maxPartitionDate > upperDate then
dbms_output.put_line(dropPartitionName ||'分区内数据最大日期为' || maxPartitionDate || ',无需删除');
code := 0;
remark := '分区' || dropPartitionName || '内数据最大日期为' || v_date || ',无需删除';
return;
END IF;
--4.交换分区数据
IF maxPartitionDate is not null then
-- 4.1交换msg_message_list表分区数据至msg_message_temp临时表
v_sql_exchange_partition1 := 'Alter table MSG_MESSAGE_LIST exchange partition ' ||
dropPartitionName ||
' with table MSG_MESSAGE_TEMP WITHOUT VALIDATION';
execute immediate v_sql_exchange_partition1;
dbms_output.put_line('交换LIST表分区至临时表：' || dropPartitionName||' ('|| dropPartitionHighValue||') ');
commit;
-- 4.2历史表msg_message_history中插入一条数据，产生分区，然后回滚
v_sql_create_partition := 'insert into MSG_MESSAGE_HISTORY select * from MSG_MESSAGE_TEMP where rownum=1';
execute immediate v_sql_create_partition;
rollback;
dbms_output.put_line('HIS表创建分区完毕');
-- 4.3交换单表msg_message_temp数据到msg_message_history历史分区表
v_sql_exchange_partition2 := 'ALTER TABLE MSG_MESSAGE_HISTORY EXCHANGE PARTITION
FOR(to_date(''' || v_date || '' || ''',''YYYY-MM-DD''))' ||
' WITH TABLE MSG_MESSAGE_TEMP WITHOUT VALIDATION';
execute immediate v_sql_exchange_partition2;
dbms_output.put_line('交换temp数据至HIS表');
commit;
END IF;
dbms_output.put_line('即将删除LIST表分区：' || dropPartitionName||' ('|| dropPartitionHighValue||')');
-- 5 删除MSG_MESSAGE_LIST表分区
v_sql_drop_partition := 'Alter table MSG_MESSAGE_LIST drop partition ' || dropPartitionName;
execute immediate v_sql_drop_partition;
code := 0;
remark := '删除LIST表分区' || dropPartitionName||' ('|| dropPartitionHighValue||')';
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ROLLBACK;
err_code := sqlcode;
err_msg := subStr(sqlerrm, 1, 1500);
dbms_output.put_line(err_code || err_msg);
insert into MSG_PROC_LOG
values (sysdate, 'PROC_DROP_PARTITION_MSG_LIST', err_code, err_msg, '-1');
commit;
code := -1;
remark := err_code|| ':' ||err_msg;
return;
END;
注意：
- 存储过程有入参和返回值，入参upperDate为某月的第一天。返回值code和remark，用实体接收。
- temp表是必须的，不可以省略，两个分区表直接交换会报错 ORA-14095: ALTER TABLE EXCHANGE 要求非分区, 非聚簇的表。
- 虽说在history表里创建了一个分区，但是该分区的名字我并不知道，所以temp向history分区交换时的sql要复杂一些
- 其实我还有一张日志表，用于记录该存储过程工作的具体步骤。
- 参考连接https://www.jianshu.com/p/d523831554d7
- 分区交换时需要关注索引问题。因为我没涉及到这部分，这里就不班门弄斧了。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
