數據庫物理設計經驗談

概述
我們無論使用哪種數據庫，無論怎樣設計數據庫，我想都會遵從一個原則：數據安全性和性能高效這兩個主要方面，但是關於這兩個方面的話題太多，在這裡就不一一陳述，我只是從數據庫物理分布設計方面和大家一起簡單的探討一下。因為數據庫良好的物理分布設計也是對數據安全性和性能高效影響比較大, 就象我們在建大樓之前一定要先打好地基一樣。
現實中我們在應用各種不同數據庫的時候，往往會忽略數據庫的物理布局，只有在數據庫性能遇到問題的時候才去考慮，但這是得不償失的，這樣一來不僅會導致與設計相關的問題出現，而且會影響性能的調整效果，所以我們在創建數據庫之前先進行規劃數據庫的物理布局也是很必要的，這也符合人們常說的”磨刀不誤砍柴工”的道理。下面我就以Oracle為例從優化操作系統、磁盤布局優化和配置、數據庫初始化參數的選擇、設置和管理內存、設置和管理CPU、設置和管理表空間、設置和管理回滾段、設置和管理聯機重做日志、設置和管理歸檔重做日志、設置和管理控制文件等幾個方面作以陳述。

一. 優化操作系統
為了獲得最佳的服務器性能, 對操作系統的優化也是很必要的, 因為操作系統性能問題通常會涉及到進程管理、內存管理、調度等，所以用戶需要確保有足夠的I/O帶寬、CPU的處理能力、交換空間來盡可能的降低系統時間。如果應用程序在緩沖區出現過多的”忙”等待, 那麼系統調用的進程將會增加, 雖然可以通過優化SQL語句等方法來降低調用的數目，但是這也是治病不治根的。用戶可以啟動Oracle的初始化參數timed_statistics來增加系統調用的數目, 反之如果關閉此參數,那麼系統調用的數目也會減少。操作系統的緩存和Oracle自己的緩存管理是不相沖突的, 雖然它能消耗一定的資源, 但是它對性能還是有一定好處的, 因為一般所有數據庫的I/O需要通過系統文件緩存來訪問文件存儲器。
Oracle的操作可能會用到許多的進程(有的系統叫線程), 所以用戶應該確保所有Oracle的進程、後台進程、用戶進程具有相同的優先級, 否則就會產生惡化的現象, 導致高優先級的進程等待低優先級的進程處理完畢釋放出CPU資源後再處理, 更不能將Oracle的後台進程綁定到CPU中, 這樣一來也會導致被綁定的進程被CPU資源餓死。
比較好的是有些操作系統提供有操作系統資源管理器(Operating System Resource Manager), 通過它可以對系統資源訪問劃分優先級來降低峰值負載模式的影響，來實現多種管理策略和方法，控制用戶資源的訪問，限制用戶資源的可消耗量。

二．磁盤布局優化和配置
在大多數產品數據庫應用中，數據庫文件一般都放在磁盤上，因此磁盤的良好使用和布局也是很重要的。磁盤布局的目標是：磁盤性能是不能阻礙實現數據庫性能，數據庫磁盤必須專用於數據庫文件，否則非數據庫將會影響到該數據庫,且這種影響是不可預測的; 系統硬件和鏡像必須滿足恢復和性能的要求，數據文件大小和I/O不能超過磁盤的大小和I/O，數據庫一定是可以恢復的，必須使後台進程之間的競爭最小化。在規劃硬盤配置時也要注意：首先所用的磁盤容量，有時用多個容量小的磁盤比用一個大的磁盤效果更好，因為可以進行更高級的並行I/O操作; 其次磁盤的速度，如反應時間和尋道時間都將影響I/O的性能, 可以考慮使用合適的文件系統作為數據文件; 再者使用合適的RAID。
RAID(Redundant Arrays of Inexpensive Disks)廉價冗余陣列可以改善數據的可靠性，而I/O的性能又取決於RAID配置的方式：RAID1可以提供比較好的可靠性和較快的讀取速度，但寫的代價比較大，所以不適合頻繁寫的應用；RAID0+1在原RAID1的基礎上讀取的速度更快，所以這也是大家常會選擇的方式；RAID5可以提供比較好的可靠性，有順序的讀操作比較適合這種方式，但性能會受到影響，對於寫操作頻繁的應用也不適合這種。對於該選擇那種方式不能一概而論，要根據具體的情況而定。
有些應用軟件先天性受到磁盤的I/O限制, 所以在設計的時候應盡量使Oracle的性能不受I/O的限制, 所以在設計一個I/O系統時要考慮以下的數據庫需要: 存儲磁盤的最小字節; 可用性, 如24X7, 9X5; 性能如I/O的輸出和響應時間。決定Oracle文件的I/O統計信息可以來查詢下列: 物理讀數量(V$filestat.phyrds)、物理寫數量(V$filestat.phywrites)、平均時間, I/O=物理讀+物理寫。而I/O的平均數量=(物理讀+物理寫)/共用秒數), 估計這個數據對於新系統是有用的, 可以查詢出新應用程序的I/O需求與系統的I/O能力是否匹配以便及時調整。

