摘要 結合DB的使用經驗從數據庫設計查詢優化並發控制客戶/服務器模式四個方面來討論數據庫應用系統性能優化的一些原則方法等
關鍵詞 DB 性能優化 數據庫設計 查詢優化 並發控制 C/S模式
引言
DB是一種高性能的大型關系數據庫管理系統廣泛的應用在客戶/服務器體系結構中評價系統性能優化的標准有吞吐量響應時間並行能力等本文從數據庫的設計查詢的優化並發控制以及客戶/服務器模式這四個角度來討論優化系統性能
設計數據庫
熟悉業務系統
對業務系統的熟悉程度對整個數據庫系統的性能有很大影響一個對業務不熟悉的設計人員盡管有豐富的數據庫知識也很難設計出性能最佳的數據庫應用系統
規范化與非規范化
數據庫被規范化後減少了數據冗余數據量變小數據行變窄這樣DB的每一頁可以包括更多行那麼每一區裡的數據量更多從而加速表的掃描改進了單個表的查詢性能但是當查詢涉及多個表的時候需要用很多連接操作把信息從各個表中組合在一起導致更高的CPU和I/O花銷那麼有很多時候需要在規范化和非規范化之間保持平衡用適當的冗余信息來減少系統開銷用空間代價來換取時間代價有訂單信息表OrderDetail它裡面記錄了投遞員信息收款員信息物品信息價格策略客戶信息…這些信息分別在投遞員信息表收款員信息表物品信息表價格策略表客戶信息表中存放如果按照規范化的要求OrderDetail查詢時就必須要與這麼多個表進行連接或者嵌套查詢如果OrderDetail表中的數據量是在百萬級的那麼一次查詢所需要的時間可能會達到好幾個小時事實上只要在設計時保證數據的邏輯有效性很多信息都可以直接冗余在OrderDetail表中這些冗余的數據能夠極大的提高查詢的效率從而減少CPU和I/O操作
數據條帶化
如果一個表的記錄條數超過一定的規模那麼最基本的查詢操作也會受到影響需要將該表根據日期水平劃分把最近最經常用的數據和歷史的不經常用的數據劃分開來或是根據地理位置部門等等進行劃分還有一種劃分方式――垂直劃分即把一個屬性列很多的表分割成好幾個小表比如把經常用到的屬性放在一個表裡不經常用到的屬性放在另一個表裡這樣可以加快表的掃描提高效率
選擇數據類型
對每一屬性選擇什麼樣的數據類型很大程度上依據表的要求但是在不違背表要求的前提下選擇適當的數據類型可以提高系統性能比如有text列存放一本書的信息用BLOB而不是character()BLOB存放的是指針或者文件參照變量真正的文本信息可以放在數據庫之外從而減少數據庫存儲空間使得程序運行的速度提高DB提供了UDT（User Defined Datatypes）功能用戶可以根據自己的需要定義自己的數據類型
選擇索引
索引是數據庫中重要的數據結構它的根本目的就是為了提高查詢效率現在大多數的數據庫產品都采用IBM最先提出的ISAM索引結構使用索引可以快速直接有序的存取數據索引的建立雖然加快了查詢另一方面卻將低了數據更新的速度因為新數據不僅要增加到表中也要增加到索引中另外索引還需要額外的磁盤空間和維護開銷因此要合理使用索引
●在經常進行連接但是沒有指定為外鍵的屬性列上建立索引
●在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引按索引來排序或分組可以提高效率
●在條件表達式中經常用到的不同值較多的列上建立檢索在不同值少的列上不要建立索引
●如果待排序的列有多個可以在這些列上建立復合索引（compound index）即索引由多個字段復合而成
查詢優化
現在的數據庫產品在系統查詢優化方面已經做得越來越好但由於用戶提交的SQL語句是系統優化的基礎很難設想一個原本糟糕的查詢計劃經過系統的優化之後會變得高效因此用戶所寫語句的優劣至關重要下面重點說明改善用戶查詢計劃的解決方案
． 排序
在很多時候應當簡化或避免對大型表進行重復的排序當能夠利用索引自動以適當的次序產生輸出時可以避免排序的步驟當以下的情況發生時排序就不能省略
●索引中不包括一個或幾個待排序的列
●group by或order by子句中列的次序與索引的次序不一樣
●排序的列來自不同的表
為了避免不必要的排序就要正確地增建索引合理地合並數據庫表盡管有時可能影響表的規范化但相對於效率的提高是值得的如果排序不可避免那麼應當試圖簡化它如縮小排序列的范圍等
． 主鍵
主鍵用整型會極大的提高查詢效率而字符型的比較開銷要比整型的比較開銷大很多用字符型數據作主鍵會使數據插入更新與查詢的效率降低數據量小的時候這點降低可能不會被注意可是當數據量大的時候小的改進也能夠提高系統的響應速度
． 嵌套查詢
在SQL語言中一個查詢塊可以作為另一個查詢塊中謂詞的一個操作數因此SQL查詢可以層層嵌套例如在一個大型分布式數據庫系統中有訂單表Order訂單信息表OrderDetail如果需要兩表關聯查詢
SELECT CreateUser
FROM Order
WHERE OrderNo IN
(SELECT OrderNo
FROM OrderDetail
WHERE Price=)
在這個查詢中找出報紙單價為元的收訂員名單下層查詢返回一組值給上層查詢然後由上層查詢塊再根據下層塊提供的值繼續查詢在這種嵌套查詢中對上層查詢的每一個值OrderNo下層查詢都要對表OrderDetail進行全部掃描執行效率顯然不會高在該查詢中有層嵌套如果每層都查詢行那麼這個查詢就要查詢萬行數據在系統開銷中對表Order的掃描占％對表OrderDetail的搜索占％如果我們用連接來代替即
SELECT CreateUser
FROM OrderOrderDetail
WHERE OrderOrderNo=OrderDetailOrderNo AND Praice=
那麼對表Order的掃描占％對表OrderDetail的搜索占％
而且一個列的標簽同時在主查詢和where子句中的查詢中出現那麼很可能當主查詢中的列值改變之後子查詢必須重新查詢一次查詢嵌套層次越多效率越低因此應當盡量避免子查詢如果子查詢不可避免那麼要在子查詢中過濾掉盡可能多的行
． 通配符
在SQL語句中LIKE關鍵字支持通配符匹配但這種匹配特別耗費時間例如SELECT * FROM Order WHERE CreateUser LIKE M_ _ _ 即使在CreateUser字段上建立了索引在這種情況下也還是采用順序掃描的方式Order表中有條記錄就需要比較次如果把語句改為SELECT * FROM Order WHERE CreateUser >M AND CreateUser <N在執行查詢時就會利用索引來查詢顯然會大大提高速度
． distinct
使用distinct是為了保證在結果集中不出現重復值但是distinct會產生一張工作表並進行排序來刪除重復記錄這會大大增加查詢和I/O的操作次數因此應當避免使用distinct關鍵字
． 負邏輯
負邏輯如!=<>not in等都會導致DB用表掃描來完成查詢當表較大時會嚴重影響系統性能可以用別的操作來代替
． 臨時表
使用臨時表時數據庫會在磁盤中建立相應的數據結構因為內存的訪問速度遠遠大於外部存儲器的訪問速度在復雜查詢中使用臨時表時中間結果會被導入到臨時表中這種磁盤操作會大大降低查詢效率另外在分布式系統中臨時表的使用還會帶來多個查詢進程之間的同步問題所以在進行復雜查詢時最好不要使用臨時表
． 存儲過程
DB中的Stored Procedure Builder可以產生存儲過程運行並測試存儲過程存儲過程可以包含巨大而復雜的查詢或SQL操作經過編譯後存儲在DB數據庫中用戶在多次使用同樣的SQL操作時可以先把這些SQL操作做成存儲過程在需要用到的地方直接引用其名字進行調用存儲過程在第一次執行時建立優化的查詢方案DB將查詢方案保存在高速緩存裡以後調用運行時可以直接從高速緩存執行省去了優化和編譯的階段節省了執行時間從而提高效率和系統利用率
最優的查詢方案按照某些標准選擇往往不可行要根據實際的要求和具體情況通過比較進行選擇DB提供的Query Patroller可以對不同的查詢方案的查詢代價進行比較通過追蹤查詢語句返回查詢不同階段的系統開銷從而作出最佳選擇DB提供的Performance Monitor也對整個數據庫系統的性能進行監控包括I/O時間查詢次數排序時間同步讀寫時間等等
數據庫系統的並發控制也能影響系統性能多個用戶的同時操作可能導致數據的不一致性DB為了防止同時修改造成數據丟失和訪問未被提交的數據以及數據的保護讀采用Lock機制來實現控制
DB中可以對表空間表表列和索引加鎖鎖的粒度越大鎖越簡單開銷小並發度低粒度小鎖機制復雜開銷大並發度高大型系統在並發處理中如果遇到所要分配的資源處於鎖定狀態系統會把進程掛起等待如果一個很耗時的查詢操作工作於一個經常使用的表上此時使用表一級鎖意味著整個系統都要等待你的查詢結束以後才能夠繼續運行所以在復雜查詢中盡量避免使用表一級鎖如果有這一類的需要該怎麼辦呢？可以利用視圖來解決這一類問題視圖避免了對表的直接操作同時有能夠保證數據庫的高效運轉
From:http://tw.wingwit.com/Article/program/DB2/201311/21921.html
