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

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

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

3、的过程，所以在本文中你还会了解到一些相关的PowerDesigner的使用技巧。一个简单的例子某个开发人员着手设计一个订单的系统，这个系统中有两个主要的业务表，分别是订单基本信息表和订单条目表，这两张表具有主从关系的表，其中T_ORDER是订单主表，而T_ORDER_ITEM是订单条目表。数据库设计人员的设计成果如图 1所示：图 1 订单主主从表ORDER_IID是订单号号，为T_OORDER的的主键，通过过名为SEQQ_ORDEER_ID的的序列产生键键值，而ITTEM_IDD是T_ORRDER_IITEM表的的主键，通过过名为SEQQ_ORDEER_ITEEM的序列产产生键值，TT_ORD

4、EER_ITEEM通过ORRDER_IID外键关联联到T_ORRDER表。需求文档指出订单记录将通过以下两种方式来查询数据：CLIENT + ORDER_DATE+IS_SHPPED：根据客户订货日期+是否发货条件查询订单及订单条目。ORDER_DATE+IS_SHIPPED：根据订货日期+是否发货条件查询订单及订单条目。数据库设计人员根据这个要求，在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上建立了一个复合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM为外键 ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。

5、让我们看一下该份设计的最终SQL脚本：/*订单表*/ creaate taable TT_ORDEER (ORRDER_IID NUMMBER(110) noot nulll, ADDDRESSS VARCCHAR2(100), CLIEENT VAARCHARR2(60), ORDDER_DAATE CHHAR(8), IS_SHIPPPED CHHAR(1), connstraiint PKK_T_ORRDER pprimarry keyy (ORDDER_IDD);creaate inndex IIDX_CLLIENT on T_ORDERR (CLLIENT ASC, ORDEER_D

6、ATTE ASCC, ISS_SHIPPPED AASC);/*订单条目子子表*/crreate tablee T_ORRDER_IITEM (ITEMM_ID NNUMBERR(10) not nnull,ORDERR_ID NNUMBERR(10), ITEEM VARRCHAR22(20), COUUNT NUUMBER(10), consstrainnt PK_T_ORDDER_ITTEM prrimaryy key (ITEMM_ID);creaate inndex IIDX_ORRDER_IITEM_OORDER_ID onn T_ORRDER_IITEM (ORDEER_ID A

7、SC);alteer tabble T_ORDERR_ITEMM add consttraintt FK_TT_ORDEER_REEFERENNCE_T_ORDERR foreeign kkey (OORDER_ID) rrefereences T_ORDDER (OORDER_ID);我们承认在ERR关系上，这这份设计并不不存在的缺陷陷，但却存在在以下有待优优化的地方：没有将表表数据和索引引数据存储到到不同的表空空间中，而不不加区别地将将它们存储到到同一表空间间里。这样，不不但会造成II/O竞争，也也为数据库的的维护工作带带来不便。ORACLLE会自动为为表的主键列列创建一个普普通B-Trr

8、ee索引，但但由于这两张张表的主键值值都通过序列列提供，具有有严格的顺序序性（升序或或降序），此此时手工为其其指定一个反反键索引（rreversse keyy indeex）将更加加合理。在在子表T_OORDER_ITEM外外键列ORDDER_IDD上建立的IIDX_ORRDER_IITEM_OORDER_ID的普通通B-Treee索引非常常适合设置为为压缩型索引引，即建立一一个压缩型的的B-Treee索引。因因为一份订单单会对应多个个订单条目，这这就意味着TT_ORDEER_ITEEM表存在许许多同值的 ORDERR_ID列值值，通过将其其索引指定为为压缩型的BB-Treee索引，不但但可以

9、减少IIDX_ORRDER_IITEM_OORDER_ID所需的的存储空间，还还将提高表操操作的性能。企图仅通过建立一个包含3字段IDX_ORDER_COMPOSITE复合索引满足如前所述的两种查询条件方式的索引是有问题的，事实上使用ORDER_DATE+IS_SHIPPED复合条件的查询将利用不到IDX_ORDER_COMPOSITE索引。 优化设计1、将将表数据和索索引数据分开开表空间存储储1.1 表表数据和索引引为何需要使使用独立的表表空间Oraacle强烈烈建立，任何何一个应用程程序的库表至至少需要创建建两个表空间间，其中之一一用于存储表表数据，而另另一个用于存存储表索引数数据。因为如

10、如果将表数据据和索引数据据放在一起，表表数据的I/O操作和索索引的I/OO操作将产生生影响系统性性能的I/OO竞争，降低低系统的响应应效率。将表表数据和索引引数据存放在在不同的表空空间中（如一一个为APPP_DATAA，另一个为为APP_IIDX），并并在物理层面面将这两个表表空间的数据据文件放在不不同的物理磁磁盘上，就可可以避免这种种竞争了。拥拥有独立的表表空间，就意意味着可以独独立地为表数数据和索引数数据提供独立立的物理存储储参数，而不不会发生相互互影响，毕竟竟表数据和索索引数据拥有有不同的特性性，而这些特特性又直接影影响了物理存存储参数的设设定。此外，表表数据和索引引数据独立存存储，还会

11、带带来数据管理理和维护上的的方面。如你你在迁移一个个业务数据库库时，为了降降低数据大小小，可以只迁迁出表数据的的表空间，在在目标数据库库中通过重建建索引的方式式就可以生成成索引数据了了。1.2 表数据和索索引使用不同同表空间的SSQL语法指指定表数据及及索引数据存存储表空间语语句最简单的的形式如下。将表数据存储在APP_DATA表空间里：create table T_ORDER ( ORDER_ID NUMBER(10) not null, )tablespace APP_DATA;将索引数据存储在APP_IDX表空间里：create index IDX_ORDER_ITEM_ORDER_ID

12、 on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;1.3 PowerDesigner中如何操作1) 首先，必须创建两个表空间。通过Model-Tablespace.在List of Tablespaces中创建两个表空间：图 2 创建表表空间2) 为每张表表指定表数据据存储的表空空间。在设计计区中双击表表，打开Taable PProperrties设设计窗口，切切换到opttions 页，按图 3所示指定定表数据的存存储表空间。图 3 指定表表数据的存储储表空间3) 为每个索索引指定索引引数据的存储储表空间。在在Tablee Proppert

