性能优化(案例一)-横向分表

问题：

项目为工作流系统，工作流核心表涉及三张表，分别为

表A:工单表

表B:工单任务表

表C:工单任务参与表

随着数据量急剧增加，表C达到1亿，表B达到2000万，表A达到500万

此时对这三张表做关联查询时，尽管通过索引以及SQL优化等方式，也无法解决性能问题时

只能对表结构进行切分

?

方案：

数据库分表主要有两种：横向切分、纵向切分

通过分析这三张表主要应用于工作流系统，主要根据表A的endTime字段从数据上可划分为活动数据、不活动数据、历史数据（endTime字段的意义是：如果工单结束，endTime为结束时间）。

活动数据-频繁的查询、增删改操作

不活动数据-查询统计报表操作

历史数据-很少进行查询，一般为几年前的数据

?

实施：

步骤一、

以表A为例，增加archive字段，根据该字段将数据划分为

archive=-1：历史数据

archive=0：不活动数据

archive=1：活动数据

原始表A的定义为：

create table table_A

(

?? ID??????????????? VARCHAR(128) PRIMARY KEY,

?? PARENTID??? VARCHAR(128),

?? ……

?? ENDTIME????? VARCHAR(128)

);

改造后的表A定义为：

create table table_A

(

?? ID??????????????? VARCHAR(128) PRIMARY KEY,

?? PARENTID??? VARCHAR(128),

?? ……

?? ENDTIME????? VARCHAR(128) ,

?? ARCHIVE????? NUMBER(1) DEFAULT 1 NOT NULL

);

partition by range (ARCHIVE)
(
? partition SUBS_0 values less than (0)
? partition SUBS_1 values less than (1)
? partition SUBS_2 values less than (2)
);

?

步骤二、

insert、update、delete操作不做改动

对表A的查询操作增加条件（只针对活动数据的查询）

archive=1

?

步骤三、

建立oracle job完成每天数据的自动归档任务

存储过程为：

create or replace procedure PROC_TABLE_A as
? TYPE ridArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
? type cur_type is ref cursor;
? v_rid??? ridArray;
? v_cursor cur_type;

begin
? dbms_output.put_line(' Start purging finished processes...... ');
? open v_cursor for 'select? ROWID from table_A partition(SUBS_2) where endtime is not null';
? loop
??? FETCH v_cursor BULK COLLECT
????? INTO v_rid LIMIT 10000;
??? forall row in 1 .. v_rid.count()
????? UPDATE tbl_process_draft partition(SUBS_2)
???????? set archive = 0
?????? where rowid = v_rid(row);
??? COMMIT;
??? EXIT WHEN v_cursor%NOTFOUND;
? END LOOP;
? close v_cursor;
? dbms_output.put_line(' done! ');

exception
? when others then
??? dbms_output.put_line('error is' || sqlcode ||
???????????????????????? '?? error?? message?? is' ||substr(sqlerrm, 1, 100));
??? rollback;
end;

?

job为：

begin
? sys.dbms_job.submit(job => :job,
????????????????????? what => 'PROC_TABLE_A;',
????????????????????? next_date => to_date('04-05-2011 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
????????????????????? interval => 'TRUNC(sysdate) + 1 +1/ (24)');
? commit;
end;

有没有搞错，提交之后，发现内容变一半了，只能再把丢掉的内容补上

javaeye，别让我失望！