ORACLE數據庫應用開發的三十個註意事項

Posted on by

引言

筆者及所在團隊從2000年開始的CRM等系統開發，一直主要使用ORACLE數據庫作為應用數據庫，開發方式包括使用PLSQL編寫存儲過程/數據庫函數/觸發器、使用ODBC或OCI和ProC開發C++應用、使用JDBC開發Java應用、使用tuxdeo開發中間件應用等。這些應用開發筆者所在團隊自己做過，也委托華為、亞信、思特奇等國內廠商合作做過，整體來說ORACLE數據庫功能強大、性能出眾、系統健壯，確實是OLTP聯機事務處理的最受歡迎的數據庫。

因ORACLE服務費居高不下、加上最近幾年美國的操弄打壓，國產數據庫也走出瞭一條自己的路，因此數據庫國產化也越來越被提上日程，也有部分應用走出瞭成功之路，但眾多傳統應用進行國產數據庫的改造需要大量投入，也需要一個逐步試點及改造的過程，因此ORACLE仍然是國內眾多單位持續應用的選擇。

今天老猿結合二十餘年的ORACLE數據庫應用開發和運維的經驗教訓，總結在使用ORACLE數據庫環境中的應用開發中需要註意的一些註意事項，這些問題不但可以作為ORACLE數據庫開發的註意事項，大多數也適用於常見的關系型數據庫開發甚至非關系型數據開發。

實際上，在數據庫應用開發上，開發和維護關聯度是非常大的，好的開發設計會給維護帶來極大方便。因此雖然維護關註的角度和開發有所不同，但在部分內容上二者是統一的。

禁忌1：觸發器代碼忌復雜

數據庫觸發器由於可以基於表級進行所有應用或手工DML操作數據增刪改查的前向或後向處理，易於收斂邏輯，使用方便，容易受到眾多開發人員的喜愛。

但在使用上觸發器與操作數據的事務處於同一個事務，因此比較適合簡單處理邏輯，切忌不能在觸發器上使用復雜邏輯，一般推薦在10行左右代碼比較適合，否則容易導致事務處理出現問題。

如果一定要通過觸發器進行復雜邏輯處理，最好的做法是通過觸發器將需要處理的數據寫入到單獨的任務表中，然後使用單獨進程對任務表數據進行處理。這樣能使得觸發器和觸發源二者的事務解耦，又能收斂相關數據處理。

禁忌2：忌使用dblink

dblink提供的機制可以使得在一個數據庫的存儲過程、觸發器、數據庫函數中方便的訪問另一個數據庫，可以方便地為應用隻需連接一個數據庫就可以訪問另一個數據庫中的數據，因此給多數據庫環境使用帶來瞭很大的便利性。

但dblink在跨數據庫事務提交上容易引發問題，一般可以在不帶事務的DML簡單查詢中使用，如果一定要帶事務必須確保事務提交迅速，否則容易引發分佈式事務鎖。而應用程序中使用時，由於運行的環境復雜多變，無法百分之百保障事務的完整性和響應快速，很容易引發分佈式事務鎖並有一定幾率觸發ORACLE的BUG，同時dblink本身會大概率甚至百分之百帶來scn號跳變bug，並引發scn號跳變在數據庫間傳播。導致系統故障甚至數據庫癱瘓。因此不要在代碼開發中使用dblink。平時運維也盡量少用，如果一定要用最好不帶事務，並盡快釋放連接。

禁忌3：忌用大表關聯統計

在一個系統中，除瞭實時類交易外，也存在一定要求數據實時的統計或查詢需求，針對這種數據統計，切忌使用大表關聯進行統計，因為會導致數據庫消耗大量計算資源、占用過多的臨時空間，影響其他實時業務的響應甚至導致系統無法響應。

對於這種需要跨多個大表的統計，最理想的是不放在OLTP數據庫執行，如果一定要執行，一是要想辦法限制數據的范圍（如基於時間限制隻能統計當天的），二是對於兩個大表關聯的SQL進行拆分，拆分成兩個SQL，前一個SQL獲取的數據通過遊標打開後再逐條去另一個大表使用索引逐條數據進行訪問，再用客戶端進行統計運算，或者通過遊標獲取數據生產臨時表再基於臨時表進行統計。

禁忌4：忌用字典式字段索引

