數(shù)據(jù)庫編碼規(guī)范

例會制度第頁數(shù)據(jù)庫編碼規(guī)范技術(shù)研發(fā)中心2020年12月12日文檔修訂序號版本日期更改人描述（注明修改的條款或頁）1V1.0初稿

目錄1. 概述 41.1 編寫目的 41.2 使用范圍 41.3 參考文獻 42. 數(shù)據(jù)庫開發(fā) 52.1 概述 52.2 注釋編寫規(guī)范 52.3 SQL在JAVA代碼中的編寫規(guī)范 52.4 SQL編寫原則 63. 附錄 103.1 建議 103.2 SQL優(yōu)化 133.2.1 利用NavicatPremium工具分析SQL執(zhí)行效率 13

概述編寫目的本文描述了使用SQL技術(shù)進行編程的一些規(guī)范。為保證在開發(fā)過程中產(chǎn)出高效、格式統(tǒng)一、易閱讀、易維護的SQL代碼，利于不同開發(fā)人員維護，所有的數(shù)據(jù)庫代碼和相關(guān)文檔都應遵循本文檔描述的規(guī)則和約定。使用范圍開發(fā)人員、工程實施人員、系統(tǒng)維護人員參考文獻《數(shù)據(jù)庫開發(fā)規(guī)范》

數(shù)據(jù)庫開發(fā)概述數(shù)據(jù)庫開發(fā)工具使用NavicatPremium，便于應用統(tǒng)一的美化器對所有數(shù)據(jù)庫程序代碼實現(xiàn)統(tǒng)一的格式化；便于使用數(shù)據(jù)庫程序模版創(chuàng)建統(tǒng)一格式的數(shù)據(jù)程序?qū)ο?。?shù)據(jù)庫中存儲的程序代碼不可具備事務提交功能，所有事務的提交在應用層完成。數(shù)據(jù)庫中的程序代碼統(tǒng)一使用存儲包的形式，不再出現(xiàn)單獨的存儲過程或者函數(shù)。存儲包超過6000行需另建存儲包。動態(tài)SQL執(zhí)行必須使用BIND變量。程序代碼內(nèi)中不可出現(xiàn)DDL語句。注釋編寫規(guī)范每個數(shù)據(jù)庫程序?qū)ο螅ò?、包?nèi)過程函數(shù)、觸發(fā)器、自定義類等）、變量及常量必須使用注釋。注釋方式：/**/SQL在JAVA代碼中的編寫規(guī)范SQL語句在程序中應盡量少的出現(xiàn)(寫在數(shù)據(jù)庫存儲過程中，后臺執(zhí)行效率較高)，必須出現(xiàn)則一般以字符串的形式出現(xiàn)，現(xiàn)對程序中的SQL書寫做以下約定。java代碼中出現(xiàn)的SQL語句包括（字段名，表名，SQL關(guān)鍵字、保留字）均應采用大寫形式；避免在java代碼中，使用循環(huán)語句多次執(zhí)行數(shù)據(jù)庫查詢；用于查詢/更新/刪除的SQL，WHERE條件固定的，必須使用預編譯方式；對SQL語句加上適當?shù)淖⑨?，特別是對語句上出現(xiàn)的枚舉值要標明其含義；合理使用空格和縮進，保證SQL語句結(jié)構(gòu)清晰；在SQL內(nèi)置運算符前后加上空格；在SQL關(guān)鍵字、保留字前后都要有空格；拼接SQL時，新一行加上空格；SQL語句較長時，應把SQL語句放在一個方法內(nèi)；在每一個子句及邏輯判斷占一行；對子查詢使用縮進，一般為4個字符；SQL語句應該保持在80行以內(nèi)；代碼中嚴禁使用SELECT*代替具體的字段名。Oracle在解析的過程中,會將’*’依次轉(zhuǎn)換成所有的列名,這個工作是通過查詢數(shù)據(jù)字典完成的,這意味著將耗費更多的時間。在做插入SQL時，必須列出要插入表的字段名。不允許出現(xiàn)類似如下SQL：INSERTINTOTABLE_NAME1SELECT*FROMTABLE_NAME2WHERE……;代碼中定義的SQL，如果為定長SQL，必須定義為finalstatic類型。如果SQL中有特殊業(yè)務含義的值的引用，要加上注釋說明如：舉例：下面是修改一個用戶狀態(tài)的SQL語句//STATUS=’11’其中‘11’代表用戶正常狀態(tài)finalstaticStringupdateUserStatusSql=”UPDATEPUB_USERSSETSTATUS=’11’WHEREUSER_ID=’superadmin’SQL編寫原則多表查詢，要使用別名；當在SQL語句中連接多個表時,請使用表的別名并把別名前綴于每個Column上。這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。多表關(guān)聯(lián)效率優(yōu)化1)大數(shù)量的表和數(shù)據(jù)量非常小的表不宜直接做關(guān)聯(lián)，可以考慮通過程序或子查詢的方式先處理小表，根據(jù)獲取的數(shù)據(jù)，再檢索大表。2)WHERE條件順序SQL解析器采用自下而上的順序解析WHERE子句，建議將表之間的連接放在其他條件之前，將可以過濾掉最大數(shù)量記錄的條件放在WHERE子句的末尾。低效SQL,執(zhí)行時間156.3秒SELECT...