三．創建數據庫初始化參數的選擇
管理數據庫的第一階段就是初始化數據庫的創建，盡管可以在數據庫創建好以後再來調整性能，但是有些參數是不能修改的或很難修改，比如： Db_block_size、Db_name、Db_domain、Compatible、Nls_language、

$False$

Nls_characterset、Nls_nchar_characterset。
Db_block_size參數決定Oracle數據庫塊的大小，一般可以選擇的范圍是2K、4K、8K、16K、32K，使用下一個較大值數據庫塊大小的效果一般可以集中查詢中性能提高50%。但是按常規來說對於一般服務器不提倡把這個值設的很大，小型機除外，因為這樣一來數據庫塊中將會有更多的行，在數據庫維護期間發生塊級競爭的可能性比較大，避免這種競爭的辦法是在表級和索引級增大Freelists、maxtrans和initrans 的設置值，通常Freelists設置為大於4會帶來更多的好處。
Db_name該參數指定一個數據庫標識符，一般在Create Database中指定的名稱，改參數是可選的（在Oracle9i實時應用集群時是必選的，多個實例有相同的參數值），但是建議在Create Database之前設置它，如果不指定則要出現在Startup或Alter Database mount命令中。
Db_domain該參數指定全局數據庫名的擴展部分，在Oracle9i實時應用集群時是必選的，多個實例有相同的參數值。
Compatible該參數指定Oracle服務器維護版本的兼容性，保證與早期的版本向下兼容的時候允許用戶使用新的版本，在Oracle9i實時應用集群時是必選的，多個實例有相同的參數值。
Nls_language和Nls_characterset及Nls_nchar_characterset三個參數是數據庫的字符集參數，在數據庫創建完成後一般也不能改變或很難改變，所以在創建數據庫的時候要先設置好。

四．設置和管理內存
Oracle使用共享內存來管理其內存和文件結構，Oracle常使用的內存結構如下：
系統全局區（System Global Area，SGA），SGA隨著不同的環境而不同，沒有一種普通的最佳方案，我們在設置它直前要先考慮以下的幾個方面：物理內存多大；操作系統是那種及占多大的內存，數據庫系統是文件系統還是裸設備；數據庫運行的模式。SGA包括：Fixed size、Variable size、Database Buffers、Redo Buffers。SGA占有物理內存的比例沒有嚴格的規定，只能遵從一般的規則：SGA占據物理內存的40%--60%左右。如果通過直觀的公式化來表達則為：OS使用內存+SGA+並發進程數*（Sort_area_size+Hash_area_size+2M）<0.7RAM,這個公式也只是參考，不必拘於此，實際情況可以自由發揮。初始化參數文件中的一些參數對SGA的大小有決定性的影響。參數Db_block_Buffers（SGA中存儲區高速緩存的緩沖區數目），參數Shared_pool_size（分配給共享SQL區的字節數），是SGA大小的主要影響者。Database Buffers 參數是SGA大小和數據庫性能的最重要的決定因素。該值較高，可以提高系統的命中率，減少I/O。每個緩沖區的大小等於參數Db_block_size的大小。Oracle數據庫塊以字節表示大小。Oracle SGA區共享池部分由庫高速緩存、字典高速緩存及其他一些用戶和服務器會話信息組成，共享池是最大的消耗成分。調整SGA區各個結構的大小，可以極大地提高系統的性能。
數據塊緩沖緩存區（Data block buffers cache），Data buffers在8i中是Db_block_buffers*Db_block_size，9i中用Db_cache_size來代替這個參數。在內存的配置中把別的參數設置完成後，應該把能給的都給Data buffers。Oracle 在運行期間向數據庫高速緩存讀寫數據，高速緩存命中表示信息已在內存中，高速緩存失敗意味著Oracle必需進行磁盤I/O。保持高速緩存失敗率最小的關鍵是確保高速緩存的大小。Oracle8i中初始化參數Db_block_

