大规模数据库的性能改善方法之一 数据分割

大规模数据库的性能改善方法之一 数据分割

http://blog.sina.com.cn/s/blog_803d9ba90100xg25.html

1、引言

随着计算机应用领域的拓展和计算机硬件性能的提高，企业对数据保存量的要求不断提高。今天，企业所保存的数据量可能是几百个GB，甚至是几个TB，我们把这种数据库叫大规模数据库（Very Large Database,缩写为VLDB）。面对如此庞大的数据量，如何提高数据库的性能，以及高效地进行数据库的维护，成为一个重要的课题。

2、解决方法

企业的业务需求决定了对数据库的使用方式，不可能有统一的解决方法。但可从三个方面考虑，数据分割、并行处理、数据压缩。本文以数据分割为中心进行探讨。

对数据进行分割，可缩小待访问数据对象的范围或磁盘空间，提高检索性能。如果把分割后的数据放到不同的磁盘上，可提高数据库并行访问的能力。

数据分割分为水平分割和垂直分割。水平分割指把表中的记录分成若干部分，把各个部分单独存储。垂直分割，是指把不可能在检索条件中出现的长字段（Field），例如，Binary、Text、Xml等字段剥离到别的表，通过表结构的变化，进行数据分割。对BLOB字段，尽管在表中保存的可能是一个参照，但也还是分离出去为宜，以确保该字段只在需要时读取。

3、主流数据库管理系统（RDBMS）提供的解决方法—— 分区

目前，Oracle、SQLServer、PostgreSQL、MySQL都提供数据分区技术，分区（Partitioning），属于数据的水平分割，是把表中的数据分割成较小的、更易于管理的若干部分，每一部分叫分区（partition）。每个分区具有完全相同的逻辑属性，例如，相同的列名、相同的数据类型，但是每个分区可具有不同的物理属性，即不同的存储设置。通过分区的设定可以把数据分配到不同的文件中或者分配到不同的磁盘上。分区在表定义时创建。当数据插入时，数据库管理系统会自动地把数据分配到相应的分区里。

分区对应用层没有影响，当执行DML操作（如Select、Update、Delete）时，仍是对同一个表进行操作，SQL语句无需修改。

4、分区的优点

分区的优点主要有，改善检索性能，提高数据操作的并行性，加强数据库的维护性，提高运行效率。

根据检索条件，只对特定的分区进行搜索，而非整个表，可以预见检索性能会有大的提高。

如果不使用分区的话，对过时数据进行删除时，在数据量大，执行时间长，给系统造成很大负担的情况下，常限制对整个数据库的使用。但使用分区的话，只对过时数据所在分区进行操作，不仅对其他分区的使用没有限制，并且可采用对过时数据所在分区进行删除的方式，缩短操作时间。

对数据库进行备份（Backup）、恢复（Restore）、输入（Import）、输出（Export）等维护性操作时，可针对特定分区进行，即减少了维护操作执行时间，同时对其他分区的数据操作可并行执行。

5、分区划分方法

本文以Oracle为例，介绍三种比较典型的分区划分方法。

（1）范围（Range）划分法

把数据按数值范围划片分割。例如日志记录、销售记录，数据以时间顺序生成。分区可按日期范围，如月份、季度，划分。例如把销售记录表按季度划分。

CREATETABLE sales_detail
(

product_cdCHAR2(10), --商品编码
sales_amountNUMBER(10), --销售数量
sales_dateDATE --销售日
)

PARTITIONBY RANGE(sales_date)(

PARTITIONp2006q1 VALUES LESS THAN(TO_DATE(‘2006-04-01‘,‘YYYY-MM-DD‘)),--1季度分区
PARTITIONp2006q2 VALUES LESS THAN(TO_DATE(‘2006-07-01‘,‘YYYY-MM-DD‘)),--2季度分区
PARTITIONp2006q3 VALUES LESS THAN(TO_DATE(‘2006-10-01‘,‘YYYY-MM-DD‘)),--3季度分区
PARTITIONp2006q4 VALUES LESS THAN(TO_DATE(‘2007-01-01‘,‘YYYY-MM-DD‘)) --4季度分区

);

（2）列表（List）划分法

以不连续的任意的数值或一组数值对数据进行划分。例如数据以地区、部门为单位自然分组，可按自然单位划分。例如把销售记录表按地区划分。