FROMEMPE

WHERESAL>50000

ANDJOB='MANAGER'

AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);高效SQL,執(zhí)行時間10.6秒SELECT...

FROMEMPE

WHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)

ANDSAL>50000

ANDJOB='MANAGER';FROM子句順序(選擇基礎(chǔ)表)：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎(chǔ)表drivingtable)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表(放置在from子句的最后面)作為基礎(chǔ)表。例如：表TAB1共16,384條記錄表TAB2共1條記錄選擇TAB2作為基礎(chǔ)表(最好的方法)selectcount(*)fromtab1，tab2執(zhí)行時間0.96秒選擇TAB2作為基礎(chǔ)表(不佳的方法)selectcount(*)fromtab2，tab1執(zhí)行時間26.09秒不要對索引列使用函數(shù)，不要在索引列上使用NOT，不要在索引列上使用計算，不要在索引列上使用ISNULL和ISNOTNULL，不要改變索引列的類型。對索引列使用函數(shù)會使索引失效，執(zhí)行全表掃描，導致查詢性能降低。對索引列使用NOT，會產(chǎn)生與上述操作相同的影響。對索引列使用計算，優(yōu)化器將不再使用索引而使用全表掃描。例如:低效:SELECT…FROMDEPTWHERESAL*12>25000;高效:SELECT…FROMDEPTWHERESAL>25000/12;索引列上使用ISNULL和ISNOTNULL,該索引失效。例如：低效:(索引失效)SELECT…FROMDEPARTMENTWHEREDEPT_CODEISNOTNULL;高效:(索引有效)SELECT…FROMDEPARTMENTWHEREDEPT_CODE>=0;改變索引列類型,該索引失效。故查詢時值應該與索引列類型保持一致。當比較不同數(shù)據(jù)類型的數(shù)據(jù)時,ORACLE自動對列進行簡單的類型轉(zhuǎn)換。假設(shè)EMPNO是一個數(shù)值類型的索引列：SELECT…FROMEMPWHEREEMPNO=‘123'。實際上,經(jīng)過Oracle類型轉(zhuǎn)換,語句轉(zhuǎn)化為:SELECT…FROMEMPWHEREEMPNO=TO_NUMBER(‘123')。幸運的是,類型轉(zhuǎn)換沒有發(fā)生在索引列上,索引的用途沒有被改變?，F(xiàn)在,假設(shè)EMP_TYPE是一個字符類型的索引列：SELECT…FROMEMPWHEREEMP_TYPE=123。這個語句被Oracle轉(zhuǎn)換為:SELECT…FROMEMPWHERETO_NUMBER(EMP_TYPE)=123。因為內(nèi)部發(fā)生的類型轉(zhuǎn)換,這個索引將不會被用到!為了避免Oracle對你的SQL進行隱式的類型轉(zhuǎn)換,最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來。例如：低效:SELECT…FROMDEPTWHERESUBSTR(STARTDATE,0,4)>’2012’;高效:SELECT…FROMDEPTWHERESTARTDATE>TO_DATE(‘2012-01-01’,’yyyy-MM-dd’);若索引建立在多個列上，則只有第一列作為查詢條件時，優(yōu)化器才會使用它，否則，忽略該索引使用全表掃描。這也是一條簡單而重要的規(guī)則。其他索引失效的情況有：(1)‘!=’將不使用索引。記住,索引只能告訴你什么存在于表中,而不能告訴你什么不存在于表中。(2)‘||’是字符連接函數(shù)。就象其他函數(shù)那樣,停用索引。(3)‘+’、‘-’、‘*’、‘/’、‘%’是數(shù)學函數(shù)。就象其他數(shù)學函數(shù)那樣,停用索引。使用IN語句時，要確保IN中的數(shù)據(jù)不能超過1000。用EXISTS替換DISTINCT當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換,EXISTS使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果。例子：(低效):SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO(高效):SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X'FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);不準使用CROSSJOIN，使用OUTER，INNERJOIN用EXISTS替代IN、用NOTEXISTS替代NOTIN語句在許多基于基礎(chǔ)表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯(lián)接。在這種情況下,使用EXISTS(或NOTEXISTS)通常將提高查詢的效率，而在子查詢中,NOTIN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下,NOTIN都是最低效的(因為它對子查詢中的表執(zhí)行了一個全表遍歷)。為了避免使用NOTIN,我們可以把它改寫成外連接(OuterJoins)或NOTEXISTS。(低效)SELECT*FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A');