13、iees中切换到到Indexxes页，在在这里列出了了表的所有索索引，双击需需设置表空间间的索引，在在弹出的Inndex PProperrties窗窗口中切换到到Optioons页，按按如下方式指指定索引的存存储表空间。图 4 指定索索引数据的存存储表空间将表空间的问题题延展一下：一个应用系系统库表的表表空间可以进进行更精细的的划分。首先先，如果表中中存在LOBB类型的字段段，有为其指指定一个特定定的表空间，因因为LOB类类型的数据在在物理存储结结构的管理上上和一般数据据的策略有很很大的不同，将将其放在一个个独立的表空空间中，就可可方便地设置置其物理存储储参数了。其其次，需要考考虑库表数据据的

14、DML操操作特性：根根据DML（IINSERTT,UPDAATE,DEELETE）操操作频繁程度度，将几乎不不发生任何DDML操作的的数据放在独独立的表空间间中，因为极极少DML操操作的表可设设置符合其特特性的物理参参数：如PCCTFREEE可置为0，其其BUFFEER_POOOL指定为KKEEP，以以便将数据缓缓存在KEEEP数据缓存存区中等等，不不一而足。此此外，还可以以考虑按业务务需要将不同同的业务模块块分开存放，这这主要是考虑虑到备份问题题。假设我们们有一部分业业务数据重要要性很强，而而其他的业务务数据重要性性相对较弱，这这样就可以将将两者分开存存储，以便设设置不同的备备份策略。 当然

