分享
 
 
 

优化Oracle库表设计的若干方法(组图)

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

前言

绝大多数的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所示:

border="0" alt="优化Oracle库表设计的若干方法(组图)(图五)" /

图 5不对称的B-Tree索引

而假如

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有