現在某家企業的Oracle數據庫中有一張產品信息表這張表中的記錄已經超過了萬條其中成品信息大概萬條零件記錄有萬條左右剩余的都是包裝信息數據庫工程師通過相關的分析與監測用戶訪問這張表的時候會有嚴重的等待現象這主要是因為這張表中的數據存放在同一塊硬盤上當不同的用戶並發訪問這張表時會因為磁盤I/O性能的瓶頸而導致等待如下圖所示當各位讀者遇到這種情況該如何采取措施來優化性能呢?筆者這裡的建議是采用Oracle分區表減少磁盤的I/O沖突改善數據庫的性能
一分區表的原理與優勢
分區表對於提高大表的訪問性能會有很大的幫助如上圖所示可以將一張產品信息表分按產品類別分為三個部分分別為成品信息零件信息和原材料信息然後將這三部分對應一個單獨的分區並將它們存放在不同的硬盤上此時當不同的用戶訪問不同的信息時就可以有效的降低磁盤的I/O沖突即使是同一個用戶需要同時訪問這三部分信息如產品的物料清單時由於其分別從不同的硬盤中讀取數據為此也可以明顯的降低磁盤I/O沖突
所以分區的基本原理就是通過訪問一個表或者索引的較小片斷而不是訪問整個表和索引以提高數據庫的性能當然這有一個前提條件需要將一個表的不同分區放置在不同的磁盤上此時磁盤整體的吞吐量就會成倍上升
采取分區不僅可以提高用戶訪問時的性能而且還可以提高備份時的靈活性如上面這個例子將產品信息根據其類別分為不同的區並將它們保存在不同的硬盤上此時就可以對各自的分區進行單獨的備份如最近因為國家環保的要求原材料信息進行了大規模的調整此時就可以對原材料信息所在的分區數據進行單獨的備份這不僅可以減少備份的時間而且可以降低備份作業過程中出現的I/O沖突問題
一般來說分區表的優勢有一個前提就是需要將分區數據放置在不同的磁盤上如果不這麼做的話那麼分區往往很難起到降低磁盤I/O的效果所以為了最大程度的降低一個大表的磁盤I/O(特別是經常會有並發行的訪問)此時應該將表分割到多個分區上然後再將這些分區存放在不同的磁盤上
二分區表模式的選擇
那麼該如何對表進行分區呢?這又是一個比較關鍵的問題根據經驗筆者認為要讓分區取得更好的效果分區表模式的選擇至關重要也就是說按什麼內容對表進行分區管理有時候先同的數據采取不同的分區表模式往往會有不同的效果在下面的內容中筆者會結合企業實際應用的情景對分區表模式的選擇進行舉例希望這些內容能夠幫助各位讀者更好的維護分區表
第一種模式按行來進行分區
上面這個例子中筆者談到有一張產品信息表其包括成品信息零件信息和原材料信息當這張表的記錄比較多並且當用戶訪問這張表時已經出現了嚴重的I/O沖突的時候則就可以根據行記錄來進行分區如在產品信息表中有一個產品類別的字段數據庫管理員就可以根據這個字段對標進行分區具體的分區方法也比較簡單在建立表的時候在產品類別字段後加上Partion關鍵字然後指定按字段的內容進行分區這個操作比較簡單筆者就不過多展開了筆者這裡需要強調的是分區完之後一定要將數據存放在不同的磁盤上即不同的表空間否則的話不能夠起到改善磁盤I/O的效果
第二種模式按列來進行分區
在實際工作中還有這麼一種情況如現在有一張員工信息表這這張表中除了包含員工的基本信息如身份證號碼姓名籍貫等內容還包括員工的身份證復印件或者照片等圖片信息大家都知道圖片信息的數據流量是很大的有可能一張身份證復印件的數據流量相當於幾千條的員工基本信息而且當用戶訪問員工信息表的時候並不是每個時候都需要查看身份證復印件大部分時候他們可能只是查詢員工的聯系方式或者住址等等另外一般身份證復印件等照片不會隨意更改而員工的聯系方式或這住址等等則更改比較頻繁此時如果需要更改員工信息卻將不需要更改的員工身份證復印件也查詢出來顯然那這會加重磁盤的I/O沖突
當企業存在這種情況時也可以對這個表進行分區此時分區並不是對行進行分區而是對列進行分區如可以將身份證復印件信息或者照片信息分為一個獨立的分區並將其保存在另外一個硬盤上這麼做能夠帶來如下的好處
一是在數據訪問時可以指定是否需要查詢身份證信息所在的分區在查詢員工信息的時候在語句中可以指定從哪個分區中查詢信息當用戶平時只是查詢員工的聯系方式或者住址時就不需要訪問身份證復印件所在的分區如可以使用如下語句查詢
Select * from ad_user partition(uinfo) –假設員工的基本信息存放在分區Unifo中
在查詢語句中使用Partition關鍵字可以指定其查詢的是哪個分區如果不特指的話則系統會查詢這個表所對應的全部分區而指定的話就只訪問某個特定分區的內容上面這條語句就只讀取Uinfo分區中的信息而不會讀取身份證復印件等相關信息如此的話就可以降低磁盤的I/O沖突減少不必要的數據流量提高查詢的性能
二是方便對數據的備份根據使用習慣一般身份證復印件或者員工照片等信息不怎麼會更改為此對這些數據的備份頻率可以比較低一點況且這些信息的容量往往會很大如果經常對其進行備份顯然會增加磁盤的I/O負擔而對於員工的聯系信息或者住址等等其變化的頻率就會高許多對這些信息就需要進行經常性的備份對大表進行分區管理還有一個很大的優勢就在於可以對各個分區中的數據采取獨立的備份為此就可以對身份證復印件所在的分區進行單獨的備份如一個月或者有大的變動時進行備份而對於其他的信息則可以每天進行備份這就可以實現在性能與安全方面的均衡一般來說在訪問某個表時如果經常需要訪問的信息只是特定的幾列而不需要訪問的信息容量比較大此時就可以采用按列分區的模式在這種情況下用戶就可以在查看某個分區內容的時候避免訪問其他分區同時還可以在不妨礙其它分區的情況下對某個分區的數據進行獨立的備份
第三種分區模式散列分區
有時候某個表可能沒有明顯的分區特征即不符合上面提到的這些情況但是表中的記錄又非常的多在這種情況下也有必要進行分區不過此時我們可以讓系統進行隨機的分區這種分區模式就叫做散列分區通常情況下為了提高散列分區的效果即得到一個比較均勻的分布往往可以將的N次方指定為散列分區數一般來說N越大其分布的越均勻
也就是說當數據庫管理員不知道該如何對表進行分區時但確實有分區的必要時可以使用散列分區不過筆者需要提醒的是散列分區其有一個重大的限制在使用散列分區的時候僅僅支持本地索引而不支持其他的索引方式這一點需要特別的注意在實際工作中不能夠因為采取了散列分區而降低或者取消了索引這往往是得不償失的不可行
From:http://tw.wingwit.com/Article/program/Oracle/201311/16752.html
