Oracle 資料庫最佳實踐

孟利青 王升元 李海剛

應用設計

1.應用必須使用綁定變數（尤其是OLTP型應用）；

2.頻繁使用的小表要放入緩存中；

3.頻繁使用的index需要放入庫緩存的keep池中；

4.不使用select * from xxxxx for update；如果可能的話，考慮使用select *from xxxxx for update no wait替代；

5.加大序列的cache值，可以減少對於X$SEQ等基表的鎖爭用，但是會造成序列的不連續性，應用程序不要將任何商業邏輯建立在序列的完全連續性上。

6.在RAC環境下使用sequence，sequence的cache屬性不建議使用預設值（20），需要增加 cache size，如cache size 10000（可以根據業務需求定，如使用較頻繁的設置為更多）。

7.對於較小的表或者訪問較快的表，不使用parallel且不設置degree；對於通常的並行操作，通過設置並行參數（instance_groups和parallel_instance_group）將各自節點發起的請求在一個節點完成；

8.建表時必須指明所存儲的表空間。

9.表和索引建立在不同表空間上。

10.生成建表腳本時非空的列放在表的前部，可空的列放置在表的後部。

11.不允許將表建立在SYSTEM表空間上。

12.小表（數據量小於5000條記錄為標準）不需要創建索引。

13.對於OLTP應用，分區表使用分區索引。

14.分區索引如必須包含分區列，通常將分區列按序放置在分區索引的末尾。

15.建立分區索引必須指明表空間，不允許只寫一個LOCAL。

16.單個表上索引的個數不超過5個。

17.將記錄差別數最多的列放在索引順序的最前面。

18.索引數據的重複率不能超過20%。

19.進行order by column desc排序時，創建column desc索引。

20.物化視圖的基表必須創建主鍵，刷新間隔時間最小為3分鐘。

21.在有大量數據導入表中的情況下，不使用觸發器。

22.只允許從其它資料庫中查詢少量數據時使用dblink。

23.不使用dblink更新其它資料庫中的數據。

部署

1.儘可能主要是根據應用訪問的數據進行劃分，主要是減少不同資料庫節點之間數據的交互；連接方式上，最好手工指定連接到特定節點，取消負載均衡，並打開failover；

2.對於表空間，建議使用自動段空間管理（ASSM）；

3.對於存儲頻繁更新的數據的表空間或者表，建議設置較大的pctfree，以避免行遷移和行鏈接；

4.儘可能把應用數據表空間、應用的索引表空間以及相應得分區表空間分布在獨立的物理卷上。

5.把UNDO、TEMP、REDOLOG分布在不同的物理卷上。

6.RAC的private、public IP嚴格要求要在不同網段，兩個IP都要求進行網卡綁定：HP使用APA，AIX使用EthernetChannel，按主備方式進行，需要保證網卡綁定後從ORACLE看到的是一個固定的邏輯設備。

SQL語句編寫

索引

1.一般情況下，使用索引可以縮短查詢語句的執行時間，提高系統的執行效率，但是要避免以下兩種過度索引的情況出現：

2.對一個表建立了過多的索引，從而造成維護索引所需要的時間超過使用索引所降低的時間，從而造成整個系統效率的下降，這一般發生在對一些進行大量更新的表上面。因此一個聯機表上的索引，最多不要超過5個。

3.由於索引數據的區分度不夠，造成了使用索引而引起的效率的下降，這一般發生在對數據進行大的統計分析的時候。可以通過指定全表掃描等提示(hint)來避免。

4.複合索引是使用多個數據列的索引，第一個欄位的數據區分度非常重要，也是影響一個聯合索引效率的關鍵所在。

LIKE運算符

1.在應用程序中為了使用方便，對字元型變數進行比較時經常使用LIKE運算符進行字元模式的匹配。

2.需要注意的是對於LIKE運算，如果通配符%出現在字元串的尾部或中間，LIKE運算符將可以使用索引進行字元串的匹配，否則如果通配符%出現在字元串的開始，則LIKE必須使用全表掃描的方式去匹配字元串，這將產生較大的系統負荷。

3.一般情況下，為了提高系統的效率，我們希望用戶能夠在通配符的左端提供較多的數據信息以降低查詢的數量。

NULL值

