商業智慧領域需要瞭解的資料庫最佳化理論

標籤：

        

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

     由於資料倉儲系統儲存大量的曆史資料和當前資料，並且資料量在不斷增加，為了提高資料存放區、檢索的運行效率，建立一個優良的資料倉儲系統，資料倉儲設計人員和開發人員需要掌握Oracle最佳化理論方面的知識，這有助於設計、開發出高效能的資料倉儲系統。

 

1          外部最佳化原則

CPU是直接影響資料庫效能的外部因素，同時Oracle的記憶體大小也會影響SQL查詢的

效率，大量的Net8通訊同樣會使SQL的效能變差。

         下面介紹外部最佳化原則的具體調整方法。

1）  適當增加伺服器CPU的數量，伺服器的效能會受CPU的限制，最好的方法就是為伺服器增加額外的CPU，或者關閉很多等待處理資源的各種組件，以減少CPU資源的浪費。

2）  當記憶體分頁時，如果記憶體容量不足，最好的方法就是增加更多的記憶體，減小SGA的大小，或者關閉Oracle多線程伺服器，以提高資料庫系統的運行效率。

2          SQL最佳化基本規則

1）  在對大表進行全表搜尋時，為了避免不必要的全表搜素而導致的大量I/O操作，最常見的調優方法是適量增加索引，加快查詢的速度，以提高資料庫啟動並執行效率。

2）  保證最優的索引使用，對於改善查詢的速度和提高資料庫的效能是至關重要的。在某些情況下也可以選擇多個索引進行查詢，還包括位元影像索引和基於函數索引的使用。

3          SQL使用規範

1）  盡量避免使用遊標。因為遊標的效率較差，如果遊標操作的資料超過1W行，那麼就應該採用其他的方法：如果使用了遊標，還要盡量避免在遊標迴圈中再進行表串連的操作。

2）  不要在where子句中的”=”左邊進行函數、算術運算或其他運算式運算，否則資料庫系統可能無法正常使用索引。

3）  盡量使用exists代替select count(*)語句來判斷表中是否存在滿足條件的記錄。Count函數只有統計表中所有行數時才使用，而且count(1)或count(‘X’)比count(*)更有效率。

4）  注意表之間關聯欄位的資料類型，避免使用不同類型的欄位作為關聯條件進行多表串連。

4          索引使用規範

1）  索引的建立要與實際應用情況結合考慮，建議大的聯機交易處理系統（OLTP）表最好不要超過6個索引，以免系統增加不必要的負擔。

2）  儘可能使用索引欄位作為查詢條件，以提高查詢效率。

3）  盡量避免在大表查詢時使用全表掃描的方式，必要時可以考慮重建索引。

4）  注意索引的定期維護，可以周期性地重建索引和重新編譯預存程序。

例如使用：

  SELECT ‘ALTER INDEX’||index_name||’REBUILD;’

  FROM user_indexes

語句查詢出目前使用者下所有的索引重建語句，然後在SQL視窗下一條執行該重建索引語句。

5          暫存資料表使用規範

1）  盡量避免在暫存資料表中使用distinct、order by、group by、having、join，因為這些語句會加重暫存資料表的負擔。同時也盡量避免暫存資料表的頻繁建立和刪除，以減少系統資料表資源的消耗。

2）  在建立暫存資料表時，如果一次性插入資料量非常大，那麼可以使用select into語句替代create table語句，避免對日誌的大量操作，可以縮短啟動並執行時間；如果資料量並不大，為了緩和系統資料表的資源消耗，建議先建立暫存資料表，然後再進行插入操作。

3）  如果暫存資料表的資料量較大，則需要為暫存資料表建立相應的索引，同時保證資料庫系統對該暫存資料表索引的使用。

4）  如果預存程序中使用了暫存資料表，在代碼的結尾處，一定要將所有的暫存資料表都刪除，通常的做法是先執行truncate table語句，然後再執行drop table語句，這樣就可以避免資料庫系統較長時間鎖定暫存資料表與其他表的串連查詢和關聯，從而減輕資料庫系統的負擔。

6          索引建立原則

索引建立原則

1）  先插入資料，再進行索引的建立工作。

2）  限制每個表的索引數量，避免大量的索引導致資料庫系統的效能降低。

