每日一得

key words: oracle優(yōu)化 優(yōu)化oracle? 表設計優(yōu)化 powerdesigner

前言

絕 大多數的Oracle數據庫性能問題都是由于數據庫設計不合理造成的，只有少部分問題根植于Database Buffer、Share Pool、Redo Log Buffer等內存模塊配置不合理，I/O爭用，CPU爭用等DBA職責范圍上。所以除非是面對一個業(yè)已完成不可變更的系統(tǒng)，否則我們不應過多地將關注點 投向內存、I/O、CPU等性能調整項目上，而應關注數據庫表本身的設計是否合理，庫表設計的合理性才是程序性能的真正執(zhí)牛耳者。

合理的數據庫設計需要考慮以下的方面：

·業(yè)務數據以何種方式表達。如一個員工有多個Email，你可以在T_EMPLOYEE表中建立多個Email字段如email_1、email_2、 email_3，也可以創(chuàng)建一個T_EMAIL子表來存儲，甚至可以用逗號分隔開多個Email地址存放在一個字段中。

·數據以何種方式物理存儲。如大表的分區(qū)，表空間的合理設計等。

·如何建立合理的數據表索引。表索引幾乎是提高數據表查詢性能最有效的方法，Oracle擁有類型豐富的數據表索引類型，如何取舍選擇顯得特別重要。

本 文我們將目光主要聚焦于數據表的索引上，同時也將提及其他兩點的內容。通過對一個簡單的庫表設計實例的分析引出設計中的不足，并逐一改正。考慮到手工編寫 庫表的SQL腳本原始且低效，我們將用目前最流行的庫表設計工具PowerDesigner 10來講述表設計的過程，所以在本文中你還會了解到一些相關的PowerDesigner的使用技巧。

一個簡單的例子

某個開發(fā)人員著手設計一個訂單的系統(tǒng)，這個系統(tǒng)中有兩個主要的業(yè)務表，分別是訂單基本信息表和訂單條目表，這兩張表具有主從關系的表，其中T_ORDER是訂單主表，而T_ORDER_ITEM是訂單條目表。數據庫設計人員的設計成果如圖 1所示：

圖 1 訂單主從表

ORDER_ID 是訂單號，為T_ORDER的主鍵，通過名為SEQ_ORDER_ID的序列產生鍵值，而ITEM_ID是T_ORDER_ITEM表的主鍵，通過名為 SEQ_ORDER_ITEM的序列產生鍵值，T_ORDER_ITEM通過ORDER_ID外鍵關聯到T_ORDER表。

需求文檔指出訂單記錄將通過以下兩種方式來查詢數據：

·CLIENT + ORDER_DATE+IS_SHPPED：根據"客戶＋訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

·ORDER_DATE+IS_SHIPPED：根據"訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

數 據庫設計人員根據這個要求，在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上建立了一個復合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM為外鍵 ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。

讓我們看一下該份設計的最終SQL腳本：

/*訂單表*/ create table T_ORDER ( ORDER_ID NUMBER(10) not null, ADDRESS VARCHAR2(100), CLIENT VARCHAR2(60), ORDER_DATE CHAR(8), IS_SHIPPED CHAR(1), constraint PK_T_ORDER primary key (ORDER_ID) ); create index IDX_CLIENT on T_ORDER ( 　CLIENT ASC, 　ORDER_DATE ASC, 　IS_SHIPPED ASC); /*訂單條目子表*/ create table T_ORDER_ITEM ( 　ITEM_ID NUMBER(10) not null, 　ORDER_ID NUMBER(10), 　ITEM VARCHAR2(20), 　COUNT NUMBER(10), 　constraint PK_T_ORDER_ITEM primary key (ITEM_ID) ); create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( 　ORDER_ID ASC); 　alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID);

我們承認在ER關系上，這份設計并不存在的缺陷，但卻存在以下有待優(yōu)化的地方：

·沒有將表數據和索引數據存儲到不同的表空間中，而不加區(qū)別地將它們存儲到同一表空間里。這樣，不但會造成I/O競爭，也為數據庫的維護工作帶來不便。

·ORACLE會自動為表的主鍵列創(chuàng)建一個普通B-Tree索引，但由于這兩張表的主鍵值都通過序列提供，具有嚴格的順序性（升序或降序），此時手工為其指定一個反鍵索引（reverse key index）將更加合理。