您正在看的SQLserver教程是:數據庫物理設計經驗談。buffers控制數據庫緩沖區高速緩存的大小。可通過查詢V$sysstat 命中率，以確定是否應當增加Db_block_buffers的值。
　　SELECT name,value FROM V$sysstat
　　WHERE name in (’dbblock gets’,’consistent gets’,’physical reads’);
通過查詢結果命中率=1-physical reads/(dbblock gets+consistent gets) 如果命中率<0.6～0.7，則應增大Db_block_buffers。
字典緩存區（Dictionary CACHE），數據字典緩存區的大小由數據庫內部管理，大小由參數Shared_pool_size來設置。數據字典高速緩存包括了有關數據庫的結構、用戶、實體信息等。數據字典的命中率對系統有很大的影響。命中率的計算中，getmisses 表示失敗次數，gets表示成功次數。

查詢V$ROWCACHE表：
　　SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100
　　FROM V$rowcache;
如果該值>90%，說明命中率合適。否則，應增大共享池的大小。
重做日志緩沖區（Read log buffer）,下面將有陳述，在此就不做說明。
SQL共享池（Shared pool size），該共享池包括包括執行計劃及針對數據庫執行SQL語句的語法分析用的，在第二次運行相同的SQL語句時可用SQL中的語法分析來加快執行速度。如果它太小，語句會連續不斷地再裝入到庫緩存區，從而影響性能。可以通過Alter system命令來修改此參數，9I以後的版本可以動態地修改其大小。
大池（Large pool size），是一個可選的內存區。如果選擇可對數據庫象備份/恢復這些大的操作提高性能。如果不選擇此參數，則系統會使用共享池。
JAVA池（Java pool size），由其名字而言可知，是為滿足JAVA命令語法分析的需求。在UNIX系統中如果區組的大小為4MB，則默認大小應該為24M，如果區組大小為 16MB，則默認大小為32M。如果數據庫沒有使用Java，則保持在10M—20M足夠。
多緩沖區池（Multiple buffer pools），可以使用多緩沖區池把大數據集與應用的剩余部分分開，以減少它們爭奪緩存區內相同資源的可能性，創建時需要在初始化參數中設定其大小。
程序全局區（Program global area，PGA）是Oracle的一個私有的內存區，9i以後的版本中，如果Workarea_size_policy=auto，則所有的會話共用一塊內存，該內存在參數Pga_aggregate_target設置，它的一個好的初始設置是：對於一個OLTP系統 Pga_aggregate_target=（totalL_mem*80%）*20%；對於一個DSS系統Pga_aggregate_target= （total_mem*80%）*50%。這裡的total_mem是物理內存。在調整Pga_aggregate_target參數時，下面的幾個動態視圖會有幫助的：V$sysstat和V$sesstat；V$sql_workarea_active；V$pgastat；V$ sql_workarea； V$process。

五．設置和管理CPU
在設置和安裝數據庫的過程中，基本不用對CPU做什麼配置的，系統會自動默認的，但是在管理過程中我們可以利用操作系統監控工具來監控CPU的狀況。例如在UNIX系統中，可以運行sar–u的工具來檢查整個系統使用CPU的水平。其統計信息包括：用戶時間、系統時間、空閒時間、I/O等待時間。在正常工作負載的情況下，如果空閒時間和I/O等待時間接近於0或少於5%，那就表示CPU的使用存在問題。
對於Windows系統可以通過性能監視器（Performance monitor）來檢查CPU的使用狀況可以提供以下信息：處理器時間、用戶時間、特權時間、中斷時間、DPC時間。
如果CPU的使用存在問題，則可以通過以下的方式來解決：優化系統和數據庫；增加硬件的能力；對CPU資源分配進行劃分優先級，Oracle數據庫資源管理器（Database Resource Manager）負責在用戶和應用程序之間分配和管理CPU資源。

