數(shù)據(jù)庫設(shè)計參考標準

文檔控制文檔屬性文檔編號作者版本日期密級EDS06黃翠微V12011年6月16文檔修訂歷史概述為明確公司項目中數(shù)據(jù)庫邏輯設(shè)計及物理設(shè)計的內(nèi)容和流程，特制定本規(guī)范，供數(shù)據(jù)庫設(shè)計、開發(fā)及維護人員參考。數(shù)據(jù)庫設(shè)計方法目前可分為四類：直觀設(shè)計法、規(guī)范設(shè)計法、計算機輔助設(shè)計法和自動化設(shè)計法。新奧爾良法是目前公認的比較完整和權(quán)威的一種規(guī)范設(shè)計法。新奧爾良法將數(shù)據(jù)庫設(shè)計分成需求分析（分析用戶需求）、概念設(shè)計（信息分析和定義）、邏輯設(shè)計（設(shè)計實現(xiàn)）和物理設(shè)計（物理數(shù)據(jù)庫設(shè)計）。目前，常用的規(guī)范設(shè)計方法大多起源于新奧爾良法，并在設(shè)計的每一階段采用一些輔助方法來具體實現(xiàn)。以下是兩種常用的規(guī)范設(shè)計方法：基于E-R模型的數(shù)據(jù)庫設(shè)計方法。該方法是由，其基本思想是在需求分析的基礎(chǔ)上，用E-R（實體一聯(lián)系）圖構(gòu)造一個反映現(xiàn)實世界實體之間聯(lián)系的企業(yè)模式，然后再將此企業(yè)模式轉(zhuǎn)換成基于某一特定的DBMS的概念模式。基于3NF的數(shù)據(jù)庫設(shè)計方法。該方法是由S?Atre提出的結(jié)構(gòu)化設(shè)計方法,其基本思想是在需求分析的基礎(chǔ)上，確定數(shù)據(jù)庫模式中的全部屬性和屬性間的依賴關(guān)系，將它們組織在一個單一的關(guān)系模式中，然后再分析模式中不符合3NF的約束條件，將其進行投影分解，規(guī)范成若干個3NF關(guān)系模式的集合。其具體設(shè)計步驟分為五個階段：（1）設(shè)計企業(yè)模式，利用規(guī)范化得到的3NF關(guān)系模式畫出企業(yè)模式；（2）設(shè)計數(shù)據(jù)庫的概念模式，把企業(yè)模式轉(zhuǎn)換成DBMS所能接受的概念模式，并根據(jù)概念模式導(dǎo)出各個應(yīng)用的外模式；（3）設(shè)計數(shù)據(jù)庫的物理模式（存儲模式）；（4）對物理模式進行評價；（5）實現(xiàn)數(shù)據(jù)庫。備注：數(shù)據(jù)庫設(shè)計規(guī)范、數(shù)據(jù)編程規(guī)范、數(shù)據(jù)庫物理設(shè)計規(guī)范中以O(shè)racle數(shù)據(jù)庫為例，其它結(jié)構(gòu)的數(shù)據(jù)庫類似。二、數(shù)據(jù)庫設(shè)計流程以規(guī)范性設(shè)計為例，把數(shù)據(jù)庫設(shè)計流程分為以下幾個階段。（一）需求分析階段需求收集和分析，得到數(shù)據(jù)字典描述的數(shù)據(jù)需求和數(shù)據(jù)流圖描述的處理需求。需求分析的重點：調(diào)查、收集與分析用戶在數(shù)據(jù)管理中的信息要求、處理要求、安全性與完整性要求。需求分析的方法：調(diào)查組織機構(gòu)情況、各部門的業(yè)務(wù)活動情況、協(xié)助用戶明確對新系統(tǒng)的各種要求、確定新系統(tǒng)的邊界。常用的調(diào)查方法有：跟班作業(yè)、開調(diào)查會、請專人介紹、詢問、設(shè)計調(diào)查表請用戶填寫、查閱記錄。分析和表達用戶需求的方法：主要包括自頂向下和自底向上兩類方法。采用逐層分解的方式分析系統(tǒng)，并把每一層用數(shù)據(jù)流圖和數(shù)據(jù)字典描述。數(shù)據(jù)流圖（DataFlowDiagram，DFD）表達了數(shù)據(jù)和處理過程的關(guān)系。系統(tǒng)中的數(shù)據(jù)則借助數(shù)據(jù)字典（DataDictionary,簡稱DD）來描述。（二）概念結(jié)構(gòu)設(shè)計階段概念模型用于信息世界的建模。概念模型不依賴于某一個DBMS支持的數(shù)據(jù)模型。通過對用戶需求進行綜合、歸納與抽象，形成一個獨立于具體DBMS的概念模型，可以用E-R圖表示。概念模型可以轉(zhuǎn)換為計算機上某一DBMS支持的特定數(shù)據(jù)模型，其特點為：具有較強的語義表達能力，能夠方便、直接地表達應(yīng)用中的各種語義知識。應(yīng)該簡單、清晰、易于用戶理解，是用戶與數(shù)據(jù)庫設(shè)計人員之間進行交流的語言。（三）邏輯設(shè)計階段將概念結(jié)構(gòu)轉(zhuǎn)換為某個DBMS所支持的數(shù)據(jù)模型（例如關(guān)系模型），并對其進行優(yōu)化。將E-R圖轉(zhuǎn)換為關(guān)系模型實際上就是要將實體、實體的屬性和實體之間的聯(lián)系轉(zhuǎn)化為關(guān)系模式,這種轉(zhuǎn)換一般遵循如下原則：一個實體型轉(zhuǎn)換為一個關(guān)系模式，實體的屬性就是關(guān)系的屬性；一個多對多的聯(lián)系轉(zhuǎn)換為一個關(guān)系模式。數(shù)據(jù)模型的優(yōu)化，確定數(shù)據(jù)依賴，消除冗余的聯(lián)系，確定各關(guān)系模式分別屬于第幾范式。確定是否要對它們進行合并或分解。一般來說將關(guān)系分解為3NF的標準。（四）物理設(shè)計階段為邏輯數(shù)據(jù)模型選取一個最適合應(yīng)用環(huán)境的物理結(jié)構(gòu)（包括存儲結(jié)構(gòu)和存取方法）。根據(jù)DBMS特點和處理的需要，進行物理存儲安排，設(shè)計索引，形成數(shù)據(jù)庫內(nèi)模式。（五）數(shù)據(jù)庫實施階段運用DBMS提供的數(shù)據(jù)語言（例如SQL）及其宿主語言（例如C），根據(jù)邏輯設(shè)計和物理設(shè)計的結(jié)果建立數(shù)據(jù)庫，編制與調(diào)試應(yīng)用程序，組織數(shù)據(jù)入庫，并進行試運行。（六）數(shù)據(jù)庫運行維護階段在數(shù)據(jù)庫系統(tǒng)運行過程中必須不斷地對其進行評價、調(diào)整與修改。內(nèi)容包括：數(shù)據(jù)庫的轉(zhuǎn)儲和恢復(fù)、數(shù)據(jù)庫的安全性、完整性控制、數(shù)據(jù)庫性能的監(jiān)督、分析和改進、數(shù)據(jù)庫的重組織和重構(gòu)造。為加快數(shù)據(jù)庫設(shè)計速度，目前有很多數(shù)據(jù)庫輔助工具（CASE工具），如Rational公司的RationalRose，CA公司的Erwin和Bpwin，Sybase公司的PowerDesigner以及Oracle公司的OracleDesigner等。三、數(shù)據(jù)庫設(shè)計規(guī)范（一）數(shù)據(jù)庫規(guī)范化的總體要求數(shù)據(jù)表中避免可空列雖然表中允許空列，但空字段為一種特殊的數(shù)據(jù)類型，數(shù)據(jù)庫將對其進行特殊的處理，為此將增加數(shù)據(jù)庫處理記錄的復(fù)雜性，且當表中存在較多空字段時，在同等條件下，數(shù)據(jù)庫處理的性能將降低許多。因此，在數(shù)據(jù)庫表設(shè)計時應(yīng)盡量避免。若確實需要，可通過一些折中方式處理，讓其對數(shù)據(jù)庫性能的影響降低至最少。在此推薦兩個方式。A．設(shè)置默認值。默認值設(shè)置原則為業(yè)務(wù)啟動后不可能達到的值，或者為原始狀態(tài)的對應(yīng)值。如無法從數(shù)值上區(qū)分是否為原始狀態(tài)則不推薦設(shè)置默認值。以數(shù)值型字段為例，假設(shè)其用于存放采集終端所獲取的子艙位訂座，由于訂座數(shù)默認至少為大于等于0的數(shù)值，為此可設(shè)置默認值為-1。另一個例子，字符型的航班狀態(tài)，默認情況下航班為正常執(zhí)行的正班，當因為天氣、機械或其它調(diào)控等原因狀態(tài)才發(fā)生改變，此時可設(shè)置默認值為“正班”。日期型數(shù)據(jù)可默認為1900-01-01，在應(yīng)用層面顯示需要根據(jù)實際情況進行轉(zhuǎn)換。B．第二種方式是建立副表。這是當一張表中允許為空的列較多時，如接近表全部列數(shù)的三分之一，且這些列數(shù)據(jù)在大多情況下均為空，此時建議另外建立一張副表，以保存這些列；主表與副表之間根據(jù)關(guān)鍵字關(guān)聯(lián)，這樣將數(shù)據(jù)分別存儲于兩個獨立的表中使得主表設(shè)計更為簡單，既保證了數(shù)據(jù)庫性能，又能滿足存儲空值的應(yīng)用需要。如果字段默認值設(shè)置導(dǎo)致部分業(yè)務(wù)需求無法滿足或開發(fā)代價大量增加，此時不建議設(shè)置，保留可空。如數(shù)值型字段，如在該字段上需要與其他字段進行組合運算、或基于該字段進行統(tǒng)計匯總時，空值具有不可替代的重要作用。數(shù)據(jù)表中不應(yīng)存在重復(fù)數(shù)據(jù)值或列如客戶數(shù)據(jù)與客戶經(jīng)理數(shù)據(jù)存儲問題，如將二者放在同一張表中，為解決多個客戶經(jīng)理問題，表中必須設(shè)置首選客戶經(jīng)理、備選客戶經(jīng)理相關(guān)信息。在客戶經(jīng)理離職后，必須修改表中所有相關(guān)數(shù)據(jù)，造成很多不便且不利于追蹤。為此，在數(shù)據(jù)庫設(shè)計的時候要盡量避免現(xiàn)象發(fā)生，建議改變策略，將客戶信息存入一張表，客戶經(jīng)理信息存入一張表，而客戶與客戶經(jīng)理之間的聯(lián)系關(guān)系存入第三張表。當聯(lián)系關(guān)系發(fā)生變化時，僅需設(shè)置過期日期或狀態(tài)即可。數(shù)據(jù)表主鍵設(shè)置為無意義數(shù)值序列號在進行數(shù)據(jù)庫表設(shè)計的時候，采用一個無意義的、數(shù)值型的pkid對行記錄進行唯一的標識，而不是通過航班號、航段、姓名、工號等具有業(yè)務(wù)意義的字段區(qū)分記錄。由于人工管理下很難保證pkid值的不重復(fù)，為此建議每個表對應(yīng)的PKiD列的值由數(shù)據(jù)庫自動管理。建議在原有業(yè)務(wù)上需要唯一區(qū)分的字段或字段組合上，建立唯一性索引。以存儲10年的航班計劃表為例，業(yè)務(wù)上唯一區(qū)分一個航班的通常包含三個字段，航班號、航段、起飛日期，先前的做法是在這三個上建立復(fù)合主鍵。在此推薦用一個Number(8)的PKID列作為主鍵，原因是以每天600個航班、一年365天，10年總計219萬個航班，考慮到業(yè)務(wù)的擴展速度為10年內(nèi)翻三番,657萬，按理可設(shè)置7位，考慮到其它可能損耗的ID值，多設(shè)置一位，故為Number(8)；在航班數(shù)據(jù)的增刪改過程中，通常需要判斷是否存在重復(fù)數(shù)據(jù)，是則為修改操作，否則為插入操作，此時僅以PKID列無法區(qū)分，為此建議在航班號、航段、起飛日期上建立唯一性索引，由數(shù)據(jù)庫后臺執(zhí)行一致性校驗。利用Sequence生成的PKID若需要用于其他數(shù)據(jù)庫使用，需要在應(yīng)用層面記錄，以防止數(shù)據(jù)庫出現(xiàn)問題時恢復(fù)后sequenee不匹配問題。數(shù)據(jù)庫對象前綴名統(tǒng)一一個應(yīng)用系統(tǒng)對應(yīng)的數(shù)據(jù)庫表、視圖、過程等對象，通常均高達千計，為對其進行規(guī)范管理與快速定位，要求遵循數(shù)據(jù)對象命名規(guī)范。具體命名規(guī)范詳見下文第(二)點。數(shù)據(jù)表上避免設(shè)置外鍵外鍵的作用在于兩方面，一是保證主從表數(shù)據(jù)一致性，這是數(shù)據(jù)庫提供的、用于保證數(shù)據(jù)質(zhì)量的強制性手段，二是定義主表數(shù)據(jù)刪除時從表數(shù)據(jù)的級聯(lián)刪除方式；二者是外鍵的優(yōu)勢所在，減少了開發(fā)人員不分工作量。但是，外鍵的存在同樣帶來困擾。一是從應(yīng)用系統(tǒng)的三層架構(gòu)上考慮，如何設(shè)置外鍵關(guān)系到業(yè)務(wù)邏輯，這是將業(yè)務(wù)邏輯層部分功能后移至數(shù)據(jù)庫層，導(dǎo)致各層職責不清的緊耦合現(xiàn)象，而且當業(yè)務(wù)邏輯隨著需求發(fā)展發(fā)生變化將引起數(shù)據(jù)庫層面與應(yīng)用層面的雙重修改；二是在開發(fā)過程中，開發(fā)人員不得不了解數(shù)據(jù)庫物理設(shè)計細節(jié)，在項目協(xié)作分工上造成不便；三是從后續(xù)主數(shù)據(jù)、各業(yè)務(wù)方向公共數(shù)據(jù)建設(shè)上，為數(shù)據(jù)庫管理員在執(zhí)行數(shù)據(jù)表從“公有區(qū)”移入移出操作增加難度。為此，建議不設(shè)置外鍵；如果設(shè)置了外鍵，則僅保留數(shù)據(jù)一致性校驗功能，不建議設(shè)置級聯(lián)刪除的選項。此時要求開發(fā)人員通過培養(yǎng)一種良好的編程習慣，從程序邏輯上實現(xiàn)主表、從表數(shù)據(jù)的增、刪、改、查的規(guī)范操作。具體可參考如下說明。從表數(shù)據(jù)增加操作首先從主表上查詢是否存在所需數(shù)據(jù)，是則可進行增加操作；否則需先增加主表數(shù)據(jù)后才可進行從表數(shù)據(jù)增加操作。從表數(shù)據(jù)刪除操作直接刪除從表數(shù)據(jù)。當主表數(shù)據(jù)未被任何從表引用時，根據(jù)實際業(yè)務(wù)需要判斷是否級聯(lián)刪除主表數(shù)據(jù)。從表數(shù)據(jù)修改操作從表數(shù)據(jù)修改后的內(nèi)容必須是主表內(nèi)存在的數(shù)據(jù)，否則需先增加主表數(shù)據(jù)后才可進行數(shù)據(jù)修改操作。主表數(shù)據(jù)增加操作直接數(shù)據(jù)數(shù)據(jù)，與從表數(shù)據(jù)無關(guān)。主表數(shù)據(jù)刪除操作刪除之前判斷是否存在引用的從表數(shù)據(jù)，是則根據(jù)業(yè)務(wù)規(guī)則決定是否繼續(xù)，并根據(jù)業(yè)務(wù)規(guī)則判斷從表引用數(shù)據(jù)的處理方式為對應(yīng)數(shù)據(jù)項置空或刪除從表數(shù)據(jù)記錄。如果未存在引用的從表數(shù)據(jù)，則可直接刪除。注意保持事務(wù)的原子性，即主、從表數(shù)據(jù)同時提交或同時回滾。主表數(shù)據(jù)修改操作判斷是否存在引用的從表數(shù)據(jù)，是則首先修改從表數(shù)據(jù)，然后進行主表數(shù)據(jù)修改。注意保持事務(wù)的原子性，即主、從表數(shù)據(jù)同時提交或同時回滾。為了在應(yīng)用程序上能順利完成上述六項操作，開發(fā)人員除了嚴格記錄主從表調(diào)用關(guān)系外，還必須在程序上實現(xiàn)數(shù)據(jù)一致性檢查功能，以及時發(fā)現(xiàn)可能的數(shù)據(jù)不一致現(xiàn)象。如果應(yīng)用程序未能實現(xiàn)這種一致性檢查工作，則需將檢查規(guī)則提交予DBA，由DBA在后臺進行定期檢查。避免在數(shù)據(jù)庫上編寫存儲過程、代碼包、觸發(fā)器通常存儲過程、代碼包、觸發(fā)器包含很多業(yè)務(wù)邏輯，違背了三層架構(gòu)設(shè)計的松耦合原則。（二）數(shù)據(jù)對象命名規(guī)范命名規(guī)范總要求所有數(shù)據(jù)對象命名必須遵循如下規(guī)范要求。（1）通常采用“望文知義”的形式命名，部分對安全具有特殊要求的可以編號命名。（2）命名格式是，以字母打頭，包含具有特定含義的一個或多個英文單詞，單詞之間以下劃線“_”間隔，長度不超過30個字符；（3）大小寫不敏感。（4）禁止使用關(guān)鍵字命名。（5）不同數(shù)據(jù)對象命名的前綴要求如下表所示。編號數(shù)據(jù)對象前綴要求非臨時數(shù)據(jù)表“4位模塊名—表名”。臨時數(shù)據(jù)表“TMP表名”分區(qū)表“PT”主鍵“PK表名”索引“IDX”函數(shù)索引“FIDX”視圖“VW”實體化視圖“MV”存儲過程“SP”觸發(fā)器“TRG”函數(shù)“SF”數(shù)據(jù)包“PKG”序列“SEQ”數(shù)據(jù)類型“TYP”后臺任務(wù)“JB”2.表命名規(guī)范對于同一應(yīng)用，在命名上區(qū)分不同功能模塊的所使用的表。假設(shè)一個系統(tǒng)包含三個模塊，基礎(chǔ)維護、查詢、系統(tǒng)管理，則基礎(chǔ)維護表名前綴為“TB_BASE_”，查詢模塊表名前綴為“TB_QURY_”，后面加上具體的表名。對于一些特定作用的表，增加特定后綴表示。如歷史航班表可命名為TB_FLIGHT_HISTORY，復(fù)雜計算中間過程所產(chǎn)生的臨時數(shù)據(jù)表可命名為TB_MIDTERM_DATA_TEMP。字段命名規(guī)范字段命名規(guī)范符合命名規(guī)范總要求。字段類型選擇滿足（1）固定長度的字符串類型采用CHAR，非固定長度的字串類型采用VARCHAR2。（2）日期型字段采用日期類型。（3）數(shù)字型字段采用NUMBER類型，并標明長度與小數(shù)位數(shù)。（4）如無特殊需要，避免使用大字段（BLOB,CLOB,LONG等）。（5）相同業(yè)務(wù)字段出現(xiàn)在不同表中，使用相同命名，且保證類型和長度一致。索引命名規(guī)范復(fù)合索引至少包含前兩個字段的縮寫或全名。在表上創(chuàng)建主鍵時，必須顯式指定主鍵唯一性約束名稱。舉例：為表TB_FLIGHT創(chuàng)建主鍵，正確格式為“altertableTB_FLIGHTaddconstraintPK_FLIGHTprimarykey（FLIGHT_ID）”，語句中指定了主鍵的唯一性約束名為PK_FLIGHT。而錯誤的格式：“altertableTB_FLIGHTaddprimarykey（FLIGHT_ID）”此中，主鍵唯一性約束將被系統(tǒng)隨機分配一個名字。分區(qū)命名規(guī)范采用可容易判斷各分區(qū)內(nèi)所存數(shù)據(jù)內(nèi)容的名字，如對表TB_FLIGHT按季度進行分區(qū)，則各分區(qū)命名格式為“PT_XXXXQN”，其中XXXX為年份，N為1至4的季度編號。觸發(fā)器命名規(guī)范命名規(guī)則：trg_表名_<before/after>_<ins/upd/del>。如表TB_FLIGHT行插入后的觸發(fā)器名字為,TRG_TB_FLIGHT_AFTER」NS。（三）數(shù)據(jù)庫設(shè)計說明書格式數(shù)據(jù)庫設(shè)計說明書的格式至少包含如下部分。概述：從整體上說明數(shù)據(jù)庫設(shè)計思想、采用的方法、作用范圍、參考的文獻、文檔結(jié)構(gòu)組成等。數(shù)據(jù)表清單編號表英文名表中文名稱表描述使用的功能模塊1表12表2???

