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

正在看的ORACLE教程是:最佳化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所示：

　　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指令碼：

　　我們承認在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索引。

[NextPage]

　最佳化設計

　　1、將表資料和索引資料分開資料表空間儲存

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

　　Oracle強烈建立，任何一個應用程式的庫表至少需要建立兩個資料表空間，其中之一用於儲存表資料，而另一個用於儲存表索引資料。因為如果將表資料和索引資料放在一起，表資料的I/O操作和索引的I/O操作將產生影響系統效能的I/O競爭，降低系統的響應效率。將表資料和索引資料存放在不同的資料表空間中（如一個為APP_DATA，另一個為APP_IDX），並在物理層面將這兩個資料表空間的資料檔案放在不同的物理磁碟上，就可以避免這種競爭了。

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

　　此外，表資料和索引資料隔離儲存區 (Isolated Storage)，還會帶來資料管理和維護上的方面。如你在遷移一個業務資料庫時，為了降低資料大小，可以只遷出表資料的資料表空間，在目標資料庫中通過重建索引的方式就可以產生索引資料了。

　　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中建立兩個資料表空間：

　　2) 為每張表指定表資料存放區的資料表空間。在設計區中雙擊表，開啟Table Properties設計視窗，切換到options 頁，按圖 3所示指定表資料的儲存資料表空間。

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

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

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

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

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

　　當然，無節制的細化資料表空間也將帶來管理上和部署上的複雜，根據業務需求合理地規劃資料表空間以達到管理和效能上的最佳往往需要更多的權衡。

[NextPage]

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

　　2

[1] [2] [3] 下一頁

正在看的ORACLE教程是:最佳化Oracle庫表設計的若干方法。.1 反向鍵索引的原理和用途

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

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

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

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

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

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

　　2.2 反向鍵索引的SQL語句

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

　　ORDER_ID為T_ORDER表的主鍵，主鍵名為PK_ORDER，我們為ORDER_ID列上建立一個反向鍵索引IDX_ORDER_ID，並使PK_ORDER_ID使用這個索引，其SQL語句如下：

　　要保證建立IDX_ORDER_ID的SQL語句在建立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的方式將其設定為反向鍵索引。

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

　　不可否認PowerDesigner確實是目前業界最強大易用的資料庫設計工具，但很遺憾，當我們為表主鍵指定一個索引時，其產生的語句在順序上有問題：即建立主鍵的語句位於建立索引語句之前：

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

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

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

　　點擊"確定"後，產生建立資料庫表及其索引的SQL語句，運行該SQL建立資料庫後，再按圖 10設定產生為表添加主鍵和外鍵的SQL語句：

　　除此設定外，還必須切換到Tables & Views頁下，取消所有選項，避免重建建立表的語句。

[NextPage]

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

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

　　在前面的例子中，由於一條訂單會對應多條訂單條目，所以T_ORDER_ITEM的ORDER_ID欄位總會出現重複的值，如：

　　在ORDER_ID列上建立一個普通未壓縮的B-Tree索引，則索引資料的物理上的儲存形式如下：

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

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

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

　　3.2 壓縮型索引的SQL語句

　　建立壓縮型索引的SQL語句非常簡單，在T_ORDER_ITEM的ORDER_ID上建立壓縮型索引的SQL如下所示：

　　需要在建立索引的語句後附上compress關鍵字就可以了。

　　3.3 PowerDesigner如何建立壓縮型索引

　　1) 開啟T_ORDER_ITEM表的Table Properties的視窗，切換到Indexes頁，為ORDER_ID列建立一個名為IDX_ORDER_ITEM_ORDER_ID的索引。

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

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

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

　　·CLIENT + ORDER_DATE + IS_SHIPPED

　　·ORDER_DATE + IS_SHIPPED

　　為方便闡述，我們特地將IDX_ORDER_COMPOSITE的建立SQL語句再次列出：

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

　　可以用許多工具來瞭解查詢語句的執行計畫，通過SET AUTOTRACE ON來查詢以上兩個複合查詢的執行計畫：

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

　　分析得到的執行計畫為：

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

　　而下面查詢語句：

　　的執行計畫則為：

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

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

　

上一頁 [1] [2] [3] 下一頁

正在看的ORACLE教程是:最佳化Oracle庫表設計的若干方法。　假設在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不會在索引儲存出現兩次，較為節省空間的，查詢效率比之於第一種方案會稍低一些，但影響不大。

　　我們採用第二種方案為CLIENT和ORDER_DATE分別建立索引IDX_CLIENT和IDX_ORDER_DATE，組合查詢條件為CLIENT + ORDER_DATE + IS_SHIPPED時的執行計畫為：

　　而組合條件為ORDER_DATE + IS_SHIPPED時的執行計畫為：

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

　　總結

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

　　在一般情況下，這樣的設計並不會導致嚴重系統的效能問題，但是精益求精是每一位優秀軟體設計師的品質，此外，對於設計師，一定要清楚這樣一條規律：對於等質的效能提升，在編碼層面往往需要比設計層面付出更多的艱辛。

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

　　·對於大表，可以考慮建立分區表，分區表有定界分割、散列分區、列表分區和散列分區幾種，通過它可以達到化大表為小表的目的。

　　·考慮適量的資料冗餘，如一個業務表有一個審批狀態，審批需要經過多步，每一步對應審批表的一條記錄，最後審批的那條記錄決定了業務的狀態。我們大可在業務表中存放一個審批狀態的標誌，以取消每次需要通過關聯審批表擷取業務審批狀態的複雜的關聯表查詢。

　　·不要做太多的關聯表查詢，一些幾乎不發生資料變動的表碼錶，如性別，學曆，婚姻狀態等表碼錶，可以考慮在應用程式啟動時一次性地下載到應用程式的記憶體中緩衝起來，在從資料庫擷取結果集後，再由程式利用這些緩衝的表碼錶資料來翻譯這些表碼欄位，而不要在資料庫中通過表間的關聯查詢方式來翻譯這些欄位。

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

上一頁

上一頁 [1] [2] [3]