关于Oracle数据库设计、开发、应用的探讨

2014-02-10徐卓

铁路计算机应用 2014年9期

关键词：历史数据数据表分区

徐卓

（哈尔滨铁路局信息技术所，哈尔滨 150001）

关于Oracle数据库设计、开发、应用的探讨

徐卓

（哈尔滨铁路局信息技术所，哈尔滨 150001）

Oracle数据库具有良好、稳定的大数据处理能力，安全、高可用的并发数据访问功能，被铁路运输等大型国有企业广泛应用。随着Oracle数据库的不断升级，不少企业在应用过程中，设计和开发方面存在一些“误区”，不同程度地导致企业在Oracle数据库的应用上效率不高，反映迟缓等现象。因此，本文结合作者多年开发和维护Oracle数据库的经验、体会，总结了Oracle数据库在设计、开发、应用方面容易出现的“误区”，阐明了需要重点注意的若干问题。

I/O；索引；多对多关系

数据库设计的质量好坏直接关系到开发周期和系统性能，其开发水平则直接影响到代码的可读性和可维护性。本文结合笔者多年的工作实践，主要论述在Oracle数据库的设计和开发方面的一些切身感悟。

1 数据库设计

数据库设计简单的说就是库、表的设计，即数据库物理存储结构（表空间）和数据表结构的设计。数据库物理存储结构涉及诸多操作系统和DBA建库方面的理论和概念，在此不过多论述。本文重点探讨数据表结构设计方面的一些问题。

1.1 多对多关系的表结构设计

Oracle数据库的表都是一张二维表，关于单独一张表的结构设计，只需要结合具体实体属性，适当的符合数据库第三范式3NF即可。而对于实体之间抽象出的一对一，多对一（一对多）和多对多3种类型的关系，通过Oracle数据库主、外键的合理设置也能够通过主、从表的方式实现其中的一对一，多对一（一对多）两种关系。多对多关系的实现，是通过设立中间表来实现的。

例如：表A（项目表）（id,名称，功能，开发时间，...）；

数据：（1，确报系统，收发确报，2006-08，...）

（2，货票系统，生成电子货票，2013-12，...)表B（设备表）（id，名称，序列号，型号，...）。数据：(1，IBM服务器，99BHBH3，X3650M3，…)（2，HP服务器，G215LJC13G，DL380，…)（3，IBM小机，10C2F8C，P570，…) )

表A中的一个项目可对应多个设备；表B中的一个设备可被多个项目使用，表A和表B是标准的多对多关系。要实现A、B表的多对多关系，通常的方法是引入一个中间表C（项目ID，设备ID，备注）。

表C数据：（1，1，）

（1，3，）

（2，2，）

（2，3，）

通过设置表A的ID和表C的项目ID；表B的ID和表C的设备ID为主外键关系，来实现表A、C和表B、C的同步更新，通过表A、B、C的关联查询，就可以实现项目表和设备表相关联数据的多对多查询和展示。

1.2 索引

通过Oracle数据库的索引，可以迅速定位记录的位置，而不必去定位整个表，这样极大地提高了数据库的查询速度。但是，表中的索引越多，维护索引所需要的成本也就越大，每当数据表中记录有增加、删除、更新变化时，数据库系统都需要对所有索引进行更新。所以，数据库表中的索引绝对不是多多益善。Oracle数据库创建索引通常应遵循如下原则：

（1）在基数小的字段上要善于使用位图索引。基数是位图索引中的一个基本定义，它是指Oracle数据库表中某个字段内容中不重复的数值。如在员工信息表中的性别字段，一般就只有男跟女两个值，所以，其基数为2。再如婚姻状况、民族等字段都适用位图索引。除了在数据表某列基数比较小的情况下，采用位图索引外，在Where限制条件中，若多次采用AND或者OR条件时，也建议采用位图索引。因为当一个查询，引用了一些部署了位图索引的列时，这些位图可以很方便的与AND或者OR 运算符操作结合以快速的找出用户所需要的记录。

（2）对于满足查询条件的数据不超过10%的查询列和用于集函数、连接、group by和order by的列，应该建索引。这样可以利用索引顺序的特点加快排序速度。连接中经常使用的列或表中有外键约束的列应该建立索引，如果列处在索引顺序中则系统可更快执行连接。

（3）对于顺序增长的列索引，以及具有如“餐厅甲”，“餐厅乙”等相似但不重复的列值，使用反向键索引。

（4）索引与数据表应分别建在不同的表空间上。

（5）表的主键列和唯一性约束列自动建立唯一性索引，不需要单独指定索引。

1.3 分区表

表结构设计一定要考虑应用数据长期积累或暴发式增长对系统性能和数据维护带来的影响。否则，应用系统在积累了大量数据时再去修改表结构，由此带来的维护工作量和异常，对开发人员和用户来说都将是灾难性的。Oracle的分区表，就是解决包含大量历史数据的大数据表的性能和维护瓶颈的一个非常有效的表结构设计方式。

Oracle的分区表具有以下优点：（1）增强可用性：如果表的某个分区出现故障，表在其它分区的数据仍然可用。（2）维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。（3）均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。（4）改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