1.NULL值是系統中目前尚無法確定的值，在Oracle資料庫系統中NULL是一個比所有的確定值都大的值，然而又不能用大於小於等於運算符來比較，對NULL值的處理只能用是與否來判定，所有的對NULL值的判定都會引起全表掃描，除非同時使用其它的查詢條件。

改寫查詢語句

1.關聯子查詢與非關聯子查詢

a)對於一個關聯子查詢，子查詢是每行外部查詢的記錄都要計算一次，然而對於一個非關聯子查詢，子查詢只會執行一次，而且結果集被保存在內存中。

b)因此，通常在外部查詢返回相對較少的記錄時，關聯子查詢比非關聯子查詢執行得更快；而子查詢中只有少量的記錄的時候，則非關聯子查詢將會比關聯子查詢執行得更快。

2.盡量用相同的數據類型的數據進行比較，以避免發生數據轉換

a)SQL語言對於數據類型不像JAVA和C++那樣進行嚴格的數據類型檢查，不同種數據間可以進行某些運算，但是在做數據操作時需要資料庫進行隱含的類型轉換，在大數據量的查詢中，由於要對每一個數據項做同樣的操作，會造成時間和CPU處理能力的浪費。

b)實際應用中通常發生的隱含的數據類型的轉換有：

l字元型到數字型的轉換，如：SELECT 『1234』+3 FROM DUAL等

l數字型到字元型的轉換，如：UPDATE DEPT SET EMPNO=5678等

l日期型到字元型的轉換，如：UPDATE EMP SET DNAME=SYSDATE等

c)上述的轉換都是隱含發生的，在實際使用中要避免使用不同類型的數據操作。

減少排序的發生

排序是資料庫中執行頻度比較大的一種操作，根據排序執行的範圍不同又可以分為內排序和外排序。我們希望資料庫中的排序操作的數量能夠被盡量的減少同時每個排序的時間能夠縮短。為此我們可以：

1.使用UNION ALL來代替UNION

2.添加索引。在表連接的時候使用索引可以避免排序的發生，比如添加了合適的索可以使連接方式由排序合併連接(Sort Merge Join)轉變為索引的嵌套循環連接(IndexedNestted Loop Join)。

3.在DISTINCT，GROUPBY，ORDER BY子句涉及到的列上創建索引。

4.使用較大SORT_AREA_SIZE

5.在用戶的臨時表空間上使用大的extent大小。

使用並行查詢

並行查詢適合下列情況：

1.全表掃描的查詢語句

2.返回大數據量的查詢所改造的語句

3.其它一些數據操作中的查詢子句

對於較大的數據量的查詢，我們可以使用提示(hint)來強制資料庫使用並行查詢，在Oracle資料庫中，並行查詢的優先順序為語句提示(hint)，表的定義，資料庫初始化參數。

減少死鎖的發生

在Oracle資料庫中大量的資料庫的鎖都是行級鎖，不同的會話間競爭同一條記錄的可能性較小，同時Oracle資料庫中提供了自動的死鎖檢測機制來避免資料庫的死鎖，保證資料庫系統的可用性。因此一般情況下應用系統不需要特殊的設計來解決系統的死鎖問題，但是在下列情況下系統可能出現死鎖：

1.表A上的列n上有一個索引，表B上的列m使用A上的列n作為外鍵，然後表A的列n上的索引被刪除，此時更新表B上列m將造成對錶A的表級鎖，會導致死鎖的發生。

2.應用大量的使用SELECT ……FOR UPDATE語句造成系統不必要的加鎖。

對於第一種情況要對出現死鎖的相關表進行檢查，確認是否相關索引被錯誤的刪除。對於第二種情況要修改應用，避免對數據的不必要的加鎖。

集合運算符的使用

Oracle資料庫的集合運算包括: UNION， UNION ALL， INTERSECT和MINUS操作。

一般情況下當兩個集合中的數據都比較多時，集合運算都是比較耗時的操作，使用時需要謹慎小心。如果可能，可以使用UNION ALL操作代替UNION操作。

限制表連接操作所涉及的表的個數

對於資料庫的連接操作操作，我們可以簡單的將其想像為一個循環匹配的過程，每一次匹配相當於一次循環，每一個連接相當於一層循環，則N個表的連接操作就相當於一個N-1層的循環嵌套。

一般的情況下在資料庫的查詢中涉及的數據表越多，則其查詢的執行計劃就越複雜，其執行的效率就越低，為此我們需要儘可能的限制參與連接的表的數量。

