數據庫物理設計原則 數據庫環境配置原則
操作系統環境
對於中小型數據庫系統
采用linux操作系統比較合適
對於數據庫冗余要求負載均衡能力要求較高的系統
可以采用Oracle
i RAC的集群數據庫的方法
集群節點數范圍在
—
個
對於大型數據庫系統
可以采用Sun Solaris SPARC
位小型機系統或HP
系列小型機系統
RAD
適合只讀操作的數據庫
RAD
適合OLTP數據庫
內存要求
對於linux操作系統下的數據庫
由於在正常情況下Oracle對SGA的管理能力不超過
G
所以總的物理內存在
G以下
SGA的大小為物理內存的
%—
%
對於
位的小型系統
Oracle數據庫對SGA的管理超過
G的限制
SGA設計在一個合適的范圍內
物理內存的
%—
%
當SGA過大的時候會導致內存分頁
影響系統性能
交換區設計
當物理內存在
G以下的情況下
交換分區swap為物理內存的
倍
當物理內存>
G的情況下
swap大小為物理內存的
—
倍
其他環境變量參考Oracle相關的安裝文檔和隨機文檔
數據庫設計原則
數據庫SID
數據庫SID是唯一標志數據庫的符號
命名長度不能超過
個字符
對於單節點數據庫
以字符開頭的
個長度以內字串作為SID的命名
對於集群數據庫
當命名SID後
各節點SID自動命名為SIDnn
其中nn為節點號
…
例如rac
rac
rac
數據庫全局名
![]()
數據庫全局名稱
<sid>
domain
數據庫類型選擇
對於海量數據庫系統
采用data warehouse的類型
對於小型數據庫或OLTP類型的數據庫
采用Transaction Processing類型
![]()
數據庫連接類型選擇
Oracle數據庫有專用服務器連接類型和多線程服務器MTS連接類型
對於批處理服務
需要專用服務器連接方式
而對於OLTP服務則MTS的連接方式比較合適
由於采用MTS後
可以通過配置網絡服務實現某些特定批處理服務采用專用服務器連接方式
所以數據庫設計時一般采用MTS類型
![]()
數據庫SGA配置
數據庫SGA可以采用手工配置或按物理內存比例配置
在數據庫初始設計階段采用按比例配置方式
在實際應用中按系統調優方式修改SGA
![]()
數據庫字符集選擇
為了使數據庫能夠正確支持多國語言
必須配置合適的數據庫字符集
采用UTF
字符集
注意
如果沒有大對象
在使用過程中進行語言轉換沒有什麼影響
具體過程如下（切記設定的字符集必須是ORACLE支持
不然不能start）
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=
;
SQL> alter database open;
SQL> alter database character set internal_use we
iso
p
;
SQL> shutdown immediate;
SQL> startup
數據庫其他參數配置
DB_FILES
Db_files是數據庫能夠同時打開的文件數量
默認值是
個
當數據庫規劃時文件數量FILES接近或超過
個時候
按以下估計值配置:
DB_FILES = FILES *
Db_block_size
一個extent要是
個blocks的倍數為好
如
一個blocks是
字節
那一個extent就是
M
M或
M為好
Db_block_size是數據庫最小物理單元
一旦數據庫創建完成
該參數無法修改
db_block_size按以下規則調整
數據倉庫類型
db_block_size盡可能大
采用
或
OLTP類型
db_block_size 用比較小的取值范圍
或
Blocks推薦是系統操作的塊倍數（裸設備塊大小是
字節
NTFS是
K
使用
K的方式在大部分系統上通用）
數據庫控制文件配置
控制文件鏡象
多個控制文件存放在不同的物理位置
控制文件配置
控制文件中參數設置
最大的數據文件數量不能小於數據庫參數db_files
數據庫日志文件配置
日志文件大小
日志文件的大小由數據庫事務處理量決定
在設計過程中
確保每
分鐘切換一個日志文件
所以對於批處理系統
日志文件大小為幾百M 到幾G的大小
對於OLTP系統
日志文件大小為幾百M以內
日志文件組數量
對於批處理系統
日志文件組為
—
組
對於OLTP系統
日志文件組為
—
組
每組日志大小保持一致
對於集群數據庫系統
每節點有各自獨立的日志組
日志成員數量
為了確保日志能夠鏡象作用
每日志組的成員為
個
數據庫回滾段配置
在Oracle
i數據庫中
設計Undo表空間取代以前版本的回滾段表空間
Undo 表空間大小的設計規范由以下公式計算
Undospace = UR * UPS *db_block_size+ 冗余量
UR
表示在undo中保持的最長時間數（秒）
由數據庫參數UNDO_RETENTION值決定
UPS
表示在undo中
每秒產生的數據庫塊數量
例如
在數據庫中保留
小時的回退數據
假定每小時產生
個數據庫塊
則Undospace =
*
*
*
K =
G
數據庫臨時段表空間配置
數據庫臨時段表空間根據實際生產環境情況調整其大小
表空間屬性為自動擴展
數據庫系統表空間配置
系統表空間大小
G左右
除了存放數據庫數據字典的數據外
其他數據不得存儲在系統表空間
數據庫表空間設計原則
表空間大小定義原則
當表空間 大小小於操作系統對最大文件限制時
表空間由一個文件組成
如果表空間大小大於操作系統對最大文件限制時
該表空間由多個數據文件組成
表空間的總大小為估算為
Tablespace + sum (數據段+索引段)*
%
表空間擴展性設計原則
表空間數據文件采用自動擴展的方式
擴展容量快大小按
的整數倍（
M
M
M
M
M
M
M）進行擴展
創建表空間時盡量采用nologing選項
表空間的最大限制一般采用unlimited
除非確切知道表空間數據文件的最大使用范圍
（一般windows ３２位系統的文件最大２Ｇ
６４位的unix系統系統文件最大１２８G
但也要注意文件格式設定的文件大小）
建議最大為
G
表空間采用local管理方式
例如
CREATE TABLESPACE TBS_USERINFO
DATAFILE
/oradata/tbs_userinfo
dbf
SIZE
M
REUSE
AUTOEXTEND ON
NEXT
M
MAXSIZE UNLIMITED
NOLOGGING
EXTENT MANAGEMENT
LOCAL
AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
裸設備的使用
一個scsi設備可以
個分區
unix操作系統
個分區
性能比文件系統方式高
％左右
空間大於要小於(實際分區大小減兩個ORACLE的數據塊)
比如
M
大於為
K
推薦在unix使用軟連接(ln)方式把裸設備形成文件
用加入表空間時加resue 選項
當然也可只接把設備加入表空間
移動裸設備使用dd命令
對於windows平台
oracle提供軟連接工具
實現裸設備的使用
計算一條記錄的長度
數據庫邏輯設計原則 命名規范
表屬性規范
表名
前綴為Tbl_
數據表名稱必須以有特征含義的單詞或縮寫組成
中間可以用
_
分割
例如
tbl_pstn_detail
表名稱不能用雙引號包含
表分區名
前綴為p
分區名必須有特定含義的單詞或字串
例如
tbl_pstn_detail 的分區p
表示該分區存儲
時段的數據
字段名
字段名稱必須用字母開頭
采用有特征含義的單詞或縮寫
不能用雙引號包含
主鍵名
前綴為PK_
主鍵名稱應是 前綴+表名+構成的字段名
如果復合主鍵的構成字段較多
則只包含第一個字段
表名可以去掉前綴
外鍵名
前綴為FK_
外鍵名稱應是 前綴+ 外鍵表名 + 主鍵表名 + 外鍵表構成的字段名
表名可以去掉前綴
索引
普通索引
前綴為IDX_
索引名稱應是 前綴+表名+構成的字段名
如果復合索引的構成字段較多
則只包含第一個字段
並添加序號
表名可以去掉前綴
主鍵索引
前綴為IDX_PK_
索引名稱應是 前綴+表名+構成的主鍵字段名
在創建表時候用using index指定主鍵索引屬性
唯一所以
前綴為IDX_UK_
索引名稱應是 前綴+表名+構成的字段名
外鍵索引
前綴為IDX_FK_
索引名稱應是 前綴+表名+構成的外鍵字段名
函數索引
前綴為IDX_func_
索引名稱應是 前綴+表名+構成的特征表達字符
蔟索引
前綴為IDX_clu_
索引名稱應是 前綴+表名+構成的簇字段
視圖
前綴為V_
按業務操作命名視圖
實體化視圖
前綴為MV_
按業務操作命名實體化視圖
存儲過程
前綴為Proc_
按業務操作命名存儲過程
觸發器
前綴為Trig_
觸發器名應是 前綴 + 表名 + 觸發器名
函數
前綴為Func_
按業務操作命名函數
數據包
前綴為Pkg_
按業務操作集合命名數據包
序列
前綴為Seq_
按業務屬性命名
表空間
公用表空間
前綴為Tbs_
根據存儲的特性命名
例如
tbs_parameter
專用表空間
Tbs_<表名稱>_nn
該表空間專門存儲指定的某一個表
或某一表的若干個分區的數據
數據文件
<表空間名>nn
dbf
nn =
…等
普通變量
前綴為Var_
存放字符
數字
日期型變量
游標變量
前綴為Cur_
存放游標記錄集
記錄型變量
前綴為Rec_
存放記錄型數據
表類型變量
前綴為Tab_
存放表類型數據
數據庫鏈
前綴為dbl_
表示分布式數據庫外部鏈接關系
命名
語言
命名應該使用英文單詞
避免使用拼音
特別不應該使用拼音簡寫
命名不允許使用中文或者特殊字符
英文單詞使用用對象本身意義相對或相近的單詞
選擇最簡單或最通用的單詞
不能使用毫不相干的單詞來命名
當一個單詞不能表達對象含義時
用詞組組合
如果組合太長時
采用用簡或縮寫
縮寫要基本能表達原單詞的意義
當出現對象名重名時
是不同類型對象時
加類型前綴或後綴以示區別
大小寫
名稱一律大寫
以方便不同數據庫移植
以及避免程序調用問題
單詞分隔
命名的各單詞之間可以使用下劃線進行分隔
保留字
命名不允許使用SQL保留字
命名長度
表名
字段名
視圖名長度應限制在
個字符內(含前綴)
字段名稱
同一個字段名在一個數據庫中只能代表一個意思
比如telephone在一個表中代表
電話號碼
的意思
在另外一個表中就不能代表
手機號碼
的意思
不同的表用於相同內容的字段應該采用同樣的名稱
字段類型定義
數據類型
字符型
固定長度的字串類型采用char
長度不固定的字串類型采用varchar
避免在長度不固定的情況下采用char類型
如果在數據遷移等出現以上情況
則必須使用trim()函數截去字串後的空格
數字型
數字型字段盡量采用number類型
日期和時間
系統時間
由數據庫產生的系統時間首選數據庫的日期型
如DATE類型
外部時間
由數據導入或外部應用程序產生的日期時間類型采用varchar類型
數據格式采用
YYYYMMDDHH
MISS
大字段
如無特別需要
避免使用大字段(blob
clob
long
text
image等)
唯一鍵
對於數字型唯一鍵值
盡可能用系列sequence產生
設計
范式
如無性能上的必須原因
應該使用關系數據庫理論
達到較高的范式
避免數據冗余
但是如果在數據量上與性能上無特別要求
考慮到實現的方便性可以有適當的數據冗余
但基本上要達到
NF
如非確實必要
避免一個字段中存儲多個標志的做法
如
表示
個標志的一種取值
這往往是增加復雜度
降低性能的地方
表設計
邏輯段設計原則
Tablespace
每個表在創建時候
必須指定所在的表空間
不要采用默認表空間以防止表建立在系統表空間上導致性能問題
對於事務比較繁忙的數據表
必須存放在該表的專用表空間中
Pctused
默認pctused導致數據庫物理空間利用率非常低
%左右
對於update比較少或update不導致行增大的表
pctused可設置在
—
之間
對於update能夠導致行增大的表
update設置在
—
之間
Initrans
對於需要並行查詢或者在RAC數據庫中需要並行處理的表
initrans設置為
的倍數
否則
不設該值
Storage
Initial
盡量減少表數據段的extents數量
initial的大小盡量接近數據段的大小
K
K
…
M
M
M
M
M
…
等按
的倍數進行圓整
例如表或分區數據段大小為
M
則initial取
M
Next
表或分區擴展extents的大小
按上述方法進行圓整
當表或分區數據段無法按Initial接近值進行圓整的情況下
其大小可以按 Initial+Next進行圓整
此時
必須設置Minextents=
例如
表或分區數據段大小為
M
則Initial=
M
Next=
M
Minextents=
Minextents
該參數表示表創建時候Extents的初始數量
一般取
—
Pctincrease
表示每個擴展Extents的增長率
設置pctincrease=
能夠獲得較好的存儲性能
特殊表設計原則
分區表
對於數據量比較大的表
根據表數據的屬性進行分區
以得到較好的性能
如果表按某些字段進行增長
則采用按字段值范圍進行范圍分區
如果表按某個字段的幾個關鍵值進行分布
則采用列表分區
對於靜態表
則采用hash分區或列表分區
在范圍分區中
如果數據按某關鍵字段均衡分布
則采用子分區的復合分區方法
聚蔟表
如果某幾個靜態表關系比較密切
則可以采用聚蔟表的方法
完整性設計原則
主鍵約束
關聯表的父表要求有主健
主健字段或組合字段必須滿足非空屬性和唯一性要求
對於數據量比較大的父表
要求指定索引段
外鍵關聯
對於關聯兩個表的字段
一般應該分別建立主鍵
外鍵
實際是否建立外鍵
根據對數據完整性的要求決定
為了提高性能
對於數據量比較大的標要求對外健建立索引
對於有要求級聯刪除屬性的外鍵
必須指定on delete cascade
NULL值
對於字段能否null
應該在sql建表腳本中明確指明
不應使用缺省
由於NULL值在參加任何運算中
結果均為NULL
所以在應用程序中必須利用nvl()函數把可能為NULL值得字段或變量轉換為非NULL的默認值
例如
NVL（sale
）
Check條件
對於字段有檢查性約束
要求指定check規則
觸發器
觸發器是一種特殊的存儲過程
通過數據表的DML操作而觸發執行
起作用是為確保數據的完整性和一致性不被破壞而創建
實現數據的完整約束
觸發器的before或after事務屬性的選擇時候
對表操作的事務屬性必須與應用程序事務屬性保持一致
以避免死鎖發生
在大型導入表中
盡量避免使用觸發器
注釋
表
字段等應該有中文名稱注釋
以及需要說明的內容
索引設計
對於查詢中需要作為查詢條件的字段
可以考慮建立索引
最終根據性能的需要決定是否建立索引
對於復合索引
索引字段順序比較關鍵
把查詢頻率比較高的字段排在索引組合的最前面
在分區表中
盡量采用local分區索引以方便分區維護
除非時分區local索引
否則在創建索引段時候必須指定指定索引段的tablespace
storage屬性
具體參考
內容
From:http://tw.wingwit.com/Article/program/Oracle/201311/17381.html
