现在某家企业的Oracle数据库中有一张产品信息表这张表中的记录已经超过了万条其中成品信息大概万条零件记录有万条左右剩余的都是包装信息数据库工程师通过相关的分析与监测用户访问这张表的时候会有严重的等待现象这主要是因为这张表中的数据存放在同一块硬盘上当不同的用户并发访问这张表时会因为磁盘I/O性能的瓶颈而导致等待如下图所示当各位读者遇到这种情况该如何采取措施来优化性能呢?笔者这里的建议是采用Oracle分区表减少磁盘的I/O沖突改善数据库的性能
一分区表的原理与优势
分区表对于提高大表的访问性能会有很大的帮助如上图所示可以将一张产品信息表分按产品类别分为三个部分分别为成品信息零件信息和原材料信息然后将这三部分对应一个单独的分区并将它们存放在不同的硬盘上此时当不同的用户访问不同的信息时就可以有效的降低磁盘的I/O沖突即使是同一个用户需要同时访问这三部分信息如产品的物料清单时由于其分别从不同的硬盘中读取数据为此也可以明显的降低磁盘I/O沖突
所以分区的基本原理就是通过访问一个表或者索引的较小片断而不是访问整个表和索引以提高数据库的性能当然这有一个前提条件需要将一个表的不同分区放置在不同的磁盘上此时磁盘整体的吞吐量就会成倍上升
采取分区不仅可以提高用户访问时的性能而且还可以提高备份时的灵活性如上面这个例子将产品信息根据其类别分为不同的区并将它们保存在不同的硬盘上此时就可以对各自的分区进行单独的备份如最近因为国家环保的要求原材料信息进行了大规模的调整此时就可以对原材料信息所在的分区数据进行单独的备份这不仅可以减少备份的时间而且可以降低备份作业过程中出现的I/O沖突问题
一般来说分区表的优势有一个前提就是需要将分区数据放置在不同的磁盘上如果不这么做的话那么分区往往很难起到降低磁盘I/O的效果所以为了最大程度的降低一个大表的磁盘I/O(特别是经常会有并发行的访问)此时应该将表分割到多个分区上然后再将这些分区存放在不同的磁盘上
二分区表模式的选择
那么该如何对表进行分区呢?这又是一个比较关键的问题根据经验笔者认为要让分区取得更好的效果分区表模式的选择至关重要也就是说按什么内容对表进行分区管理有时候先同的数据采取不同的分区表模式往往会有不同的效果在下面的内容中笔者会结合企业实际应用的情景对分区表模式的选择进行举例希望这些内容能够帮助各位读者更好的维护分区表
第一种模式按行来进行分区
上面这个例子中笔者谈到有一张产品信息表其包括成品信息零件信息和原材料信息当这张表的记录比较多并且当用户访问这张表时已经出现了严重的I/O沖突的时候则就可以根据行记录来进行分区如在产品信息表中有一个产品类别的字段数据库管理员就可以根据这个字段对标进行分区具体的分区方法也比较简单在建立表的时候在产品类别字段后加上Partion关键字然后指定按字段的内容进行分区这个操作比较简单笔者就不过多展开了笔者这里需要强调的是分区完之后一定要将数据存放在不同的磁盘上即不同的表空间否则的话不能够起到改善磁盘I/O的效果
第二种模式按列来进行分区
在实际工作中还有这么一种情况如现在有一张员工信息表这这张表中除了包含员工的基本信息如身份证号码姓名籍贯等内容还包括员工的身份证复印件或者照片等图片信息大家都知道图片信息的数据流量是很大的有可能一张身份证复印件的数据流量相当于几千条的员工基本信息而且当用户访问员工信息表的时候并不是每个时候都需要查看身份证复印件大部分时候他们可能只是查询员工的联系方式或者住址等等另外一般身份证复印件等照片不会随意更改而员工的联系方式或这住址等等则更改比较频繁此时如果需要更改员工信息却将不需要更改的员工身份证复印件也查询出来显然那这会加重磁盘的I/O沖突
当企业存在这种情况时也可以对这个表进行分区此时分区并不是对行进行分区而是对列进行分区如可以将身份证复印件信息或者照片信息分为一个独立的分区并将其保存在另外一个硬盘上这么做能够带来如下的好处
一是在数据访问时可以指定是否需要查询身份证信息所在的分区在查询员工信息的时候在语句中可以指定从哪个分区中查询信息当用户平时只是查询员工的联系方式或者住址时就不需要访问身份证复印件所在的分区如可以使用如下语句查询
Select * from ad_user partition(uinfo) –假设员工的基本信息存放在分区Unifo中
在查询语句中使用Partition关键字可以指定其查询的是哪个分区如果不特指的话则系统会查询这个表所对应的全部分区而指定的话就只访问某个特定分区的内容上面这条语句就只读取Uinfo分区中的信息而不会读取身份证复印件等相关信息如此的话就可以降低磁盘的I/O沖突减少不必要的数据流量提高查询的性能
二是方便对数据的备份根据使用习惯一般身份证复印件或者员工照片等信息不怎么会更改为此对这些数据的备份频率可以比较低一点况且这些信息的容量往往会很大如果经常对其进行备份显然会增加磁盘的I/O负担而对于员工的联系信息或者住址等等其变化的频率就会高许多对这些信息就需要进行经常性的备份对大表进行分区管理还有一个很大的优势就在于可以对各个分区中的数据采取独立的备份为此就可以对身份证复印件所在的分区进行单独的备份如一个月或者有大的变动时进行备份而对于其他的信息则可以每天进行备份这就可以实现在性能与安全方面的均衡一般来说在访问某个表时如果经常需要访问的信息只是特定的几列而不需要访问的信息容量比较大此时就可以采用按列分区的模式在这种情况下用户就可以在查看某个分区内容的时候避免访问其他分区同时还可以在不妨碍其它分区的情况下对某个分区的数据进行独立的备份
第三种分区模式散列分区
有时候某个表可能没有明显的分区特征即不符合上面提到的这些情况但是表中的记录又非常的多在这种情况下也有必要进行分区不过此时我们可以让系统进行随机的分区这种分区模式就叫做散列分区通常情况下为了提高散列分区的效果即得到一个比较均匀的分布往往可以将的N次方指定为散列分区数一般来说N越大其分布的越均匀
也就是说当数据库管理员不知道该如何对表进行分区时但确实有分区的必要时可以使用散列分区不过笔者需要提醒的是散列分区其有一个重大的限制在使用散列分区的时候仅仅支持本地索引而不支持其他的索引方式这一点需要特别的注意在实际工作中不能够因为采取了散列分区而降低或者取消了索引这往往是得不偿失的不可行
