資料庫分段

㈠ 資料庫分區和分表的區別

分區、分表、分庫的詳細理解
一、什麼是分區、分表、分庫
分區
就是把一張表的數據分成N個區塊，在邏輯上看最終只是一張表，但底層是由N個物理區塊組成的

分表
就是把一張表按一定的規則分解成N個具有獨立存儲空間的實體表。系統讀寫時需要根據定義好的規則得到對應的字表明，然後操作它。

分庫
一旦分表，一個庫中的表會越來越多

將整個資料庫比作圖書館，一張表就是一本書。當要在一本書中查找某項內容時，如果不分章節，查找的效率將會下降。而同理，在資料庫中就是分區。

二、常用的單機資料庫的瓶頸
問題描述
單個表數據量越大，讀寫鎖，插入操作重新建立索引效率越低。
單個庫數據量太大（一個資料庫數據量到就是極限）
單個資料庫伺服器壓力過大
讀寫速度遇到瓶頸（並發量幾百）
三、分區
什麼時候考慮使用分區？
一張表的查詢速度已經慢到影響使用的時候。

sql經過優化

數據量大

表中的數據是分段的
對數據的操作往往只涉及一部分數據，而不是所有的數據

分區解決的問題
主要可以提升查詢效率

分區的實現方式（簡單）
mysql5 開始支持分區功能

四、分表
什麼時候考慮分表？
一張表的查詢速度已經慢到影響使用的時候。

sql經過優化

數據量大
當頻繁插入或者聯合查詢時，速度變慢

分表解決的問題
分表後，單表的並發能力提高了，磁碟I/O性能也提高了，寫操作效率提高了

查詢一次的時間短了
數據分布在不同的文件，磁碟I/O性能提高
讀寫鎖影響的數據量變小
插入資料庫需要重新建立索引的數據減少
分表的實現方式（復雜）
需要業務系統配合遷移升級，工作量較大

分區和分表的區別與聯系
分區和分表的目的都是減少資料庫的負擔，提高表的增刪改查效率。

分區只是一張表中的數據的存儲位置發生改變，分表是將一張表分成多張表。
當訪問量大，且表數據比較大時，兩種方式可以互相配合使用。
當訪問量不大，但表數據比較多時，可以只進行分區。

常見分區分表的規則策略（類似）
Range（范圍）
Hash（哈希）
按照時間拆分
Hash之後按照分表個數取模
在認證庫中保存資料庫配置，就是建立一個DB，這個DB單獨保存user_id到DB的映射關系

㈡ Oracle資料庫中利用ASSM改善分段存儲

為了保持其最強大和最靈活資料庫的地位 Oracle在最近發布的幾個版本里一直都在創建新的機制來對表格和索引的存儲進行簡化和分塊 從Oracle i開始 Oracle開始在tablespace內部將對象管理進行自動化 第一個增強的地方原來叫做本地管理tablespace（或者簡寫作LMT） 在LMT里 Oracle將tablespace里的信息從數據字典的表格空間里移出去 而直接將其保存到tablespace自身里 這在Oracle i里已經成為了一個事實的標准 因為它減輕了數據字典的負擔

表格空間的第二個主要增強的是自動分段空間管理（ASSM） 它首次出現在Oracle i里 有了ASSM 鏈接列表freelist被點陣圖所取代 它是一個二進制的數組 能夠迅速有效地管理存儲擴展和剩餘區塊（free block） 因此能夠改善分段存儲本質

管理空間的兩種方法

讓我們從比較這兩種空間管理開始

本地管理tablespace（LMT）——LMT是通過把EXTENT MANAGEMENT LOCAL子句添加到tablespace的定義句法而實現的 和原來由字典管理的tablespace（DMT）不同 LMT會將擴展管理自動化 並保持Oracle DBA不會被用來指定管理擴展大小的NEXT存儲參數 這個原則唯一的例外是在NEXT和MINEXTENTS一起用在表格創建的時候

自動區段空間管理（ASSM）——ASSM的tablespace是通過將SEGMENT SPACE MANAGEMENT AUTO子句添加到tablespace的定義句法里而實現的 通過使用點陣圖freelist取代傳統單向的鏈接列表freelist ASSM的tablespace會將freelist的管理自動化 並取消為獨立的表格和索引指定PCTUSED FREELISTS和FREELIST GROUPS存儲參數的能力

Oracle值得贊揚的地方是 這兩個空間管理的方法都是可選的特性 而且Oracle的老手可能仍會使用更加詳細的方法 只要他們願意的話 要注意 點陣圖區段管理在Oracle i里是可選的 而且只能在tablespace這一層實現 這一點是十分重要的 原有的系統還能夠繼續使用傳統方法來管理freelist

點陣圖freelist挑戰傳統的空間管理

在我討論點陣圖freelist和傳統的空間管理之前 讓我們看看點陣圖freelist是如何實現的 我會從使用區段空間管理自動參數創建tablespace開始

