现在某家企业的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越大 其分布的越均匀
lishixinzhi/Article/program/Oracle/201311/16752