15、，无节节制的细化表表空间也将带带来管理上和和部署上的复复杂，根据业业务需求合理理地规划表空空间以达到管管理和性能上上的最佳往往往需要更多的的权衡。2、显式为主主键列建立反反向键索引22.1 反向向键索引的原原理和用途我我们知道Orracle会会自动为表的的主键列建立立索引，这个个默认的索引引是普通的BB-Treee索引。对于于主键值是按按顺序(递增增或递减)加加入的情况，默默认的B- Tree索索引并不理想想。这是因为为如果索引列列的值具有严严格顺序时，随随着数据行的的插入，索引引树的层级增增长很快。搜搜索索引发生生的I/O读读写次数和索索引树的层级级数成正比，也也就是说，一一棵具有5个个层级

16、的B-Tree索索引，在最终终读取到索引引数据时最多多可能发生多多达5次I/O操作。因因而，减少索索引的层级数数是索引性能能调整的一个个重要方法。如果索引列的数据以严格的有序的方式插入，那么B-Tree索引树将变成一棵不对称的歪树，如图 5所示：图 5不对称的的B-Treee索引而如果索引列的的数据以随机机值的方式插插入，我们将将得到一棵趋趋向对称的索索引树，如图图 6所示：图 6对称的BB-Treee索引比较图 5和图图 6，在图图 5中搜索索到A块需要要进行5次II/O操作，而而图 6仅需需要3次I/O操作。既既然索引列数数据从序列中中获取，其有有序性无法规规避，但在建建立索引时，OOra

17、clee允许对索引引列的值进行行反向，即预预先对列值进进行比特位的的反向，如 1000,100011,100111,101111,11100经过反反向后的值将将是00011,10011,11011,00111。显然经过过位反向处理理的有序数据据变得比较随随机了，这样样所得到的索索引树就比较较对称，从而而提高表的查查询性能。但但反向键索引引也有它局限限性：如果在在WHEREE语句中，需需要对索引列列的值进行范范围性的搜索索，如BETTWEEN、等，其其反向键索引引无法使用，此此时，Oraacle将执执行全表扫描描；只有对反反向键索引列列进行 和 = 的比较操作作时，其反向向键索引才会会得到使用。

18、2.2 反向键索引的SQL语句回到我们上面的例子，由于T_ORDER和T_ORDER_ITEM的主键值来源于序列，主键值是有严格顺序的，所以我们应该摒弃默认的Oracle所提供的索引，而采取显式为主键指定一个反向键索引的方式。ORDER_ID为T_ORDER表的主键，主键名为PK_ORDER，我们为ORDER_ID列上建立一个反向键索引IDX_ORDER_ID，并使PK_ORDER_ID使用这个索引，其SQL语句如下：create tablee T_ORRDER ( ORDDER_IDD NUMBBER(100) nott nulll,CLIIENT VVARCHAAR2(600), AADD

19、RESSS VARRCHAR22(100),ORDDER_DAATE CHHAR(8);creeate uuniquee indeex IDXX_ORDEER_ID on T_ORDERR ( ORRDER_IID ASCC) revverse;alterr tablle T_OORDER add cconstrraint PK_ORRDER pprimarry keyy (ORDDER_IDD) usiing inndex IIDX_ORRDER_IID;要保证创建IDDX_ORDDER_IDD的SQL语语句在创建PPK_ORDDER主键的的SQL语句句之前，因为为主键需要引引用到这个反反向

20、键索引。由于主键列的数据是唯一的，所以为IDX_ORDER_ID加上unique限定，使其成为唯一型的索引。2.3 PowerdDesigner如何操作1) 首先，需要为ORDER_ID列建立一个反向键索引。打开T_ORDER的Table Properties的窗口，切换到Indexes页，新建一个名为IDX_ORDER_ID的索引。填写完索引的名称后，双击这个索引，弹出Index Properties窗口，在这个窗口的Columns中选择ORDER_ID列。然后，切换到Options页，按图 7的方式将其设置为反向键索引。图 8 为主键键指定特定的的索引不可否认PowwerDessigner

21、r确实是目前前业界最强大大易用的数据据库设计工具具，但很遗憾憾，当我们为为表主键指定定一个索引时时，其产生的的语句在顺序序上有问题：即创建主键键的语句位于于创建索引语语句之前：create tablee T_ORRDER ();allter ttable T_ORDDER addd connstraiint PKK_T_ORRDER pprimarry keyy (ORDDER_IDD) usiing inndex IIDX_ORRDER_IID;creeate uuniquee indeex IDXX_ORDEER_ID on T_ORDERR ( ORRDER_IID ASCC) revv

