資料庫優化策略

Ⅰ 基於DB2的資料庫應用系統的性能優化

摘要 結合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操作）的列上建立索引 按索引來排序或分組 可以提高效率

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

●如果待排序的列有多個 可以在這些列上建立復合索引（pound index） 即索引由多個欄位復合而成

查詢優化

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

． 排序

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

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

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

●排序的列來自不同的表

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

． 主鍵

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

． 嵌套查詢

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

SELECT CreateUserFROM Order WHERE OrderNo IN(SELECT OrderNoFROM OrderDetail WHERE Price= )

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

SELECT CreateUserFROM Order OrderDetailWHERE Order OrderNo=OrderDetail OrderNo 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 Procere Builder可以產生存儲過程 運行並測試存儲過程 存儲過程可以包含巨大而復雜的查詢或SQL操作 經過編譯後存儲在DB 資料庫中 用戶在多次使用同樣的SQL操作時 可以先把這些SQL操作做成存儲過程 在需要用到的地方直接引用其名字進行調用 存儲過程在第一次執行時建立優化的查詢方案 DB 將查詢方案保存在高速緩存里 以後調用運行時可以直接從高速緩存執行 省去了優化和編譯的階段 節省了執行時間 從而提高效率和系統利用率

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

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

Ⅱ 關系代數表達式的優化策略中，首先要做的是

關系代數表達式的優化策略中，首先要做的是：盡早執行選擇運算。
關系代數是關系資料庫系統查宏稿讓詢語言的理論基礎。

一、關系代數的9種操作：
關系代數中包括了：並、交、差、乘、選擇、投影、聯接、除、自然敬褲聯接等操作。

五個基本操作：

並(∪)、差(-)、笛卡爾積(×)、投影(σ)、選擇(π)
四個組合操作：

交(∩)、聯接(等值聯接)、自然聯接(RS)、除法(÷)

注意：等值連接表示先做笛卡爾積(×)之後，對相應列蔽局進行選擇或等值關聯後的結果(僅篩選行、不篩選列)

注2：自然連接表示兩個關系中若有相同名稱的屬性，則自動作為關聯條件，且僅列出一列

二、關系代數表達式：
由關系代數運算經有限次復合而成的式子稱為關系代數表達式。這種表達式的運算結果仍然是一個關系。可以用關系代數表達式表示對資料庫的查詢和更新操作。
三、關系代數表達式的優化：
目的：為了系統在執行時既省時間又能提高效率。
基本策略：先做選擇，運用投影去除多餘屬性等等。
優化演算法：語法樹(盡量提前做選擇操作；在每個操作後，應做個投影操作，去掉不用的屬性值)。
例如：
π SNO,SNAME(σGRADE>60(SSC)) 進行優化後轉換為：
π SNO,SNAME(πSNO,SNAME(S)πSNO(σGRADE>60(SC)))
--即提前做選擇操作；在每個操作後，應做個投影操作，去掉不用的屬性值。

Ⅲ 那個mysql 子查詢和連接查詢 一般常用哪個 誰效率高些

子查詢優化策略

對於不同類型的子查詢，優化器會選擇不同的策略。

1. 對於 IN、=ANY 子查詢，優化器有如下策略選擇：

• semijoin

• Materialization

• exists

2. 對於 NOT IN、<>ALL 子查詢，優化器有如下策略選擇：

• Materialization

• exists

3. 對於 derived 派生表，優化器有如下策略選擇：

• derived_merge，將派生表合並到外部查詢中（5.7 引入 ）；

• 將派生表物化為內部臨時表，再用於外部查詢。

注意：update 和 delete 語句中子查詢不能使用 semijoin、materialization 優化策略

Ⅳ 怎麼進行mysql資料庫優化

有八個方面可以對mysql進行優化：
1、選取最適用的欄位屬性
MySQL可以很好的支持大數據量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中欄位的寬度設得盡可能小。
2. 使用連接（JOIN）來代替子查詢(Sub-Queries)
MySQL從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。
3、使用聯合(UNION)來代替手動創建的臨時表
MySQL從4.0的版本開始支持union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合並的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。
4、事務
盡管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯合（UNION）來創建各種各樣的查詢，但不是所有的資料庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個數據同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，資料庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成數據的不完整，甚至會破壞資料庫中的數據。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗
5、鎖定表
盡管事務是維護資料庫完整性的一個非常好的方法，但卻因為它的獨占性，有時會影響資料庫的性能，尤其是在很大的應用系統中。由於在事務執行的過程中，資料庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。其實，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。
6、使用外鍵
鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。
7、使用索引
索引是提高資料庫性能的常用方法，它可以令資料庫伺服器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，性能提高更為明顯。
8、優化的查詢語句
絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。