科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道优化Oracle库表设计的若干方法

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

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

  前言   绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的,只有少部分问题根植于Database Buffer、Share Pool、Redo Log Buffer等内存模块配置不合理,I/O争用,CPU争用等DBA职责范围上。

作者:中国IT实验室 来源:中国IT实验室 2007年10月7日

关键字: 优化 数据库 ORACLE

  • 评论
  • 分享微博
  • 分享邮件

在本页阅读全文(共3页)

  前言

  绝大多数的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索引。

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章