科研设计管理信息系统数据库性能优化研究

管伟元，金海丰

（1. 708研究所，上海 200011；2. 天津大学建筑工程学院，天津 100072）

0 引 言

船舶及海洋工程科研设计管理信息系统简称RDM。系统采用信息化手段实现对各类项目活动的管理，具有机构及人员管理、用户管理、专业管理、项目模版管理、项目进度管理、实动工时管理、报表管理和系统管理等功能[1,2]。

系统选用甲骨文公司的Oracle10g企业版作为数据库。随着系统使用时间的延续以及用户的增多，数据库中的数据量越来越庞大，同时对系统响应时间的要求也越来越高，这就迫切需要对数据库性能进行优化。数据库优化涉及的内容很多，最主要的包括三个方面，即单表检索优化、多表联合检索优化和数据库配置优化[3]，本文围绕前两个方面展开研究。

1 单表检索优化

数据库引擎访问数据，有两种方法定位数据表中数据。第一种为全表扫描；第二种是基于索引等冗余的访问结构进行检索[4]。对于不同情况，两种访问方法各有优劣。识别低效的访问路径，引导数据库引擎采用高效的访问路径，是提高单表检索效率的根本方法。要想提高单表检索效率，首先应考虑添加新的访问结构，如在经常充当检索条件的列上建立适当类型的索引。同时，优化SQL语句，改变数据表结构，改变检索条件等也能对提高检索效率起到一定作用。

在系统中，常用的一项业务之一就是员工日常工时登记，这是一项涉及范围特别广，使用频率特别高的操作，需要重点优化，本文以此为例对单表检索优化的技术途径进行剖析。

日常工时记录储存在数据库scott方案下的dworkhour表中，共包括14个字段，其中dworkhourid字段为表的主键。通过count(*)函数统计得出此表一共有1091239行数据，数据量较为庞大。

某员工获得日常工时记录的SQL查询语句为（记为查询语句A）：

select * from dworkhour where staffid = '2441' and to_char(ddate, 'yyyy-mm-dd') > '2012-03-01' and affirm =0;

收集并获取此语句的执行计划：

explain plan for select * from dworkhour where staffid = '2441' and to_char (ddate, 'yyyy-mm-dd')>'2012-03-01' and affirm =0;

select * from table(dbms_xplan.display);

得到部分结果如表1所示。

表1 原始执行计划

从表中数据可以发现，未进行优化时，系统评估此操作的开销为2406。

首先考虑对查询条件中3个字段分别建立合适的索引。staffid字段与ddate字段重复值较少，适合建立B-树索引，而affirm字段只有0和1两个值，建立位图索引更加妥当。完成索引的建立后重新收集表的统计信息，然后运行查询语句A并查看执行计划，发现系统开销下降到1958，效果不明显。

研究查询语句A，查询条件中对ddate字段使用了to_char()函数，这将导致建立在ddate字段上的索引不可用。解决这一问题，可以对查询语句A进行适当修改，得到如下语句（记为查询语句B）：

select * from dworkhour where staffid = '2441' and ddate > to_date('2012-03-01', ‘yyyy-mm-dd’) and affirm=0;

通过修改 SQL语言来激活建立在 ddate字段上的索引后，重新查看执行计划中的系统开销，结果为1460，较之前又有所降低。

由于以上方法优化效果并不明显，故考虑对查询条件中的3个字段建立组合索引。组合索引是一种特殊的索引，又可称为复合索引。组合索引包含有多个列，比单个字段建立的索引更具有选择性。以 ddate字段为先导列建立组合索引：

create index idx_dw_a on dworkhour (ddate, staffid, affirm);

运行查询语句B并查看执行计划，得到系统开销为1028。再分别以staffid字段、affirm字段为先导列各自建立组合索引，查看它们的系统开销，结果分别为51和458（见表2）。因此得到最终优化方案：以staffid字段作为先导列，对查询条件中的3个字段建立组合索引，修改SQL查询语句，激活涉及ddate字段的索引。

表2 优化后执行计划

通过监控eclipse服务器后台的时间记录，优化后，业务耗时降为原来的12.8%，效果十分明显。综合以上分析，我们可以得到以下结论：对于单表检索优化，提高效率的基本思路是根据字段的实际情况，分析建立组合索引，这种方法具有普适性，可以应用到各种数据库的单表检索优化中。其基本步骤包括四步，一是分析单表的各个字段，找出常用的检索字段；二是对常用字段建立联合索引；三是依次调整先导列，求出各种情况下的优化效果；四是归纳对比获得最优优化路径。

2 表间检索优化

当查询语句需要用到多张表数据的时候，查询优化器除了要确定每张表的访问路径外，还需要确定这些表的连接方法和连接顺序。

通过对该系统实际应用反馈，发现项目进度管理功能模块下对部门图纸负荷线进行计算的操作耗时巨大，达到5min22s，严重影响了业务的开展，急需进行优化，本文以此为例来剖析多表联合检索的优化方法。

分析操作过程，发现主要涉及到数据库中三张数据表：Itemtask表、Itemprocess表及Approval表。