· 在子表T_ORDER_ITEM外鍵列ORDER_ID上建立的IDX_ORDER_ITEM_ORDER_ID的普通B-Tree索引非常適合設置為壓 縮型索引，即建立一個壓縮型的B-Tree索引。因為一份訂單會對應多個訂單條目，這就意味著T_ORDER_ITEM表存在許多同值的 ORDER_ID列值，通過將其索引指定為壓縮型的B-Tree索引，不但可以減少IDX_ORDER_ITEM_ORDER_ID所需的存儲空間，還將 提高表操作的性能。

·企圖僅通過建立一個包含3字段IDX_ORDER_COMPOSITE復合索引滿足如前所述的兩種查詢條件方式的索引是有問題的，事實上使用ORDER_DATE+IS_SHIPPED復合條件的查詢將利用不到IDX_ORDER_COMPOSITE索引。

?優(yōu)化設計

1、將表數據和索引數據分開表空間存儲

1.1 表數據和索引為何需要使用獨立的表空間

Oracle 強烈建立，任何一個應用程序的庫表至少需要創(chuàng)建兩個表空間，其中之一用于存儲表數據，而另一個用于存儲表索引數據。因為如果將表數據和索引數據放在一起， 表數據的I/O操作和索引的I/O操作將產生影響系統(tǒng)性能的I/O競爭，降低系統(tǒng)的響應效率。將表數據和索引數據存放在不同的表空間中（如一個為 APP_DATA，另一個為APP_IDX），并在物理層面將這兩個表空間的數據文件放在不同的物理磁盤上，就可以避免這種競爭了。

擁有獨立的表空間，就意味著可以獨立地為表數據和索引數據提供獨立的物理存儲參數，而不會發(fā)生相互影響，畢竟表數據和索引數據擁有不同的特性，而這些特性又直接影響了物理存儲參數的設定。

此外，表數據和索引數據獨立存儲，還會帶來數據管理和維護上的方面。如你在遷移一個業(yè)務數據庫時，為了降低數據大小，可以只遷出表數據的表空間，在目標數據庫中通過重建索引的方式就可以生成索引數據了。

1.2 表數據和索引使用不同表空間的SQL語法

指定表數據及索引數據存儲表空間語句最簡單的形式如下。

將表數據存儲在APP_DATA表空間里：

create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;

將索引數據存儲在APP_IDX表空間里：

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;

1.3 PowerDesigner中如何操作

1) 首先，必須創(chuàng)建兩個表空間。通過Model->Tablespace...在List of Tablespaces中創(chuàng)建兩個表空間：

圖 2 創(chuàng)建表空間

2) 為每張表指定表數據存儲的表空間。在設計區(qū)中雙擊表，打開Table Properties設計窗口，切換到options 頁，按圖 3所示指定表數據的存儲表空間。

圖 3 指定表數據的存儲表空間

3) 為每個索引指定索引數據的存儲表空間。在Table Properties中切換到Indexes頁，在這里列出了表的所有索引，雙擊需設置表空間的索引，在彈出的Index Properties窗口中切換到Options頁，按如下方式指定索引的存儲表空間。

圖 4 指定索引數據的存儲表空間

將表空間的問題延展一下：一個應用系統(tǒng)庫表的表空間可以進行更精細的劃分。

首先，如果表中存在LOB類型的字段，有為其指定一個特定的表空間，因為LOB類型的數據在物理存儲結構的管理上和一般數據的策略有很大的不同，將其放在一個獨立的表空間中，就可方便地設置其物理存儲參數了。

其 次，需要考慮庫表數據的DML操作特性：根據DML（INSERT,UPDATE,DELETE）操作頻繁程度，將幾乎不發(fā)生任何DML操作的數據放在獨 立的表空間中，因為極少DML操作的表可設置符合其特性的物理參數：如PCTFREE可置為0，其BUFFER_POOL指定為KEEP，以便將數據緩存 在KEEP數據緩存區(qū)中等等，不一而足。

此外，還可以考慮按業(yè)務需要將不同的業(yè)務模塊分開存放，這主要是考慮到備份問題。假設我們有一部分業(yè)務數據重要性很強，而其他的業(yè)務數據重要性相對較弱，這樣就可以將兩者分開存儲，以便設置不同的備份策略。

當然，無節(jié)制的細化表空間也將帶來管理上和部署上的復雜，根據業(yè)務需求合理地規(guī)劃表空間以達到管理和性能上的最佳往往需要更多的權衡。

?2、顯式為主鍵列建立反向鍵索引

2.1 反向鍵索引的原理和用途