(高效)SELECT*FROMEMPEWHERENOTEXISTS(SELECT'X'FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT='A');若業(yè)務允許，用UNION-ALL替換UNIONUNIONALL是將多個查詢語句，查詢列意義相同并且一一對應，在一起執(zhí)行。UNIONALL與UNION的區(qū)別是UNION會對查詢出的多個結(jié)果集做排序和DISTINCT操作。而UNIONALL不會。因此在查詢性能上,UNIONALL會比UNION快很多。因此在使用時要根據(jù)情況，看是否多個sql查詢出的結(jié)果是否會有重復行，如果沒有或業(yè)務上不關(guān)注重復，建議使用UNIONALL語法。DATA_DATE的使用對時間條件可使用“=”、“>=”、“<=”、“BETWENN...AND”、to_Date,不可使用任何函數(shù)。對較長的SQL要進行執(zhí)行計劃分析，對于在系統(tǒng)使用比較復雜的sql，需要與有經(jīng)驗的同事或技術(shù)經(jīng)理討論。

附錄建議減少訪問數(shù)據(jù)庫的次數(shù)Oracle在內(nèi)部執(zhí)行了許多工作:解析SQL語句,估算索引的利用率,綁定變量,讀數(shù)據(jù)塊等。用Where子句替換HAVING子句避免使用HAVING子句,HAVING只會在檢索出所有記錄之后才對結(jié)果集進行過濾。這個處理需要排序,總計等操作.如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。優(yōu)化GROUPBY提高GROUPBY語句的效率，可以通過將不需要的記錄在GROUPBY之前過濾掉。下面兩個查詢返回相同結(jié)果但第二個明顯就快了許多。低效：SELECTJOB，AVG(SAL)FROMEMPGROUPbyJOBHAVINGJOB=“PRESIDENT?ORJOB=“MANAGER?高效：SELECTJOB，AVG(SAL)FROMEMPWHEREJOB=“PRESIDENT?ORJOB=“MANAGER?GROUPbyJOB用>=替代>如果DEPTNO上有一個索引，高效：SELECT*FROMEMPWHEREDEPTNO>=4低效：SELECT*FROMEMPWHEREDEPTNO>3兩者的區(qū)別在于，前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。刪除重復記錄的方法：最高效的刪除重復記錄方法(因為使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);減少對表的查詢在含有子查詢的SQL語句中,要特別注意減少對表的查詢。例子：SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VERFROMTAB_COLUMNSWHEREVERSION=604)比較：SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)用索引提高效率索引是用來提高檢索數(shù)據(jù)的效率，Oracle使用了一個復雜的自平衡B-tree結(jié)構(gòu)。通常,通過索引查詢數(shù)據(jù)比全表掃描要快。當Oracle找出執(zhí)行查詢和Update語句的最佳路徑時,Oracle優(yōu)化器將使用索引。同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率。雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價：索引需要空間來存儲,也需要定期維護,每當有記錄在表中增減或索引列被修改時,索引本身也會被修改。這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出磁盤I/O。因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢。定期的重構(gòu)索引是有必要的：ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>用UNION替換OR(適用于索引列)通常情況下,用UNION替換WHERE子句中的OR將會起到較好的效果。對索引列使用OR將造成全表掃描。注意,以上規(guī)則只針對多個索引列有效.如果有column沒有被索引,查詢效率可能會因為你沒有選擇OR而降低。避免使用耗費資源的操作帶有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能。如有可能,帶有UNION,MINUS,INTERSECT的SQL語句都可以用其他方式重寫。任何對列的操作都將導致表掃描，它包括數(shù)據(jù)庫函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊。注意rownum的使用：1.rownum的使用：如下兩條語句：selectrownum,id,namefromstudentwhererownum>2;selectrownum,id,namefromstudentwhererownum<=10;第一條語句的執(zhí)行結(jié)果為空，第二條語句的執(zhí)行結(jié)果為前10條記錄；為什么會這樣呢，我們知道rownum是偽列，是oracle為查詢結(jié)果自動添加的偽列，第一行是1，如果whererownum>2，這時候查找第一