優化Oracle庫表設計的若干方法(組圖)

2024-08-29 13:39:16

字體：大中小

來源：轉載

供稿：網友

　　前言
　　
　　絕大多數的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所示：
　　　優化Oracle庫表設計的若干方法(組圖)（圖一）

　　圖 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索引。
　　
　　優化設計
　　
　　1、將表數據和索引數據分開表空間存儲
　　
　　1.1 表數據和索引為何需要使用獨立的表空間
　　
　　Oracle強烈建立，任何一個應用程序的庫表至少需要創建兩個表空間，其中之一用于存儲表數據，而另一個用于存儲表索引數據。因為假如將表數據和索引數據放在一起，表數據的I/O操作和索引的I/O操作將產生影響系統性能的I/O競爭，降低系統的響應效率。將表數據和索引數據存放在不同的表空間中（如一個為APP_DATA，另一個為APP_IDX），并在物理層面將這兩個表空間的數據文件放在不同的物理磁盤上，就可以避免這種競爭了。
　　
　　擁有獨立的表空間，就意味著可以獨立地為表數據和索引數據提供獨立的物理存儲參數，而不會發生相互影響，究竟表數據和索引數據擁有不同的特性，而這些特性又直接影響了物理存儲參數的設定。
　　
　　此外，表數據和索引數據獨立存儲，還會帶來數據治理和維護上的方面。如你在遷移一個業務數據庫時，為了降低數據大小，可以只遷出表數據的表空間，在目標數據庫中通過重建索引的方式就可以生成索引數據了。
　　
　　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) 首先，必須創建兩個表空間。通過Model->Tablespace...在List of Tablespaces中創建兩個表空間：
　　　優化Oracle庫表設計的若干方法(組圖)（圖二）

　　圖 2 創建表空間
　　
　　2) 為每張表指定表數據存儲的表空間。在設計區中雙擊表，打開Table Properties設計窗口，切換到options 頁，按圖 3所示指定表數據的存儲表空間。
　　　優化Oracle庫表設計的若干方法(組圖)（圖三）

　　圖 3 指定表數據的存儲表空間
　　
　　3) 為每個索引指定索引數據的存儲表空間。在Table Properties中切換到Indexes頁，在這里列出了表的所有索引，雙擊需設置表空間的索引，在彈出的Index Properties窗口中切換到Options頁，按如下方式指定索引的存儲表空間。
　　　優化Oracle庫表設計的若干方法(組圖)（圖四）

　　圖 4 指定索引數據的存儲表空間
　　
　　將表空間的問題延展一下：一個應用系統庫表的表空間可以進行更精細的劃分。
　　
　　首先，假如表中存在LOB類型的字段，有為其指定一個特定的表空間，因為LOB類型的數據在物理存儲結構的治理上和一般數據的策略有很大的不同，將其放在一個獨立的表空間中，就可方便地設置其物理存儲參數了。
　　
　　其次，需要考慮庫表數據的DML操作特性：根據DML（INSERT,UPDATE,DELETE）操作頻繁程度，將幾乎不發生任何DML操作的數據放在獨立的表空間中，因為極少DML操作的表可設置符合其特性的物理參數：如PCTFREE可置為0，其BUFFER_POOL指定為KEEP，以便將數據緩存在KEEP數據緩存區中等等，不一而足。
　　
　　此外，還可以考慮按業務需要將不同的業務模塊分開存放，這主要是考慮到備份問題。假設我們有一部分業務數據重要性很強，而其他的業務數據重要性相對較弱，這樣就可以將兩者分開存儲，以便設置不同的備份策略。
　　
　　當然，無節制的細化表空間也將帶來治理上和部署上的復雜，根據業務需求合理地規劃表空間以達到治理和性能上的最佳往往需要更多的權衡。
　　
　　2、顯式為主鍵列建立反向鍵索引
　　
　　2.1 反向鍵索引的原理和用途
　　
　　我們知道Oracle會自動為表的主鍵列建立索引，這個默認的索引是普通的B-Tree索引。對于主鍵值是按順序(遞增或遞減)加入的情況，默認的B-Tree索引并不理想。這是因為假如索引列的值具有嚴格順序時，隨著數據行的插入，索引樹的層級增長很快。搜索索引發生的I/O讀寫次數和索引樹的層級數成正比，也就是說，一棵具有5個層級的B-Tree索引，在最終讀取到索引數據時最多可能發生多達5次I/O操作。因而，減少索引的層級數是索引性能調整的一個重要方法。
　　
　　假如索引列的數據以嚴格的有序的方式插入，那么B-Tree索引樹將變成一棵不對稱的"歪樹"，如圖 5所示：
　　　