Itemtask表为项目任务表，共有11687行数据。Itemprocess表为项目进度表，共有5637行数据，且表中taskid字段的值均取自Itemtask表中的itemtaskid字段。Approval表为项目审查表，共有32060行数据，且表中itemtaskid字段的值均取自Itemprocess表中的taskid字段。

由于涉及到三个大表的联合查询，因此在各表上建立索引、直方图等一般方法并不能明显提升查询速度。考虑到对这三个表的连接查询比较频繁，而且数据的更新相对而言并不是很频繁，因此可以考虑采用创建物化视图的方法对其进行优化。物化视图是有别于一般视图的一种特殊实体视图。一般视图是一个虚拟表，并不储存实际数据，而物化视图是对已经存储于别处的数据的转换和复制，类似于实体表。物化视图属于冗余的访问结构，所带来的额外开销比索引更高，一般情况下并不提倡使用。但对于那些查询频繁、更新较少的大表汇总和连接，具有相当不错的效果[5,6]。

将Itemtask表作为创建物化视图时的主表，其余两表作为从表，对两个从表分别添加主表的外键约束。对三个表中所有字段进行筛选，仅将部门图纸负荷线计算过程中需要用到的字段加入到创建语句中，去除不必要字段。考虑到三个表中数据行数的不一致，故采用外连接的方法，以主表的行数作为物化视图行数。具体命令如下：

create materialized view viewofItemTPA as select a.itemtaskid, a.itemid, a.picturenumber,……

b.dplanetime, b.cplanetime, b.aplanetime, ……, c.issuedtime, c.approvaltime, c.issuedcod, ……, from itemtask a,itemprocess b, approval c where b.taskid(+)=a.itemtaskid and c.itemtaskid(+)=a.itemtaskid;

创建完成时，还需要对此物化视图开启查询重写功能。当查询重写开启时，查询优化器会根据具体情况对一些合适的SQL语句进行重写，从而提高其执行效率。开启查询重写的具体命令如下：

alter materialized view viewofItemTPA enable query rewrite;

由于物化视图的特殊性，当基础表通过DML或者DDL语句进行修改时，物化视图可能会包含过期数据，由于这个原因，在基础表发生数据更新后，需要对物化视图进行刷新操作，以保持物化视图的实效性。物化视图根据刷新方法不同可以分为完全刷新、快速刷新、强制刷新三种，根据刷新时间点不同又可以分为根据需要刷新；在提交时刷新两种。刷新类型的选择应该根据实际情况决定。此例中，选择根据需要完全刷新作为此物化视图的刷新类型。由于这三个基表的数据修改频率并不高，因此可以将刷新间隔时间设置为1d，在每天下班后进行，命令如下：

alter materialized view viewofItemTPA refresh complete on demand start with <下班时间> next <下班时间>+to_dsinterval(‘1 00:00:00’);

在一些特殊情况下，也可以通过下述命令来手动进行物化视图的即时快速刷新：

execute dbms_mview.refresh (list=>’viewofItemTPA’,method=>’f’);

为了实现快速刷新，需要在每个基础表上分别创建物化视图日志。物化视图日志被用来跟踪基础表上发生的变更。可以用如下语句创建物化视图日志：

create materialized view log on <表名> with rowed;

通过以上一系列优化，使得此项操作耗时由5min多钟下降到11.5s，效果十分明显。表3为优化前后开销与耗时对比。

物化视图对于多表联合优化是一种较好的方法，其核心思想是生成物化视图后，使得表的数据检索简化，可根据单表检索的方法进行优化。基本步骤包括三步：一是根据多表之间的关联字段生成一张完整的数据表；二是在此基础上建立符合实际需求的数据刷新模式；三是对物化视图进行检索，分析优化的效果。

表3 执行计划对比

3 结 语

对数据库的性能进行优化，应当根据实际工程问题进行具体分析。从技术角度主要分为两种，一是单表优化，二是多表联合优化。具体选择哪个表或哪几个表联合进行优化，一般要根据系统实际应用情况而定，选择原则为优先选择系统中耗时长、使用频率高、面向范围广的一系列业务操作进行优化，然后逐步延伸，最终达到优化整个系统数据库性能的目的。对于单表检索来说，优化查询性能的根本出发点即是优化访问路径，提供给查询优化器更高效的访问路径，如创建合适的索引、修改调整SQL语句等，具体需根据工程问题的实际情况确定。对于表间检索来说，与一般的单独针对各个表的优化方法相比，建立多表联合视图的方法往往能明显提升数据库查询速度，尤其是物化视图的应用，如果应用恰当，能极大地提升查询性能，缩短数据读取时间。

[1] 管伟元. 船舶科研开发设计管理信息化研究[J]. 中国造船，2012(1): 186-193.

[2] 龚成刚. 船舶产品数据管理集成开发与应用[J]. 上海造船，2011, (4): 69-72.

[3] Christian Antognini. Troubleshooting Oracle Performance[M]. Berkeley: Apress, 2008.

[4] 李国伟. 充分利用索引作用提高Oracle数据库的性能[J]. 科技情报开发与经济，2010(18): 118-120.

[5] 郭忠南，孟凡荣. 关系数据库性能优化研究[J]. 计算机工程与设计，2006(23): 4484-4490.

[6] 盖国强，冯春培，叶 梁，等. Oracle数据库性能优化[M]. 北京：人民邮电出版社，2005.