索引隻有說數據在索引字段比較分散才有效果，如果基於一些字典式字段（如性別、課程等）建索引，起不到很好的效果不說，還浪費存儲空間。這種字典式的字段如果一定要發揮類似索引的效果，可以按字典值建分區鍵。

禁忌5：慎用主鍵約束

某個表的主鍵理論上看起來是個很好的機制，但在一般性應用中，由於主鍵不能更新，因此在運維時會帶來很多不便，一般建議慎用，而是可以用非空和唯一性約束方式來替代。

禁忌6：慎用外鍵關聯

外鍵關聯可以確保某個表的主鍵被其他表作為非主鍵使用時來保障兩個表數據的一致性，但外鍵關聯給程序開發、運維都帶來瞭更多的復雜性，而好的開發習慣能確保兩個有外鍵關聯的表滿足數據一致性的要求，因此一般情況下慎用外鍵關聯。這其實是根據在方便性、數據一致性之間應用更傾向於哪方面來決策使用方式。

禁忌7：組合索引使用要註意

  1. 使用多個字段的混合索引是常見的，但索引使用的字段越多，就意味著開發時需要關註的字段越多，開發時部分人員容易忘記索引字段，導致容易寫出用不到索引的語句。因此一般建議復合索引使用字段不超過5個；
  2. 組合索引中字段的順序是非常重要的，越是唯一的字段越是要靠前；
  3. 程序代碼使用組合索引時，在使用索引字段作為條件時，如果該索引是復合索引，那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，並且應盡可能的讓字段順序與索引順序相一致。

禁忌8：慎重考慮表字段調整

當一個初始設計的表在運行一段時間後，隨著業務的發展和系統的持續運營，對表結構進行調整是遲早的事，但調整表結構如增加新字段、字段長度調整等都需要慎重，特別是針對數據量大訪問頻繁的表更要謹慎。

在評估表結構調整時，一般需要考慮：

  1. 是否需要停系統調整，對於高並發訪問頻繁的表至少要等到業務閑時進行調整；
  2. 如果是調整字段大小需要評估是否有代碼限制瞭字段大小；
  3. 如果是新增字段需要評估是否有代碼采用瞭select *方式訪問；
  4. 是否需要初始化歷史數據？如果有是否會造成行遷移？是否需要重建表？
  5. 是否會影響外圍接口或系統數據的交互？

為瞭應對字段增加可能帶來的風險，有2個方法來提取預防：

  1. 給一些大表預留一定的字段，這樣可以避免停系統、減少行數據遷移、並避免系統運行時進行表結構調整的風險，但要規劃好預留字段的數量、並做好啟用管理；
  2. 盡量不動大表本身，而是設計擴展表來解決。

禁忌9：忌直接使用用戶名和密碼連接數據

在信息安全非常重視的今天，數據庫的安全性是重中之重，應用系統不應該在程序代碼或配置文件中直接使用用戶名和密碼方式連接訪問業務數據。如果這樣，對開發人員和維護人員密碼就和沒有密碼一樣，另外如果出現數據庫必須修改密碼時，需要到處改密碼相關的代碼或配置文件。

比較好的解決辦法是用最小權限的用戶登錄，登錄後通過專用加密配置表獲取用戶真正使用的用戶和密碼，這就是二次登錄。

禁忌10：慎用數據庫連接

在一個大型系統內，數據庫連接是寶貴的資源，ORACLE的連接數單實例一般限制在4096個，看起來不少，但如果連接節點多真正使用起來後會發現連接數往往不夠用。為此需要對數據庫的訪問進行連接收斂管控，實現連接的復用。
要實現連接的收斂，有如下做法：

  1. WEB服務器通過連接池管理收斂客戶端的數據訪問；
  2. 後臺進程或中間間通過數據訪問代理層來進行連接的復用和收斂；
  3. 後臺維護限制單機登錄會話數。

禁忌11：忌用並行

在程序代碼或表的參數設置裡，都可以設置並行參數，並行對於單表或單語句能起到迅速提高執行效率的作用，但這種並行是以搶占其他任務的資源為代價，因此在OLTP數據庫應用中，最好別使用並行的DML語句或將表的並行參數打開。臨時執行任務考慮到執行速度需要使用並行時，一定要與DBA協商是否可以開啟並行，並在任務執行結束後關閉
表的並行參數。