Oracle的分区表可分为：范围分区，哈希分区，复合分区，列表分区，混合分区，间隔分区等。对这些分区的具体应用，由于篇幅所限, 在此不再赘述，本文仅对建立数据库分区表应遵循的原则，作以下简要说明：

（1）表的大小：当表的大小超过1.5 GB～ 2 GB，或对于OLTP系统，表的记录超过1 000万，都应考虑对表进行分区。

（2）数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。

（3）数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。

（4）数据备份和恢复：按时间周期进行表空间的备份时，将分区与表空间建立对应关系。

（5）只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份有益。

（6）并行数据操作：对于经常执行并行操作（如Parallel Insert，Parallel Update等）的表应考虑进行分区。

（7）表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。

2 数据库开发和应用

本文只选取SQL共享，数据库客户端连接，历史数据处理等在企业日常的数据库维护工作中，具有代表性并且容易出错的3个方面进行分析和探讨。

2.1 SQL共享

Oracle将执行过的SQL语句存放在内存的共享池（sharedbuffer pool）中，可以被所有的数据库用户共享。当执行一个SQL语句时,如果它和之前执行过的语句完全相同, Oracle就能很快获得已经被解析的语句以及最好的执行路径。这就是SQL共享，这个功能大大地提高了SQL的执行性能并节省了内存空间。

SQL共享有3个条件：（1）当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）。（2）两个语句所指的对象必须完全相同（同义词与表是不同的对象）。（3）两个SQL语句中必须使用相同名字的绑定变量（bindvariables）。

其中，绑定变量是许多数据库开发人员在编程时容易忽视之处。如：select * from 表B where序列号=‘99BHBH3’；上面这个语句，每执行一次就需要在SHARE POOL 硬解析一次，100万用户就是100万次，消耗CPU和内存，如果业务量大，很可能导致宕库。如果绑定变量，则只需要硬解析一次，重复调用即可。

以下是绑定变量与不绑定变量在写法上的异同，在数据库编程时尽可能的应用绑定变量的方式。

未使用绑定变量的语句：

sprintf(sqlstr, "insert into scott.test1 (num1, num2) values (%d,%d)",n_var1, n_var2);

EXEC SQL EXECUTE IMMEDIATE :sqlstr ;

EXEC SQL COMMIT;

使用绑定变量的语句：

strcpy(sqlstr, "insert into test (num1, num2) values (:v1, :v2)");

EXEC SQL PREPARE sql_stmt FROM :sqlstr;

EXEC SQL EXECUTE sql_stmt USING : n_var1, :n_var2;

EXEC SQL COMMIT;

2.2 数据库连接

通常情况下，应用程序与后台的Oracle数据库进行数据交换是通过Oralcle的客户端软件,配置TNASNAMES.ORA文件中的“连接串”来实现的。目前,很多企业的后台Oracle数据库都建成了兼顾安全和效率的RAC方式。但是客户端的数据库连接配置，还停留在以往单机数据库的配置方式。并没有真正发挥RAC数据库对客户端的负载均衡和故障切换功能。以下是两个客户端的TNSNAMES.ORA 文件的配置：

（1）客户端A：

SMIS.MZL=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=10.25.3.25)

(PORT=1521)

)

(CONNECT_DATA=

(SERVER=dedicated)

(SERVICE_NAME=mzl)

)

) )

（2）客户端B:

SMIS.MZL=

(DESCRIPTION=

(ADDRESS_LIST=

(FAILOVER=on)

(LOAD_BALANCE=off)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=10.27.3.25)

(PORT=1521)

)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=10.16.3.27)

(PORT=1521)

)

(CONNECT_DATA=

(FAILOVER_MODE=

(TYPE=select)

(METHOD=basic)

(RETRIES=50)

(DELAY=5)

)

(SERVER=dedicated)

(SERVICE_NAME=mzl)

)

客户端A只连接了10.27.3.25上的单个数据库实例，当10.27.3.25这个RAC节点因故宕机时，客户端连接不能实现自动切换到另外的数据库节点。客户端B的配置实现了对RAC数据库10.25.3.25和10.25.3.27两个节点的故障切换功能。

客户端B关闭了对两个数据库节点的负载均衡功能(LOAD_BALANCE=off)。这是因为，有时为了避免节点争用，要进行人为的用户分区。即把执行相同业务功能的用户，固定连接到同一个数据库节点上，避免不同的节点，访问同一个数据块，形成节点争用，影响性能。客户端B把10.27.3.25配置在前面，则每次都连接10.27.3.25节点，只有10.27.3.25节点因故无法启动时，才连接10.27.3.27节点。上述客户端配置LOAD_BALANCE=off，有时需要将后台数据库的remote_listener参数置空才能生效。

2.3 历史数据处理

建议采取如下方式处理应用项目的历史数据：（1）建立单独表空间，用户来管理历史数据。（2）在单独的表空间中建立与产生历史数据的表同结构的数据表来存放历史数据。

（3）建立通用的管理表，记录哪些生产表及对应子表需要历史；这些表的关联字段、时间字段、历史周期、删除历史数据周期以及相应的where条件等信息。

（4）编写通用的存储过程，根据（3）中管理表的记录，负责按相应的where条件，定期将记录的生产表及子表中的数据转储到历史表中，并按记录的周期删除历史表中的过期数据。

（5）调试应用程序，使其具备专门的查询生产数据和历史数据的功能。