科研設計管理信息系統數據庫性能優化研究

管偉元，金海豐

（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.