优化Oracle库表设计的若干方法.docx

《优化Oracle库表设计的若干方法.docx》由会员分享，可在线阅读，更多相关《优化Oracle库表设计的若干方法.docx（15页珍藏版）》请在第一文库网上搜索。

1、优化OraC1e库表设计的若干方法前言绝大多数的Orac1e数据库性能问题都是由于数据库设计不合理造成的，只有少部分问题根植于DatabaSeBuffer、SharePoo1、Redo1ogBUffCr等内存模块配置不合理，I/O争用，CPU争用等DBA职责范围上。因此除非是面对一个业已完成不可变更的系统，否则我们不应过多地将关注点投向内存、I/O、CPU等性能调整项目上，而应关注数据库表本身的设计是否合理，库表设计的合理性才是程序性能的真正执牛耳者。合理的数据库设计需要考虑下列的方面： 业务数据以何种方式表达。如一个员工有多个Emai1,你能够在EMP1OYEE表中建立多个Emai1字段如e

2、mai1、emai1_2、emai1_3,也能够创建一个TEMA11子表来存储，甚至能够用逗号分隔开多个Emai1地址存放在一个字段中。 数据以何种方式物理存储。如大表的分区，表空间的合理设计等。 如何建立合理的数据表索引。表索引几乎是提高数据表查询性能最有效的方法，Orac1e拥有类型丰富的数据表索引类型，如何取舍选择显得特别重要。本文我们将目光要紧聚焦于数据表的索引上，同时也将提及其他两点的内容。通过对一个简单的库表设计实例的分析引出设计中的不足，并逐一改正。考虑到手工编写库表的SQ1脚本原始且低效，我们将用目前最流行的库表设计工具PowerDesigner10来讲述表设计的过程,因此在本

3、文中你还会熟悉到一些有关的PowerDesigner的使用技巧。一个简单的例子某个开发人员着手设计一个订单的系统，这个系统中有两个要紧的业务表，分别是订单基本信息表与订单条目表，这两张表具有主从关系的表，其中TORDER是订单主表，而TJ)RDERTEM是订单条目表。数据库设计人员的设计成果如图1所示：TqRDERRr-REFERENC!.ORDERJTeMORDRJDNUmBER(IO)ADDRESSVRCHM2(10Q)C1IENTV炽CHM2(60)ORDER_DATECHIS.SHIPPEDCHMa)ITEMJDNMBR(10)pkORDERJDNMR(1O)ITEMVWeHM2(20

4、)COUNTNUMR(10)国IDX.ORDERJTEM-ORDERJDSDX.ORDER.COMPOSITE图1订单主从表ORDER_ID是订单号，为ORDER的主键,通过名为SEQ_ORDER_ID的序列产生键值,而ITEM_ID是TJ)RDERJTEM表的主键，通过名为SEQJ)RDERJTEM的序列产生键值，T_ORDER_ITEM通过ORDER_ID外键关联到TJ)RDER表。需求文档指出订单记录将通过下列两种方式来查询数据：C1IENT+ORDER_DATE+IS_SHPPED：根据客户+订货日期+是否发货条件查询订单及订单条目。ORDER_DATE+IS_SHIPPED：根据订货

5、日期+是否发货条件查询订单及订单条目。数据库设计人员根据这个要求，在TJ)RDER表的C1IENT.0RDER_DATE及IS_SHPPED三字段上建立了一个复合索引IDX_0RDER_C0MP0SITE；在TJ)RDER_ITEM为外键0RDERD建立IDX_0RDER_ITEM_0RDER_ID索引。让我们看一下该份设计的最终SQ1脚本：产订单表*/createtab1eT_ORDER(ORDERJDNUMBER(10)notnu11,ADDRESSVARCHAR2(100),C1IENTVARCHAR2(60),ORDER_DATECHAR(8),IS.SHIPPEDCHAR(I),co

6、nstraintPK_T_ORDERprirnarykey(ORDER-ID)；createindexIDX,C1IENTonT_ORDER(C1IENTASC,ORDER_DATEASC,IS.SHIPPEDASC);产订单条目子表*/createtab1eT_ORDER_ITEM(ITEMJDNUMBER(10)notnu11,ORDERDNUMBER(10),ITEMVARCHAR2(20),COUNTNUMBER(10),constraintPK_T_ORDER_ITEMprimarj,key(ITEM_ID);createindexIDX_ORDER_ITEM_ORDER_IDonT

7、_ORDER_ITEM(ORDERJDASC);a1tertab1eT_ORDER_ITEMaddconstraintFK_T_ORDER_REFERENCE_T_ORDERforeignkey(0RDER-ID)referencesT_ORDER(ORDER_ID);我们承认在ER关系上，这份设计并不存在的缺陷，但却存在下列有待优化的地方：没有将表数据与索引数据存储到不一致的表空间中，而不加区别地将它们存储到同一表空间里.这样，不但会造成I/O竞争，也为数据库的保护工作带来不便。ORAC1E会自动为表的主键列创建一个普通B-Tree索引，但由于这两张表的主键值都通过序列提供，具有严格的顺序性

8、(升序或者降序)，如今手工为其指定一个反键索引(reversekeyindex)将更加合理。在子表Tj)RDERJTEM外键列ORDER_ID上建立的IDX_ORDERTEMJ)RDER_ID的普通B-Tree索引非常适合设置为压缩型索引，即建立一个压缩型的B-Tree索引。由于一份订单会对应多个订单条目，这就意味着ORDERTEM表存在许多同值的ORDER_ID列值，通过将其索引指定为压缩型的B-Tree索引，不但能够减少IDXORDERITEMORDERID所需的存储空间，还将提高表操作的性能。企图仅通过建立一个包含3字段IDX_ORDER_CoMPOSITE复合索引满足如前所述的两种查询

