程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 深入研究DB2數據庫的優化

深入研究DB2數據庫的優化

編輯:DB2教程

本文從數據庫設計、查詢優化、查詢的優化、並發控制以及客戶/服務器模式等方面對DB2數據庫的系統性能優化進行了深入的研究探討,供您參考,希望能夠對您有所啟迪。

DB2是一種高性能的大型關系數據庫管理系統,廣泛的應用在客戶/服務器體系結構中。評價系統性能優化的標准有:吞吐量、響應時間、並行能力等。本文從數據庫的設計、查詢的優化、並發控制以及客戶/服務器模式這四個角度來討論優化系統性能。

設計數據庫

1. 熟悉業務系統

對業務系統的熟悉程度對整個數據庫系統的性能有很大影響,一個對業務不熟悉的設計人員,盡管有豐富的數據庫知識,也很難設計出性能最佳的數據庫應用系統。

2. 規范化與非規范化

數據庫被規范化後,減少了數據冗余,數據量變小,數據行變窄。這樣DB2的每一頁可以包括更多行,那麼每一區裡的數據量更多,從而加速表的掃描,改進了單個表的查詢性能。但是,當查詢涉及多個表的時候,需要用很多連接操作把信息從各個表中組合在一起,導致更高的CPU和I/O花銷。那麼,有很多時候需要在規范化和非規范化之間保持平衡,用適當的冗余信息來減少系統開銷,用空間代價來換取時間代價。有訂單信息表OrderDetail,它裡面記錄了投遞員信息,收款員信息,物品信息,價格策略,客戶信息…..這些信息分別在投遞員信息表、收款員信息表、物品信息表、價格策略表、客戶信息表中存放。如果按照規范化的要求,OrderDetail查詢時就必須要與這麼多個表進行連接或者嵌套查詢。如果OrderDetail表中的數據量是在百萬級的,那麼一次查詢所需要的時間可能會達到好幾個小時。事實上,只要在設計時保證數據的邏輯有效性,很多信息都可以直接冗余在OrderDetail表中,這些冗余的數據能夠極大的提高查詢的效率,從而減少CPU和I/O操作。

3. 數據條帶化

如果一個表的記錄條數超過一定的規模,那麼最基本的查詢操作也會受到影響,需要將該表根據日期水平劃分,把最近、最經常用的數據和歷史的、不經常用的數據劃分開來,或是根據地理位置、部門等等進行劃分。還有一種劃分方式――垂直劃分,即把一個屬性列很多的表分割成好幾個小表,比如把經常用到的屬性放在一個表裡,不經常用到的屬性放在另一個表裡,這樣可以加快表的掃描,提高效率。

4. 選擇數據類型

對每一屬性選擇什麼樣的數據類型很大程度上依據表的要求,但是在不違背表要求的前提下,選擇適當的數據類型可以提高系統性能。比如有text列存放一本書的信息,用BLOB而不是character(1024),BLOB存放的是指針或者文件參照變量,真正的文本信息可以放在數據庫之外,從而減少數據庫存儲空間,使得程序運行的速度提高。DB2提供了UDT(User Defined Datatypes)功能,用戶可以根據自己的需要定義自己的數據類型。

5. 選擇索引

索引是數據庫中重要的數據結構,它的根本目的就是為了提高查詢效率。現在大多數的數據庫產品都采用IBM最先提出的ISAM索引結構。使用索引可以快速、直接、有序的存取數據。索引的建立雖然加快了查詢,另一方面卻將低了數據更新的速度,因為新數據不僅要增加到表中,也要增加到索引中。另外,索引還需要額外的磁盤空間和維護開銷。因此,要合理使用索引:

●在經常進行連接,但是沒有指定為外鍵的屬性列上建立索引。

●在頻繁進行排序或分組(即進行group by或order by操作)的列上建立索引。按索引來排序或分組,可以提高效率。

●在條件表達式中經常用到的不同值較多的列上建立檢索,在不同值少的列上不要建立索引。

●如果待排序的列有多個,可以在這些列上建立復合索引(compound index),即索引由多個字段復合而成。

查詢優化

現在的數據庫產品在系統查詢優化方面已經做得越來越好,但由於用戶提交的SQL語句是系統優化的基礎,很難設想一個原本糟糕的查詢計劃經過系統的優化之後會變得高效,因此用戶所寫語句的優劣至關重要。下面重點說明改善用戶查詢計劃的解決方案。

1. 排序

在很多時候,應當簡化或避免對大型表進行重復的排序。當能夠利用索引自動以適當的次序產生輸出時,可以避免排序的步驟,當以下的情況發生時,排序就不能省略:

●索引中不包括一個或幾個待排序的列;

●group by或order by子句中列的次序與索引的次序不一樣;

●排序的列來自不同的表。

為了避免不必要的排序,就要正確地增建索引,合理地合並數據庫表,盡管有時可能影響表的規范化,但相對於效率的提高是值得的。如果排序不可避免,那麼應當試圖簡化它,如縮小排序列的范圍等。

2. 主鍵

主鍵用整型會極大的提高查詢效率,而字符型的比較開銷要比整型的比較開銷大很多,用字符型數據作主鍵會使數據插入、更新與查詢的效率降低。數據量小的時候這點降低可能不會被注意,可是當數據量大的時候,小的改進也能夠提高系統的響應速度。

