科研設(shè)計(jì)管理信息系統(tǒng)數(shù)據(jù)庫性能優(yōu)化研究

管偉元，金海豐

（1. 708研究所，上海 200011；2. 天津大學(xué)建筑工程學(xué)院，天津 100072）

0 引 言

船舶及海洋工程科研設(shè)計(jì)管理信息系統(tǒng)簡稱RDM。系統(tǒng)采用信息化手段實(shí)現(xiàn)對(duì)各類項(xiàng)目活動(dòng)的管理，具有機(jī)構(gòu)及人員管理、用戶管理、專業(yè)管理、項(xiàng)目模版管理、項(xiàng)目進(jìn)度管理、實(shí)動(dòng)工時(shí)管理、報(bào)表管理和系統(tǒng)管理等功能[1,2]。

系統(tǒng)選用甲骨文公司的Oracle10g企業(yè)版作為數(shù)據(jù)庫。隨著系統(tǒng)使用時(shí)間的延續(xù)以及用戶的增多，數(shù)據(jù)庫中的數(shù)據(jù)量越來越龐大，同時(shí)對(duì)系統(tǒng)響應(yīng)時(shí)間的要求也越來越高，這就迫切需要對(duì)數(shù)據(jù)庫性能進(jìn)行優(yōu)化。數(shù)據(jù)庫優(yōu)化涉及的內(nèi)容很多，最主要的包括三個(gè)方面，即單表檢索優(yōu)化、多表聯(lián)合檢索優(yōu)化和數(shù)據(jù)庫配置優(yōu)化[3]，本文圍繞前兩個(gè)方面展開研究。

1 單表檢索優(yōu)化

數(shù)據(jù)庫引擎訪問數(shù)據(jù)，有兩種方法定位數(shù)據(jù)表中數(shù)據(jù)。第一種為全表掃描；第二種是基于索引等冗余的訪問結(jié)構(gòu)進(jìn)行檢索[4]。對(duì)于不同情況，兩種訪問方法各有優(yōu)劣。識(shí)別低效的訪問路徑，引導(dǎo)數(shù)據(jù)庫引擎采用高效的訪問路徑，是提高單表檢索效率的根本方法。要想提高單表檢索效率，首先應(yīng)考慮添加新的訪問結(jié)構(gòu)，如在經(jīng)常充當(dāng)檢索條件的列上建立適當(dāng)類型的索引。同時(shí)，優(yōu)化SQL語句，改變數(shù)據(jù)表結(jié)構(gòu)，改變檢索條件等也能對(duì)提高檢索效率起到一定作用。

在系統(tǒng)中，常用的一項(xiàng)業(yè)務(wù)之一就是員工日常工時(shí)登記，這是一項(xiàng)涉及范圍特別廣，使用頻率特別高的操作，需要重點(diǎn)優(yōu)化，本文以此為例對(duì)單表檢索優(yōu)化的技術(shù)途徑進(jìn)行剖析。

日常工時(shí)記錄儲(chǔ)存在數(shù)據(jù)庫scott方案下的dworkhour表中，共包括14個(gè)字段，其中dworkhourid字段為表的主鍵。通過count(*)函數(shù)統(tǒng)計(jì)得出此表一共有1091239行數(shù)據(jù)，數(shù)據(jù)量較為龐大。

某員工獲得日常工時(shí)記錄的SQL查詢語句為（記為查詢語句A）：

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

收集并獲取此語句的執(zhí)行計(jì)劃：

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);

得到部分結(jié)果如表1所示。

表1 原始執(zhí)行計(jì)劃

從表中數(shù)據(jù)可以發(fā)現(xiàn)，未進(jìn)行優(yōu)化時(shí)，系統(tǒng)評(píng)估此操作的開銷為2406。

首先考慮對(duì)查詢條件中3個(gè)字段分別建立合適的索引。staffid字段與ddate字段重復(fù)值較少，適合建立B-樹索引，而affirm字段只有0和1兩個(gè)值，建立位圖索引更加妥當(dāng)。完成索引的建立后重新收集表的統(tǒng)計(jì)信息，然后運(yùn)行查詢語句A并查看執(zhí)行計(jì)劃，發(fā)現(xiàn)系統(tǒng)開銷下降到1958，效果不明顯。

研究查詢語句A，查詢條件中對(duì)ddate字段使用了to_char()函數(shù)，這將導(dǎo)致建立在ddate字段上的索引不可用。解決這一問題，可以對(duì)查詢語句A進(jìn)行適當(dāng)修改，得到如下語句（記為查詢語句B）：

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

通過修改 SQL語言來激活建立在 ddate字段上的索引后，重新查看執(zhí)行計(jì)劃中的系統(tǒng)開銷，結(jié)果為1460，較之前又有所降低。

由于以上方法優(yōu)化效果并不明顯，故考慮對(duì)查詢條件中的3個(gè)字段建立組合索引。組合索引是一種特殊的索引，又可稱為復(fù)合索引。組合索引包含有多個(gè)列，比單個(gè)字段建立的索引更具有選擇性。以 ddate字段為先導(dǎo)列建立組合索引：

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

