优化oracle库表设计的若干方法.doc

优化优化 OracleOracle 库表设计的若干方法库表设计的若干方法优化 Oracle 库表设计的若干方法个人分类：技术文章 前言绝大多数的 Oracle 数据库性能问题都是由于数据库设计不合理造成的，只有少部分问题根植于 Database Buffer、Share Pool、Redo Log Buffer 等内存模块配置不合理，I/O 争用，CPU 争用等 DBA 职责范围上所以除非是面对一个业已完成不可变更的系统，否则我们不应过多地将关注点投向内存、I/O、CPU 等性能调整项目上，而应关注数据库表本身的设计是否合理，库表设计的合理性才是程序性能的真正执牛耳者合理的数据库设计需要考虑以下的方面：·业务数据以何种方式表达如一个员工有多个 Email，你可以在 T_EMPLOYEE 表中建立多个 Email 字段如 email_1、 email_2、email_3，也可以创建一个 T_EMAIL 子表来存储，甚至可以用逗号分隔开多个 Email 地址存放在一个字段中·数据以何种方式物理存储如大表的分区，表空间的合理设计等·如何建立合理的数据表索引表索引几乎是提高数据表查询性能最有效的方法，Oracle 拥有类型丰富的数据表索引类型，如何取舍选择显得特别重要。

本文我们将目光主要聚焦于数据表的索引上，同时也将提及其他两点的内容通过对一个简单的库表设计实例的分析引出设计中的不足，并逐一改正考虑到手工编写库表的 SQL 脚本原始且低效，我们将用目前最流行的库表设计工具 PowerDesigner 10 来讲述表设计的过程，所以在本文中你还会了解到一些相关的 PowerDesigner的使用技巧一个简单的例子某个开发人员着手设计一个订单的系统，这个系统中有两个主要的业务表，分别是订单基本信息表和订单条目表，这两张表具有主从关系的表，其中 T_ORDER 是订单主表，而 T_ORDER_ITEM 是订单条目表数据库设计人员的设计成果如图 1 所示：图 1 订单主从表 ORDER_ID 是订单号，为 T_ORDER 的主键，通过名为SEQ_ORDER_ID 的序列产生键值，而 ITEM_ID 是 T_ORDER_ITEM 表的主键，通过名为 SEQ_ORDER_ITEM 的序列产生键值，T_ORDER_ITEM通过 ORDER_ID 外键关联到 T_ORDER 表需求文档指出订单记录将通过以下两种方式来查询数据：·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 索引让我们看一下该份设计的最终 SQL 脚本：/*订单表*/create table T_ORDER (ORDER_ID NUMBER(10) not null,ADDRESS VARCHAR2(100),CLIENT VARCHAR2(60),ORDER_DATE CHAR(8),IS_SHIPPED CHAR(1),constraint PK_T_ORDER primary key (ORDER_ID));create index IDX_CLIENT on T_ORDER (CLIENT ASC,ORDER_DATE ASC,IS_SHIPPED ASC);/*订单条目子表*/create table T_ORDER_ITEM (ITEM_ID NUMBER(10) not null,ORDER_ID NUMBER(10),ITEM VARCHAR2(20),COUNT NUMBER(10),constraint PK_T_ORDER_ITEM primary key (ITEM_ID));create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (ORDER_ID ASC);alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID); 我们承认在 ER 关系上，这份设计并不存在的缺陷，但却存在以下有待优化的地方：·没有将表数据和索引数据存储到不同的表空间中，而不加区别地将它们存储到同一表空间里。

这样，不但会造成 I/O 竞争，也为数据库的维护工作带来不便·ORACLE 会自动为表的主键列创建一个普通 B-Tree 索引，但由于这两张表的主键值都通过序列提供，具有严格的顺序性（升序或降序） ，此时手工为其指定一个反键索引（reverse key index）将更加合理·在子表 T_ORDER_ITEM 外键列 ORDER_ID 上建立的IDX_ORDER_ITEM_ORDER_ID 的普通 B-Tree 索引非常适合设置为压缩型索引，即建立一个压缩型的 B-Tree 索引因为一份订单会对应多个订单条目，这就意味着 T_ORDER_ITEM 表存在许多同值的 ORDER_ID 列值，通过将其索引指定为压缩型的 B-Tree 索引，不但可以减少 IDX_ORDER_ITEM_ORDER_ID 所需的存储空间，还将提高表操作的性能·企图仅通过建立一个包含 3 字段 IDX_ORDER_COMPOSITE 复合索引满足如前所述的两种查询条件方式的索引是有问题的，事实上使用 ORDER_DATE+IS_SHIPPED 复合条件的查询将利用不到IDX_ORDER_COMPOSITE 索引优化设计1、将表数据和索引数据分开表空间存储1.1 表数据和索引为何需要使用独立的表空间Oracle 强烈建立，任何一个应用程序的库表至少需要创建两个表空间，其中之一用于存储表数据，而另一个用于存储表索引数据。

