利用表分区的大数据库优化方法

2013-09-19王佳，王智森

大连工业大学学报 2013年6期

王佳，王智森

(1.大连工业大学实验仪器中心，辽宁大连 116034；2.大连工业大学信息科学与工程学院，辽宁大连 116034)

0 引言

随着大数据时代和云计算时代的到来，海量数据存储和管理是人们亟待解决的问题，大型数据库的存储量通常都到达数百GB，有的甚至达到TB级[1]。其中数据库中单表的数据记录往往也达到上亿条，而且记录的条数会随着时间不断增长。这不但影响了数据库的运行效率，同时增大了数据库的维护难度。为了提高对大数据表的查询和管理效率，传统的关系型数据库引入表分区的方法[2]。本文以SQL Server 2008为后台数据库，以某监测系统为例对表分区方法做了介绍，并通过实验验证了表分区的优化效果。

1 表分区

表分区是指在数据库中将大表在物理上分成多个小表存储，而在逻辑上仍然是一张表的分区方法。即表分区对程序员是透明的，开发人员不需要关心表在物理上是怎样存储的。在SQL Server中表分区分为水平分区和垂直分区，Oracle数据库中还包括散列分区等不同的表分区方式[3]。其中水平分区是指将一个表分为多个表，每个表包含的列数相同，但是行数更少。垂直分区是指将表分为包含更少列的表，而各表的行数相同的分区方法。

通过对大表做分区，当查询某一分区中的对象时，仅需要扫描对象所在分区，这无形中提高了对数据的检索效率。例如当对一个数据记录数达上亿条的数据表按月进行分区后，对特定月份的查询，只需要扫描一个上千万的表，大大缩小了查询范围。图1为表分区的示意图，如果系统是多CPU或多磁盘系统，将不同的分区放置在不同的磁盘上，通过并行操作提高了系统响应性能，同时能使I／O均衡，提高系统吞吐量。可以以分区为单位对数据做移动和备份操作，某一分区出现故障，不会影响其他的分区，只需对故障分区作恢复即可，使数据的维护和管理更加方便。

图1 表分区示意图Fig.1 Schematic plan of partitioned table

2 表分区的实现方法

不同的数据库实现分区表的方法各不相同，本节以SQL Server 2008为例说明了分区表的实现过程。

2.1 SQL Server数据库数据文件构成

SQL Server数据库中包含3种类型的文件：

(1)主数据文件：每个数据库中都有一个主数据文件，主要用于存储与系统相关的信息，还可以跟踪数据库中的其他文件。通常扩展名为.mdf。

(2)辅助数据文件：一个数据库中有0到多个辅助数据文件，只用于存储用户数据。文件扩展名.ndf。

(3)日志文件：每个数据库中至少有一个日志文件，其包含恢复数据库中所有事务所需的信息。日志文件任何不属于文件组，单独管理其文件扩展名.idf。

出于分配和管理的目的将数据库文件分为多个组，其中包含主数据文件的文件组被称为主文件组，一个数据库中只有一个主文件组，除了主文件组外数据库中还可以有一个或多个用户定义的文件组，以上结构可以参考图2。

图2 数据库的文件构成Fig.2 Files in a database

2.2 表分区的实现

创建分区依次按如下几个步骤操作：

(1)首先要对表分区有一个整体的规划，确定分区字段、分区个数等。

(2)创建分区函数

分区函数确定分区表要使用的键(字段)，以及各分区的边界。可以说分区函数确定了分区表的逻辑架构，使用T－SQL语句创建分区函数：

CREATE FUNCTION Partition＿Function－Name(ParameterType)AS RANGE [LEFT／RIGHT]FOR VALUES([boundary＿value[，…..n]])。

分区函数虽然也是用户自定义的函数，但是它不同于普通的用户自定义的函数，分区函数只用于分区表的创建。对于分区表中分区边界的归属问题，可见参考文献[4]。

(3)创建分区架构(分区方案)

分区架构将每个分区映射到具体的文件组中，规定了分区表的物理结构实现逻辑结构到物理结构的映射，生成分区架构的语句：

CREATE PARTITION SCHEME partition＿scheme＿name AS PARTITION partition＿name TO (file＿group＿name，…)。