3. 嵌套查詢

在SQL語言中,一個查詢塊可以作為另一個查詢塊中謂詞的一個操作數。因此,SQL查詢可以層層嵌套。例如在一個大型分布式數據庫系統中,有訂單表Order、訂單信息表OrderDetail,如果需要兩表關聯查詢:

SELECT CreateUser
FROM Order 
WHERE OrderNo IN
( SELECT OrderNo
FROM OrderDetail 
WHERE Price=0.5)

在這個查詢中,找出報紙單價為0.5元的收訂員名單。下層查詢返回一組值給上層查詢,然後由上層查詢塊再根據下層塊提供的值繼續查詢。在這種嵌套查詢中,對上層查詢的每一個值OrderNo,下層查詢都要對表OrderDetail進行全部掃描,執行效率顯然不會高。在該查詢中,有2層嵌套,如果每層都查詢1000行,那麼這個查詢就要查詢100萬行數據。在系統開銷中,對表Order的掃描占82%,對表OrderDetail的搜索占16%。如果我們用連接來代替,即:

SELECT CreateUser
FROM Order,OrderDetail
WHERE Order.OrderNo=OrderDetail.OrderNo AND Praice=0.5

那麼對表Order的掃描占74%,對表OrderDetail的搜索占14%。

而且,一個列的標簽同時在主查詢和where子句中的查詢中出現,那麼很可能當主查詢中的列值改變之後,子查詢必須重新查詢一次。查詢嵌套層次越多,效率越低,因此應當盡量避免子查詢。如果子查詢不可避免,那麼要在子查詢中過濾掉盡可能多的行。

4. 通配符

在SQL語句中,LIKE關鍵字支持通配符匹配,但這種匹配特別耗費時間。例如:SELECT * FROM Order WHERE CreateUser LIKE ‘M_ _ _’ 。即使在CreateUser字段上建立了索引,在這種情況下也還是采用順序掃描的方式,Order表中有1000條記錄,就需要比較1000次。如果把語句改為SELECT * FROM Order WHERE CreateUser >’M’ AND CreateUser <’N’,在執行查詢時就會利用索引來查詢,顯然會大大提高速度。

5. distinct

使用distinct是為了保證在結果集中不出現重復值,但是distinct會產生一張工作表,並進行排序來刪除重復記錄,這會大大增加查詢和I/O的操作次數。因此應當避免使用distinct關鍵字。

6. 負邏輯

負邏輯如!=、<>、not in等,都會導致DB2用表掃描來完成查詢。當表較大時,會嚴重影響系統性能,可以用別的操作來代替。

7. 臨時表

使用臨時表時數據庫會在磁盤中建立相應的數據結構,因為內存的訪問速度遠遠大於外部存儲器的訪問速度,在復雜查詢中使用臨時表時,中間結果會被導入到臨時表中,這種磁盤操作會大大降低查詢效率。另外,在分布式系統中,臨時表的使用還會帶來多個查詢進程之間的同步問題。所以,在進行復雜查詢時最好不要使用臨時表。

8. 存儲過程

DB2中的Stored Procedure Builder可以產生存儲過程,運行並測試存儲過程。存儲過程可以包含巨大而復雜的查詢或SQL操作,經過編譯後存儲在DB2數據庫中。用戶在多次使用同樣的SQL操作時,可以先把這些SQL操作做成存儲過程,在需要用到的地方直接引用其名字進行調用。存儲過程在第一次執行時建立優化的查詢方案,DB2將查詢方案保存在高速緩存裡,以後調用運行時可以直接從高速緩存執行,省去了優化和編譯的階段,節省了執行時間,從而提高效率和系統利用率。

最優的查詢方案按照某些標准選擇往往不可行,要根據實際的要求和具體情況,通過比較進行選擇。DB2提供的Query Patroller可以對不同的查詢方案的查詢代價進行比較,通過追蹤查詢語句,返回查詢不同階段的系統開銷,從而作出最佳選擇。DB2提供的Performance Monitor也對整個數據庫系統的性能進行監控,包括I/O時間、查詢次數、排序時間、同步讀寫時間等等。

數據庫系統的並發控制也能影響系統性能。多個用戶的同時操作可能導致數據的不一致性,DB2為了防止同時修改造成數據丟失和訪問未被提交的數據,以及數據的保護讀,采用Lock機制來實現控制。

DB2中可以對表空間、表、表列和索引加鎖。鎖的粒度越大,鎖越簡單,開銷小,並發度低;粒度小,鎖機制復雜,開銷大,並發度高。大型系統在並發處理中如果遇到所要分配的資源處於鎖定狀態,系統會把進程掛起等待。如果一個很耗時的查詢操作工作於一個經常使用的表上,此時使用表一級鎖,意味著整個系統都要等待你的查詢結束以後才能夠繼續運行。所以在復雜查詢中,盡量避免使用表一級鎖。如果有這一類的需要該怎麼辦呢?可以利用視圖來解決這一類問題。視圖避免了對表的直接操作,同時有能夠保證數據庫的高效運轉。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved