徐 卓

（哈爾濱鐵路局 信息技術(shù)所，哈爾濱 150001）

關(guān)于Oracle數(shù)據(jù)庫設(shè)計(jì)、開發(fā)、應(yīng)用的探討

徐 卓

（哈爾濱鐵路局 信息技術(shù)所，哈爾濱 150001）

Oracle數(shù)據(jù)庫具有良好、穩(wěn)定的大數(shù)據(jù)處理能力，安全、高可用的并發(fā)數(shù)據(jù)訪問功能，被鐵路運(yùn)輸?shù)却笮蛧衅髽I(yè)廣泛應(yīng)用。隨著Oracle數(shù)據(jù)庫的不斷升級(jí)，不少企業(yè)在應(yīng)用過程中，設(shè)計(jì)和開發(fā)方面存在一些“誤區(qū)”，不同程度地導(dǎo)致企業(yè)在Oracle數(shù)據(jù)庫的應(yīng)用上效率不高，反映遲緩等現(xiàn)象。因此，本文結(jié)合作者多年開發(fā)和維護(hù)Oracle數(shù)據(jù)庫的經(jīng)驗(yàn)、體會(huì)，總結(jié)了Oracle數(shù)據(jù)庫在設(shè)計(jì)、開發(fā)、應(yīng)用方面容易出現(xiàn)的“誤區(qū)”，闡明了需要重點(diǎn)注意的若干問題。

I/O；索引；多對(duì)多關(guān)系

數(shù)據(jù)庫設(shè)計(jì)的質(zhì)量好壞直接關(guān)系到開發(fā)周期和系統(tǒng)性能，其開發(fā)水平則直接影響到代碼的可讀性和可維護(hù)性。本文結(jié)合筆者多年的工作實(shí)踐，主要論述在Oracle數(shù)據(jù)庫的設(shè)計(jì)和開發(fā)方面的一些切身感悟。

1 數(shù)據(jù)庫設(shè)計(jì)

數(shù)據(jù)庫設(shè)計(jì)簡單的說就是庫、表的設(shè)計(jì)，即數(shù)據(jù)庫物理存儲(chǔ)結(jié)構(gòu)（表空間）和數(shù)據(jù)表結(jié)構(gòu)的設(shè)計(jì)。數(shù)據(jù)庫物理存儲(chǔ)結(jié)構(gòu)涉及諸多操作系統(tǒng)和DBA建庫方面的理論和概念，在此不過多論述。本文重點(diǎn)探討數(shù)據(jù)表結(jié)構(gòu)設(shè)計(jì)方面的一些問題。

1.1 多對(duì)多關(guān)系的表結(jié)構(gòu)設(shè)計(jì)

Oracle數(shù)據(jù)庫的表都是一張二維表，關(guān)于單獨(dú)一張表的結(jié)構(gòu)設(shè)計(jì)，只需要結(jié)合具體實(shí)體屬性，適當(dāng)?shù)姆蠑?shù)據(jù)庫第三范式3NF即可。而對(duì)于實(shí)體之間抽象出的一對(duì)一，多對(duì)一（一對(duì)多）和多對(duì)多3種類型的關(guān)系，通過Oracle數(shù)據(jù)庫主、外鍵的合理設(shè)置也能夠通過主、從表的方式實(shí)現(xiàn)其中的一對(duì)一，多對(duì)一（一對(duì)多）兩種關(guān)系。多對(duì)多關(guān)系的實(shí)現(xiàn)，是通過設(shè)立中間表來實(shí)現(xiàn)的。

例如：表A（項(xiàng)目表）（id,名稱，功能，開發(fā)時(shí)間，...）；

數(shù)據(jù)：（1，確報(bào)系統(tǒng)，收發(fā)確報(bào)，2006-08，...）

（2，貨票系統(tǒng)，生成電子貨票，2013-12，...)表B（設(shè)備表）（id，名稱，序列號(hào)，型號(hào)，...）。數(shù)據(jù)：(1，IBM服務(wù)器，99BHBH3，X3650M3，…)（2，HP服務(wù)器，G215LJC13G，DL380，…)（3，IBM小機(jī)，10C2F8C，P570，…) )

表A中的一個(gè)項(xiàng)目可對(duì)應(yīng)多個(gè)設(shè)備；表B中的一個(gè)設(shè)備可被多個(gè)項(xiàng)目使用，表A和表B是標(biāo)準(zhǔn)的多對(duì)多關(guān)系。要實(shí)現(xiàn)A、B表的多對(duì)多關(guān)系，通常的方法是引入一個(gè)中間表C（項(xiàng)目ID，設(shè)備ID，備注）。

表C數(shù)據(jù)：（1，1，）

（1，3，）

（2，2，）

（2，3，）

通過設(shè)置表A的ID和表C的項(xiàng)目ID；表B的ID和表C的設(shè)備ID為主外鍵關(guān)系，來實(shí)現(xiàn)表A、C和表B、C的同步更新，通過表A、B、C的關(guān)聯(lián)查詢，就可以實(shí)現(xiàn)項(xiàng)目表和設(shè)備表相關(guān)聯(lián)數(shù)據(jù)的多對(duì)多查詢和展示。

1.2 索引

通過Oracle數(shù)據(jù)庫的索引，可以迅速定位記錄的位置，而不必去定位整個(gè)表，這樣極大地提高了數(shù)據(jù)庫的查詢速度。但是，表中的索引越多，維護(hù)索引所需要的成本也就越大，每當(dāng)數(shù)據(jù)表中記錄有增加、刪除、更新變化時(shí)，數(shù)據(jù)庫系統(tǒng)都需要對(duì)所有索引進(jìn)行更新。所以，數(shù)據(jù)庫表中的索引絕對(duì)不是多多益善。Oracle數(shù)據(jù)庫創(chuàng)建索引通常應(yīng)遵循如下原則：

（1）在基數(shù)小的字段上要善于使用位圖索引?；鶖?shù)是位圖索引中的一個(gè)基本定義，它是指Oracle數(shù)據(jù)庫表中某個(gè)字段內(nèi)容中不重復(fù)的數(shù)值。如在員工信息表中的性別字段，一般就只有男跟女兩個(gè)值，所以，其基數(shù)為2。再如婚姻狀況、民族等字段都適用位圖索引。除了在數(shù)據(jù)表某列基數(shù)比較小的情況下，采用位圖索引外，在Where限制條件中，若多次采用AND或者OR條件時(shí)，也建議采用位圖索引。因?yàn)楫?dāng)一個(gè)查詢，引用了一些部署了位圖索引的列時(shí)，這些位圖可以很方便的與AND或者OR 運(yùn)算符操作結(jié)合以快速的找出用戶所需要的記錄。

（2）對(duì)于滿足查詢條件的數(shù)據(jù)不超過10%的查詢列和用于集函數(shù)、連接、group by和order by的列，應(yīng)該建索引。這樣可以利用索引順序的特點(diǎn)加快排序速度。連接中經(jīng)常使用的列或表中有外鍵約束的列應(yīng)該建立索引，如果列處在索引順序中則系統(tǒng)可更快執(zhí)行連接。

（3）對(duì)于順序增長的列索引，以及具有如“餐廳甲”，“餐廳乙”等相似但不重復(fù)的列值，使用反向鍵索引。

（4）索引與數(shù)據(jù)表應(yīng)分別建在不同的表空間上。

（5）表的主鍵列和唯一性約束列自動(dòng)建立唯一性索引，不需要單獨(dú)指定索引。

1.3 分區(qū)表

表結(jié)構(gòu)設(shè)計(jì)一定要考慮應(yīng)用數(shù)據(jù)長期積累或暴發(fā)式增長對(duì)系統(tǒng)性能和數(shù)據(jù)維護(hù)帶來的影響。否則，應(yīng)用系統(tǒng)在積累了大量數(shù)據(jù)時(shí)再去修改表結(jié)構(gòu)，由此帶來的維護(hù)工作量和異常，對(duì)開發(fā)人員和用戶來說都將是災(zāi)難性的。Oracle的分區(qū)表，就是解決包含大量歷史數(shù)據(jù)的大數(shù)據(jù)表的性能和維護(hù)瓶頸的一個(gè)非常有效的表結(jié)構(gòu)設(shè)計(jì)方式。

Oracle的分區(qū)表具有以下優(yōu)點(diǎn)：（1）增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其它分區(qū)的數(shù)據(jù)仍然可用。（2）維護(hù)方便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可。（3）均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個(gè)系統(tǒng)性能。（4）改善查詢性能：對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。

Oracle的分區(qū)表可分為：范圍分區(qū)，哈希分區(qū)，復(fù)合分區(qū)，列表分區(qū)，混合分區(qū)，間隔分區(qū)等。對(duì)這些分區(qū)的具體應(yīng)用，由于篇幅所限, 在此不再贅述，本文僅對(duì)建立數(shù)據(jù)庫分區(qū)表應(yīng)遵循的原則，作以下簡要說明：

（1）表的大?。寒?dāng)表的大小超過1.5 GB～ 2 GB，或?qū)τ贠LTP系統(tǒng)，表的記錄超過1 000萬，都應(yīng)考慮對(duì)表進(jìn)行分區(qū)。

（2）數(shù)據(jù)訪問特性：基于表的大部分查詢應(yīng)用，只訪問表中少量的數(shù)據(jù)。對(duì)于這樣表進(jìn)行分區(qū)，可充分利用分區(qū)排除無關(guān)數(shù)據(jù)查詢的特性。

（3）數(shù)據(jù)維護(hù)：按時(shí)間段刪除成批的數(shù)據(jù)，例如按月刪除歷史數(shù)據(jù)。對(duì)于這樣的表需要考慮進(jìn)行分區(qū)，以滿足維護(hù)的需要。

（4）數(shù)據(jù)備份和恢復(fù)：按時(shí)間周期進(jìn)行表空間的備份時(shí)，將分區(qū)與表空間建立對(duì)應(yīng)關(guān)系。

（5）只讀數(shù)據(jù)：如果一個(gè)表中大部分?jǐn)?shù)據(jù)都是只讀數(shù)據(jù)，通過對(duì)表進(jìn)行分區(qū)，可將只讀數(shù)據(jù)存儲(chǔ)在只讀表空間中，對(duì)于數(shù)據(jù)庫的備份有益。

（6）并行數(shù)據(jù)操作：對(duì)于經(jīng)常執(zhí)行并行操作（如Parallel Insert，Parallel Update等）的表應(yīng)考慮進(jìn)行分區(qū)。

（7）表的可用性：當(dāng)對(duì)表的部分?jǐn)?shù)據(jù)可用性要求很高時(shí)，應(yīng)考慮進(jìn)行表分區(qū)。

2 數(shù)據(jù)庫開發(fā)和應(yīng)用

本文只選取SQL共享，數(shù)據(jù)庫客戶端連接，歷史數(shù)據(jù)處理等在企業(yè)日常的數(shù)據(jù)庫維護(hù)工作中，具有代表性并且容易出錯(cuò)的3個(gè)方面進(jìn)行分析和探討。

2.1 SQL共享

Oracle將執(zhí)行過的SQL語句存放在內(nèi)存的共享池（sharedbuffer pool）中，可以被所有的數(shù)據(jù)庫用戶共享。當(dāng)執(zhí)行一個(gè)SQL語句時(shí),如果它和之前執(zhí)行過的語句完全相同, Oracle就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑。這就是SQL共享，這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存空間。

SQL共享有3個(gè)條件：（1）當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同（包括大小寫、空格、換行等）。（2）兩個(gè)語句所指的對(duì)象必須完全相同（同義詞與表是不同的對(duì)象）。（3）兩個(gè)SQL語句中必須使用相同名字的綁定變量（bindvariables）。

其中，綁定變量是許多數(shù)據(jù)庫開發(fā)人員在編程時(shí)容易忽視之處。如：select * from 表B where序列號(hào)=‘99BHBH3’；上面這個(gè)語句，每執(zhí)行一次就需要在SHARE POOL 硬解析一次，100萬用戶就是100萬次，消耗CPU和內(nèi)存，如果業(yè)務(wù)量大，很可能導(dǎo)致宕庫。如果綁定變量，則只需要硬解析一次，重復(fù)調(diào)用即可。

以下是綁定變量與不綁定變量在寫法上的異同，在數(shù)據(jù)庫編程時(shí)盡可能的應(yīng)用綁定變量的方式。

未使用綁定變量的語句：

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 數(shù)據(jù)庫連接