这里可以选择将所有的分区都映射到一个文件组中，也可以将多个分区映射到一个文件组中，最好是将不同分区表映射到不同的文件组中，这样可以达到I／O的均衡。分区函数和分区架构是分区表要依赖的两个对象，这两个对象只用于分区表和分区索引。它们之间的依赖关系如图3所示[4]。

图3 分区表依赖关系Fig.3 Partitioned table dependencies

(4)创建分区表或索引

要在创建对象时指定要使用的分区方案，就可以创建分区表或分区索引了，使用T－SQL语句：

CREATE TABLE table＿name(columns，...)on partition＿scheme＿name(partition＿key)。

3 分区的管理

使用表分区方便了数据的维护和管理，对大表而言可以以表分区为单位操作表中的数据。本节介绍了2种基本分区表操作。对分区的操作，是通过改变分区函数实现的，分区函数改变后，改变通过分区架构传递到分区表中。

(1)拆分分区－添加分区，使用对应的 T－SQL语句：

ALTER PARTITION FUNCTION function＿name()SPLIT RANGE(rangeValue)。

(2)合并分区－删除分区，合并现有的分区中的两个，对应的T－SQL语句：

ALTER PARTITION FUNCTION function＿name()MERGE RANGE(rangeValue)。

对分区的拆分和合并只需要修改分区函数，但是新的分区要映射到哪个文件组上，需要由分区架构通过NEXT USED指定T－SQL语句：

ALTER PARTITION SCHEME partition＿scheme＿name NEXT USED filegroup＿name。

分区的拆分与合并主要用于数据的转移备份。SQL Server通过一种基于元数据的操作把数据从一个分区移到另一个分区，在这个移动过程中数据本身不需要移动。数据移动在很短的时间完成，对系统产生的影响很小。用户可以对分区数据做如下操作：①将一张表作为一个分区转移到另一个已存在的分区表中；②将一个分区从一张分区表转移到另一张分区表上；③重新指派一个分区以形成一张单表[5]。

4 查询效率提高的验证实验

本节结合具体的应用系统，对系统的中大表做了分区优化。在某监控系统中，系统采集的监控数据存储在SampleData表中，其表结构如表1所示。

表1 SampleData表结构Tab.1 SampleData table

系统的数据采集频率高，时间长，为加快查询速度决定将SampleData表做分区处理，以SampleTime字段为分区字段，每个月的数据作为一个分区。

分区函数：

CREATE PARTITION FUNCTION pf(datetime)RANGE RIGHT AS RANGE FOR(‘20100601 00：00：00’，‘20100701 00：00：00’，….，‘20110401 00：00：00’)。

分区架构：

CREATE PARTITION SCHEME ps AS PARTITION pf TO([PRIMARY]，FG1，FG2，FG3)。

生成分区表：

CREATE TABLE SampleData(DataID，TrackID，DataValue，SampleTime)on ps(SampleTime)。

表的分区如图4所示：

图4 SampleData表分区示意图Fig.4 The partitioned table of SampleData

本文模拟系统数据，分别做了2组对比测试。

(1)4个分区的分区表，分区信息如表2所示。

表2 分区表信息一Tab.2 Partitioned table info one

做了8组查询对比，对比试验的结果如表3所示。

表3 查询时间对比一Tab.3 Query time comparison one

从表3中每组对比试验的查询时间可知，分区表中的查询速度明显快于未分区表。

(2)4个分区的分区表，数据总量10G，分区信息如表4所示。

表4 分区表信息二Tab.4 Partitioned table info two

分别查询1、2、3、6、13、15d的数据量，查询所用时间如表5所示。

表5 查询时间对比二Tab.5 Query time comparison two

根据每次查询的数据总量与相应的查询时间比可以得到图5。

图5 不同数据总量单位时间查询量Fig.5 Query data amount per unit of different data

通过对图5的分析可得，对表做分区后单位时间的查询效率有显著的提高。随着查询数据量的增加，分区后查询效率也随之增加，但是当数据量到达一定量时，查询效率突然下降，主要是由于硬件条件的限制导致的。

常年采集数据的监测系统会产生大量数据。大多数情况下，只需要对最近3年的数据进行查询，所以对数据库按照时间进行分区操作，随着时间的增加，利用表分区的拆分、合并和移动，始终保持3年的数据。同时表分区处理数据拆分、合并和移动操作只是元数据的操作，避免了数据被移动到只读表或从只读表中删除而造成的时间延迟、空间占用。