六．設置和管理表空間
數據庫文件之間的I/O競爭是數據庫之大忌, 所以對數據庫規劃之前要先對數據文件的I/O進行初步的評估, 通常情況下, 應用的產品數據庫表所在的表空間會很活躍, 索引表空間和數據字典之類的表空間也很活躍的, 對於事物比較頻繁的應用中, 重做表空間也很活躍的, 所以對不同類型的數據庫其數據文件的I/O競爭也會略有不同的, 但是基本上還是遵從以下的原則比較好: 應用的表和索引通常應該被分配或分區到多個表空間中, 以降低單個數據文件的I/O, 最好把每一種功能相同的區域對象建立單獨的表空間; 沒有理由把除數據字典表和系統回退段外的其他東西放到系統表空間中, 要把能移出系統表空間的對象都移出; 索引段不應該和相關表放在同一表空間中, 因為他們在數據管理和查詢時會產生很多的並發I/O; 臨時表空間是用以存儲大量的排序, 所以其它的應用對象是不能放在臨時表空間。
以上講的是數據庫文件分布的原理, 原理歸原理, 事實應用中, 我們還是以經驗來設置分布比較好些, 當然在沒有經驗之前還是參考原理以致於不會走彎路。
數據庫和表空間可以是一對多的關系，表空間和數據文件也可以是一對多的關系，數據文件和數據對象也可以是一對多的關系。當創建一個數據對象（如表或索引）時，可以通過默認值或特殊命令將其賦予一個表空間，這樣就會在該表空間中創建一個段（Segment）來存儲與該對象有關的數據。一個段由一些稱作區間（Extent，一組連續的Oracle塊）的區段組成，一但現有的區段不能存儲數據時，這個段就要獲得另一個區間來支持將數據插入到對象中。

因此這個段所使用的空間由它的參數決定的，這些參數可以在創建時指定，也可以在以後更改。如在Create table，Create index，Create cluster，Create rollback segment命令中沒有指定存儲參數，則數據庫會默認它存儲所在的表空間的參數，這些參數有initial，next，pctincrease， maxextents，minextents等。在創建後不能修改initial和minextents值，每個表空間的存儲參數默認值可以在 Dba_tablespaces視圖中查詢出來。
磁盤I/O是系統性能的瓶頸，解決好磁盤I/O，可明顯提高性能。通過查詢V$filestat可以知道每個物理文件的使用頻率（phyrds表示每個數據文件讀的次數，phywrts表示每個數據文件寫的次數）
　　SELECT name,phyrds,phywrts FROM V$datafile df,V$filestat fs
　　WHERE df.file# =fs.file#;
對於使用頻率較高的物理文件,可以采用以下策略: 將I/O盡可能平均分配在盡可能多的磁盤上；為表和索引建立不同的表空間；將數據文件與重做日志文件分離在不同的磁盤上；減少不經Oracle server的磁盤I/O。
如果大家沒有經驗的情況下不小心把數據文件規劃的不恰當, 以致於產生了大量的I/O竟爭現象, 那麼就要根據上面的原則重新調整數據文件的分布, 以平衡數據文件之間的I/O竟爭, 具體如何移動數據文件, 各種數據庫的方法不盡相同, 但是基本的原理還是相同的,下面是針對Oracle8i如何移動數據文件的兩種方法的舉例(9i略有不同)：
第一種方法﹕(Alter database)
關閉數據庫-移動數據庫文件-裝載並改名-啟動

1> svrmgrl
2> connect internal
3> shutdown
4> exit
5> mv /u/product/oradata/foxmold/user01.dbf /db3/oradata
6> svrmgrl
7> connect internal
8> startup mount foxmold
9> alter database rename file
‘/u/product/oradata/foxmold/user01.dbf’ to
‘/db3/oradata/user01.dbf’
10> alter d