因为如果将表数据和索引数据放在一起，表数据的 I/O 操作和索引的 I/O 操作将产生影响系统性能的 I/O 竞争，降低系统的响应效率将表数据和索引数据存放在不同的表空间中（如一个为 APP_DATA，另一个为 APP_IDX） ，并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上，就可以避免这种竞争了拥有独立的表空间，就意味着可以独立地为表数据和索引数据提供独立的物理存储参数，而不会发生相互影响，毕竟表数据和索引数据拥有不同的特性，而这些特性又直接影响了物理存储参数的设定此外，表数据和索引数据独立存储，还会带来数据管理和维护上的方面如你在迁移一个业务数据库时，为了降低数据大小，可以只迁出表数据的表空间，在目标数据库中通过重建索引的方式就可以生成索引数据了1.2 表数据和索引使用不同表空间的 SQL 语法指定表数据及索引数据存储表空间语句最简单的形式如下将表数据存储在 APP_DATA 表空间里：create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;将索引数据存储在 APP_IDX 表空间里：create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;1.3 PowerDesigner 中如何操作1) 首先，必须创建两个表空间。

通过 Model->Tablespace...在 List of Tablespaces 中创建两个表空间：图 2 创建表空间 2) 为每张表指定表数据存储的表空间在设计区中双击表，打开 Table Properties 设计窗口，切换到 options 页，按图 3 所示指定表数据的存储表空间图 3 指定表数据的存储表空间 3) 为每个索引指定索引数据的存储表空间在 Table Properties 中切换到 Indexes 页，在这里列出了表的所有索引，双击需设置表空间的索引，在弹出的 Index Properties 窗口中切换到Options 页，按如下方式指定索引的存储表空间图 4 指定索引数据的存储表空间 将表空间的问题延展一下：一个应用系统库表的表空间可以进行更精细的划分首先，如果表中存在 LOB 类型的字段，有为其指定一个特定的表空间，因为 LOB 类型的数据在物理存储结构的管理上和一般数据的策略有很大的不同，将其放在一个独立的表空间中，就可方便地设置其物理存储参数了其次，需要考虑库表数据的 DML 操作特性：根据DML（INSERT,UPDATE,DELETE）操作频繁程度，将几乎不发生任何DML 操作的数据放在独立的表空间中，因为极少 DML 操作的表可设置符合其特性的物理参数：如 PCTFREE 可置为 0，其 BUFFER_POOL指定为 KEEP，以便将数据缓存在 KEEP 数据缓存区中等等，不一而足。

此外，还可以考虑按业务需要将不同的业务模块分开存放，这主要是考虑到备份问题假设我们有一部分业务数据重要性很强，而其他的业务数据重要性相对较弱，这样就可以将两者分开存储，以便设置不同的备份策略当然，无节制的细化表空间也将带来管理上和部署上的复杂，根据业务需求合理地规划表空间以达到管理和性能上的最佳往往需要更多的权衡2、显式为主键列建立反向键索引2.1 反向键索引的原理和用途我们知道 Oracle 会自动为表的主键列建立索引，这个默认的索引是普通的 B-Tree 索引对于主键值是按顺序(递增或递减)加入的情况，默认的 B -Tree 索引并不理想这是因为如果索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快搜索索引发生的 I/O 读写次数和索引树的层级数成正比，也就是说，一棵具有 5 个层级的 B-Tree 索引，在最终读取到索引数据时最多可能发生多达 5 次 I/O 操作因而，减少索引的层级数是索引性能调整的一个重要方法如果索引列的数据以严格的有序的方式插入，那么 B-Tree 索引树将变成一棵不对称的“歪树“，如图 5 所示：图 5 不对称的 B-Tree 索引 而如果索引列的数据以随机值的方式插入，我们将得到一棵趋向对称的索引树，如图 6 所示：图 6 对称的 B-Tree 索引 比较图 5 和图 6，在图 5 中搜索到 A 块需要进行 5 次 I/O 操作，而图 6 仅需要 3 次 I/O 操作。

既然索引列数据从序列中获取，其有序性无法规避，但在建立索引时，Oracle 允许对索引列的值进行反向，即预先对列值进行比特位的反向，如 1000,10001,10011,10111,1100 经过反向后的值将是 0001,1001,1101,0011显然经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能但反向键索引也有它局限性：如果在 WHERE 语句中，需要对索引列的值进行范围性的搜索，如 BETWEEN、等，其反向键索引无法使用，此时，Oracle 将执行全表扫描；只有对反向键索引列进行 Generate Database...调出 Database Configuration 窗口，切换到 Keys 需要在创建索引的语句后附上 compress 关键字就可以了3.3 PowerDesigner 如何创建压缩型索引1) 打开 T_ORDER_ITEM 表的 Table Properties 的窗口，切换到Indexes 页，为 ORDER_ID 列创建一个名为IDX_ORDER_ITEM_ORDER_ID 的索引。