前幾年，有個工程割接時為瞭提升割接速度，當晚一個參與割接的工程師未和任何人商議就將一個重要的訪問頻繁的大表的並行參數設置調整多倍，割接後也未關閉，導致第二天業務起來後數據庫出現瞭鎖、主機CPU全忙，影響業務超過4個小時。經過較長時間定位才發現是該表並行被打開導致的，問題最終解決但造成瞭不良影響。而那個導致問題的工程師也在華為年底考核中得到瞭處罰離職走人。

禁忌12：忌SQL語句不使用綁定變量

在應用開發中，SQL語句大多數是動態SQL，這種動態SQL又分為兩種，一種是字段取值是變化的，一種是連數據對象名都是動態的。

對於字段取值變化的情況，基本的訪問SQL是固定的，針對變化的字段值就需要用綁定變量方式傳值，這時數據庫才不會重新編譯該SQL語句，可以提高執行效率，如果不使用綁定變量，直接將該SQL語句用字符串方式拼接，其實就與數據對象也是不同變換的方式相同，這樣SQL語句每回都會被編譯，效率會大幅下降。

對於數據對象名都是動態的SQL，不適合通過綁定變量方式傳遞數據對象名。

禁忌13：忌索引數量過多

一個表的索引是需要單獨占用存儲空間的，過多的索引會導致表數據發生變化時索引的調整占用過多的時間，會引發數據的增刪改性能下降。對於頻繁進行插入、刪除、更新的數據表，應控制索引的數量，提高效率，一般一個表的索引數控制在5個以內。如果一個表要建5個以上索引，一般說明表和應用的設計是存在問題的。

禁忌14：select for update 要帶nowait

select … for update語句 對於保證事務的完整性很有必要，但在一個並發環境中，使用該語句的應用很可能會導致數據庫鎖甚至死鎖，正確的做法是select … for update nowait，並且在獲取鎖失敗時要有重新處理的機制。

禁忌15：批量任務要控制好事務提交的頻度

在後臺進程或存儲過程處理大批量數據時，事務提交禁止單條提交或者全部完成後再提交，提交太頻繁會產生過多的數據庫日志，一次性提交容易導致回滾段不夠等問題。建議使用100、500或者1000條提交的頻度。

禁忌16：sequence使用註意

對於部分表的內部關鍵ID字段如流水號等使用sequence是個非常不錯的主意，可以有效保障並發環境下這種序列號分配的高效和唯一性。但在應用中使用sequence時要註意幾點：

  1. 長度設置要充分考慮業務發展的速度，有效序列在初期增長緩慢，但隨著業務發展會迅速增長，因此開發設計時要進行充分的評估，防止出現sequence很快被耗盡；
  2. 對於長期增長的序列號，最後采用時間戳+序列號的方式來作為字段值，防止序列號翻轉；
  3. 序列號分配時，由於序列號在不同數據庫實例的緩沖機制，可能會出現後面的數據記錄序列號比前面插入記錄序列號還要小的情況，因此序列號字段的值隻能作為唯一鍵值使用，而不能作為業務排序的依據。

禁忌17：慎用rowid更新數據

rowid是數據庫記錄的一個內部記錄ID，使用rowid訪問數據比普通索引還要高效，但如果在一個表的數據頻繁插入、刪除時，不要使用rowid來進行數據的操作，因為ORACLE的rowid在數據有刪除機制時，是有一定幾率出現重復的。

禁忌18：慎用子查詢

通過子查詢方式進行數據的嵌套查詢SQL的可讀性比較好，但子查詢很容易導致全表掃描，且容易導致回滾段或臨時表空間使用過高，因此慎用子查詢，特別是子查詢的結果集也很大時忌用。

禁忌19：忌用SELECT *

在查詢SQL語句中，要盡量減少返回的結果行，包括行數和字段列數。返回的結果越大，意味著相應的SQL語句的logical reads 就越大，對服務器的性能影響就越甚。

一個特別不好的設計就是使用SELECT * 返回表的所有數據，除瞭影響服務器性能外，還可能會由於表結構的調整在編譯階段無法發現，導致運行時錯誤。

禁忌20：where 子句中慎用!=或<>操作符

使用!=或<>意味著對應字段的訪問不會使用索引，因此隻有在其他字段使用瞭索引的基礎上才使用!=或<>操作。

禁忌21：where 子句中慎用like

