优化Oracle库表设计的若干方法 电脑资料

优化Oracle库表设计的若干方法 电脑资料 前言 绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的，只有少部分问题根植于Database Buffer、Share Pool、Redo Log Buffer等内存模块配置不合理，I/O争用，CPU争用等DBA职责范围上， 合理的数据库设计需要考虑以下的方面： 业务数据以何种方式表达。如一个员工有多个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_POSITE；在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_POSITE复合索引满足如前所述的两种查询条件方式的索引是有问题的，事实上使用ORDER_DATE+IS_SHIPPED复合条件的查询将利用不到IDX_ORDER_POSITE索引。 优化设计 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将执行全表扫描；只有对反向键索引列进行 和 = 的比较操作时，其反向键索引才会得到使用。 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 table T_ORDER ( ORDER_ID NUMBER(10) not null, CLIENT VARCHAR2(60), ADDRESS VARCHAR2(100), ORDER_DATE CHAR(8); create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;alter table T_ORDER add constraint PK_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID; 要保证创建IDX_ORDER_ID的SQL语句在创建PK_ORDER主键的SQL语句之前，因为主键需要引用到这个反向键索引， 由于主键列的数据是唯一的，所以为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的方式将其设置为反向键索引。 图 7 设置反向键索引 2) 显式指定主键PK_ORDER使用这个索引。在Table Properties窗口中切换到Keys页，默认情况下，PowerDesigner为T_ORDER所指定的主键名为Key1，我们将其更名为PK_ORDER，双击这个主键，弹出Key Properties窗口，切换到Options页，按图 8的方式为PK_ORDER指定IDX_ORDER_ID。 图 8 为主键指定特定的索引 不可否认PowerDesigner确实是目前业界最强大易用的数据库设计工具，但很遗憾，当我们为表主键指定一个索引时，其产生的语句在顺序上有问题：即创建主键的语句位于创建索引语句之前： create table T_ORDER ();alter table T_ORDER add constraint PK_T_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse; 我们可以通过对PowerDesigner生成SQL语句的设置进行调整，先生成创建表和索引的SQL语句，再创建为表添加主键和外键的SQL语句来达到曲线救国的目的，请看下一步。 3)通过菜单Database-Generate Database.调出Database Configuration窗口，切换到Keys&Indexes页，按图 9设置： 图 9 设置生成键和索引SQL的选项 这里，我们将Primary Keys和Foreign keys的选项都取消，而将Indexes勾选，以达到只生成表的索引SQL语句的目的。 点击确定后，生成创建数据库表及其索引的SQL语句，运行该SQL创建数据库后，再按图 10设置生成为表添加主键和外键的SQL语句： 图 10 生成创建表主键和外键的SQL语句 除此设置外，还必须切换到Tables & Views页下，取消所有选项，避免重新生成创建表的语句。 3、将子表的外键列的索引改为压缩型 3.1 压缩型索引的原理和用途 在前面的例子中，由于一条订单会对应多条订单条目，所以T_ORDER_ITEM的ORDER_ID字段总会出现重复的值，如： ITEM_ID ORDER_ID ITEM COUNT 1 100 101 1 2 100 104 2 3 100 201 3 4 200 301 2 5 200 401 1 6 200 205 3 在ORDER_ID列上创建一个普通未压缩的B-Tree索引，则索引数据的物理上的存储形式如下： 图 11 未进行压缩的索引存储 ORDER_ID的重复值在索引块中重复出现，这样不但增加了存储空间的需求，而且因为查询时需要读取更多的索引数据块，所以查询性能也会降低。让我们来看一下经过压缩后索引数据的存储方式： 图 12 进行压缩的索引存储 压缩型的索引消除了重复的索引值，将相同索引列值所关联的ROWID存储在一起。这样，不但节省了存储空间，查询效率也提高了，真可谓两全齐美了。 对象T_ORDER和T_ORDER_ITEM这样的主从表进行查询时，一般情况下，我们都必须通过外键查询出子表所有关联的记录，所以在子表的外键上建立压缩型的索引是非常适合的。 3.2 压缩型索引的SQL语句 创建压缩型索引的SQL语句非常简单，在T_ORDER_ITEM的ORDER_ID上创建压缩型索引的SQL如下所示： create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC) press; 需要在创建索引的语句后附上press关键字就可以了。 3.3 PowerDesigner如何创建压缩型索引 1) 打开T_ORDER_ITEM表的Table Properties的窗口，切换到Indexes页，为ORDER_ID列创建一个名为IDX_ORDER_ITEM_ORDER_ID的索引。 2) 双击IDX_ORDER_ITEM_ORDER_ID弹出Index Properties窗口，切换到Options页，按图 13将索引设置为压缩型： 图 13 将索引指定为压缩型 4、建立满足需求的复合键索引 设计人员希望通过T_ORDER表上的IDX_ORDER_POSITE复合索引满足以下两种组合条件的查询： CLIENT + ORDER_DATE + IS_SHIPPED ORDER_DATE + IS_SHIPPED 为方便阐述，我们特地将IDX_ORDER_POSITE的创建SQL语句再次列出： create index IDX_ORDER_POSITE on T_ORDER ( CLIENT ASC, ORDER_DATE ASC, IS_SHIPPED ASC); 事实上，在CLIENT + ORDER_DATE + IS_SHIPPED 三列上所执行的复合条件查询会应用到这个索引，而在ORDER_DATE + IS_SHIPPED列上所执行的复合查询不会使用这个索引，因而将导致一个全表扫描的操作。 可以用许多工具来了解查询语句的执行计划，通过SET AUTOTRACE ON来查询以上两个复合查询的执行计划： 打开SQL/Plus，输入以下的语句： SQL set autotrace on SQL select * from t_order where CLIENT = 1 and ORDER_DATE=1 and IS_SHIPPED=1; 分析得到的执行计划为： SELECT STATEMENT ptimizer=CHOOSETABLE ACCESS (BY INDEX ROWID) OF T_ORDER INDEX (RANGE SCAN) OF IDX_ORDER_POSITE (NON-UNIQUE) 可见Oracle先利用IDX_ORDER_POSITE得到满足条件的记录ROWID，再通过ROWID返回记录。 而下面查询语句： SQL select * from t_order where ORDER_DATE=1 and IS_SHIPPED=1 的执行计划则为： SELECT STATEMENT ptimizer=CHOOSE TABLE ACCESS (FULL) OF T_ORDER 很明显，Oracle在T_ORDER表上执行了一个全表扫描的操作，没有用到IDX_ORDER_POSITE索引。 对复合列索引，我们得出这个结论： 假设在COL_1,COL_2,COL_n这些列上建立了一个复合索引： create index IDX _POSITE on TABLE1 COL_1, COL_2, , COL_n 则只有WHERE语句上包含COL_1(复合索引的第一个字段)的查询才会使用这个复合索引，而未包含COL_1的查询则不会使用这个复合索引。 回到我们的例子，如何建立满足CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED两种查询的索引呢？ 考虑到IS_SHIPPED列基数很小，只有两个可能的值：0,1。在这种情况下，有两种方案：第一，分别为CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED建立一个复合索引；第二，分别在CLIENT和ORDER_DATE列上建立一个索引，而IS_SHIPEED列不建立索引。 第一种方案的查询效率最快，但因为CLIENT和ORDER_DATE在索引中会重复出现两次，占用较大的存储空间。第二种方案CLIENT和ORDER_DATE不会在索引存储出现两次，较为节省空间，查询效率比之于第一种方案会稍低一些，但影响不大。 我们采用第二种方案为CLIENT和ORDER_DATE分别创建索引IDX_CLIENT和IDX_ORDER_DATE，组合查询条件为CLIENT + ORDER_DATE + IS_SHIPPED时的执行计划为： SELECT STATEMENT ptimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF T_ORDER AND-EQUAL INDEX (RANGE SCAN) OF IDX_CLIENT (NON-UNIQUE) INDEX (RANGE SCAN) OF IDX_O