9、条件方式的索引是有问题的，事实上使用ORDERDATE+IS_SHIPPED复合条件的查询将利用不到IDX_ORDER_CoUPOSITE索引。优化设计1、将表数据与索引数据分开表空间存储1.1 表数据与索引为何需要使用独立的表空间OraCIC强烈建立，任何一个应用程序的库表至少需要创建两个表空间，其中之一用于存储表数据，而另一个用于存储表索引数据。由于假如将表数据与索引数据放在一起，表数据的1/0操作与索引的0操作将产生影响系统性能的0竞争，降低系统的响应效率。将表数据与索引数据存放在不一致的表空间中(如一个为ApPDATA,另一个为ApPIDX),并在物理层面将这两个表空间的数据文件放在不

10、一致的物理磁盘上，就能够避免这种竞争了。拥有独立的表空间，就意味着能够独立地为表数据与索引数据提供独立的物理存储参数，而不可能发生相互影响，毕竟表数据与索引数据拥有不一致的特性，而这些特性又直接影响了物理存储参数的设定。此外，表数据与索引数据独立存储，还会带来数据管理与保护上的方面。如你在迁移一个业务数据库时，为了降低数据大小，能够只迁出表数据的表空间，在目标数据库中通过重建索引的方式就能够生成索引数据了。1.2 表数据与索引使用不一致表空间的SQ1语法指定表数据及索引数据存储表空间语句最简单的形式如下。将表数据存储在APP_DATA表空间里：createtab1eT_ORDER(ORDER_

11、1DNUMBER(10)notnu11,)tab1espaceAPP_DATA;将索引数据存储在APP_IDX表空间里：createindexIDXJ)RDERTEMj)RDERDonT_ORDER_ITEM(ORDEREDASOtab1espaceAPP_IDX;1.3PowerDesigner中如何操作1)首先，务必创建两个表空间。通过ModeI-Tab1espace.在1istofTab1espaces中创建两个表空间：图2创建表空间2）为每张表指定表数据存储的表空间。在设计区中双击表，打开Tab1ePr。PertieS设计窗口，切换到OPtiOnS页，按图3所示指定表数据的存储表空间。

12、回PreviewJoinIndexExtendtdAttributesDePede孰CiNppnIPernissionsNotesGtn1CG1BnSIndexesKtysesIEXSnddDependenciesTnccersProceduresChckRu1esVersionInfoScriptOpHonsonCOftftit(preserverows)-pctfr(10)ctuzd(40)initrn(1)mxtrans(M)storc*(stor0)tb1espee()OnCd”Q0M)or(mxton4.c1str(加)-=H”I1Syntax/APrtMoI-c1proptrt-s

13、0hett.ttributesaavc1tb1xpc(?)tatspcesa1IK1tenisXSQ1A?PmrA1A,-Tab1eProperties-JORDER(T_ORDER)图3指定表数据的存储表空间3)为每个索引指定索引数据的存储表空间。在Tab1ePrOPertieS中切换到IndeXeS页,在这里列出了表的所有索引，双击需设置表空间的索引，在弹出的IndeXProPertieS窗口中切换到OPtionS页,按如下方式指定索引的存储表空间。图4指定索引数据的存储表空间将表空间的问题延展一下：一个应用系统库表的表空间能够进行更精细的划分。首先，假如表中存在1oB类型的字段，有为其指

14、定一个特定的表空间，由于1oB类型的数据在物理存储结构的管理上与通常数据的策略有很大的不一致，将其放在一个独立的表空间中，就可方便地设置其物理存储参数了。其次，需要考虑库表数据的DM1操作特性：根据DM1(INSERT,UPDATE,DE1ETE)操作频繁程度，将几乎不发生任何DM1操作的数据放在独立的表空间中，由于极少DM1操作的表可设置符合其特性的物理参数：如PCTFREE可置为0,其BUFFERPOO1指定为KEEP,以便将数据缓存在KEEP数据缓存区中等等，不一而足。此外，还能够考虑按业务需要将不一致的业务模块分开存放，这要紧是考虑到备份问题。假设我们有一部分业务数据重要性很强，而其他

15、的业务数据重要性相对较弱，这样就能够将两者分开存储，以便设置不一致的备份策略。当然，无克制的细化表空间也将带来管理上与部署上的复杂，根据业务需求合理地规划表空间以达到管理与性能上的最佳往往需要更多的权衡。2、显式为主键列建立反向键索引2.1 反向键索引的原理与用途我们明白OraC1e会自动为表的主键列建立索引，这个默认的索引是普通的BTree索引。关于主键值是按顺序（递增或者递减）加入的情况，默认的B-Tree索引并不理想。这是由于假如索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快。搜索索引发生的I/O读写次数与索引树的层级数成正比，也就是说，一棵具有5个层级的B-Tree索