描述表之間存在的主外鍵聯(lián)系等。4.數(shù)據(jù)表定義1)表1字段編號字段名稱中文描述數(shù)據(jù)類型Null標志是否主鍵外鍵是否索引備注12???備注數(shù)據(jù)量增長說明、外鍵關(guān)系說明等2)表25.各系統(tǒng)相關(guān)的其它信息。要求在“數(shù)據(jù)表清單”每一行的表名上定義“超鏈接”指向“數(shù)據(jù)表定義”中的對應(yīng)內(nèi)容，以方便地在文檔中瀏覽跳轉(zhuǎn)等。四、數(shù)據(jù)庫編程規(guī)范（一）書寫規(guī)范關(guān)鍵字大寫，其他代碼統(tǒng)一使用小寫。確保變量和參數(shù)在類型和長度上與表數(shù)據(jù)列類型和長度相匹配。建議采用如下格式：vs_flightnotb_flight.flight_no%TYPE這樣當tB_flight表的fligHT_no字段定義發(fā)生變化時程序無需調(diào)整。參數(shù)和變量命名前綴遵循如下規(guī)范：項目前綴要求傳入?yún)?shù)“ip”傳出參數(shù)“op”數(shù)值型變量"vn”字符型變量“vs”日期型變量“vd”4.程序塊中的begin、end獨立成行。5.程序塊采用縮進風格書寫，保證代碼清晰易讀，縮進格數(shù)統(tǒng)一。一行僅寫一條語句。同一語句占用多行時，每行的第一個關(guān)鍵字左對齊。8.對于Insert…values和update語句，一行寫一個字段，字段后面緊跟注釋（注釋語句左對齊），values和insert左對齊，左括號和右括號與insert、values左對齊例：insertintotable_name（user_id,--用戶ID，主鍵user_name,--用戶名login_name--登錄名）values（v_user_id,v_user_name,v_login_name）相對獨立的程序塊之間增加一空行。超過110列的語句要分行書寫，長表達式應(yīng)在低優(yōu)先級操作符處換行，操作符或關(guān)鍵字放在新行之首。（二）注釋規(guī)范以統(tǒng)一格式在頭部對代碼進行注釋，以說明代碼所要完成功能、傳入?yún)?shù)、傳出參數(shù)、作者、創(chuàng)建時間，并在其后從修改日期、修改者、修改內(nèi)容角度描述各次修改歷史。/*功能描述：傳入?yún)?shù)：傳出參數(shù)：返回結(jié)果：作者：創(chuàng)建日期：修改歷史：（1）YYYY-MM-DD:作者1,修改內(nèi)容1；（2）YYYY-MM-DD:作者2,修改內(nèi)容2；*/在所有變量定義的右側(cè)或上方,進行注釋以說明變量的用途與含義。注釋內(nèi)容清晰、明了,并確保無二義性。對每一程序分支書寫注釋。在代碼的功能分支、子層次上注釋,以幫助維護人員理解代碼。例:casevs_type1when1then--里程獎勵when2then--里程扣除when3then--里程兌換endcase;代碼注釋應(yīng)放在描述的代碼上方或右方相近位置,不可放在下面。注釋與所描述的內(nèi)容進行字數(shù)的縮進排列。在函數(shù)中,對所返回的代碼進行詳細描述。在程序塊的結(jié)束行右方加注釋,以表示程序塊結(jié)束。（三）語法規(guī)范存儲過程的In、out參數(shù)應(yīng)按類別分開書寫,不要交叉。存儲過程中變量的聲明應(yīng)集中在is和begin關(guān)鍵字之間申明盡可能使用相關(guān)表字段類型來定義相關(guān)的變量,如%type,%rowtype。刪除無用的變量與參數(shù)。存儲過程中存在多分支時,若有事務(wù)控制,需確保各分支均有事務(wù)結(jié)束處理,且發(fā)生異常時,必須Exception中進行異常捕獲。（四）SQL性能規(guī)范1.避免在批量操作時頻繁使用commit頻繁的COMMIT會導(dǎo)致物理I/O增大，同時會產(chǎn)生logfilesync等待，但長時間不提交將帶來更多的性能問題。建議小于3秒的事務(wù)可以一次提交,大于3秒的操作盡可能3秒左右提交一次。實際應(yīng)用中使用COMMIT時必須保證事務(wù)的完整性。使用動態(tài)sql時，需綁定變量，而非實際值。為了不重復(fù)解析相同的SQL語句，在第一次解析之后，ORACLE將SQL語句緩存在內(nèi)存中，當你執(zhí)行一個SQL語句時，如果它和之前的執(zhí)行過的語句完全相同，ORACLE就能很快獲得內(nèi)存中相同語句的執(zhí)行計劃，然后就直接執(zhí)行該語句，而不需要進行解析。如果沒有使用綁定變量，雖然語句結(jié)構(gòu)相同，但因傳入的變量不一樣導(dǎo)致語句不相同，ORACLE還是需要對每條結(jié)構(gòu)相同的語句重新解析一遍，同時在內(nèi)存中緩存該語句，導(dǎo)致共享池中存放了N條結(jié)構(gòu)相同的語句，既達不到共享的目的，又浪費了大量的內(nèi)存空間，如果語句執(zhí)行的太頻繁，會導(dǎo)致數(shù)據(jù)庫無法申請共享內(nèi)存而出錯。避免不必要的排序?qū)Σ樵兘Y(jié)果進行排序會大大的降低系統(tǒng)的性能用WHERE子句替換HAVING子句例如：SELECTNAME，SUM(AGE)FROMEMPLOYEEGROUPBYNAMEHAVINGNAME！=‘ABC'修改為以下語句效果更好SELECTNAME，SUM(AGE)FROMEMPLOYEEWHERENAME！=‘ABC'GROUPBYNAME用“>=”替代“>”女如在ID列上建有索引，則語句SELECT*FROMEMPLOYEEWHEREID>=9要比語句SELECT*FROMEMPLOYEEWHEREID>8高效。這是由于前者DBMS將直接跳到第一個ID等于9的記錄而后者將首先定位到8的記錄并且向前掃描到第一個DEPT大于8的記錄。刪除表中所有記錄時用TRUNCATE替代DELETE當刪除表中的記錄時，在通常情況下，回滾段用來存放可以被恢復(fù)的信息，如果你沒有COMMIT事務(wù)，ORACLE可以將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)；而當運用TRUNCATE時，回滾段不存放任何可被用于恢復(fù)的信息，當命令運行后，數(shù)據(jù)不能被恢復(fù),因此很少的資源被調(diào)用，執(zhí)行時間也會很短,空間立即釋放，detele操作后的空間可以被重新利用，但不會釋放。用UNION-ALL代替UNION說明：UNION-ALL不過慮重復(fù)記錄，UNION過濾重復(fù)記錄，所以需要先排序。如果不需要過濾重復(fù)的記錄，UNION-ALL比UNION性能更好。用(NOT)EXISTS替代(NOT)IN。在許多基于驅(qū)動表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯(lián)接。在這種情況下，使用EXISTS(或NOTEXISTS)通常將提高查詢的效率。例如：SELECT*FROMEMPWHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC='MELB')沒有以下語句高效SELECT*FROMEMPWHEREEMPNO>0ANDEXISTS(SELECT'X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC='MELB')使用DECODE函數(shù)來減少處理時間。使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。例：SELECTNAME|‘|UNDERAGE'FROMEMPLOYEEWHEREAGE<18UNIONSELECTNAME|‘|OVERAGE'FROMEMPLOYEEWHEREAGE>=18可以改成：SELECTDECODE(SIGN(AGE-18),-1,NAME||‘UNDERAGE'，1，NAME||‘OVERAGE')FROMEMPLOYEE使用表的別名(Alias)。當在SQL語句中連接多個表時，應(yīng)使用表的別名并把別名前綴于每個列上，這樣可以減少解析的時間，并減少那些由Column歧義引起的語法錯誤。盡量減少對表的查詢次數(shù)。說明：在含有子查詢的SQL語句中，要特別注意減少對表的查詢。例：UPDATEEMPSETEMP_CAT=(SELECTMAX(CTEGORY)FROMEMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT='0020'改寫成下面這樣會更高效:UPDATEEMPSETEMP_CAT,SAL_RANGE=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=‘0020'用表連接替換EXISTS。例：SELECTENAMEFROMEMPEWHEREEXISTS(SELECT'X'FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT='A');改寫成下面這樣會更高效:SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT='A';避免使用DISTINCT，可以用EXISTS替換DISTINCT。當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT.一般可以考慮用EXIST替換例:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO改寫成下面這樣會更高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT'X'FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);EXISTS使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果。避免使用耗費資源的操作帶有DISTINCT，UNION，MINUS,INTERSECT，ORDERBY,GROUPBY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能，如果可能，應(yīng)盡量避免使用或改寫。避免對索引列使用數(shù)據(jù)庫函數(shù)、計算表達式等等對查詢索引列使用數(shù)據(jù)庫函數(shù)、計算表達式會造成對索引失效例：selectt.reserver1,count(*)coutfromcustcare.password_login_failtwheret.operating_source=1andto_char(t.login_time,'yyyymmdd')betweenandgroupbyt.reserver1havingcount(*)>30custcare.password_login_fail表的login_time列上建了索引，上面語句to_char函數(shù)會造成索引失效，把andto_char(t.login_time,'yyyymmdd')betweenand修改成：andt.login_timebetweento_date('','yyyymmdd')andto_date('','yyyymmdd')可以正確索引。查詢時盡可能將操作移至等式的右邊，甚至去掉函數(shù)。在查詢條件中，避免不必要的類型轉(zhuǎn)換。字符類型列賦予數(shù)值型數(shù)據(jù)時會使用隱含類型轉(zhuǎn)換，而數(shù)值型列賦予字符型數(shù)據(jù)時不會隱含類型轉(zhuǎn)換，而是將右邊值轉(zhuǎn)換為數(shù)值類型。例：select*fromatp_para_cfgwherecfg_type=35cfg_type是字符型列，上面語句會造成隱含類型轉(zhuǎn)換to_number(cfg_type)=35，即Oracle內(nèi)部會做一次類型轉(zhuǎn)換。盡量避免字段與“NULL”比較索引并不存儲null值，使用NULL作為條件將不會使用索引。例：select*fromstaff_memberwhereaddressisnull；(或notnull)該語句將不會使用索引在索引列上使用<>(!=)和like將不會使用索引。例：select*fromstaff_memberwheredept_no<>2001;select*fromstaff_memberwherefirst_namelike‘%DON';以上語句都不會用到索引注:like語句改為select*fromstaff_memberwherefirst_namelike‘DON%';當前面沒有％時，將使用索引，ORACLE內(nèi)部轉(zhuǎn)換為范圍操作用Case語句合并多重掃描例：selectcount(*)fromempwheresal<1000;selectcount(*)fromempwheresalbetween1000and5000;selectcount(*)fromempwheresal>5000;這樣我們需要進行三次全表查詢，但是如果我們使用case語句：selectcount(salewhensal<1000then1elsenullend)count_poor,count(salewhenbetween1000and5000then1elsenullend)count_blue_collar,count(salewhensal>5000then1elsenullend)count_poorfromemp;這樣查詢的結(jié)果一樣，但是執(zhí)行計劃只進行了一次全表查詢。JOB使用規(guī)范Job的執(zhí)行時間盡量避開業(yè)務(wù)高峰；如果有多個占用資源較多的job任務(wù)，考慮將它們執(zhí)行的時間錯開；禁止在job運行期間對其調(diào)用的存儲過程進行修改和編譯。在Oracle10g之后，利用dbms_schedule建立Job。索引使用規(guī)范1.索引創(chuàng)建原則（1）表的主鍵、外鍵必須有索引；（2）數(shù)據(jù)量超過300條的表應(yīng)該有索引；（3）經(jīng)常與其他表進行連接的表，在連接字段上應(yīng)該建立索引；（4）經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；（5）索引應(yīng)該建在選擇性高的字段上；（6）索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；（7）復(fù)合索引的建立需要進行仔細分析；盡量考慮用單字段索引代替：＞正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；＞復(fù)合索引的幾個字段是否經(jīng)常同時以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；＞如果復(fù)合索引中包含的字段經(jīng)常單獨出現(xiàn)在Where子句中，則分解為多個單字段索引；＞如果復(fù)合索引所包含的字段超過3個，那么仔細考慮其必要性，考慮減少復(fù)合的字段；（8）頻繁進行數(shù)據(jù)增刪改操作的表，不要建立太多的索引；（9）刪除無用的索引，避免對執(zhí)行計劃造成負面影響；索引使用建議（1）避免對條件字段進行操作對條件字段的操作會導(dǎo)致該列的索引失效，這里所謂的操作包括數(shù)據(jù)庫函數(shù)計算表達式等。如以下3個sql,其條件列上都建有索引，但不會被使用：select*fromrecordwheresubstrb（CardNo,1,4）='5378'--（13秒）select*fromrecordwhereamount/30＜1000--（11秒）select*fromrecordwhereto_char（ActionTime,'yyyymmdd'）=''--（10秒）可改為如下形式：select*fromrecordwhereCardNolike'5378%'--（＜1秒）select*fromrecordwhereamount＜1000*30--（＜1秒）select*fromrecordwhereActionTime=to_date（'','yyyymmdd'）--（＜1秒）（2）避免隱式轉(zhuǎn)換對于條件值書寫不規(guī)范的，oracle會自動對條件列進行隱式轉(zhuǎn)換，以完成比較，但同時也會造成索引失效。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會自動將字符型用to_number（）函數(shù)進行轉(zhuǎn)換，從而導(dǎo)致全表掃描。例：表tabl中的列coll是字符型（char），則以下語句存在類型轉(zhuǎn)換：selectcol1,col2fromtab1wherecol1>10，應(yīng)該寫為：selectcol1,col2fromtab1wherecol1>'10'。（3）盡量避免“<>”盡量去掉"<>"，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍固定，則修改為"OR"方式。例：UPDATESERVICEINFOSETSTATE=0WHERESTATE<>0;以上語句由于其中包含了"<>",執(zhí)行計劃中用了全表掃描（TABLEACCESSFULL），沒有用到state字段上的索引。實際應(yīng)用中，由于業(yè)務(wù)邏輯的限制，字段state為枚舉值，只能等于0，1或2,而且，值等于=1,2的很少，因此可以去掉"<>"，利用索引來提高效率。修改為：UPDATESERVICEINFOSETSTATE=0WHERESTATE=1ORSTATE=2。（4）避免Where子句中的ISNULL和ISNOTNULLWhere字句中的ISNULL和ISNOTNULL將不會使用索引而是進行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，去掉Where子句中的ISNULL和ISNOTNULL。（5）數(shù)據(jù)分布不均勻列的索引使用當列值選擇性不高，但查詢返回值恰好是返回較少的數(shù)值，則應(yīng)創(chuàng)建索引。例：表ServiceInfo中數(shù)據(jù)量很大，有一百萬行，其中有一個字段DisposalCourseFlag,取值范圍為枚舉值：［0,1,2,3,4,5,6,7］。按照前面說的索引建立的規(guī)則，“選擇性不高的字段不應(yīng)該建立索引，該字段只有8種取值，索引值的重復(fù)率很高，索引選擇性明顯很低。然而，由于該字段上數(shù)據(jù)值的分布情況非常特殊，具體如下表：取值范圍1~567占總數(shù)據(jù)量的百分比1%98%1%而且，常用的查詢中，查詢DisposalCourseFlag<6的情況既多又頻繁，毫無疑問，如果能夠建立索引，并且被應(yīng)用，那么將大大提高這種情況的查詢效率。（6）需要時利用HINT強制指定索引對于ORACLE優(yōu)化器無法用上合理索引的情況下，一方面可以單獨對該字段或該表用analyze語句進行分析，對該列搜集足夠的統(tǒng)計數(shù)據(jù)，使ORACLE在查詢選擇性較高的值時能用上索引；另一方面，可以利用HINT提示，在SELECT關(guān)鍵字后面，加上“/*+INDEX（表名稱，索引名稱）*/”的方式，強制ORACLE優(yōu)化器用上該索引。例：select/*+index（mms_cdrIDX_MMS_USERID）*/*FROMmms_cdrWHERE（month_number>=6andmonth_number<=7）ANDuser_id=to_number（'00741621'）ANDsend_time>='000000'ANDsend_time<='235959'ORDERBYsend_time;當user_id和send_time列上均有索引的情況下使用user_id列上的索引更高效，因為user_id列上的索引選擇性更高，但是由于統(tǒng)計信息不全，優(yōu)化器可能選擇使用全表掃描或者走send_time列上的索引，導(dǎo)致執(zhí)行計劃不是最優(yōu)，此時可以使用強制索引來解決。（7）屏蔽無用索引如果了解到條件字段建有索引，但返回值較多，如果走索引的話效率更低，則可通過特殊方法屏蔽掉索引的使用。如字段為數(shù)值型的就在表達式的字段名后添加“+0”，為字符型的就并上空串：“||""”例：select*fromserviceinfowhereDisposalCourseFlag+0=6andworkNo='36'（8）like子句盡量前端匹配當like字句前段為通配符，會導(dǎo)致索引失效。如：select*fromcitywherenamelike‘%S%';--該語句執(zhí)行全表掃描修改為：select*fromcitywherenamelike‘S%';--會正確使用索引（9）使用基于函數(shù)的索引對條件列的操作可以使索引失效，但如果根據(jù)操作創(chuàng)建相應(yīng)索引，則可以被使用。女口：select*fromempwheresubstr（ename,1,2）='SM';在ename列上的索引將不會被使用，如果創(chuàng)建基于函數(shù)的索引，即：createindexemp_ename_substroneemp（substr（ename,1,2））;則在查詢的時候可正確使用到索引。使用函數(shù)索引有如下限制：＞無法索引LOB字段＞無法索引嵌套表字段＞表達式不允許有分組函數(shù)，如SUM,AVG等＞無法使用基于規(guī)則的優(yōu)化器＞無法儲存空值＞對于一個輸入，要求函數(shù)返回同一值，即確定性＞當函數(shù)改變時，要重建函數(shù)索引（10）使用分區(qū)索引在用分析命令對分區(qū)索引進行分析時，每一個分區(qū)的數(shù)據(jù)值的范圍信息會放入Oracle的數(shù)據(jù)字典中。Oracle可以利用這個信息來提取出那些只與SQL查詢相關(guān)的數(shù)據(jù)分區(qū)。例如，假設(shè)你已經(jīng)定義了一個分區(qū)索引，并且某個SQL語句需要在一個索引分區(qū)中進行一次索引掃描。Oracle會僅僅訪問這個索引分區(qū)，而且會在這個分區(qū)上調(diào)用一個此索引范圍的快速全掃描。因為不需要訪問整個索引，所以提高了查詢的速度。但是當查詢跨多個分區(qū)，涉及到比較大小時，考慮使用全局索引。（11）使用位圖索引當列值的選擇性非常低時，比如性別字段只有男與女兩個值，可以考慮使用位圖索引。位圖索引占用空間非常小，效率較高。位圖索引適合于不會變化的表，如果dml操作頻繁，則極易產(chǎn)生大面積鎖。（12）使用復(fù)合索引當幾個字段經(jīng)常同時以and方式出現(xiàn)在where子句中，可以建立復(fù)合索引；將最常用的列放在索引最前面；復(fù)合索引字段不宜過多，盡量在3個以內(nèi)。（13）少數(shù)情況下用全表掃描替代索引掃描在大多數(shù)情況下，全表掃描可能會導(dǎo)致更多的物理磁盤輸入輸出，但是全表掃描有時又可能會因為高度并行化的存在而執(zhí)行的更快。如果查詢的表完全沒有順序，那么一個要返回記錄數(shù)小于10％的查詢可能會讀取表中大部分的數(shù)據(jù)塊，這樣使用索引會使查詢效率提高很多。但是如果表非常有順序，那么如果查詢的記錄數(shù)大于40％時，可能使用全表掃描更快。因此，有一個索引范圍掃描的總體原則是：＞對于原始排序的表：僅讀取少于表記錄數(shù)40%的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的40％的查詢應(yīng)該使用全表掃描。＞對于未排序的表：僅讀取少于表記錄數(shù)7%的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的7%的查詢應(yīng)該使用全表掃描。（七）分區(qū)表使用規(guī)范當表的數(shù)據(jù)量達到1G或者1000萬行時，考慮將表分區(qū)；分區(qū)鍵的選擇參考業(yè)務(wù)查詢要求，通常是時間字段，以range方式來劃分。分區(qū)間隔也要看業(yè)務(wù)需要，比如單次查詢總是以天為單位，則按天分區(qū)，如果總是以小時為單位，則按小時分區(qū)，但并非分得越細越好；如果插入數(shù)據(jù)所需要的分區(qū)不存在，則插入失敗，所以要制定分區(qū)維護策略。通常有兩種方式，一種是提前劃分，比如把一年的分區(qū)一次性劃分出來，用完時再劃一年，另一種是編寫分區(qū)維護存儲過程自動添加或刪除分區(qū)。五、數(shù)據(jù)庫物理設(shè)計規(guī)范（一）環(huán)境配置數(shù)據(jù)庫環(huán)境包含生產(chǎn)、開發(fā)、測試三種，各種環(huán)境之間的配置原則為：生產(chǎn)、開發(fā)環(huán)境分離；生產(chǎn)、開發(fā)職責分離；由于一個業(yè)務(wù)方向配置一套生產(chǎn)庫集群，不同業(yè)務(wù)方向數(shù)據(jù)交互通過中間件平臺實現(xiàn)，不直接使用DBLlink訪問。一個生產(chǎn)應(yīng)用對應(yīng)一個開發(fā)環(huán)境。測試環(huán)境根據(jù)實際需要部署。各種環(huán)境的硬件需求（CPU頻率，內(nèi)存容量，磁盤容量等）及軟件需求（操作系統(tǒng)版本及內(nèi)核參數(shù)，交換分區(qū)大小，用戶權(quán)限等）詳見可參考業(yè)務(wù)實際需要及Oracle在各操作系統(tǒng)下的官方安裝文檔。（二）相關(guān)命名要求服務(wù)名命名要求：以字母開頭、不超過8個字符；如為集群數(shù)據(jù)庫，各節(jié)點實例名以‘集群服務(wù)名'后加序號組成。表空間命名要求：以用戶名、主要存儲對象類型組成。數(shù)據(jù)文件命名要求：以表空間名后加序號組成，擴展名為“*DBF”控制文件命名要求：以“CTL”為前綴，后加序號組成,擴展名為“*CTL”日志文件命名要求：以“LOG”為前綴，后加日志組號、文件編號組成，擴展名為“*.RDO”臨時文件命名要求：以“TMP”為前綴，后加文件編號組成，擴展名為“*TMP”。為保證OEM（OracleEnterpriseManager，企業(yè)管理器）的安裝，主機名不得包含大寫字母，以及“_”。對于RAC的安裝，要求主機名長度盡量小，在6位以內(nèi)，同時不得為大寫，不要包含“-”和“_”。（三）數(shù)據(jù)庫配置數(shù)據(jù)庫連接類型通常使用專用數(shù)據(jù)庫連接。SGA配置初次設(shè)置為系統(tǒng)內(nèi)存60%，在數(shù)據(jù)庫運行期間根據(jù)運行性能調(diào)整。PGA配置初次設(shè)置為系統(tǒng)內(nèi)存20%或大于1G,在數(shù)據(jù)庫運行期間根據(jù)運行性能調(diào)整。數(shù)據(jù)庫字符集統(tǒng)一使用“SIMPLIFIEDCHINESE_CHINA.ZHS16GBK”字符集。數(shù)據(jù)塊大小為操作系統(tǒng)塊大?。阍O(shè)備塊大小512字節(jié)，NTFS塊大小4K）整數(shù)倍,默認取值為8K。DB_FILESDb_files是數(shù)據(jù)庫能夠同時打開的文件數(shù)量，默認值是200個。當數(shù)據(jù)庫規(guī)劃時文件數(shù)量FILES接近或超過200個時候，按以下估計值配置：DB_FILES=FILES*1.5SGA_TARGET設(shè)置該項即打開SGA自動管理，建議使用。PROCESSES:操作系統(tǒng)層所允許連接的最大進程數(shù)（包含會話數(shù)），建議其值至少設(shè)置為500。SESSIONS:數(shù)據(jù)庫最大會話數(shù)，其值為（系統(tǒng)最大并發(fā)連接數(shù)+后臺進程數(shù)）*1.1,如果未指定，則系統(tǒng)根據(jù)PROCESSES參數(shù)值計算其默認值。OPEN_CURSORS:由用戶可能同時打開的游標數(shù)、會話數(shù)決定，建議其值至少設(shè)置為1000；OPTIMIZE_MODE：默認值為EXACT，為提高執(zhí)行計劃復(fù)用度可設(shè)置為CHOOSE。USER_DUMP_DEST、BACKGROUDD_DUMP_DEST、CORE_DUMP_DEST:此三個參數(shù)用于定義系統(tǒng)跟蹤文件的存放位置,建議指向數(shù)據(jù)庫系統(tǒng)主目錄下的三個分目錄。GOLBAL_NAMES：取值為真時，客戶端TNS配置中的服務(wù)名須為“數(shù)據(jù)庫服務(wù)名.域名”方可正常連接；取值為假時，用“數(shù)據(jù)庫服務(wù)名”即可。（四）數(shù)據(jù)庫文件管理控制文件（1）為保證安全及性能，使用多個控制文件，并放置在不同物理位置。（2）控制文件中參數(shù)設(shè)置，最大的數(shù)據(jù)文件數(shù)量不能小于初始化參數(shù)db_files。日志文件（1）日志文件大小日志文件的大小由數(shù)據(jù)庫事務(wù)處理量決定，在設(shè)計過程中，確保每20分鐘切換一個日志文件。所以對于業(yè)