我 們知道Oracle會自動為表的主鍵列建立索引，這個默認的索引是普通的B-Tree索引。對于主鍵值是按順序(遞增或遞減)加入的情況，默認的B- Tree索引并不理想。這是因為如果索引列的值具有嚴格順序時，隨著數據行的插入，索引樹的層級增長很快。搜索索引發(fā)生的I/O讀寫次數和索引樹的層級數 成正比，也就是說，一棵具有5個層級的B-Tree索引，在最終讀取到索引數據時最多可能發(fā)生多達5次I/O操作。因而，減少索引的層級數是索引性能調整 的一個重要方法。

如果索引列的數據以嚴格的有序的方式插入，那么B-Tree索引樹將變成一棵不對稱的"歪樹"，如圖 5所示：

圖 5不對稱的B-Tree索引

而如果索引列的數據以隨機值的方式插入，我們將得到一棵趨向對稱的索引樹，如圖 6所示：

圖 6對稱的B-Tree索引

比較圖 5和圖 6，在圖 5中搜索到A塊需要進行5次I/O操作，而圖 6僅需要3次I/O操作。

既 然索引列數據從序列中獲取，其有序性無法規(guī)避，但在建立索引時，Oracle允許對索引列的值進行反向，即預先對列值進行比特位的反向，如 1000,10001,10011,10111,1100經過反向后的值將是0001,1001,1101,0011。顯然經過位反向處理的有序數據變得 比較隨機了，這樣所得到的索引樹就比較對稱，從而提高表的查詢性能。

但反向鍵索引也有它局限性：如果在WHERE語句中，需要對索引列的 值進行范圍性的搜索，如BETWEEN、<、>等，其反向鍵索引無法使用，此時，Oracle將執(zhí)行全表掃描；只有對反向鍵索引列進行 <> 和 = 的比較操作時，其反向鍵索引才會得到使用。

2.2 反向鍵索引的SQL語句

回到我們上面的例子，由于T_ORDER和T_ORDER_ITEM的主鍵值來源于序列，主鍵值是有嚴格順序的，所以我們應該摒棄默認的Oracle所提供的索引，而采取顯式為主鍵指定一個反向鍵索引的方式。

ORDER_ID為T_ORDER表的主鍵，主鍵名為PK_ORDER，我們?yōu)镺RDER_ID列上建立一個反向鍵索引IDX_ORDER_ID，并使PK_ORDER_ID使用這個索引，其SQL語句如下：

create table T_ORDER ( 　ORDER_ID NUMBER(10) not null, 　CLIENT VARCHAR2(60), 　ADDRESS VARCHAR2(100), 　ORDER_DATE CHAR(8)); create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;alter table T_ORDER add constraint PK_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;

要保證創(chuàng)建IDX_ORDER_ID的SQL語句在創(chuàng)建PK_ORDER主鍵的SQL語句之前，因為主鍵需要引用到這個反向鍵索引。

由于主鍵列的數據是唯一的，所以為IDX_ORDER_ID加上unique限定，使其成為唯一型的索引。

2.3 PowerdDesigner如何操作

1) 首先，需要為ORDER_ID列建立一個反向鍵索引。打開T_ORDER的Table Properties的窗口，切換到Indexes頁，新建一個名為IDX_ORDER_ID的索引。填寫完索引的名稱后，雙擊這個索引，彈出Index Properties窗口，在這個窗口的Columns中選擇ORDER_ID列。然后，切換到Options頁，按圖 7的方式將其設置為反向鍵索引。

圖 7 設置反向鍵索引 ?

2) 顯式指定主鍵PK_ORDER使用這個索引。在Table Properties窗口中切換到Keys頁，默認情況下，PowerDesigner為T_ORDER所指定的主鍵名為Key1，我們將其更名為 PK_ORDER，雙擊這個主鍵，彈出Key Properties窗口，切換到Options頁，按圖 8的方式為PK_ORDER指定IDX_ORDER_ID。

圖 8 為主鍵指定特定的索引

不可否認PowerDesigner確實是目前業(yè)界最強大易用的數據庫設計工具，但很遺憾，當我們?yōu)楸碇麈I指定一個索引時，其產生的語句在順序上有問題：即創(chuàng)建主鍵的語句位于創(chuàng)建索引語句之前：

create table T_ORDER (…);alter table T_ORDER add constraint PK_T_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;

我們可以通過對PowerDesigner生成SQL語句的設置進行調整，先生成創(chuàng)建表和索引的SQL語句，再創(chuàng)建為表添加主鍵和外鍵的SQL語句來達到曲線救國的目的，請看下一步。