通常情況下，應(yīng)用程序與后臺(tái)的Oracle數(shù)據(jù)庫進(jìn)行數(shù)據(jù)交換是通過Oralcle的客戶端軟件,配置TNASNAMES.ORA文件中的“連接串”來實(shí)現(xiàn)的。目前,很多企業(yè)的后臺(tái)Oracle數(shù)據(jù)庫都建成了兼顧安全和效率的RAC方式。但是客戶端的數(shù)據(jù)庫連接配置，還停留在以往單機(jī)數(shù)據(jù)庫的配置方式。并沒有真正發(fā)揮RAC數(shù)據(jù)庫對(duì)客戶端的負(fù)載均衡和故障切換功能。以下是兩個(gè)客戶端的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上的單個(gè)數(shù)據(jù)庫實(shí)例，當(dāng)10.27.3.25這個(gè)RAC節(jié)點(diǎn)因故宕機(jī)時(shí)，客戶端連接不能實(shí)現(xiàn)自動(dòng)切換到另外的數(shù)據(jù)庫節(jié)點(diǎn)?？蛻舳薆的配置實(shí)現(xiàn)了對(duì)RAC數(shù)據(jù)庫10.25.3.25和10.25.3.27兩個(gè)節(jié)點(diǎn)的故障切換功能。

客戶端B關(guān)閉了對(duì)兩個(gè)數(shù)據(jù)庫節(jié)點(diǎn)的負(fù)載均衡功能(LOAD_BALANCE=off)。這是因?yàn)?，有時(shí)為了避免節(jié)點(diǎn)爭用，要進(jìn)行人為的用戶分區(qū)。即把執(zhí)行相同業(yè)務(wù)功能的用戶，固定連接到同一個(gè)數(shù)據(jù)庫節(jié)點(diǎn)上，避免不同的節(jié)點(diǎn)，訪問同一個(gè)數(shù)據(jù)塊，形成節(jié)點(diǎn)爭用，影響性能?？蛻舳薆把10.27.3.25配置在前面，則每次都連接10.27.3.25節(jié)點(diǎn)，只有10.27.3.25節(jié)點(diǎn)因故無法啟動(dòng)時(shí)，才連接10.27.3.27節(jié)點(diǎn)。上述客戶端配置LOAD_BALANCE=off，有時(shí)需要將后臺(tái)數(shù)據(jù)庫的remote_listener參數(shù)置空才能生效。

2.3 歷史數(shù)據(jù)處理

建議采取如下方式處理應(yīng)用項(xiàng)目的歷史數(shù)據(jù)：（1）建立單獨(dú)表空間，用戶來管理歷史數(shù)據(jù)。（2）在單獨(dú)的表空間中建立與產(chǎn)生歷史數(shù)據(jù)的表同結(jié)構(gòu)的數(shù)據(jù)表來存放歷史數(shù)據(jù)。

（3）建立通用的管理表，記錄哪些生產(chǎn)表及對(duì)應(yīng)子表需要?dú)v史；這些表的關(guān)聯(lián)字段、時(shí)間字段、歷史周期、刪除歷史數(shù)據(jù)周期以及相應(yīng)的where條件等信息。

（4）編寫通用的存儲(chǔ)過程，根據(jù)（3）中管理表的記錄，負(fù)責(zé)按相應(yīng)的where條件，定期將記錄的生產(chǎn)表及子表中的數(shù)據(jù)轉(zhuǎn)儲(chǔ)到歷史表中，并按記錄的周期刪除歷史表中的過期數(shù)據(jù)。

（5）調(diào)試應(yīng)用程序，使其具備專門的查詢生產(chǎn)數(shù)據(jù)和歷史數(shù)據(jù)的功能。

3 結(jié)束語

Oracle數(shù)據(jù)庫的設(shè)計(jì)和開發(fā)是一個(gè)抽象性、系統(tǒng)性、規(guī)律性、挑戰(zhàn)性相結(jié)合的工作。以上只是拋磚引玉，淺嘗輒止地談了一些筆者在數(shù)據(jù)庫設(shè)計(jì)和開發(fā)過程中的一些經(jīng)驗(yàn)、感悟，希望大家能從中有所啟發(fā)。

[1] 盧 濤. 劍破冰山—Oracle開發(fā)藝術(shù)[M].北京：電子工業(yè)出版社，2011.

[2] 羅 敏. Oracle數(shù)據(jù)庫高級(jí)技術(shù)交流—大批量數(shù)據(jù)處理技術(shù)[EB/OL]. http://wenku.baidu.com/list/161，2011.

[3] Bill Karwin. SQL反模式[M]. 譚振林，譯.北京：人民郵電出版社，2011.

責(zé)任編輯 陳 蓉

U29∶TP392

A

1005-8451（2014）09-0059-04

2014-03-04

徐 卓，高級(jí)工程師。