如果在where字句中使用字段A like ‘%xxx’ 或字段A like ‘%xxx%’方式匹配結果記錄時，該字段不會使用索引，因此隻有在其他字段使用瞭索引的基礎上才使用字段A like ‘%xxx’ 或字段A like ‘%xxx%’方式，即忌用前置百分號匹配。

禁忌22：where 子句中慎用in和not in

如果in後面的結果數據比較多，很可能會導致全表掃描。因此隻能和其他能使用索引的條件組合使用時使用。

禁忌23：where字句中慎用字段函數操作

應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

禁忌24：忌用select count（*）

使用select count（*）的效率不如使用select count(1)。

禁忌25：索引字段訪問慎用OR

索引字段用OR的字句容易導致全表掃描，一定要判斷多個值可以分解成多個查詢，並且通過UNION 連接多個查詢。

禁忌26：慎重考慮字符集

在一個應用系統中，涉及應用服務器主機字符集、數據庫字符集以及客戶端字符集的兼容性問題，要在系統構架時統一考慮，確保從前臺到後臺到數據庫的字符集都是兼容的。

禁忌27、慎用視圖嵌套

視圖最好建在表上，盡量不要基於視圖嵌套建立視圖，一方面一般視圖嵌套不要超過2個為宜，視圖嵌套層級多訪問效率會下降，並且可維護性變差。

禁忌28： 忌數據對象名過長

oracle的表名、字段名等對象命名字節個數限制在瞭30個字節，不能超過30。

禁忌29：謹慎表和索引的inittrans設置

每個塊都有一個塊首部。這個塊首部中有一個事務表。事務表中會建立一些條目來描述哪些事務將塊上的哪些行/元素鎖定。這個事務表的初始大小由對象的INITRANS 設置指定。對於表，這個值默認為2（索引的INITRANS 也默認為2）。事務表會根據需要動態擴展，最大達到MAXTRANS 個條目（假設塊上有足夠的自由空間）。所分配的每個事務條目需要占用塊首部中的23～24 字節的存儲空間。註意，對於Oracle 10g以上版本，MAXTRANS 則會忽略，所有段的MAXTRANS 都是255。

也就是說，如果某個事物鎖定瞭這個塊的數據，則會在這個地方記錄事務的標識，當然那個事務要先看一下這個地方是不是已經有人占用瞭，如果有，則去看看那個事務是否為活動狀態。如果不活動，比如已經提交或者回滾，則可以覆蓋這個地方。如果活動，則需要等待(閂的作用)。

所以，如果有大量的並發訪問使用的這個塊，則參數不能太小，否則資源競爭將導致系統並發性能下降。

在創建表和索引時，需要根據表數據的訪問頻度和數據量來評估設置的inittrans值，雖然不夠時Oracle會自動增加，但增加的過程需要ORACLE進行評估，且該評估機制是針對所有對象的，需要排隊，如果inittrans值設置過小，就會影響系統的並發性能。因此系統設計和部署時需要考慮不同表的inittrans的設置。

禁忌30：數據模型和數據對象的設計必須商DBA確認

一個業務系統，會有大量的數據模型，會創建大量數據對象，在考慮業務需求的同時必須考慮系統運維，因此請DBA參與設計是非常必要的。
在做設計時，要考慮：

  1. 制定數據庫對象命名規范；
  2. 數據庫表空間的使用規劃（包括業務數據表和索引、字典表、臨時表空間、回滾段等）；
  3. 表和索引的inittrans大小；
  4. 各個表的PCTFREE 和 PCTUSED設置；
  5. 數據維護周期
  6. …

小結：

本文詳細介紹瞭筆者在二十餘年ORACLE應用開發中遇到的三十條開發註意事項（本來想寫成三十六忌的，奈何沒湊夠），這些註意事項是實際工作中的經驗和教訓總結，好些註意事項是筆者的團隊以及華為等合作廠商一些工作兩三年的人都容易犯的錯，筆者前2年遭遇的一次事件還導致瞭一個華為工程師的考核離職。

這些註意事項不但可以在和ORACLE有關的應用開發中遇到，大部分也可以在其他關系型數據庫有關的應用開發中遇到，甚至部分問題可以在非關系型數據有關的應用開發中遇到。

以上就是ORACLE數據庫應用開發的三十個註意事項的詳細內容，更多關於ORACLE數據庫應用開發的註意事項的資料請關註WalkonNet其它相關文章！

推薦閱讀：