DB2大数据量优化查询解决方案

时间:2022-03-12 11:53:23
利用DB2表分区的功能对大数据量的表进行分区,可以优化查询。

表分区介绍:

表分区是一种数据组织方案,它根据一列或多列中的值把表数据划分为多个称为数据分区 的存储对象。

(我觉得表分区就类似于Windows系统的磁盘分区,通常我们将磁盘分区成:系统盘,娱乐盘,学习盘,工作盘。如果我想找一个《Thinking in java》的电子书,我们会直接去学习盘去搜索,相反,如果我们电脑只分一个区,那么我们必须要搜索整块硬盘。)

When需要进行表分区

在数据仓库中,事实表或历史表的大小是摆在设计人员和管理员面前的一个挑战。这些表通常包含数亿行数据,有时候甚至包含数千亿行数据。对于这种规模的表,主要关心以下几点:

  • 查询性能
  • 将大量新数据插入到这些表中
  • 每月或每个季度删除大量过时的数据

(我觉得分区表主要用在记录型表中,表中数据按时间日期进行累计,记录的可利用性随时间的流逝逐渐变小,历史数据不会被使用或者被利用的可能性很小)

How建立表分区:

create table person1(

   id varchar(32) NOT NULL,

   p_name varchar(32),

   p_age INTEGER,

   birthday DATE,

   job char(32)

) partition by range(birthday)(

starting  minvalue ending ‘9/1/2013’ exclusive,

 starting  '9/1/2013' ending '9/1/2014’ exclusive every(1 months)

);

DB2使用partition by range对表字段进行分区,利用starting 开始时间 ending 结束时间指定分区范围。上面红色部分首先建立一个最小时间值到2013-9-1(exclusive表示不包括2313-9-1这一天)的一个分区,第二个starting是自动建立多个分区,建立一个从2013-9-12014-9-1时间范围每月建立一个分区,也就是12个分区。这里没有指定分区名称,系统默认提供分区名是PART0PART1PART2….

How添加一个分区:

ALTER TABLE DB2INST1.PERSON1 ADD PARTITION PART13 STARTING FROM ('2014-9-1') INCLUSIVE ENDING AT ('2014-10-1') EXCLUSIVE IN PERSON_SP2 ;

添加一个范围是2014-9-12014-10-1PART13分区。

分区过多怎么办:

分区按时间进行,历史分区太过,想删除一些历史分区怎么办?DB2提供了一个拆离的功能,可以将历史分区数据从表中分离(滚出)出去,放在其他表中。

ALTER TABLE DB2INST1.PERSON1 DETACH PARTITION PART1 INTO TABLE DB2INST1.PERSON_HISTORY;

分离后再加入怎么办:

DB2连接(滚入)功能,可以连接一张表,将制定表中的数据连接到一个分区中。

ALTER TABLE DB2INST1.PERSON1 ATTACH PARTITION PART1 STARTING FROM ('2013-9-1') INCLUSIVE ENDING AT ('2013-10-1') EXCLUSIVE FROM TABLE DB2INST1.PERSON_HISTORY;

 

报警表推荐方案:

报警表单日数据量部局可达10万级,建议采用两张表,一张为事实表,一张为历史表,事实表按天进行分区,历史表按年进行分区。

1、 初始化表建立一个月的分区,后每个月末建立下个月的分区;

2、 一年后拆离(滚出)上一年上半年的分区数据至历史表;

3、 为了加快查询速度,建议在查询条件主要字段上建立索引;

4、 报警表建立独立表空间。