1.3-5個表的處理方法

a)對於較少的數據表的連接操作，需要合理的確定連接的驅動表，從某種意義上說，確定合理的驅動表就是確定多層循環嵌套中的最外層的循環，可以最大限度的提高連接操作的效率，可見選擇合適的驅動表的重要性。

b)RBO模式下，在SQL語句中FROM子句後面的表就是我們要進行連接操作的數據表，Oracle 按照從右到左的順序處理這些表，讓它們輪流作為驅動表去參加連接操作，這樣我們可以把包含參與連接的數據量最少的表放在FROM子句的最右端，按照從右到左的順序依次增加表中參與連接數據的量。

c)CBO模式下，則不需要考慮表放置的位置。

2.5個表以上的處理方法

a)對於涉及較多的表(>5+)的數據連接查詢，其查詢的複雜度迅速增加，其連接的存取路徑的變化更大，存取路徑的個數與連接的表的個數的階乘有關：當n=5時存取路徑＝1X2X3X4X5=120個，而當連接的表的個數為6時存取路徑變為1X2X3X4X5X6=720個，資料庫優化器對於數據的存取路徑的判斷近乎為不可能，此時完全依賴與用戶的語句書寫方式。

b)對於較多的表的連接，要求開發人員查詢返回的結果能夠有所預測，同時判斷出各個參與連接的表中符合條件的記錄的數量，從而控制查詢的運行時間。

c)同時為了提高查詢的效率，此時可以把部分表的一些連接所形成的中間結果來代替原來的連接表，從而減少連接的表的數目。

3.對錶連接操作涉及的表數目不應多於8個表

l如果查詢語句擁有過多的表連接，那麼它的執行計划過於複雜且可控性降低，容易引起資料庫的運行效率低下，即使在開發測試環境中已經經過充分的測試驗證，也不能保證在生產系統上由於數據量的變化而引發的相關問題。應該在應用設計階段就避免這種由於範式過高而導致的情況出現。

限制嵌套查詢的層數

應用中影響數據查詢的效率的因素除了參與查詢連接的表的個數以外，還有查詢的嵌套層數。對於非關聯查詢，嵌套的子查詢相當於使查詢語句的複雜度在算術級數的基礎上增長，而對於關聯查詢而言，嵌套的子查詢相當於使查詢語句的複雜度在幾何級數的基礎上增長。

因此，降低查詢的嵌套層數有助於提高查詢語句的效率。

對嵌套查詢層數的限制要求：如果查詢語句擁有過多的嵌套層數，那麼會使該查詢語句的複雜度高速增加，應該在資料庫設計階段就避免這種情況出現，不應多於5層。

靈活應用中間表或臨時表

在對涉及較多表的查詢和嵌套層數較多的複雜查詢的優化過程中，使用中間表或臨時表是優化、簡化複雜查詢的一個重要的方法。

通過使用一些中間表，我們可以把複雜度為M*N的操作轉化為複雜度為M+N的操作，當M和N都比較大時M+N

下面顯示了一個使用中間結果集來替代多表操作的例子。

改寫複雜查詢的技巧

1.轉換連接類型順序

2.把OR轉換為UNION ALL

3.區分不同的情況使用IN或EXISTS對於主查詢中包含較多條件而子查詢條件較少的表使用EXISTS，對於主查詢中包含較少條件而子查詢條件較多的表使用IN。

4.使用合理的連接方式

操作系統和主機

1.內部互連的連接方式：RAC之間的內部通訊網路（inter-connect）建議不使用交叉直連(crosscable)，Oracle不支持這種模式，一定要使用SAN(switch)的連接方式(如，交換機)，直連方式的穩定性差，在網路故障時，兩個節點都會down或hang ；需要使用千兆網線（光纖）連接千兆網卡（光纖卡）；

2.關閉操作系統CLUSTER 軟體中網卡的failover功能， 如HACMP 中的IP failover功能，MC SERVERS GUARD如果有類似功能也建議關閉。可以採用網卡綁定的方式實現網卡的failover功能；

3.如果使用raw device，建議使用AIO；操作系統內核參數MAX_ASYNC_PORTS設置要大於資料庫參數PROCESSES；

4.使用netstat –s檢查，不可出現網路buffer overflow

5.平均單次I/O （以Oracle數據塊大小為8K為例），響應時間不超過12毫秒。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！