Oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范建議

Oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范建議oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口1目的本規(guī)范的主要目的是希望規(guī)范數(shù)據(jù)庫設(shè)計(jì)，盡量提前避免由于數(shù)據(jù)庫設(shè)計(jì)不當(dāng)而產(chǎn)生的麻煩；同時好的規(guī)范，在執(zhí)行的時候可以培養(yǎng)出好的習(xí)慣，好的習(xí)慣是軟件質(zhì)量的很好的保證。數(shù)據(jù)庫設(shè)計(jì)是指對于一個給定的應(yīng)用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)庫模式，建立數(shù)據(jù)庫及其應(yīng)用系統(tǒng)，有效存儲數(shù)據(jù)，滿足用戶信息要求和處理要求。2適用范圍本規(guī)范的適用人員范圍包括我司的所有應(yīng)用開發(fā)人員以及在我司承接數(shù)據(jù)庫應(yīng)用開發(fā)的軟件人員。本規(guī)范的適用IT范圍包括數(shù)據(jù)庫對象的命名規(guī)范、設(shè)計(jì)原則、SQL語句的設(shè)計(jì)和使用、SQL語句的性能優(yōu)化建議、其他與性能有關(guān)的設(shè)計(jì)原則以及設(shè)計(jì)工具的選擇。3數(shù)據(jù)對象的命名規(guī)范通用規(guī)范使用英文：要用簡單明了的英文單詞，不要用拼音，特別是拼音縮寫。主要目的很明確，讓人容易明白這個對象是做什么用的；一律大寫，特別是表名：有些數(shù)據(jù)庫，表的命名乃至其他數(shù)據(jù)對象的命名是大小寫敏感的，為了避免不必要的麻煩，并且尊重通常的習(xí)慣，最好一律用大寫；數(shù)據(jù)庫對象命名規(guī)范表的命名表名的前綴：前綴一表名_T。為表的名稱增加一個或者多個前綴，前綴名不要太長，可以用縮寫，最好用下劃線與后面的單詞分開；其目的有這樣幾個：為了不與其他項(xiàng)目或者其他系統(tǒng)、子系統(tǒng)的表重名；表示某種從屬關(guān)系，比如表明是屬于某個子系統(tǒng)、某個模塊或者某個項(xiàng)目等等。表示這種從屬關(guān)系的一個主要目的是，從表名能夠大概知道如何去找相關(guān)的人員。比如以子系統(tǒng)為前綴的，當(dāng)看到這個表的時候，就知道有問題可以去找該子系統(tǒng)的開發(fā)和使用人員；視圖命名：相關(guān)表名_V（或者根據(jù)需要另取名字）；口程序包命名：程序包名_PKG（用英文表達(dá)程序包意義）；口存儲過程命名：存儲過程名_PRO（用英文表達(dá)存儲過程意義）；口oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口函數(shù)命名：函數(shù)名稱_FUN（用英文表達(dá)函數(shù)作用）；口觸發(fā)器命名：觸發(fā)器名稱_TRI（用英文表達(dá)觸發(fā)器作用）；口索引命名：表名.字段名_ID某（如果存在多字段索引，取每字段前三個字符加下劃線組合，如在cutom,cutting,curtail上建立聯(lián)合索引，命名為表名_cu_cut_cur_ID某，如果前三個截取字符相同，就從字段名稱中不同的字符開始取三個字符加下劃線組合，如在cutid,cutom,cutname上建立聯(lián)合索引，就命名為表_tid_tom_tna_ID某;口唯一索引命名：表名一字段名_UNI（如果存在多字段唯一索引，取每字段前三個字符加下劃線組合，如在cutom,cutting,curtail上建立唯一索引，命名為表名_cu_cut_cur_UNI,如果前三個截取字符相同，就從字段名稱中不同的字符開始取三個字符加下劃線組合，如：在cutid,cutom,cutname上建立唯一索引，命名：表_tid_tom_tna_UNI;口主鍵命名：表名一字段名_PK（如果存在多字段主鍵，取每字段前三個字符加下劃線組合，如在cutom,cutting,curtail上建立主鍵，命名為表名_cu_cut_cur_PK,如果前三個截取字符相同，就從字段名稱中不同的字符開始取三個字符加下劃線組合，如在cutid,cutom,cutname上建立主鍵，命名：表_tid_tom_tna_PK;口外鍵命名：表名—主表名一字段名_FK；口equence命名：表名一列名_SEQ（或者根據(jù)需要另取名字）；口ynonym命名：與對應(yīng)的數(shù)據(jù)庫對象同名；口AVA命名：遵守公司相應(yīng)的JAVA命名規(guī)范；口4數(shù)據(jù)庫對象設(shè)計(jì)原則表的設(shè)計(jì)主、外鍵每個表，都必須要有主鍵。主鍵是每行數(shù)據(jù)的唯一標(biāo)識，保證主鍵不可隨意更新修改，在不知道是否需要主鍵的時候，請加上主鍵，它會為你的程序以及將來查找數(shù)據(jù)中的錯誤等等，提供一定的幫助；oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口一個表的某列與另一表有關(guān)聯(lián)關(guān)系的時候，如果加得上的話，請加上外鍵約束。外鍵是很重要的，所以要特別強(qiáng)調(diào)：適量建外鍵。為了保證外鍵的一致性，數(shù)據(jù)庫會增加一些開銷，如果有確鑿的并且是對性能影響到無法滿足用戶需求的證據(jù)，可以考慮不建外鍵。否則，還是應(yīng)該建外鍵；不要以數(shù)據(jù)操作不方便為理由而不建外鍵。是的，加上外鍵以后，一些數(shù)據(jù)操作變得有些麻煩，但是這正是對數(shù)據(jù)一致性的保護(hù)。正是因?yàn)檫@種保護(hù)很有效，所以最好不要拒絕它；以缺省的方式建立外鍵（即用deleteretrict方式），以達(dá)到保護(hù)數(shù)據(jù)一致性的目的；外鍵在保護(hù)數(shù)據(jù)一致方面非常有效。如果不建外鍵，數(shù)據(jù)庫中容易出現(xiàn)垃圾數(shù)據(jù)，并且無人知曉。當(dāng)數(shù)據(jù)量很大的時候，查找這些垃圾數(shù)據(jù)也是相當(dāng)困難的。而應(yīng)用程序在設(shè)計(jì)時，往往沒有考慮或者也無法照顧到垃圾數(shù)據(jù)。因此垃圾數(shù)據(jù)很可能造成應(yīng)用程序工作不正常，并且表現(xiàn)出來的現(xiàn)象會很奇怪，讓人摸不著頭腦。列的設(shè)計(jì)字段的寬度要在一定時間內(nèi)足夠用，但也不要過寬，占用過多的存儲空間,對于長度不確定的列，采用可變長度的數(shù)據(jù)類型如varchar類型；口字段的類型及寬度在設(shè)計(jì)以及后面進(jìn)行開發(fā)時，往往要與應(yīng)用的設(shè)計(jì)、開發(fā)人員商討，以得到雙方認(rèn)可的類型及寬度；除非必要，否則盡量不加冗余列。所謂冗余列，是指能通過其他列計(jì)算出來的列，或者是與某列表達(dá)同一含義的列，或者是從其他表復(fù)制過來的列等等。冗余列需要應(yīng)用程序來維護(hù)一致性，相關(guān)列的值改變的時候，冗余列也需要隨之修改，而這一規(guī)則未必所有人都知道，就有可能因此發(fā)生不一致的情況。如果是應(yīng)用的特殊需要，或者是為了優(yōu)化某些邏輯很復(fù)雜的查詢等操作，可以加冗余列；4.1.3.3除非必要，否則盡量不使用LONG,TE某T,BLOB,CLOB,NCLOB,LONG,LONGRAW這一類的數(shù)據(jù)類型，而是使用其他可以替代的數(shù)據(jù)類型；優(yōu)先使用varchar2類型替代CHAR類型，除非列寬有嚴(yán)格的要求而且得到應(yīng)用嚴(yán)格支持；4.1.3記錄數(shù)單表的記錄數(shù)一般控制在兩千萬條（參考值，各應(yīng)用可以根據(jù)實(shí)際情況進(jìn)行適量調(diào)整）以內(nèi)；oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口記錄數(shù)在兩千萬和兩億條之間的表一定要采用分區(qū)技術(shù)，并根據(jù)應(yīng)用的使用情況創(chuàng)建合適的分區(qū)標(biāo)準(zhǔn)，單個分區(qū)內(nèi)的記錄數(shù)一般控制在兩千萬條（參考值，各應(yīng)用可以根據(jù)實(shí)際情況進(jìn)行適量調(diào)整）以內(nèi)，同時表的索引使用對應(yīng)的分區(qū)索引；記錄數(shù)超過兩億條的表一定要考慮信息生命周期，必須考慮歷史數(shù)據(jù)的剝離，并在應(yīng)用設(shè)計(jì)中完成對歷史數(shù)據(jù)的相應(yīng)處理功能（歷史數(shù)據(jù)的剝離規(guī)則須經(jīng)業(yè)務(wù)使用部門的確認(rèn)）；索引的設(shè)計(jì)索引是從數(shù)據(jù)庫中獲取數(shù)據(jù)的最高效方式之一。95%的數(shù)據(jù)庫性能問題都可以采用索引技術(shù)得到解決。但大量的DML操作（增刪改查）會增加系統(tǒng)對索引的維護(hù)成本，對性能會有一定影響，對于插入相當(dāng)頻繁的表要慎重建索引，索引也會占相當(dāng)?shù)拇鎯臻g，所以要根據(jù)硬件環(huán)境和應(yīng)用需求在空間和時間上達(dá)到最好的平衡點(diǎn)，主要原則：適當(dāng)利用索引提高查詢速度：當(dāng)數(shù)據(jù)量比較大，了解應(yīng)用程序的會有哪些查詢，依據(jù)這些查詢需求建相應(yīng)的索引；最好親自試驗(yàn)一下，模擬一下生產(chǎn)環(huán)境的數(shù)據(jù)量，在此數(shù)據(jù)量下，比較一下建索引前后的查詢速度；索引對性能會有一定影響，對于DML頻繁列的索引要定期維護(hù)（重建）。但是，索引的結(jié)構(gòu)對于索引的更新（比如在插入數(shù)據(jù)的時候）是有一定優(yōu)化的，所以不要在沒有試驗(yàn)以前過分夸大它對性能的影響。最終還是以試驗(yàn)為準(zhǔn)；不要建實(shí)際用不上的索引，與上條相關(guān)，如果建的索引并不提高任何一應(yīng)用中的查詢速度，則要把它刪除；有些數(shù)據(jù)庫有相關(guān)工具可以發(fā)現(xiàn)實(shí)際未被使用的索引，可以利用一下；索引類型的選擇：要根據(jù)數(shù)據(jù)分布及應(yīng)用來決定如何建立索引，一般的高基數(shù)數(shù)據(jù)列（高基數(shù)數(shù)據(jù)列是指該列有很多不同的值）時,建立BTree索引（一般數(shù)據(jù)庫索引的缺省類型）；當(dāng)?shù)突鶖?shù)數(shù)據(jù)列（該列有大量相同的值）時，可以考慮建立位圖索引（如果所選數(shù)據(jù)庫支持的話），但位圖索引是壓縮類型索引，所以DML（增、刪、改）的代價更高，要綜合考慮；索引列的選擇：如果檢索條件有可能包含多列，創(chuàng)建聯(lián)合主鍵或者聯(lián)合索引，把最常用于檢索條件的列放在最前端，其他的列排在后面；不要索引使用頻繁的小型表，假如這些小表有頻繁的DML就更不要建立索引，維護(hù)索引的代價遠(yuǎn)遠(yuǎn)高于掃描表的代價；主鍵索引在建立的時候一定要明確的指定名稱，不能讓系統(tǒng)默認(rèn)建立主鍵索引（可能有些數(shù)據(jù)庫無法指定主鍵名，則例外）；外鍵必須需建索引。當(dāng)有一定數(shù)據(jù)量，并且經(jīng)常以外鍵所在列為關(guān)聯(lián)，進(jìn)行oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口關(guān)聯(lián)查詢時，需要建索引（可能有些數(shù)據(jù)庫自動為外鍵建索引，則例外）；當(dāng)有聯(lián)合主鍵或者聯(lián)合索引時，注意不要建重復(fù)的索引。舉例說明：聯(lián)合索引的用法：表EMPLOYEES,它的主鍵是建立在列DEPARTID和EMPLOYEEID上的聯(lián)合主鍵，并且創(chuàng)建主鍵的語句中DEPARTID在前，EMPLOYEEID在后。在這樣一個表里，通常就沒有必要再為DEPARTID建一個索引了；聯(lián)合索引的情況也一樣；更復(fù)雜的情況，比如表EMPLOYEES，有一個索引建立在列CORPID,DEPARTID,EMPLOYEEID三列上，在創(chuàng)建語句中也依據(jù)上述順序，就沒有必要再為CORPID建立索引；也沒有必要再建立以CORPID在前，DEPARTID在后的聯(lián)合索引；如果EMPLOYEEID需要索引，那么為EMPLOYEEID建立一個索引是不與上面的索引重復(fù)的；DEPARTID列也類似；口控制一個表的索引數(shù)量，盡量使得一個表的索引數(shù)量小于五個；視圖的設(shè)計(jì)在不太清楚視圖用法的情況下，盡量不建。因?yàn)橐坏┙耍陀斜粸E用的危險(xiǎn)；如果需要建視圖，只要是打算長期使用的，請寫入數(shù)據(jù)庫設(shè)計(jì)中。明確它的用途、目的；建立視圖時要明確寫出所有要選擇出的列名而不要以SELECT某來代替，可以使結(jié)構(gòu)清晰可讀性增強(qiáng)，也不會增加它對表的所有字段的依賴，而表是很可能修改的，特別是增加字段。就很有可能導(dǎo)致使用該視圖的應(yīng)用程序出錯；存儲過程、函數(shù)、觸發(fā)器的設(shè)計(jì)問：gtproject中有用到過觸發(fā)器嗎？口觸發(fā)器的功能通常可以用其他方式實(shí)現(xiàn)。在調(diào)試程序時觸發(fā)器可能成為干擾。假如你確實(shí)需要采用觸發(fā)器，一定要經(jīng)過測試再應(yīng)用在生產(chǎn)系統(tǒng)中，而且必須集中對它文檔化。請把程序包、存儲過程、函數(shù)、觸發(fā)器，與應(yīng)用程序一同加入CVS(現(xiàn)在大多數(shù)軟件開發(fā)公司都使用SVN替代了CVS)中，進(jìn)行版本控制。因?yàn)榇怂恼甙舜a，應(yīng)用程序?qū)λ麄兊囊蕾嚦潭缺葘Ρ怼⒁晥D的依賴程度更高；適量但盡量少使用存儲過程、函數(shù)、觸發(fā)器。使用存儲過程、函數(shù)、觸發(fā)器oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口的影響：(1)可以減少數(shù)據(jù)庫與客戶端的交互，提高性能；(2)有的數(shù)據(jù)庫還對他們進(jìn)行了某種程度的編譯，在執(zhí)行的時候，不用再對其中的SQL等語句進(jìn)行解析，從而提高速度；口(3)如果有多個應(yīng)用，使用了不同的開發(fā)語言，當(dāng)有某些關(guān)鍵的或者復(fù)雜邏輯希望共享，則可以考慮使用存儲過程或者函數(shù)。因?yàn)榇鎯^程等在數(shù)據(jù)庫一級是共享的；(4)增強(qiáng)了應(yīng)用對數(shù)據(jù)庫的依賴，如果打算將來移植數(shù)據(jù)庫的話，使用得越多，則移植的困難越大；數(shù)據(jù)庫中的業(yè)務(wù)邏輯越多(存儲過程等)，應(yīng)用以及存儲過程等的維護(hù)難度也會增大；(5)通常存儲過程等沒有面向?qū)ο蟮奶匦?，不容易設(shè)計(jì)出易于擴(kuò)展的結(jié)構(gòu)。當(dāng)存儲過程比較復(fù)雜時，或者它們相互間的調(diào)用關(guān)系比較復(fù)雜時，可能難于維護(hù)；5SQL的設(shè)計(jì)和使用口5.1Sql書寫規(guī)范口盡量不要寫復(fù)雜的SQL：過于復(fù)雜的SQL可以用存儲過程或函數(shù)來代替，效率更高；甚至如果能保證不造成瓶頸的話，把條SQL拆成多條也是可以的。這與一般的編碼規(guī)范很相似的，首先是要易懂。易懂也就意味著容易維護(hù)，對較為復(fù)雜的ql語句加上注釋，說明算法、功能注釋風(fēng)格：注釋單獨(dú)成行、放在語句前面。應(yīng)對不易理解的分支條件表達(dá)式加注釋；對重要的計(jì)算應(yīng)說明其功能；過長的函數(shù)實(shí)現(xiàn)，應(yīng)將其語句按實(shí)現(xiàn)的功能分段加以概括性說明；每條復(fù)雜SQL語句均應(yīng)有注釋說明(表名、字段名主要是說明此句SQL執(zhí)行的作用及所取得結(jié)果集的意義)；口常量及變量注釋時，應(yīng)注釋被保存值的含義（必須），合法取值的范圍（可選 ）；可采用單行/多行注釋。（--或/某某/方式，不同數(shù)據(jù)庫可能語法不同）；連接符or、in、and、以及=、（=、＞二等前后加上一個空格；口不要用SELECT某：SELECT語句中寫出必要的要選擇的全部列名，增強(qiáng)語句可讀性，避免不必要的選擇；SELECT某增加了對所有字段的依賴，當(dāng)表增加了字段后，有可能發(fā)生錯誤；此外還可能增加了數(shù)據(jù)的流量，查詢了一些實(shí)際不需要的字段；oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口避免長事務(wù)（Tranaction）：長事務(wù)容易造成死鎖，應(yīng)該避免，單個事務(wù)使用的數(shù)據(jù)庫和系統(tǒng)資源不宜超過總資源1-2%（參考值，各應(yīng)用可以根據(jù)實(shí)際情況進(jìn)行適量調(diào)整,這種情況不適用于數(shù)據(jù)倉庫）；行最長不能超過80字符,同一語句不同字句之間逗號以后空格，其他分割符前空格where子句書寫時，每個條件占一行，語句令起一行時，以保留字或者連接符開始，連接符右對齊；多表連接時，使用表的別名來引用列；5.1.13SQL中對視圖的引用：在不太清楚視圖用法的情況下，盡量不用。只是因?yàn)橐晥D中有自己想要的字段就拿來用，是相當(dāng)普遍和錯誤的用法。原因如下：增加了不必要的數(shù)據(jù)流量，對你的實(shí)際需求，那很可能是一個非常復(fù)雜的視圖，有大量你不需要的字段，并且關(guān)聯(lián)了很多你實(shí)際不需要的表，對數(shù)據(jù)庫資源會有過多的消耗；增加了應(yīng)用程序?qū)σ晥D的依賴，不必要的依賴是越少越好的。當(dāng)有應(yīng)用程序依賴了某個視圖，不久可能其他人因?yàn)槟撤N原因會修改此視圖，原來的應(yīng)用有可能會受到不同程度的影響；5.1.14不要在SQL語句中使用基于rule規(guī)則的hint,因?yàn)樵贠racle10g及以后版本不再支持；口5.2SQL性能優(yōu)化建議：口系統(tǒng)可能選擇基于規(guī)則的優(yōu)化器，所以將結(jié)果集返回?cái)?shù)據(jù)量小的表作為驅(qū)動表，即將結(jié)果集返回?cái)?shù)據(jù)量小的表放在FROM后邊最后一個表；大量的排序操作影響系統(tǒng)性能，所以盡量減少orderby和groupby排序操作；口如必須使用排序操作，排序盡量建立在有索引的列上；索引的使用盡量避免對索引列進(jìn)行計(jì)算。如對索引列計(jì)算較多，請?zhí)嵴垟?shù)據(jù)庫管理員建立函數(shù)索引；盡量注意比較值與索引列數(shù)據(jù)類型的一致性（number與number比較、char與char比較），避免使用數(shù)據(jù)庫的類型自動轉(zhuǎn)換功能;如：SELECT某FROMcategoryWHEREid='123';--id’typeinumber口對于復(fù)合索引，SQL語句必須使用主索引列；口索引字段中，盡量避免使用NULL值；口對于索引的比較，盡量避免使用NOT=(！=)口查詢列和排序列與索引列次序保持一致；盡量避免相同語句由于書寫格式的不同，而導(dǎo)致多次語法分析(減少數(shù)據(jù)庫的硬分析)，如可使用TOAD的格式化工具對SQL語句進(jìn)行格式化處理;查詢的WHERE過濾原則，應(yīng)使過濾記錄數(shù)最多的條件放在最前面；oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范：包括字段命名規(guī)范,表命名規(guī)范口在WHERE中，數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，要盡可能將放在等號右邊。否則會使所比較的字段上的索引失效；如：SELECT＊FROMervice_promotionWHERETO_CHAR(gmt_modified,’yyyy-mm-dd’)=‘20001-09-01’;而應(yīng)使用：SELECT某口FROMervice_promotionWHEREgmt_modified>=TO_DATE(‘2001-9-01’,’yyyy-mm-dd’)ANDgmt_modified<TO_DATE(‘2001-9-02’,’yyyy-mm-dd’)