最佳化Oracle庫表設計的若干方法

前言

絕大多數的Oracle資料庫效能問題都是由於資料庫設計不合理造成的，只有少部分問題根植於Database Buffer、Share Pool、Redo Log Buffer等記憶體模組配置不合理，I/O爭用，CPU爭用等DBA職責範圍上。所以除非是面對一個業已完成不可變更的系統，否則我們不應過多地將關注點投向記憶體、I/O、CPU等效能調整項目上，而應關注資料庫表本身的設計是否合理，庫表設計的合理性才是程式效能的真正執牛耳者。

合理的資料庫設計需要考慮以下的方面：

·業務資料以何種方式表達。如一個員工有多個Email，你可以在T_EMPLOYEE表中建立多個Email欄位如email_1、email_2、email_3，也可以建立一個T_EMAIL子表來儲存，甚至可以用逗號分隔開多個Email地址存放在一個欄位中。

·資料以何種方式實體儲存體。如大表的分區，資料表空間的合理設計等。

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

本文我們將目光主要聚焦於資料表的索引上，同時也將提及其他兩點的內容。通過對一個簡單的庫表設計執行個體的分析引出設計中的不足，並逐一改正。考慮到手工編寫庫表的SQL指令碼原始且低效，我們將用目前最流行的庫表設計工具PowerDesigner 10來講述表設計的過程，所以在本文中你還會瞭解到一些相關的PowerDesigner的提示。

一個簡單的例子

某個開發人員著手設計一個訂單的系統，這個系統中有兩個主要的業務表，分別是訂單基本資料表和訂單條目表，這兩張表具有主從關係的表，其中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：根據"客戶＋訂貨日期+是否發貨"條件查詢訂單及訂單條目。

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

資料庫設計人員根據這個要求，在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關係上，這份設計並不存在的缺陷，但卻存在以下有待最佳化的地方：

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

·ORACLE會自動為表的主鍵列建立一個普通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索引。