22、erse;我们可以通过对对PowerrDesiggner生成成SQL语句句的设置进行行调整，先生生成创建表和和索引的SQQL语句，再再创建为表添添加主键和外外键的SQLL语句来达到到曲线救国的的目的，请看看下一步。33)通过菜单单Databbase-Generrate DDatabaase.调出Dattabasee Conffiguraation窗窗口，切换到到Keys&Indexxes页，按按图 9设置置：图 9 设置生生成键和索引引SQL的选选项这里，我们将PPrimarry Keyys和Forreign keys的的选项都取消消，而将Inndexess勾选，以达达到只生成表表的索引SQQ

23、L语句的目目的。点击确定后，生生成创建数据据库表及其索索引的SQLL语句，运行行该SQL创创建数据库后后，再按图 10设置生生成为表添加加主键和外键键的SQL语语句：图 10 生成成创建表主键键和外键的SSQL语句除此设置外，还还必须切换到到Tablees & VViews页页下，取消所所有选项，避避免重新生成成创建表的语语句。 3、将子表的的外键列的索索引改为压缩缩型3.1 压缩型索引引的原理和用用途在前面的的例子中，由由于一条订单单会对应多条条订单条目，所所以T_ORRDER_IITEM的OORDER_ID字段总总会出现重复复的值，如：ITEM_IDD ORDEER_ID ITEM COU

24、NTT1 1000 101 12 1000 1044 23 1100 2001 34 200 3301 255 200 401 116 2000 205 3在ORDER_ID列上创创建一个普通通未压缩的BB-Treee索引，则索索引数据的物物理上的存储储形式如下：图 11 未进进行压缩的索索引存储ORDER_IID的重复值值在索引块中中重复出现，这这样不但增加加了存储空间间的需求，而而且因为查询询时需要读取取更多的索引引数据块，所所以查询性能能也会降低。让我们来来看一下经过过压缩后索引引数据的存储储方式：图 12 进行行压缩的索引引存储压缩型的索引消消除了重复的的索引值，将将相同索引列列值所关

25、联的的ROWIDD存储在一起起。这样，不不但节省了存存储空间，查查询效率也提提高了，真可可谓两全齐美美了。对象TT_ORDEER和T_OORDER_ITEM这这样的主从表表进行查询时时，一般情况况下，我们都都必须通过外外键查询出子子表所有关联联的记录，所所以在子表的的外键上建立立压缩型的索索引是非常适适合的。3.2 压缩型型索引的SQQL语句创建建压缩型索引引的SQL语语句非常简单单，在T_OORDER_ITEM的的ORDERR_ID上创创建压缩型索索引的SQLL如下所示：create indexx IDX_ORDERR_ITEMM_ORDEER_ID on T_ORDERR_ITEMM (

26、ORRDER_IID ASCC) commpresss;需要在创建索引引的语句后附附上comppress关关键字就可以以了。3.33 PoweerDesiigner如如何创建压缩缩型索引1) 打开T_ORDERR_ITEMM表的Tabble Prropertties的窗窗口，切换到到Indexxes页，为为ORDERR_ID列创创建一个名为为IDX_OORDER_ITEM_ORDERR_ID的索索引。2) 双击IDXX_ORDEER_ITEEM_ORDDER_IDD弹出Inddex Prropertties窗口口，切换到OOptionns页，按图图 13将索索引设置为压压缩型：图 13 将索索

