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

问题：

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

表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，别让我失望！