数据库学习之分区技术

最近一段时间的工作围绕数据库开展，学习重心也在和数据库相关的技术上。这一周分析错误数据时发现了可能在分区表上出现了一些错误，就顺便把分区技术做了下总结。

分区的原理

   分区的基本原理就是通过访问一个表或者索引的较小片断，而不是访问整个表和索引，以提高数据库的性能。如果将一个表的不同分区放置在不同的磁盘上，磁盘整体的吞吐量就会成倍上升。

分区的优点

   提高数据的可用性。如果表全部存在某个磁盘上，一个磁盘块的损坏，都会导致该磁盘块上的所有数据都无法访问。将表存在不同磁盘上，一部分磁盘损坏时，仍有数据是可用的。

   减轻管理的负担。在一个100GB的表上执行管理操作时（如重组来删除移植的行，或者在“净化”旧信息后回收表左边的“空白”空间），与在各个10GB的表分区上执行10次同样的操作相比，前者负担要大得多。另外，通过使用分区，可以让净化例程根本不留下空白空间，这就消除了重组的必要。

   改善某些查询的性能。主要在数据仓库环境中有这个好处，通过分区，消除很大的数据区间，相应地根本不用访问这些数据。但这在事务性系统中并不适用，因为这种系统本身就只是访问少量的数据。

   把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争。如果一个段遭遇激烈的竞争，可以把它分为多个段，能成比例地减少竞争。不仅可以提高用户访问时的性能，而且还可以提高备份时的灵活性。可以降低备份作业过程中出现的I/O冲突问题，减少备份的时间。

 

（插入一下OLTP的概念）

        On-Line Transaction Processing联机事务处理系统(OLTP)，也称为面向交易的处理系统，其基本特征是顾客的原始数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果。这样做的最大优点是可以即时地处理输入的数据，及时地回答。OLTP通常具有以下特征：支持大量并发；定期添加和修改数据；反映随时变化的单位状态，但不保存历史记录；包含大量数据，其中包括用于验证事务的大量数据；具有复杂的结构；可以进行优化以对事务活动做出响应；提供用于支持单位日常运营的技术基础结构；个别事务能够很快地完成，并且只需访问相对较少的数据。OLTP系统旨在处理同时输入的成百上千的事务。

 

分区原则

   考虑对表是否需要分区时，可以从以下几个方面判断。不过这也只是经验之谈。仅供参考。

    1表的大小。对于大表进行分区，有益于大表操作的性能和大表的数据维护。通常，当表大小超过1.5G，对于OLTP系统表记录超过1000万，都应该考虑分区。

    2数据的访问特性。基于表的大部分查询应用，只访问表中的少量数据。对于这样的表进行分区，可以排除无关数据查询的特性。

    3数据维护。某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据，对有这样需要的表进行分区。以满足维护需要，因为delete大量数据，对系统开销很大，有时甚至是不可接受的。

   4只读数据。如果一个表中大部分数据都只是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是十分有益的。

   5并行数据操作。对于经常执行并行操作的表，应考虑分区。

    6表的可用性。对表的某字段或者某段数据可用性要求很高时，应考虑分区。

分区机制及适用范围

    目前Oracle中有4种对表分区的方法。

   范围分区：可以指定应当存储在一起的数据区间。这可能是Oracle中最常用的分区机制。适用于数值型或日期型。如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中，并且支持指定多列做为依赖列。语法是:partition by range()。例如：

create table xxx

{

Score integer,

}

partition by range(Score)(

partition p1 values less than(60),

partition p1 values less than(80),

partition p1 values lessthan(maxvalue)

)                                   

   散列分区：指在一个（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。适用于事先不知道指定范围内有多少数据，以及如果用范围分区，各区之间数据量差异很大、很难人工干预分区间数量平衡的情况。语法是：partitionby hash()。例如：

create table department(

Deptnoid int primary key,

Deptname varchar(20)

)

partition by hash(Deptnoid) (

Partition p1,

Partition p2

)

   列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分区1中，STATUS值在（ ‘D’,P’,’Q’）中的行放在分区2中，依此类推。列表分区和范围分区的不同主要是列表分区按照预先给定的一系列离散值进行分区，新数据插入表中时，根据分区键值找到对应分区。列表分区的分区列只有一个，当然其单个分区对应值可以使多个。在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区，存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。语法是：partitionby list()。例如：

create table ListTable(

id int primary key,

area varchar(10)

)

partition by list(area)

(

partition part1 values('guangdong','beijing') ,

partition part2 values('shanghai','nanjing'),

partition part3 values(default)

); 

   复合分区：是范围分区和散列分区或列表分区混合使用的一种分区方法。复合分区在分区上用的是范围分区，在每个分区上又可以使用列表分区或散列分区的方法分成多个子分区。语法是：partitionby range() subpartition by hash()。例如：

create table salegrage

{

grade number,

losal number,

hisal number

}

partition by range(grade)

subpartition by hash(losal, hisal)(

partition p1 values less than(10),

(subpartition sp1, (subpartition sp2),

partition p1 values less than(20),

(subpartition sp3, (subpartition sp4)

)

分区管理

     分区可以和表一样进行增删改查。SQL语句示意如下。

添加分区：alter table xxx add partition p5 values less than(100)。特别注意对于list、range这样存在范围值的分区，所增加的分区值必须要大于当前分区中的最大值，否则会报错，hash分区无此限制。

删除分区：alter table student drop partition p4.

合并分区：alter table student merge partitions p3,p4  into partition p6.