27、引指定为压压缩型4、建立满足需需求的复合键键索引 设计计人员希望通通过T_ORRDER表上上的IDX_ORDERR_COMPPOSITEE复合索引满满足以下两种种组合条件的的查询：CCLIENTT + ORRDER_DDATE + IS_SSHIPPEEDORDDER_DAATE + IS_SHHIPPEDD为方便阐述述，我们特地地将IDX_ORDERR_COMPPOSITEE的创建SQQL语句再次次列出：create indexx IDX_ORDERR_COMPPOSITEE on TT_ORDEER ( CCLIENTT ASC, ORDEER_DATTE ASCC, IS_SHIPPPE

28、D ASSC);事实上，在CLLIENT + ORDDER_DAATE + IS_SHHIPPEDD 三列上所所执行的复合合条件查询会会应用到这个个索引，而在在ORDERR_DATEE + ISS_SHIPPPED列上上所执行的复复合查询不会会使用这个索索引，因而将将导致一个全全表扫描的操操作。可以用用许多工具来来了解查询语语句的执行计计划，通过SSET AUUTOTRAACE ONN来查询以上上两个复合查查询的执行计计划：打开SSQL/Pllus，输入入以下的语句句：SQL seet auttotracce onSSQL sselectt * frrom t_orderr wherre CL

29、IIENT = 1 and OORDER_DATE=1 aand ISS_SHIPPPED=1;分析得到的执行行计划为：SELECT STATEEMENT Optimmizer=CHOOSSETABLLE ACCCESS (BY INNDEX RROWID) OF T_ORDDER IINDEX (RANGGE SCAAN) OFF IDXX_ORDEER_COMMPOSITTE (NNON-UNNIQUE)可见Oraclle先利用IIDX_ORRDER_CCOMPOSSITE得到到满足条件的的记录ROWWID，再通通过ROWIID返回记录录。而下面查查询语句：SQL seelect * fr

30、oom t_oorder wheree ORDEER_DATTE=1 and IS_SHHIPPEDD=1的执行计划则为为：SELECT STATEEMENT Optimmizer=CHOOSSE TABBLE ACCCESS (FULLL) OF T_ORRDER很明显，Oraacle在TT_ORDEER表上执行行了一个全表表扫描的操作作，没有用到到IDX_OORDER_COMPOOSITE索索引。对复合合列索引，我我们得出这个个结论：假设设在COL_1,COLL_2,COL_nn这些列上建建立了一个复复合索引：create indexx IDX _COMPPOSITEE on TTABLE

31、11COL_11,COL_2,COL_n则只有WHERRE语句上包包含COL_1(复合索索引的第一个个字段)的查查询才会使用用这个复合索索引，而未包包含COL_1的查询则则不会使用这这个复合索引引。回到我们们的例子，如如何建立满足足CLIENNT + OORDER_DATE + IS_SHIPPPED和ORRDER_DDATE + IS_SSHIPPEED两种查询询的索引呢？考虑到ISS_SHIPPPED列基基数很小，只只有两个可能能的值：0,1。在这种种情况下，有有两种方案：第一，分别别为CLIEENT + ORDERR_DATEE + ISS_SHIPPPED和OORDER_DATE +

32、IS_SHIPPPED建立一一个复合索引引；第二，分分别在CLIIENT和OORDER_DATE列列上建立一个个索引，而IIS_SHIIPEED列列不建立索引引。第一种方方案的查询效效率最快，但但因为CLIIENT和OORDER_DATE在在索引中会重重复出现两次次，占用较大大的存储空间间。第二种方方案CLIEENT和ORRDER_DDATE不会会在索引存储储出现两次，较较为节省空间间，查询效率率比之于第一一种方案会稍稍低一些，但但影响不大。 我们采用第第二种方案为为CLIENNT和ORDDER_DAATE分别创创建索引IDDX_CLIIENT和IIDX_ORRDER_DDATE，组组合查询条件件为CLIEENT + ORDERR_DATEE + ISS_SHIPPPED时的的执行计划为为：SELECT STATEEMENT Optimmizer=CHOOSSE TABBLE ACCCESS (BY IINDEX ROWIDD) OF T_ORRDER AND-EEQUAL INDEXX (RANNGE SCCAN) OOF IDDX_CLIIENT (NON-UNIQUUE) INNDEX (RANGEE SCANN) OF IDX_ORDERR_DATEE (NOON-UNIIQUE)而组合条件为OORDER_DATE + IS_