3)通過菜單Database->Generate Database...調出Database Configuration窗口，切換到Keys&Indexes頁，按圖 9設置：

圖 9 設置生成鍵和索引SQL的選項

這里，我們將Primary Keys和Foreign keys的選項都取消，而將Indexes勾選，以達到只生成表的索引SQL語句的目的。

點擊"確定"后，生成創(chuàng)建數據庫表及其索引的SQL語句，運行該SQL創(chuàng)建數據庫后，再按圖 10設置生成為表添加主鍵和外鍵的SQL語句：

圖 10 生成創(chuàng)建表主鍵和外鍵的SQL語句

除此設置外，還必須切換到Tables & Views頁下，取消所有選項，避免重新生成創(chuàng)建表的語句。

?3、將子表的外鍵列的索引改為壓縮型

3.1 壓縮型索引的原理和用途

在前面的例子中，由于一條訂單會對應多條訂單條目，所以T_ORDER_ITEM的ORDER_ID字段總會出現重復的值，如：

ITEM_ID ORDER_ID ITEM COUNT 1 100 101 1 2 100 104 2 3 100 201 3 4 200 301 2 5 200 401 1 6 200 205 3

在ORDER_ID列上創(chuàng)建一個普通未壓縮的B-Tree索引，則索引數據的物理上的存儲形式如下：

圖 11 未進行壓縮的索引存儲

ORDER_ID的重復值在索引塊中重復出現，這樣不但增加了存儲空間的需求，而且因為查詢時需要讀取更多的索引數據塊，所以查詢性能也會降低＝。讓我們來看一下經過壓縮后索引數據的存儲方式：

圖 12 進行壓縮的索引存儲

壓縮型的索引消除了重復的索引值，將相同索引列值所關聯的ROWID存儲在一起。這樣，不但節(jié)省了存儲空間，查詢效率也提高了，真可謂兩全齊美了。

對象T_ORDER和T_ORDER_ITEM這樣的主從表進行查詢時，一般情況下，我們都必須通過外鍵查詢出子表所有關聯的記錄，所以在子表的外鍵上建立壓縮型的索引是非常適合的。

3.2 壓縮型索引的SQL語句

創(chuàng)建壓縮型索引的SQL語句非常簡單，在T_ORDER_ITEM的ORDER_ID上創(chuàng)建壓縮型索引的SQL如下所示：

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC) compress;

需要在創(chuàng)建索引的語句后附上compress關鍵字就可以了。

3.3 PowerDesigner如何創(chuàng)建壓縮型索引

1) 打開T_ORDER_ITEM表的Table Properties的窗口，切換到Indexes頁，為ORDER_ID列創(chuàng)建一個名為IDX_ORDER_ITEM_ORDER_ID的索引。

2) 雙擊IDX_ORDER_ITEM_ORDER_ID彈出Index Properties窗口，切換到Options頁，按圖 13將索引設置為壓縮型：

圖 13 將索引指定為壓縮型

4、建立滿足需求的復合鍵索引

設計人員希望通過T_ORDER表上的IDX_ORDER_COMPOSITE復合索引滿足以下兩種組合條件的查詢：

·CLIENT + ORDER_DATE + IS_SHIPPED

·ORDER_DATE + IS_SHIPPED

為方便闡述，我們特地將IDX_ORDER_COMPOSITE的創(chuàng)建SQL語句再次列出：

create index IDX_ORDER_COMPOSITE on T_ORDER ( CLIENT ASC, ORDER_DATE ASC, IS_SHIPPED ASC);

事實上，在CLIENT + ORDER_DATE + IS_SHIPPED 三列上所執(zhí)行的復合條件查詢會應用到這個索引，而在ORDER_DATE + IS_SHIPPED列上所執(zhí)行的復合查詢不會使用這個索引，因而將導致一個全表掃描的操作。

可以用許多工具來了解查詢語句的執(zhí)行計劃，通過SET AUTOTRACE ON來查詢以上兩個復合查詢的執(zhí)行計劃：

打開SQL/Plus，輸入以下的語句：

SQL> set autotrace on SQL> select * from t_order where CLIENT = '1' and ORDER_DATE='1' and IS_SHIPPED='1';

分析得到的執(zhí)行計劃為：

SELECT STATEMENT Optimizer=CHOOSETABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' INDEX (RANGE SCAN) OF 'IDX_ORDER_COMPOSITE' (NON-UNIQUE)

可見Oracle先利用IDX_ORDER_COMPOSITE得到滿足條件的記錄ROWID，再通過ROWID返回記錄。

而下面查詢語句：