運(yùn)行查詢語句B并查看執(zhí)行計(jì)劃，得到系統(tǒng)開銷為1028。再分別以staffid字段、affirm字段為先導(dǎo)列各自建立組合索引，查看它們的系統(tǒng)開銷，結(jié)果分別為51和458（見表2）。因此得到最終優(yōu)化方案：以staffid字段作為先導(dǎo)列，對(duì)查詢條件中的3個(gè)字段建立組合索引，修改SQL查詢語句，激活涉及ddate字段的索引。

表2 優(yōu)化后執(zhí)行計(jì)劃

通過監(jiān)控eclipse服務(wù)器后臺(tái)的時(shí)間記錄，優(yōu)化后，業(yè)務(wù)耗時(shí)降為原來的12.8%，效果十分明顯。綜合以上分析，我們可以得到以下結(jié)論：對(duì)于單表檢索優(yōu)化，提高效率的基本思路是根據(jù)字段的實(shí)際情況，分析建立組合索引，這種方法具有普適性，可以應(yīng)用到各種數(shù)據(jù)庫的單表檢索優(yōu)化中。其基本步驟包括四步，一是分析單表的各個(gè)字段，找出常用的檢索字段；二是對(duì)常用字段建立聯(lián)合索引；三是依次調(diào)整先導(dǎo)列，求出各種情況下的優(yōu)化效果；四是歸納對(duì)比獲得最優(yōu)優(yōu)化路徑。

2 表間檢索優(yōu)化

當(dāng)查詢語句需要用到多張表數(shù)據(jù)的時(shí)候，查詢優(yōu)化器除了要確定每張表的訪問路徑外，還需要確定這些表的連接方法和連接順序。

通過對(duì)該系統(tǒng)實(shí)際應(yīng)用反饋，發(fā)現(xiàn)項(xiàng)目進(jìn)度管理功能模塊下對(duì)部門圖紙負(fù)荷線進(jìn)行計(jì)算的操作耗時(shí)巨大，達(dá)到5min22s，嚴(yán)重影響了業(yè)務(wù)的開展，急需進(jìn)行優(yōu)化，本文以此為例來剖析多表聯(lián)合檢索的優(yōu)化方法。

分析操作過程，發(fā)現(xiàn)主要涉及到數(shù)據(jù)庫中三張數(shù)據(jù)表：Itemtask表、Itemprocess表及Approval表。

Itemtask表為項(xiàng)目任務(wù)表，共有11687行數(shù)據(jù)。Itemprocess表為項(xiàng)目進(jìn)度表，共有5637行數(shù)據(jù)，且表中taskid字段的值均取自Itemtask表中的itemtaskid字段。Approval表為項(xiàng)目審查表，共有32060行數(shù)據(jù)，且表中itemtaskid字段的值均取自Itemprocess表中的taskid字段。

由于涉及到三個(gè)大表的聯(lián)合查詢，因此在各表上建立索引、直方圖等一般方法并不能明顯提升查詢速度。考慮到對(duì)這三個(gè)表的連接查詢比較頻繁，而且數(shù)據(jù)的更新相對(duì)而言并不是很頻繁，因此可以考慮采用創(chuàng)建物化視圖的方法對(duì)其進(jìn)行優(yōu)化。物化視圖是有別于一般視圖的一種特殊實(shí)體視圖。一般視圖是一個(gè)虛擬表，并不儲(chǔ)存實(shí)際數(shù)據(jù)，而物化視圖是對(duì)已經(jīng)存儲(chǔ)于別處的數(shù)據(jù)的轉(zhuǎn)換和復(fù)制，類似于實(shí)體表。物化視圖屬于冗余的訪問結(jié)構(gòu)，所帶來的額外開銷比索引更高，一般情況下并不提倡使用。但對(duì)于那些查詢頻繁、更新較少的大表匯總和連接，具有相當(dāng)不錯(cuò)的效果[5,6]。

將Itemtask表作為創(chuàng)建物化視圖時(shí)的主表，其余兩表作為從表，對(duì)兩個(gè)從表分別添加主表的外鍵約束。對(duì)三個(gè)表中所有字段進(jìn)行篩選，僅將部門圖紙負(fù)荷線計(jì)算過程中需要用到的字段加入到創(chuàng)建語句中，去除不必要字段。考慮到三個(gè)表中數(shù)據(jù)行數(shù)的不一致，故采用外連接的方法，以主表的行數(shù)作為物化視圖行數(shù)。具體命令如下：

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;

創(chuàng)建完成時(shí)，還需要對(duì)此物化視圖開啟查詢重寫功能。當(dāng)查詢重寫開啟時(shí)，查詢優(yōu)化器會(huì)根據(jù)具體情況對(duì)一些合適的SQL語句進(jìn)行重寫，從而提高其執(zhí)行效率。開啟查詢重寫的具體命令如下：

