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，如果需要兩表關聯查詢：

以下是代碼片段：

　　from order

　　where orderno in

　　( select orderno

　　from orderdetail

　　where price=0.5)

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

　　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中可以對表空間、表、表列和索引加鎖。鎖的粒度越大，鎖越簡單，開銷小，并發度低；粒度小，鎖機制複雜，開銷大，并發度高。大型系統在并發進行中如果遇到所要配置設定的資源處于鎖定狀态，系統會把程序挂起等待。如果一個很耗時的查詢操作工作于一個經常使用的表上，此時使用表一級鎖，意味着整個系統都要等待你的查詢結束以後才能夠繼續運作。是以在複雜查詢中，盡量避免使用表一級鎖。如果有這一類的需要該怎麼辦呢？可以利用視圖來解決這一類問題。視圖避免了對表的直接操作，同時有能夠保證資料庫的高效運轉。