架构深渊

慢慢走进程序的深渊……关注领域驱动设计、测试驱动开发、设计模式、企业应用架构模式……积累技术细节,以设计架构为宗。
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

数据表分区解决方案

Posted on 2009-04-22 01:48  chen eric  阅读(738)  评论(0编辑  收藏  举报
数据表分区解决方案
问题归纳: 
数据仓库根据不同的业务情况数据的量也是不同的。对于数据仓库来说,处理的时候是需要对相应的表进行查询的,因此对查询的优化也就是对整个处理过程的优化。我们一般对那些数据量比较大的度量值表进行按年建表,或者按月建表的方式来优化其查询的速度。但是有时候某些维度表的数据也会非常的多(数据仓库中维度表不能使用分区),或者是已经按月建表的但是每个月的数据量仍然非常大,严重的影响了数据仓库的处理性能。

方案提出:
对那些数据量非常大的表进行分区处理,根据不同的情况按照日期或者ID进行拆分

处理方式:
在新建数据库的时候,建多个文件组,用来存储不同分区函数来分隔的数据,每个文件组分别建多个数据文件,减少I
/O的读写。建议每个数据文件不要大于3G。以后可以动态添加数据文件

1、那些按年建的表,按照季度分成4个分区,1一个季度对应一个文件组来存放

按时间方式来分区的函数和方案代码如下:

CREATE PARTITION FUNCTION [PF_DATETIME_2008](numeric(18,0)) AS RANGE RIGHT FOR VALUES (200804010000200807010000200810010000)

注:日期的分割节点分别为4月1日,7月1日和10月1日

CREATE PARTITION SCHEME [PS_DATETIME_2008] AS PARTITION [PC_DATETIME_2008] TO ([PRIMARY][FG2][FG3][FG4])

注:根据函数中的3个分割点,分别存储在4个不同的文件组里

建数据表的时候语句如下:

create table tb_pf

(

      id 
int,

      
datetime numeric(18,0)

)

on PS_DATETIME_2008(datetime)

2、对于那些按月建表的或者是没有日期标识的数据表,我们可以利用其自增长的ID来分割分区

按ID方式来分区的函数和方案代码如下:

 

CREATE PARTITION FUNCTION [PF_ID](bigintAS RANGE RIGHT FOR VALUES (200000004000000060000000)

注:ID的分割节点分别为20000000,40000000和60000000

CREATE PARTITION SCHEME [PS_ID] AS PARTITION [PF_ID] TO ([PRIMARY][FG2][FG3][FG4])

建数据表的时候语句如下:

create table tb_pf

(

      id 
int,

      
datetime numeric(18,0)

)

on PS_ID(id)

实施效果:
对一个有2500多万数据的分局数据表insert和update操作比一个没有进行分区的数据表性能提高约10
%左右