CREATETABLE sales_list

(

product_cdCHAR2(10), --商品编码

sales_stateVARCHAR2(20), --销售地区

sales_amountNUMBER(10) --销售数量

)

PARTITIONBY LIST(sales_state)(

PARTITIONsales_beijing VALUES(‘BeiJing‘), --BeiJing地区

PARTITIONsales_shanghai VALUES(‘ShangHai‘), --ShangHai地区

PARTITIONsales_liaoning VALUES(‘ShenYang‘,‘DaLian‘) --LiaoNing地区

);

（3）组合划分法

把范围划分法和列表划分法组合在一起使用。按范围划分后，可进一步细化。例如把销售记录表按季度（范围）、地区（列表）划分。

CREATETABLE sales_detail
(

product_cdCHAR2(10), --商品编码
sales_amountNUMBER(10), --销售数量
sales_dateDATE， --销售日

sales_stateVARCHAR2(20) --销售地区
)

PARTITIONBY RANGE(sales_date)

SUBPARTITIONBY LIST(sales_state）

(

PARTITIONp2006q1 VALUES LESS THAN(TO_DATE(‘2006-04-01‘,‘YYYY-MM-DD‘)) -–1季度分区

(

SUBPARTITIONsales_beijing VALUES(‘BeiJing‘), --BeiJing地区

SUBPARTITIONsales_shanghai VALUES(‘ShangHai‘), --ShangHai地区

SUBPARTITIONsales_liaoning VALUES(‘ShenYang‘,‘DaLian‘) --LiaoNing地区

),
PARTITIONp2006q2 VALUES LESS THAN(TO_DATE(‘2006-07-01‘,‘YYYY-MM-DD‘)) –2季度分区

(

SUBPARTITIONsales_beijing VALUES(‘BeiJing‘), --BeiJing地区

SUBPARTITIONsales_shanghai VALUES(‘ShangHai‘), --ShangHai地区

SUBPARTITIONsales_liaoning VALUES(‘ShenYang‘,‘DaLian‘) --LiaoNing地区

),
PARTITIONp2006q3 VALUES LESS THAN(TO_DATE(‘2006-10-01‘,‘YYYY-MM-DD‘)) –3季度分区

(

SUBPARTITIONsales_beijing VALUES(‘BeiJing‘), --BeiJing地区

SUBPARTITIONsales_shanghai VALUES(‘ShangHai‘), --ShangHai地区

SUBPARTITIONsales_liaoning VALUES(‘ShenYang‘,‘DaLian‘) --LiaoNing地区

),
PARTITIONp2006q4 VALUES LESS THAN(TO_DATE(‘2007-01-01‘,‘YYYY-MM-DD‘)) --4季度分区

(

SUBPARTITIONsales_beijing VALUES(‘BeiJing‘), --BeiJing地区

SUBPARTITIONsales_shanghai VALUES(‘ShangHai‘), --ShangHai地区

SUBPARTITIONsales_liaoning VALUES(‘ShenYang‘,‘DaLian‘) --LiaoNing地区

)

);

表是否需要进行分区划分，以及如何划分。应从以下三个方面考虑。

（1）数据分布的特点

要调查数据记录按哪个列的取值均匀发布。例如把表按某个列进行了划分，如果数据过于集中到某个分区，那么这种划分没有实际意义。

（2）业务上使用的特点

要调查业务上主要使用哪些SQL语句，条件部（Where）指定了哪些列。要注意，用于分区划分所指定的列，必须在SQL语句的条件部出现，这样，数据库管理系统才会进行分区查询。否则，仍然是在整个表中查询。

（3）数据运用维护的特点

例如，系统要求保留最近3年的数据，并且只对最近一年的数据进行插入、更新、删除操作，3年以前的数据几乎不使用，可进行备份和删除。像这种情况，如果采用表分区的话，可简化系统的管理和维护。

表分区是否获得预期的效果，应对分区前和分区后进行比较确定。

6、结束语

分区并非新概念，Oracle8i就已经提供了这一功能。由于数据库应用水平的限制，这一概念并没有深入普及。今天，面对日益增长的数据量，在积极探索新的解决办法的同时，灵活运用已有的技术手段，不失为良策。