alter materialized view viewofItemTPA enable query rewrite;

由于物化視圖的特殊性，當(dāng)基礎(chǔ)表通過DML或者DDL語句進(jìn)行修改時(shí)，物化視圖可能會(huì)包含過期數(shù)據(jù)，由于這個(gè)原因，在基礎(chǔ)表發(fā)生數(shù)據(jù)更新后，需要對(duì)物化視圖進(jìn)行刷新操作，以保持物化視圖的實(shí)效性。物化視圖根據(jù)刷新方法不同可以分為完全刷新、快速刷新、強(qiáng)制刷新三種，根據(jù)刷新時(shí)間點(diǎn)不同又可以分為根據(jù)需要刷新；在提交時(shí)刷新兩種。刷新類型的選擇應(yīng)該根據(jù)實(shí)際情況決定。此例中，選擇根據(jù)需要完全刷新作為此物化視圖的刷新類型。由于這三個(gè)基表的數(shù)據(jù)修改頻率并不高，因此可以將刷新間隔時(shí)間設(shè)置為1d，在每天下班后進(jìn)行，命令如下：

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

在一些特殊情況下，也可以通過下述命令來手動(dòng)進(jìn)行物化視圖的即時(shí)快速刷新：

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

為了實(shí)現(xiàn)快速刷新，需要在每個(gè)基礎(chǔ)表上分別創(chuàng)建物化視圖日志。物化視圖日志被用來跟蹤基礎(chǔ)表上發(fā)生的變更。可以用如下語句創(chuàng)建物化視圖日志：

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

通過以上一系列優(yōu)化，使得此項(xiàng)操作耗時(shí)由5min多鐘下降到11.5s，效果十分明顯。表3為優(yōu)化前后開銷與耗時(shí)對(duì)比。

物化視圖對(duì)于多表聯(lián)合優(yōu)化是一種較好的方法，其核心思想是生成物化視圖后，使得表的數(shù)據(jù)檢索簡化，可根據(jù)單表檢索的方法進(jìn)行優(yōu)化?；静襟E包括三步：一是根據(jù)多表之間的關(guān)聯(lián)字段生成一張完整的數(shù)據(jù)表；二是在此基礎(chǔ)上建立符合實(shí)際需求的數(shù)據(jù)刷新模式；三是對(duì)物化視圖進(jìn)行檢索，分析優(yōu)化的效果。

表3 執(zhí)行計(jì)劃對(duì)比

3 結(jié) 語

對(duì)數(shù)據(jù)庫的性能進(jìn)行優(yōu)化，應(yīng)當(dāng)根據(jù)實(shí)際工程問題進(jìn)行具體分析。從技術(shù)角度主要分為兩種，一是單表優(yōu)化，二是多表聯(lián)合優(yōu)化。具體選擇哪個(gè)表或哪幾個(gè)表聯(lián)合進(jìn)行優(yōu)化，一般要根據(jù)系統(tǒng)實(shí)際應(yīng)用情況而定，選擇原則為優(yōu)先選擇系統(tǒng)中耗時(shí)長、使用頻率高、面向范圍廣的一系列業(yè)務(wù)操作進(jìn)行優(yōu)化，然后逐步延伸，最終達(dá)到優(yōu)化整個(gè)系統(tǒng)數(shù)據(jù)庫性能的目的。對(duì)于單表檢索來說，優(yōu)化查詢性能的根本出發(fā)點(diǎn)即是優(yōu)化訪問路徑，提供給查詢優(yōu)化器更高效的訪問路徑，如創(chuàng)建合適的索引、修改調(diào)整SQL語句等，具體需根據(jù)工程問題的實(shí)際情況確定。對(duì)于表間檢索來說，與一般的單獨(dú)針對(duì)各個(gè)表的優(yōu)化方法相比，建立多表聯(lián)合視圖的方法往往能明顯提升數(shù)據(jù)庫查詢速度，尤其是物化視圖的應(yīng)用，如果應(yīng)用恰當(dāng)，能極大地提升查詢性能，縮短數(shù)據(jù)讀取時(shí)間。

[1] 管偉元. 船舶科研開發(fā)設(shè)計(jì)管理信息化研究[J]. 中國造船，2012(1): 186-193.

[2] 龔成剛. 船舶產(chǎn)品數(shù)據(jù)管理集成開發(fā)與應(yīng)用[J]. 上海造船，2011, (4): 69-72.

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

[4] 李國偉. 充分利用索引作用提高Oracle數(shù)據(jù)庫的性能[J]. 科技情報(bào)開發(fā)與經(jīng)濟(jì)，2010(18): 118-120.

[5] 郭忠南，孟凡榮. 關(guān)系數(shù)據(jù)庫性能優(yōu)化研究[J]. 計(jì)算機(jī)工程與設(shè)計(jì)，2006(23): 4484-4490.

[6] 蓋國強(qiáng)，馮春培，葉 梁，等. Oracle數(shù)據(jù)庫性能優(yōu)化[M]. 北京：人民郵電出版社，2005.