create tablespace a *** _lmt_tsdatafile c:oracleoradatadiogenesa *** _lmt dbf size mEXTENT MANAGEMENT LOCAL Turn on LMTSEGMENT SPACE MANAGEMENT AUTO Turn on ASSM;

一旦你定義好了tablespace 那麼表格和索引就能夠使用各種方法很容易地被移動到新的tablespace里 下面就是我進行創建的代碼

create table new_custtablespace as *** _lmt_tsas select * from customer; alter index cust_name_idx rebuild tablespace as *** _lmt_ts;

要注意 當表格或者索引被分配到這個tablespace以後 用於獨立對象的PCTUSED的值會被忽略 而Oracle i會使用點陣圖數組來自動地管理tablespace里表格和索引的freelist 對於在LMT的tablespace內部創建的表格和索引而言 這個NEXT擴展子句是過時的 因為由本地管理的tablespace會管理它們 但是 INITIAL參數仍然是需要的 因為Oracle不可能提前知道初始表格載入的大小 對於ASSM而言 INITIAL最小的值是三個區塊

關於一個萬能的方法對於Oracle來說是否是最好的方法還有一些爭論 在大型資料庫里 單獨的對象設置會帶來性能和存儲上的巨大不同 PCTFREE的問題

PCTFREE參數是用來指定數據塊剩餘空間大小的 這一空間為將來數據行的擴展而保留 如果PCTFREE設置得不得當 SQL的更新聲明就可能導致大量的數據行碎片和斷鏈

數據行在剛保存的時候還很小 而在後來進行了擴展 在這種情況下 PCTFREE的設置就顯得尤其重要了 在這樣的系統里 通常會把PCTFREE設置成等於 這就告訴Oracle要為數據行今後的擴展保留 ％的數據區段空間

PCTUSED的問題

對PCTUSED不正確的設置（例如設得太小了）會導致SQL插入聲明性能的急劇下降 如果數據區塊剩餘空間不是很多 那麼在SQL插入操作的過程中就會產生過量的I/O 這是因為被重新使用的Oracle數據區塊會被迅速地填滿 從極端的角度來看 沒有正確地設置PCTUSED會導致數據區塊的剩餘空間要比表格數據行的平均長度小 在這樣的情況下 Oracle會五次嘗試從freelist鏈取回區塊 在五次嘗試以後 Oracle會提升表格的水位 並為插入操作騰出五個新的數據塊

有了Oracle i的ASSM PCTUSED就不再控製表格數據塊的重新鏈接闕值了 但是你必須依靠Oracle的判斷來確定區塊在什麼時候會有足夠的剩餘空間放置到freelist里

盡管有了本地管理的tablespace和ASSM之後Oracle i會忽略PCTUSED FREELISTS和FREELIST GROUPS等參數 但是當它們用於表格定義的時候 Oracle還是不會給出錯誤信息

SQL> create table test_table (c number) tablespace a *** _test pctfree pctused storage ( freelists next m ) ;Table created

如果你不記得帶有ASSM的本地管理tablespace會略掉任何為PCTUSED NEXT和FREELISTS所指定的值的話 這將是一個十分嚴重的問題

使用ASSM的一個巨大優勢是 點陣圖freelist肯定能夠減輕緩沖區忙等待（buffer busy wait）的負擔 這個問題在Oracle i以前的版本里曾是一個嚴重的問題 現在讓我們來仔細看看這個特性

在沒有多個freelist的時候 每個Oracle表格和索引在表格的頭部都曾有一個數據塊 用來管理對象所使用的剩餘區塊 並為任何SQL插入聲明所創建的新數據行提供數據塊 當數據緩沖內的數據塊由於被另一個DML事務處理鎖定而無法使用的時候 緩沖區忙等待就會發生 當你需要將多個任務插入到同一個表格里的時候 這些任務就被強制等待 而同時Oracle會在同時分派剩餘的區塊 一次一個

有了ASSM之後 Oracle宣稱顯著地提高了DML並發操作的性能 因為（同一個）點陣圖的不同部分可以被同時使用 這樣就消除了尋找剩餘空間的串列化 根據Oracle的測試結果 使用點陣圖freelist會消除所有分段頭部（對資源）的爭奪 還能獲得超快的並發插入操作（圖A）

= ) window open( /sql/UploadFiles_ / / jpg ); src= if(this width > )this width = ;if(this height > ) this height = ; border= >

圖A Oracle公司對使用點陣圖freelist進行SQL插入操作的測試結果

ASSM的局限性

盡管ASSM顯示出了令人激動的特性並能夠簡化Oracle DBA的工作 但是Oracle i的點陣圖分段管理還是有一些局限性的

一旦DBA被分配之後 它就無法控制tablespace內部的獨立表格和索引的存儲行為

大型對象不能夠使用ASSM 而且必須為包含有LOB數據類型的表格創建分離的tablespace

你不能夠使用ASSM創建臨時的tablespace 這是由排序時臨時分段的短暫特性所決定的

只有本地管理的tablespace才能夠使用點陣圖分段管理