詳解Oracle數據庫物理設計

物理設計就是根據所選擇的關系型數據庫的特點對邏輯模型進行存儲結構設計。它涉及的內容包含以下4方面：1. 定義數據庫、表及字段的命名規范；2. 選擇合適的存儲引擎或者存儲結構；3. 為表中的字段選擇合適的數據類型；4. 建立數據庫結構。

今天主要談談表和索引的設計建議。

1）除特殊需求，建議采用普通的堆表（Help Table）。

2）每個表在創建的時候，建議指定所在的表空間。不要采用默認表空間。以防止表建立在系統表空間上導致的性能和安全問題。

3）建議應用設計開發人員提供每張表的DNL操作頻度數值。如果update操作頻度高，則可將PCTFREE設計為較高(10%,20%,...)，反之,則可將PCTFREE設置為較高(1%,...)。

當表中存在大量的delete操作時。PCTUSED值的增大，將提高表空間的利用率，并提高insert和update操作的性能。因此，建議將大量進行delete操作的表的PCTUSED設為60，否則設為缺省值40。

4）對并發訪問量比較高的表和索引，將INITRANS設置較大，特別是針對索引，例如設置INITRANS設為10。

5）對比較小的代碼和參數表，可考慮IOT表技術。

6）如果某幾個靜態表關系比較密切，則可以采用聚族表的方法。

以下就是XX表創建腳本。

create table t_card_accnt(

card_no char(19) not null,

card_medium char(1) not null,

card_type char(1) not null,

card_class char(2) not null,

cstm_name varchar2(40) not null,

app_date number(6) not null,

Pwd RAW(8) not null)

PCTFREE 20

PCTUSED 40

INITRANS 2

MAXTRANS 255

TABLESPACE TS_DATA

LOGGING

STORAGE (

INITIAL 104857600

NEXT 10485760

PCTINCREASE 0

MINEXTENTS 1

MAXEXTENTS 2147483645

FREELISTS 5

FREELISTS GROUPSS 5

BUFFER_POOL DEFAULT)

ENABLE ROW MOVEMENT

PARTITION BY HASH(CARD_NO)

PARTITIONS 64

STORE IN (TS_DATA);

需要說明的是，上述腳本是Oracle Date Modeler工具生成的。實際上，如采取手工編寫方法，大量物理屬性可以采用缺省值，例如STORAGE短語、ENABLE ROW MOVEMENT等均可省略。

以下不僅從物理設計，而且從應用開發角度提供索引設計的基本建議。

1）通常而言，交易系統設立為B*樹索引，對數據倉庫系統，則可考慮Bitmap、Bitmap Join等索引。

2）通過分析應用軟件對數據訪問方式。展開索引設計。

3）索引與數據分別存儲在不同的表空間。

4）不要對索引字段進行運算。

5）不要對索引字段進行格式轉換。

6）不要對索引字段使用函數。

7）加強索引使用和索引效率的分析。

1）分析SQL語句中的約束條件字段。

2）如果約束條件字段不固定，建議創建針對單字段的普通B*樹索引。

3）選擇可選性最高的字段建立索引。

4）如果是多表連接SQL語句，注意被驅動表 （Drived Table）的連接字段是否需要創建索引。

5）通過多種SQL分析工具。分析執行計劃并以量化形式評估效果。

1）分析SQL語句中的約束條件字段。

2）如果約束條件字段比較固定，則優先考慮創建針對多字段的普通B*樹復合索引。例如當時涉及月份、賬號、金融機構代碼三個字段的條件，則可以考慮建立一個復合索引。

3）如果單個字段是主鍵或唯一字段，或者可選性非常高的字段，盡管約束條件字段比較固定，也不一定要建成復合索引，可建成單字段索引，降低復合索引開銷。

4）在復合索引設計中，需首先考慮復合索引第一個設計原則：復合索引的前綴性（Prefixing）。即SQL語句中，只有復合索引的第一個字段作為約束條件，該復合索引字段才會啟用。在復合索引設計中，其次應考慮復合索引的可選性（Selectivity或Cardinality）。即按可選性高低，進行復合索引字段的排序。例如將上述索引的字段順序排列為：賬號、金融機構代碼、月份。

5）如果條件設計的字段不固定，組合比較靈活，則分別為月份、賬號、金融機構三個字段建立索引。

6）如果是多表連接SQL語句，注意是否可以在被驅動表的連接字段與該表的其他約束條件字段上，創建復合索引。

7）通過多種SQL分析工具，分析執行計劃并以量化形式評估效果。

后面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下！