SQL> select * from t_order where ORDER_DATE='1' and IS_SHIPPED='1'

的執(zhí)行計劃則為：

SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF 'T_ORDER'

很明顯，Oracle在T_ORDER表上執(zhí)行了一個全表掃描的操作，沒有用到IDX_ORDER_COMPOSITE索引。

對復合列索引，我們得出這個結論：

假設在COL_1,COL_2,…,COL_n這些列上建立了一個復合索引：

create index IDX _COMPOSITE on TABLE1 { COL_1, COL_2, …, COL_n }

則只有WHERE語句上包含COL_1(復合索引的第一個字段)的查詢才會使用這個復合索引，而未包含COL_1的查詢則不會使用這個復合索引。

回到我們的例子，如何建立滿足CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED兩種查詢的索引呢？

考 慮到IS_SHIPPED列基數很小，只有兩個可能的值：0,1。在這種情況下，有兩種方案：第一，分別為CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED建立一個復合索引；第二，分別在CLIENT和ORDER_DATE列上建立一個索引，而IS_SHIPEED列不建立索引。

第一種方案的查詢效率最快，但因為CLIENT和ORDER_DATE在索引中會重復出現兩次，占用較大的存儲空間。第二種方案CLIENT和ORDER_DATE不會在索引存儲出現兩次，較為節(jié)省空間，查詢效率比之于第一種方案會稍低一些，但影響不大。

我們采用第二種方案為CLIENT和ORDER_DATE分別創(chuàng)建索引IDX_CLIENT和IDX_ORDER_DATE，組合查詢條件為CLIENT + ORDER_DATE + IS_SHIPPED時的執(zhí)行計劃為：

SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' AND-EQUAL INDEX (RANGE SCAN) OF 'IDX_CLIENT' (NON-UNIQUE) INDEX (RANGE SCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)

而組合條件為ORDER_DATE + IS_SHIPPED時的執(zhí)行計劃為：

SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' INDEX (RANGE SCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)

通過這樣的改造，我們得到了一個滿足兩種組合查詢的執(zhí)行計劃。

總結

貫穿本文的訂單主從表實例結構上很簡單，但是其粗糙的設計包含了許多問題，這也是許多對Oracle物理存儲結構沒有很好理解的數據庫設計師容易忽視的地方。

在一般情況下，這樣的設計并不會導致嚴重系統(tǒng)的性能問題，但是精益求精是每一位優(yōu)秀軟件設計師的品質，此外，對于設計師，一定要清楚這樣一條規(guī)律：對于等質的性能提升，在編碼層面往往需要比設計層面付出更多的艱辛。

在Oracle中提高數據庫的性能需要考慮的問題，注意的誤區(qū)還很多，本文涵蓋是一些最常見的問題。下面，我們將提高數據庫操作性能方法及一些誤區(qū)作個小結：

·對于大表，可以考慮創(chuàng)建分區(qū)表，分區(qū)表有范圍分區(qū)、散列分區(qū)、列表分區(qū)和散列分區(qū)幾種，通過它可以達到化大表為小表的目的。

·考慮適量的數據冗余，如一個業(yè)務表有一個審批狀態(tài)，審批需要經過多步，每一步對應審批表的一條記錄，最后審批的那條記錄決定了業(yè)務的狀態(tài)。我們大可在業(yè)務表中存放一個審批狀態(tài)的標志，以取消每次需要通過關聯審批表獲取業(yè)務審批狀態(tài)的復雜的關聯表查詢。

· 不要做太多的關聯表查詢，一些幾乎不發(fā)生數據變動的表碼表，如性別，學歷，婚姻狀態(tài)等表碼表，可以考慮在應用程序啟動時一次性地下載到應用程序的內存中緩 存起來，在從數據庫獲取結果集后，再由程序利用這些緩存的表碼表數據來翻譯這些表碼字段，而不要在數據庫中通過表間的關聯查詢方式來翻譯這些字段。

· 常看到一些令我瞠目的設計：在需要進行頻繁DML(INSERT,UPDATE,DELETE)操作的表的某些基數低的字段（如性別，婚姻狀態(tài)）上創(chuàng)建位 圖索引。位圖索引是好東西，但它是有使用范圍的，在OLTP系統(tǒng)中，需要進行頻繁DML操作的表中不應該出現位圖索引，位圖索引只適用于幾乎不進行DML 操作，只進行查詢的DSS系統(tǒng)中。此外，聚簇和索引組織表也都更適合DSS系統(tǒng)，而非OLTP系統(tǒng)。 （完）