您正在看的SQLserver教程是:數據庫物理設計經驗談。atabase open
第二種方法﹕(Alter tablespace)
關閉數據庫-移動數據庫文件-裝載並改名-啟動
1> svrmgrl
2> connect internal
3> shutdown
4> exit
5> mv /u/product/oradata/foxmold/user01.dbf /db3/oradata
6> svrmgrl
7> connect internal
10> alter database rename file
‘/u/product/oradata/foxmold/user01.dbf’ to
‘/db3/oradata/user01.dbf’
8> alter database open
上面的foxmold代表當前的database name。

七．設置和管理回滾段
回滾段一般可以處理任意大小的事物，所以也就需要大小不同的回滾段。回滾段的大小是通過創建回滾段時指定存儲子句來設置，但一般會遵從下面原則：
OLTP事物特征有許多並發的事物，每個可能只修改少量的數據，可以建立10KB到20KB大小的回滾段，每個有2到4的范圍；對於時間很長的查詢為了維護讀一致性有大量的回滾信息，所以需要較大的回滾段，建立回滾段的大小最好為最大表的10%左右（大多數查詢只影響到表約10%的數據量）。設置回滾段的大小可以通過Create rollback segment和Alter rollback segment語句來實現。一般情況下，initial=next ，設置Optimal參數來節約空間的使用 ，不要設置maxextents為unlimited，回滾段應創建在一個特定的回滾段表空間內 。
回滾段的目標容量可以用存儲參數Optimal來定義，它指定回滾段要縮小到的尺寸。

如果發現回滾段由於Optimal的緣故持續地收縮，那麼很可能是回滾段設置不恰當，這種可以通過動態性視圖V$rollstat來確定是否有問題，如：
SELECT substr(name,1,40) name,extents,resize,aveactive,aveshrink,
extends,shrink FROM V$rollname rn,V$rollstat rs WHERE rn.usn=rs.usn;
結果如下：
name extents resize aveactive aveshrint extents shrinks
----- ------- ------ ------- -------- -------- --------
system 4 202876 0 0 0 0
csirsl 2 202876 55192 0 0 0
如果平均大小接近於Optimal，則Optimal正確，如果extents和shrinks高，則必須增加Optimal值。在設計Optimal值時如果有長時間查詢和運行的事物，則應該把Optimal設置大些。
使用回滾段可以改善系統性能，減少競爭，回滾段的多少應該由數據庫中的並發事物決定，太多的事物使用一個回滾段時會發生競爭，查看動態性能表V$WAITSTAT可以查看回滾段上是否有競爭：
SELECT class,count FROM V$waitstat WHERE class in(‘undo header’,’undo block’,’system undo header’,’system undo block’);
結果如下：
CLASS COUNT
------------------------------- ---------------
system undo header 0
system undo block 0
undo header 0
undo block 0
然後將這些值和數據請求的總數相比較。數據請求的總數等於V$sysstat中的db buffer gets和consistent gets之和，查詢語句為：
SELECT sum(value) ‘Data Requests’ FROM V$systat where name in(‘db block gets’,’consistent gets’);
結果如下：
Data Requests
---------------------------
5105
如果任何一個class/sum(value)>10%,那麼考慮增加回退段。回退段的數量一般按如下規律設定：
　　用戶數　　　　　　　　　 回退段個數
n<16　　　　　 　　　　　　4
16 32<=n　　　　　　　　　　　n/4 但不超過50
　　和回滾段有關的參數還有: transactions指出並發事物的數量;transcative_per_rollback_segment指出每個回滾段的事物數量。
使用set transaction語句將事物分配到適當大小的回滾段命令如下：
set transcation use rollback segment RBS13；
把當前的事物分配到回滾段RBS13。

八．設置和管理聯機重做日志
重做日志的大小也能影響性能，因為數據庫的寫入和歸檔取決於重做日志的大小，通常情況下，更大的重做日志文件可以提供好一些的性能，小的能增加檢查點的活動和降低頻率。
不可能為一個重做日志文件提供特定大小的建議，重做日志文件在幾百兆字節到幾GB字節都被認為是合理的，根據系統產生的聯機重做數量決定日志文件的大小，一般情況下應保持在約20分鐘交換日志文件一次。
如果發生重做日志緩沖區競爭，對數據庫的性能影響也將是很大的。為了減少重做日志緩沖區競爭，我們可以通過查詢V$sysstat表判定redo log 文件緩沖區是否足夠。
　　SELECT name,value FROM V$sysstat WHERE name=’redo log space reques‘;
　　此處value的值應接近於0，否則，應增大初始化參數文件的Log_buffers的值
重做日志文件被創建後大小不能被改變，但是可以增加新的、更大的文件，並且原來的文件能被刪除。