3）  為每個索引指定資料表空間，這樣有利於避免發生I/0衝突。

4）  定期重建索引，以減少索引的片段，提高資料庫系統的效能，一般來說，枚舉類型的欄位可以考慮使用位元影像索引，而非二叉樹類索引。

7       大資料量表的維護原則

 

1）  如果是超大資料量的表，並且經常進行增加、刪除、修改、查詢等操作，最好的辦法就是定期收集統計資訊傳遞給Oracle最佳化器，以提高資料庫系統的效能。

2）  如果是大資料量的表，可以考慮使用按照時間或者雜湊的分區技術，並且將不同的分區放入到規定的資料表空間中，以提高資料庫查詢的效率。

8          視圖建立原則

1）  絕對禁止在視圖的基礎上再建立視圖，這樣會嚴重影響資料庫的效能。

2）  推薦使用物化視圖技術，以提高資料庫表查詢的效率，但是物化視圖重新整理的頻率不能過高，因為這樣也會影響整個資料庫系統的效能。

3）  為保證資料的一致性和安全性，推薦將普通視圖設定成唯讀類型，不能做任何刪除操作。

9          代碼程式中使用索引的原則

1）  盡量少用in操作符。使用exists替代in操作符，使查詢的子表能夠使用索引，提高資料庫表查詢的效率。

2）  在where條件的後面盡量不用<>或!=。例如，條件count<0可以修改成count>0 or count<0，這樣可以發揮索引的作用，提高資料庫表查詢的效率。

3）  Like語句盡量不要使用萬用字元“%”或者“_”作為查詢條件的第一個字元。例如，count like ‘%100%’，這個條件會對資料庫表進行全表掃描，嚴重影響了資料庫表查詢的效能，可以修改成count like ‘2100%’ or count like ‘3100%’，這樣count欄位會利用該欄位上的索引進行不同範圍的查詢，大大提高了資料庫表的查詢效率。

4）  因為運算的欄位不能進行任何索引，所以where子句應該盡量避免在索引欄位上做任何計算。例如，substr(count,1,3)=’100’可以修改成count like ‘100%’。

5）  在where條件的後面，一般將表串連語句寫在最前面，將可以濾掉大量記錄的條件寫在最後。因為Oracle進行多表查詢時，是從後往前執行SQ代碼的。

10      代碼程式中的一些建議

1）  盡量不使用select *語句，最好的辦法是將要查詢的欄位全部列出，以提高SQL啟動並執行效率。因為Oracle在解析的過程中，會將“*”依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味著將耗費更多的時間和資料庫資源。

2）  盡量多使用execute immediate語句，可以提高SQL執行的效率。

3）  在使用遊標時，如果將大量的資料集合賦給遊標，運行時一般會出現記憶體溢出的報錯資訊。這時需要調整緩衝區的大小，所以使用遊標時要注意記憶體大小的問題。

4）  對於超大資料量的表應該每隔一段時間執行一次收集統計資訊的操作。例如，執行dbms_stats.gather_table_stats命令，將統計資訊傳遞給Oracle最佳化器，以提高資料庫系統的效能。

5）  物件導向的最佳實務方法是為每個屬性都定義一個get方法，但是不能把物件導向的實現方法應用於關係型資料庫中。關係型資料庫中的表不能等同於物件導向中的類，表中的欄位同樣不能等同於類的屬性，因此查詢資料表中的欄位時，應該一次性全部取出。

6）  謹慎使用自訂的函數。自訂函數通常會影響最佳化器對查詢的最佳化作用。

7）  檢查滿足某個條件的記錄是否存在，絕對不要使用select count(*)語句去判斷，可以考慮使用merge語句判斷該記錄是否存在。

總之，編寫SQL代碼程式的基本原則是儘可能減少資料庫的串連，盡量減少表之間的關聯（在表的設計過程中可以將第三範式的錶轉化成第二範式），少用暫存資料表，避免將大批量的資料分割成小塊的資料去處理。

此外，建議在表設計過程中盡量避免使用BLOB、CLOB等大欄位，因為這樣做可能會對資料庫的遷移備份等造成不必要的麻煩。

 

elvis

2012.12.23

知識共用~共同進步

轉載請註明：

http://blog.csdn.net/elvis_dataguru/article/details/8393933

商業智慧領域需要瞭解的資料庫最佳化理論