具體的實現方法如下：
1．假設現有三個日志組，每個組內有一個成員，每個成員的大小為1MB，現在想把此三個日志組的成員大小都改為20MB。
2．創建新的日志群組
alter database add logfile group4(‘d:\oradb\redo04.log’) size 2048k;
alter database add logfile group5(‘d:\oradb\redo05.log’) size 2048k;
3. 切換當前日志到新的日志組
alter system switch logfile；
alter system switch logfile；
4．刪除舊的日志
alter database drop logfile group 1；
alter database drop logfile group 2；
alter database drop logfile group 3；
5．到操作系統下刪除原舊的日志文件組1，2，3中的文件。
6．重建日志組1，2，3
alter database add logfile group 1 ('d:\oradb\redo01_1.log') size 20M;
alter database add logfile group 2 ('d:\oradb\redo02_1.log') size 20M;
alter database add logfile group 3 ('d:\oradb\redo03_1.log') size 20M;
7．切換日志組
alter sy

您正在看的SQLserver教程是:數據庫物理設計經驗談。stem switch logfile；
alter system switch logfile；
alter system switch logfile；
8．刪除中間過渡的日志組4，5
alter database drop logfile group 4；
alter database drop logfile group 5；
9．到操作系統下刪除過渡日志文件組4，5中的文件。
10．備份當前最新的控制文件
SQL>connect internal
SQL>alter database backup controlfile to trace resetlogs;
聯機重做日志文件也是可以移動的，具體的方法是：首先關閉數據庫，移動聯機重做日志文件，然後安裝數據庫，使用alter database命令通知數據庫聯機重做日志文件的新位置。然後就可以用新位置上的日志文件打開實例。

九．設置和管理歸檔重做日志
當Oracle以archivelog模式運行時,數據庫在每個聯機重做日志文件寫滿後,對它進行拷貝,通常是寫入磁盤,也可以寫入別的設備,但這需要人為的干預的。arch後台執行歸檔功能，如果有大量頻繁的事物的時候，會產生重做日志文件磁盤方面的競爭，避免這種競爭的方式是將聯機重做日志文件分布到多個磁盤上。為了提高歸檔的性能，可以創建具有多個成員的聯機重做日志文件組，但是必須考慮到每個設備的I/O。
歸檔重做日志文件不應與system、rbs、data、temp、indexes表空間等存儲在同一個設備中，更不能與任何的聯機重做日志文件存儲在同一個設備中，以免發生磁盤的競爭。
歸檔重做日志文件備份之後是可以刪除或移走的,否則會占據比較大的空間影響硬盤使用和降低系統的性能。

十．設置和管理控制文件
控制文件的位置在實例初始化參數文件中指定的，若要移動控制文件，必須先關閉數據庫實例，移動控制文件，編輯初始化參數文件，然後重新啟動該實例。
下面就以Oracle8i為例說明如何移動控制文件:
OS為Linux,Database為Oracle8i。
1. 查詢當前數據庫的控制文件的位置
SELECT * FROM v$controlfile;
2. 把控制文件/u/oradata/foxmold/control01.ctl
移到/db4/oradata/目錄下。
3. svrmgrl
4. connect internal
5. shutdown
6. exit
7. cp /u/oradata/foxmold/control01.ctl /db4/oradata/control01.ctl
8. chmod 660 /db4/oradata/control01.ctl
9. initsid.ora control_files=…
10. startup mount foxmold
上面的foxmold代表當前的database name。

十一．總結
以上是針對Oracle數據庫來對數據庫物理設計作以簡單陳述, 針對各種不同的數據庫可能會略有不同, 但是整體思想還是一致的。
以上寫的難免有不當之處, 歡迎大家批評指正!