軟件性能測試實施之數(shù)據(jù)倉庫ETL及存儲設(shè)計SQL效率和數(shù)據(jù)庫設(shè)計性能注意事項

1、ETL過程建議 ETL過程的設(shè)計和實現(xiàn)是數(shù)據(jù)倉庫解決方案中極其重要的一部分。ETL過程用于從多個數(shù)據(jù)源提取業(yè)務數(shù)據(jù)，清理數(shù)據(jù)，然后集成這些數(shù)據(jù)，并將它們加載到數(shù)據(jù)倉庫或數(shù)據(jù)集市中，成為聯(lián)機分析處理、數(shù)據(jù)挖掘的基礎(chǔ)。 ETL相對于關(guān)系數(shù)據(jù)庫、數(shù)據(jù)倉庫技術(shù)沒有嚴格的數(shù)學理論基礎(chǔ)，它更面向?qū)嶋H工程應用。所以從工程應用的角度來考慮，按著物理數(shù)據(jù)模型的要求加載數(shù)據(jù)并對數(shù)據(jù)進行一些系列處理，處理過程與經(jīng)驗直接相關(guān)，同時這部分的工作直接關(guān)系數(shù)據(jù)倉庫中數(shù)據(jù)的質(zhì)量，從而影響到聯(lián)機分析處理和數(shù)據(jù)挖掘的結(jié)果的質(zhì)量。通常將ETL分為準備（規(guī)范制定）、實現(xiàn)、測試、優(yōu)化和遷移階段，本文按照這五個階段對ETL過程中的建議及

2、注意事項進行說明。1 準備階段/規(guī)范制定階段 準備階段根據(jù)業(yè)務需求定義映射關(guān)系，分析數(shù)據(jù)源質(zhì)量，針對數(shù)據(jù)源中有問題的數(shù)據(jù)制定數(shù)據(jù)抽取原則，確定數(shù)據(jù)抽取的技術(shù)架構(gòu)及ETL的實現(xiàn)方式。建立高效、規(guī)范的管理機制，有利于ETL過程的規(guī)范，下面介紹元數(shù)據(jù)和數(shù)據(jù)質(zhì)量管理兩個重要方面。1.1 元數(shù)據(jù)的管理 對業(yè)務數(shù)據(jù)本身及其運行環(huán)境的描述與定義的數(shù)據(jù)，稱之為元數(shù)據(jù)（metadata），元數(shù)據(jù)是描述數(shù)據(jù)的數(shù)據(jù)。從某種意義上說，業(yè)務數(shù)據(jù)主要用于支持業(yè)務系統(tǒng)應用的數(shù)據(jù)，而元數(shù)據(jù)則是企業(yè)信息門戶、客戶關(guān)系管理、數(shù)據(jù)倉庫、決策支持和B2B等新型應用所不可或缺的內(nèi)容。 元數(shù)據(jù)的典型表現(xiàn)為對象的描述，即對數(shù)據(jù)庫、表、列、

3、列屬性（類型、格式、約束等）以及主鍵/外鍵關(guān)聯(lián)等等的描述。特別是現(xiàn)行應用的異構(gòu)性與分布性越來越普遍的情況下，統(tǒng)一的元數(shù)據(jù)就愈發(fā)重要了。“信息孤島”曾經(jīng)是很多企業(yè)對其應用現(xiàn)狀的一種抱怨和概括，而合理的元數(shù)據(jù)則會有效地描繪出信息的關(guān)聯(lián)性。 元數(shù)據(jù)對于ETL的集中表現(xiàn)為：定義數(shù)據(jù)源的位置及數(shù)據(jù)源的屬性、確定從源數(shù)據(jù)到目標數(shù)據(jù)的對應規(guī)則、確定相關(guān)的業(yè)務邏輯、在數(shù)據(jù)實際加載前的其他必要的準備工作，等等，它一般貫穿整個數(shù)據(jù)倉庫項目，而ETL的所有過程必須最大化地參照元數(shù)據(jù)，這樣才能快速實現(xiàn)ETL?，F(xiàn)在有了很多元數(shù)據(jù)管理工具，但最重要的也是最有效的是從管理機制入手，建立一個有效的管理元數(shù)據(jù)的制度。1.1.1

4、 建立ETL映射文檔 該文檔應在準備階段完成。ETL映射文檔是元數(shù)據(jù)的主要依據(jù)，它記錄數(shù)據(jù)從數(shù)據(jù)源到目標表的轉(zhuǎn)換和對應關(guān)系。映射可以是一對一、一對多、多對一的。1.1.2 建立ETL變更流程 ETL變更流程其實是與需求變更緊密結(jié)合在一起的，項目中需求的變化是很常見的，需求的變化有可能會導致ETL映射關(guān)系、以及ETL業(yè)務邏輯的變化。修改ETL映射文件和業(yè)務邏輯文件必須從文檔開始，并且應該有統(tǒng)一的入口。修改文檔的主要內(nèi)容應該包括：版本號、修改原因描述、修改過程、修改時間、修改的影響范圍。1.2 數(shù)據(jù)質(zhì)量管理 數(shù)據(jù)質(zhì)量的好壞關(guān)系到項目的成敗，前端的各種數(shù)據(jù)分析和展現(xiàn)的手段都是建立在良好的數(shù)據(jù)質(zhì)量基礎(chǔ)

5、上的。1.2.1 建立規(guī)范保證數(shù)據(jù)質(zhì)量 通過制定統(tǒng)一的ETL規(guī)范，嚴格要求ETL過程中的每一步都要按照規(guī)范制定的步驟來執(zhí)行。規(guī)范的制定可以根據(jù)各個項目組的實際情況作適當?shù)脑鰷p，但是一些原則性的步驟一定要堅持執(zhí)行。規(guī)范一旦制定下來，就必須嚴格執(zhí)行。從源頭來保證數(shù)據(jù)質(zhì)量的可信度。應撰寫下列文檔來保障數(shù)據(jù)質(zhì)量：ETL映射文檔、數(shù)據(jù)源質(zhì)量分析報告、全量數(shù)據(jù)抽取策略、增量數(shù)據(jù)抽取策略、問題數(shù)據(jù)處理規(guī)范、數(shù)據(jù)抽取中異常處理規(guī)范。1.2.2 建立數(shù)據(jù)核對和數(shù)據(jù)效驗流程 這部分應該是根據(jù)數(shù)據(jù)倉庫應用的實際情況來考慮制定的。數(shù)據(jù)效驗的方式有多種，常見的是通過報表數(shù)據(jù)與已有系統(tǒng)中的數(shù)據(jù)進行比對，比對如果出現(xiàn)問題，

6、關(guān)鍵點在于要明確不同的原因，通常原因包括：統(tǒng)計口徑不同、已有系統(tǒng)數(shù)據(jù)有誤、自己的數(shù)據(jù)有誤，建議設(shè)計數(shù)據(jù)核對和數(shù)據(jù)效驗流程對發(fā)現(xiàn)數(shù)據(jù)不一致后的驗證操作進行規(guī)范。1.2.3 建立數(shù)據(jù)修改流程    有些錯誤是可以通過制定效驗規(guī)則來自動進行修改的；而另一部分錯誤是必須有人工來判斷錯誤原因，并且由人工或者由其他系統(tǒng)來修改，建議設(shè)計數(shù)據(jù)修改流程對發(fā)現(xiàn)錯誤后的數(shù)據(jù)修改流程進行規(guī)范。2 實現(xiàn)階段 根據(jù)準備階段的產(chǎn)出物進行ETL的實現(xiàn)。ETL主要包括了數(shù)據(jù)抽取、數(shù)據(jù)轉(zhuǎn)換、數(shù)據(jù)清洗、數(shù)據(jù)裝載四個步驟，實現(xiàn)過程如下圖所示。圖 ETL過程 在ETL實現(xiàn)過程中，通常利用索引提高數(shù)據(jù)查詢的

7、效率。正確的索引可能極大的提高效率，而無效的索引可能不僅浪費了數(shù)據(jù)庫空間，甚至大大降低查詢性能。本節(jié)將介紹在實現(xiàn)過程各階段中，使用索引的注意事項。2.1 數(shù)據(jù)抽取過程 并不是源數(shù)據(jù)庫的所有數(shù)據(jù)對于數(shù)據(jù)倉庫的主題域都是有用的，必須根據(jù)已確定主題的需要，從原有操作型數(shù)據(jù)庫中提取期待的數(shù)據(jù)到數(shù)據(jù)倉庫。在數(shù)據(jù)抽取過程中，應至少考慮下列因素：l 源數(shù)據(jù)庫和目標數(shù)據(jù)庫各自的格式是否一致？若不一致，不一致的程度如何？l 源數(shù)據(jù)庫要訪問的數(shù)據(jù)文件；l 源數(shù)據(jù)庫中的數(shù)據(jù)表需要提取那些字段，按什么條件提取。 在數(shù)據(jù)抽取過程中，通常需要索引來提高執(zhí)行效率，下面對數(shù)據(jù)抽取中索引使用時應遵循的一些原則進行介紹。 單表查

8、詢時使用多個索引只有一個有效 通常存在一種錯誤的認為，對于一個單表的查詢，可以索引1進行過濾再使用索引2進行過濾，但事實上并非如此。 假設(shè)查詢語句如下： select * from t1 where c1=1 and c2=2 c1列和c2列上分別建有索引ic1、ic2。先使用ic1(或ic2，依據(jù)不同數(shù)據(jù)庫管理系統(tǒng)的策略)進行過濾，產(chǎn)生的結(jié)果集是臨時數(shù)據(jù)，不再具有索引，所以不可使用ic2(或ic1)進行再次過濾。2.1.2 避免在索引列上使用函數(shù)或計算 在WHERE子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用全表掃描。舉例: 低效： SELECT * ROM DEPT WHER

9、E SAL * 12 > 25000； 高效： SELECT * FROM DEPT WHERE SAL > 25000/12；2.1.3 避免在索引列上使用NOT和“!=” 索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當數(shù)據(jù)庫遇到NOT和“!=”類操作時，就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。2.1.4 必要時創(chuàng)建基于函數(shù)的索引 如果沒有基于函數(shù)的索引，任何在列上執(zhí)行了函數(shù)的查詢都不能使用這個列的索引。為了對使用函數(shù)的列啟用索引，Oracle9i以上版本提供了新的功能：基于函數(shù)的索引(Function-Based Index)是一個較好的方案，但該類型索引的缺點是只能針對

10、某個函數(shù)來建立和使用該函數(shù)。涉及語法如下所示： CREATE INDEX EMP_I ON EMP (UPPER( ENAME)； SELECT * FROM EMP WHERE UPPER(ENAME) = 'BLACKSNAIL；2.2 數(shù)據(jù)轉(zhuǎn)換 數(shù)據(jù)轉(zhuǎn)換的注意事項 數(shù)據(jù)倉庫中的數(shù)據(jù)往往來自一個或多個異構(gòu)的數(shù)據(jù)庫系統(tǒng),這些數(shù)據(jù)源之間往往存在著不一致的問題,如不一致的字段長度、不一致的賦值等。數(shù)據(jù)不一致會嚴重影響數(shù)據(jù)倉庫的數(shù)據(jù)質(zhì)量。數(shù)據(jù)轉(zhuǎn)換就是處理這些不一致性的過程。數(shù)據(jù)轉(zhuǎn)換過程中，應至少考慮兩個方面的內(nèi)容：l 不同源數(shù)據(jù)庫系統(tǒng)中的數(shù)據(jù)名稱和數(shù)據(jù)格式存在不一致。l 數(shù)據(jù)倉庫中存在的數(shù)

11、據(jù)可能不直接存在于源數(shù)據(jù)庫中，需要根據(jù)數(shù)據(jù)庫倉庫的需要對源數(shù)據(jù)庫中的數(shù)據(jù)進行分割、組合或運算等。2.2.2 控制游標操作的結(jié)果集 數(shù)據(jù)倉庫的數(shù)據(jù)表往往具有海量數(shù)據(jù)，在這些數(shù)據(jù)表中進行數(shù)據(jù)的刪除、更新和插入操作時，用游標處理的效率是最慢的方式。但游標允許應用程序?qū)Σ樵冋Z句select返回的行結(jié)果集中每一行進行相同或不同的操作，而不是一次對整個結(jié)果集進行同一種操作。游標還提供對基于游標位置而對表中數(shù)據(jù)進行刪除或更新的能力，因此它在ETL過程中的使用必不可少，且有著極其重要的地位。 對數(shù)據(jù)倉庫維表的數(shù)據(jù)進行維護時，因為需要保證維表ID的一致性，所以采用游標的是數(shù)據(jù)維護完整性的最好方式。由于它的效率低

12、，如果按照普通的方式將無法處理大數(shù)據(jù)量的維表數(shù)據(jù)維護(一般是指10萬條記錄以上的維表)，可以通過一下方式來限制游標操作的數(shù)據(jù)量。 (1) 在數(shù)據(jù)抽取的源表中使用時間戳，這樣每天的維表數(shù)據(jù)維護只針對更新日期為最新時間的數(shù)據(jù)來進行，大大減少需要維護的數(shù)據(jù)記錄數(shù)。 (2) 在INSERT和UPDATE維表時都加上一個條件來過濾維表中已經(jīng)存在的記錄，實例為： INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE) (3) 使用顯式的游標(CURS

13、ORs)，因為使用隱式的游標將會執(zhí)行兩次操作，第一次檢索記錄，第二次檢查TOO MANY ROWS這個EXCEPTION，而顯式游標不執(zhí)行第二次操作。2.3 數(shù)據(jù)清洗源數(shù)據(jù)庫中的數(shù)據(jù)可能存在各種各樣的問題，數(shù)據(jù)清洗應該處理許多可能存在的錯誤類型。這些類型包括數(shù)據(jù)源中丟失數(shù)據(jù)和有錯誤數(shù)據(jù),還包括兩個或多個數(shù)據(jù)源里的不一致數(shù)據(jù)和沖突數(shù)據(jù),所有這些都必須處理。有時如果系統(tǒng)不能自動處理,還必須借助于手工操作來完成。2.4 數(shù)據(jù)裝載 此過程將處理完成的數(shù)據(jù)裝載到數(shù)據(jù)倉庫，數(shù)據(jù)轉(zhuǎn)載過程中需注意下列問題。 數(shù)據(jù)裝載時出現(xiàn)亂碼 通常數(shù)據(jù)庫的遷移過程中出現(xiàn)亂碼都是由字符集的錯誤導致的，可以簡單把這種情況分為三類

14、：（1）源數(shù)據(jù)庫中保存的數(shù)據(jù)就是亂碼 如果源數(shù)據(jù)就發(fā)生了錯誤，除非能夠掌握數(shù)據(jù)錯誤發(fā)生的規(guī)律，否則難以糾正其錯誤。但是即使數(shù)據(jù)發(fā)生了錯誤，也可以對錯誤數(shù)據(jù)進行正確的遷移。（2）源數(shù)據(jù)庫正確，導出的數(shù)據(jù)文件出現(xiàn)亂碼 很多時候即使導出的數(shù)據(jù)文件中含有亂碼（使用文本工具顯示時），也并不意味著發(fā)生了錯誤，數(shù)據(jù)文件中的很多正確數(shù)據(jù)可能是文本顯示工具無法展示的。 通常數(shù)據(jù)庫廠商都提供了適配的數(shù)據(jù)導出工具或接口。如何不是顯示問題，而是導出數(shù)據(jù)文件的內(nèi)容發(fā)生了錯誤，可更換字符集后使用這些工具和接口嘗試導出，如仍出現(xiàn)錯誤，只能聯(lián)系數(shù)據(jù)庫廠商了。（3）由數(shù)據(jù)文件導入目標數(shù)據(jù)庫時發(fā)生了錯誤 此類錯誤通常包括兩種情況

15、： A源數(shù)據(jù)庫與目標數(shù)據(jù)庫數(shù)據(jù)類型不一致 如SQL Server數(shù)據(jù)庫中的text, char, varchar為單字節(jié)類型，保存一個英文字母用一個字節(jié)，一個漢字用兩個字節(jié)，而 ntext,nchar,nvarchar雙字節(jié)類型保存一個英文字母用兩個字節(jié)，一個漢字也用兩個字節(jié)。 對于導入、導出數(shù)據(jù)庫均應當考慮源數(shù)據(jù)庫和目標數(shù)據(jù)庫的格式，在創(chuàng)建新的數(shù)據(jù)庫（或數(shù)據(jù)倉庫）時，應考慮源數(shù)據(jù)庫的數(shù)據(jù)類型，是二者保持一致。 B統(tǒng)一源數(shù)據(jù)庫導出與目標數(shù)據(jù)庫導入字符集 從源數(shù)據(jù)庫導出數(shù)據(jù)時，需要為設(shè)置導出數(shù)據(jù)文件的字符集格式，使用數(shù)據(jù)文件向目標數(shù)據(jù)庫導入數(shù)據(jù)時也需要設(shè)置數(shù)據(jù)文件的字符集格式，兩個字符

16、集的格式必須一致，否則就很有可能發(fā)生錯誤。2.4.2 大數(shù)據(jù)量插入前考慮刪除索引 當插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量10%以上時，插入數(shù)據(jù)過程中維護索引的時間很可能大于數(shù)據(jù)插入后重新建立索引的時間。此時，需要考慮刪除該表的索引來提高數(shù)據(jù)的插入效率，待數(shù)據(jù)全部插入后再建立索引。3 測試階段 測試和優(yōu)化兩個階段實際上是相輔相成的，測試階段要測試ETL邏輯的準確性，通過測試過程就可以得出ETL過程的性能結(jié)果。建議產(chǎn)出物為ETL測試報告。4 優(yōu)化階段ETL優(yōu)化階段的主要內(nèi)容包括正確的使用索引和游標、對數(shù)據(jù)抽取和載入時的SQL語句進行優(yōu)化、數(shù)據(jù)庫參數(shù)優(yōu)化及ETL流程優(yōu)化等方面。本文在ETL實現(xiàn)階段提出了對

17、索引和游標的使用建議，下面主要對SQL語句的優(yōu)化進行說明。SQL語句的設(shè)計是否合理，直接影響到ETL過程的執(zhí)行效率，本文建議從下面幾個方面進行檢查。4.1 合理安排WHERE子句中的連接順序 對于不同的數(shù)據(jù)庫管理系統(tǒng)，可能會使用不同的機制解析where字句中的篩選順序。鑒于吉林銀行數(shù)據(jù)倉庫使用ORACLE數(shù)據(jù)庫，本文以該數(shù)據(jù)庫為例。 在ORACLE數(shù)據(jù)庫中，ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其它WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。 低效：SELECT * FROM EMP E WHERE SAL &

18、gt; 50000 AND JOB = 'MANAGER AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)； 高效：SELECT * FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = 'MANAGER；4.2 使用TRUNCATE刪除全表 當DELETE刪除表中的記錄時，有回滾段(rollback segments ) 用來存放可以被恢復的信息，而當運用TRUNCATE

19、時，回滾段不再存放任何可被恢復的信息，所以執(zhí)行時間也會很短。同時需要注意TRUNCATE只在刪除全表時適用，因為TRUNCATE是DDL而不是DML。4.3 多使用COMMIT ETL中同一個過程的數(shù)據(jù)操作步驟很多，數(shù)據(jù)倉庫采用的是數(shù)據(jù)抽取后分析模型重算的原理，所以對數(shù)據(jù)的COMMIT不像業(yè)務系統(tǒng)為保證數(shù)據(jù)的完整和一致性而需要某個操作過程全部完成才能進行，只要有可能就在程序中對每個DELETE、INSERT和UPDATE操作盡量多使用COMMIT, 這樣系統(tǒng)性能會因為COMMIT所釋放的資源而大大提高。4.4 用EXISTS替代IN（1）查詢條件為存在的情況在許多基于基礎(chǔ)表的查詢中，為了滿足一

20、個條件往往需要對另一個表進行聯(lián)接，例如在ETL過程寫數(shù)據(jù)到模型時經(jīng)常需要關(guān)聯(lián)10個左右的維表，在這種情況下，使用EXISTS而不用IN將提高查詢的效率。 舉例： (高效) SELECT  *  FROM  EMP (基礎(chǔ)表) WHERE EMPNO>0  AND EXISTS ( SELECT X'  FROM DEPT  WHERE DEPT.DEPTNO = EMP.DEPTNO AND  LOC = MEL

21、B')  (低效) SELECT  * FROM  EMP(基礎(chǔ)表)  WHERE  EMPNO>0 AND DEPTNO IN (SELECT DEPTNO  FROM  DEPT  WHERE  LOC = MELB')（2）查詢條件為不存在的情況 子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因為它

22、對子查詢中的表執(zhí)行了一個全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。4.5 優(yōu)化GROUP BY 提高GROUP BY 語句的效率，可以通過將不需要的記錄在GROUP BY 之前過濾掉。舉例如下： 低效： SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT OR JOB = 'MANAGER 高效： SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT OR JOB = 'MANAGER GROUP BY

23、 JOB4.6 盡量使用UNION-ALL替換UNION ETL過程針對多表連接操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所連接的各個表中無主關(guān)鍵字相同的記錄，因為UNION ALL 將兩個結(jié)果集合中的相同記錄合并為一條。 當SQL語句需要UNION兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以UNION-ALL的方式被合并，然后在輸出最終結(jié)果前進行排序。如果用UNION ALL替代UNION，這樣排序就不是必要了，效率就會因此得到提高3-5倍4.7 謹慎使用 SELECT * 在使用SELECT語句時，應只選擇所需要的列。當希望在SELECT子句中列出所有的C

24、OLUMN時，使用動態(tài)SQL列引用*是一個方便的方法。但這是一個非常低效的方法。 以O(shè)RACLE數(shù)據(jù)庫為例，ORACLE會在解析的過程中, 將*依次轉(zhuǎn)換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費更多的時間。4.8 減少對表的查詢 在含有子查詢的SQL語句中，要特別注意減少對表的查詢，減少數(shù)據(jù)庫壓力，舉例如下。 低效語句：               SELECT TAB_NAME FROM TABLES 

25、;              WHERE TAB_NAME = ( SELECT TAB_NAME                             &#

26、160; FROM TAB_COLUMNS WHERE VERSION = 604)               ANDDB_VER= ( SELECT DB_VER                      

27、         FROM TAB_COLUMNS WHERE VERSION = 604)          高效語句：               SELECT TAB_NAME FROM TABLES    

28、60;          WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER)                 FROM TAB_COLUMNS WHERE VERSION = 604)5 遷移階段 我們經(jīng)常碰到從一個環(huán)境遷移到另一個環(huán)境的情況，ETL遷移的過程應該放在

29、數(shù)據(jù)倉庫模型遷移后，也可以根據(jù)項目的實際情況一起進行遷移。建議為本階段建立ETL遷移規(guī)范。存儲設(shè)計建議 數(shù)據(jù)倉庫的存儲設(shè)計就是數(shù)據(jù)倉庫邏輯模型在物理系統(tǒng)中的實現(xiàn)模式。其中包括了邏輯模型中各種實體表的具體化，例如表的數(shù)據(jù)結(jié)構(gòu)類型、索引策略、數(shù)據(jù)存放位置和數(shù)據(jù)存儲分配等。在進行物理模型的設(shè)計實現(xiàn)時，所考慮的因素有：I/O存取時間、空間利用率及維護的代價。 為確定數(shù)據(jù)倉庫的物理模型，設(shè)計人員必須做這樣幾方面工作：首先要全面了解所選用的數(shù)據(jù)庫管理系統(tǒng)，特別是存儲結(jié)構(gòu)和存取方法；其次了解數(shù)據(jù)環(huán)境、數(shù)據(jù)的使用頻率、使用方式、數(shù)據(jù)規(guī)模及響應時間要求等，這些都是對時間和空間效率進行平衡和優(yōu)化的重要依據(jù)；最后還

30、需要了解外部存儲設(shè)備的特征。只有這樣才能在數(shù)據(jù)的存儲需求與外部存儲設(shè)備條件兩者之間獲得平衡。1  物理存儲結(jié)構(gòu) 在數(shù)據(jù)倉庫存儲的物理設(shè)計中，需要按數(shù)據(jù)的重要性、使用頻率及對反應時間的要求進行分類，并將不同類型的數(shù)據(jù)分別存儲在不同的存儲設(shè)備中。重要性高、經(jīng)常存取并對反應時間要求高的數(shù)據(jù)存放在高速存儲設(shè)備上；存取頻率低或?qū)Υ嫒№憫獣r間要求低的數(shù)據(jù)則可以存放在低速存儲設(shè)備上。另外，在設(shè)計時還要考慮數(shù)據(jù)在特定存儲介質(zhì)上的布局。在設(shè)計數(shù)據(jù)的布局時要注意遵循以下原則。1.1 設(shè)計數(shù)據(jù)庫分區(qū) 分區(qū)是一種“分而治之”的技術(shù)，通過將大表和索引分成可以管理的小塊，從而避免了對每個表作為一個大的、單獨的對

31、象進行管理，為大量數(shù)據(jù)提供了可伸縮的性能。圖 數(shù)據(jù)庫分區(qū) 如上圖所示，分區(qū)通過將操作分配給更小的存儲單元，減少了需要進行管理操作的時間，并通過增強的并行處理提高了性能，通過屏蔽故障數(shù)據(jù)的分區(qū)，還增加了可用性。 分區(qū)能夠提高許多應用程序的可管理性、性能與可用性。分區(qū)可以將表、索引及索引編排表進一步劃分，從而可以更精細地對這些數(shù)據(jù)庫對象進行管理和訪問。各種數(shù)據(jù)庫管理系統(tǒng)提供了種類繁多的分區(qū)方案以滿足所有的業(yè)務需要。另外，由于在 SQL語句中是完全透明的，所以分區(qū)可以用于幾乎所有的應用程序。 （1）分區(qū)的優(yōu)點 分區(qū)可以提高可管理性、性能與可用性，從而給各種各樣的應用程序帶來極大的好處。通常，分區(qū)可以

32、使某些查詢以及維護操作的性能大大提高。此外，分區(qū)還能夠在很大程度上簡化日常管理任務。 分區(qū)還使數(shù)據(jù)庫設(shè)計人員和管理員能夠解決尖端應用程序帶來的最難的問題。分區(qū)是建立上億萬字節(jié)數(shù)據(jù)系統(tǒng)或需要極高可用性系統(tǒng)的關(guān)鍵工具。（2）表和索引分區(qū) 分區(qū)能夠使表、索引或索引編排表進一步細分。這些數(shù)據(jù)庫對象的片段叫做分區(qū)。每個分區(qū)有自己的名稱，還可以選擇自己的存儲特征。例如，表可以在不同的表空間以壓縮表的形式存儲舊的分區(qū)。從數(shù)據(jù)庫管理員的角度看，分區(qū)的對象有多個段，可以進行統(tǒng)一管理或單獨管理。這使數(shù)據(jù)庫管理員在管理分區(qū)的對象時有相當大的靈活性。但是，從應用程序的角度看，分區(qū)的表與未分區(qū)的表是一樣的，所以在使用

33、SQL DML命令訪問分區(qū)的表時無須進行修改。1.2 使用裸設(shè)備存儲（1）裸設(shè)備介紹 裸設(shè)備也叫裸分區(qū)（原始分區(qū)），是一種沒有經(jīng)過格式化，不經(jīng)過操作系統(tǒng)（文件系統(tǒng)）而應用程序可以直接讀寫的特殊字符設(shè)備。它由應用程序負責對它進行讀寫操作，不經(jīng)過文件系統(tǒng)的緩沖。 使用裸設(shè)備可避免經(jīng)過操作系統(tǒng)這一層，數(shù)據(jù)直接從磁盤到數(shù)據(jù)庫進行傳輸，所以使用裸設(shè)備對于讀寫頻繁的數(shù)據(jù)庫應用來說，可以極大地提高數(shù)據(jù)庫系統(tǒng)的性能。當然，這是以磁盤的 I/O非常大，磁盤I/O已經(jīng)稱為系統(tǒng)瓶頸的情況下才成立。如果磁盤讀寫確實非常頻繁，以至于磁盤讀寫成為系統(tǒng)瓶頸的情況成立，那么采用裸設(shè)備確實可以大大提高性能。（2）使用裸設(shè)備的場

34、合在僅考慮數(shù)據(jù)庫寫入或更新操作的情況下，裸設(shè)備完全可以大大提供操作效率。但類似數(shù)據(jù)倉庫這樣數(shù)據(jù)讀操作為主的場合，當從數(shù)據(jù)庫中讀取的數(shù)據(jù)量級別與內(nèi)存大小級別接近時，使用文件系統(tǒng)存儲的數(shù)據(jù)庫可以從文件系統(tǒng)緩存中獲得大量數(shù)據(jù)，加快數(shù)據(jù)庫讀取操作的速度。而裸設(shè)備沒有經(jīng)過文件系統(tǒng)緩存，此時對裸設(shè)備操作的速度反而可能低于通過文件系統(tǒng)的操作速度。 但讀取數(shù)據(jù)量大于內(nèi)存大小級別時，文件系統(tǒng)往往是開始快（利用緩存），后來就慢下來（正常速度）。而且，文件系統(tǒng)緩存使用的過大，是要占用大量內(nèi)存資源或者是swap空間，更會影響數(shù)據(jù)庫操作的性能。在這種情況下，使用裸設(shè)備存儲可以大大提高數(shù)據(jù)庫的操作性能。1.3 分離連接操

35、作頻率高的大表 不要把經(jīng)常需要連接的幾張表放在同一存儲設(shè)備上，這樣可以利用存儲設(shè)備的并行操作功能加快數(shù)據(jù)查詢的速度。1.4 考慮網(wǎng)絡流量的影響 如果幾臺服務器之間的連接會造成嚴重的網(wǎng)絡業(yè)務量的問題，則要考慮服務器復制表格，因為不同服務器之間的數(shù)據(jù)連接會給網(wǎng)絡帶來沉重的數(shù)據(jù)傳輸負擔。1.5 共享數(shù)據(jù)存放在高速設(shè)備上 考慮把整個企業(yè)共享的細節(jié)數(shù)據(jù)放在主機或其他集中式服務器上，提高這些共享數(shù)據(jù)的使用速度。1.6 分離大數(shù)據(jù)表和索引 不要把表格和它們的索引放在同一設(shè)備上。一般可以將索引存放在高速存儲設(shè)備上，而表格則存放在一般存儲設(shè)備上，以加快數(shù)據(jù)的查詢速度。1.7 合理使用RAID技術(shù) 在對服務器進行

36、處理時往往要進行大量的等待磁盤數(shù)據(jù)的工作，此時，可以在系統(tǒng)中使用RAID(Redundant Array of Inexpensive Disk，廉價冗余磁盤陣列)。2  大表的存儲策略2.1 大表及其索引分區(qū) 可以將大表和索引進行分區(qū)，變成可以管理的小塊，從而避免了對每個表作為一個大的，單獨的對象進行管理。分區(qū)(partitoning)是一種”分而置之”的技術(shù)，它為大量數(shù)據(jù)提供了可伸縮的性能。分區(qū)通過將操作分配給更小的存儲單元，減少了需要進行管理操作的時間，并通過增強的并行處理提高了性能，通過包含故障的影響還增加了可用性。 管理員可以指定每個分區(qū)的存儲屬性，分區(qū)在宿主文件系統(tǒng)中的放

37、置情況，這樣便增加了對超大型數(shù)據(jù)庫的控制粒度(granularity)。分區(qū)可以被單獨地卸出或裝入(taken off-line or brought on-line)、備份、恢復、轉(zhuǎn)出和轉(zhuǎn)入(exported and imported)以及加載；因此減少了需要進行管理操作的時間。 對表分區(qū)還可以創(chuàng)建單獨的索引分區(qū)，從而限制了需要進行索引維護操作的時間。此外，還提供了種類繁多的局部和全局的索引技術(shù)。分區(qū)操作也可以被并行執(zhí)行。 分區(qū)技術(shù)還提高了數(shù)據(jù)的可用性。當部分數(shù)據(jù)由于故障或其它原因不可用時，其它分區(qū)內(nèi)的數(shù)據(jù)可用不收影響繼續(xù)使用。 分區(qū)對應用是透明的，可以通過標準的SQL語句對分區(qū)表進行操作。

38、2.2 寬表存儲 通過分析銀行業(yè)務的數(shù)據(jù)特點，同時根據(jù)前臺數(shù)據(jù)分析查看的需要，對于某些大表可采用寬表(列比較多，結(jié)構(gòu)上較寬的表)這種數(shù)據(jù)存儲方式。實現(xiàn)中，模型采用寬表形式的事實表組織業(yè)務指標數(shù)據(jù)，來達到存儲空間小、訪問速度快的目的。其實質(zhì)是根據(jù)銀行業(yè)數(shù)據(jù)的特點，在數(shù)據(jù)倉庫基本模型(星型模型)設(shè)計的基礎(chǔ)上做出的一種改進。 以銀行帳戶的余額指標為例。通常銀行分戶帳數(shù)據(jù)的存儲方式，是每日每帳戶記錄余額，既使帳戶某日的余額沒有發(fā)生變動，也需要復制前日余額作為當日余額。但事實上，帳戶余額每日變動的頻率非常小，大部分帳戶的變動集中在幾次或一次。此變化頻率可以使用寬表來記錄帳戶某個指標的變化。將每日余額值記

39、錄到列上，即一天的余額值用一列記錄，如下面的貸款帳戶指標表(ACCT_TARGET_DK)所示，它把時間維拆分為年和月、日，分別放在行和列上，而不是傳統(tǒng)地把時間(年，月，日)作為一個維(數(shù)據(jù)要求到日粒度的)，利用此方法可達到性能優(yōu)化的目的。ACCT_TARGET_DK (ACCT_SEQ，YEAR，TARGET_TYPE，COL0101，COL 0102，COL 0103，COLl230，COLL1231) 其中ACCT_SEQ:為帳戶序列號、外鍵、貸款帳戶維表的主鍵；YEAR:年份；TARGET_TYPE:指標類型；COLXXYY: XX月YY號某帳戶某個類型的指標值。 假設(shè)原表中具有168

40、0000條記錄，測試的具體結(jié)論比較值： 傳統(tǒng)結(jié)構(gòu)：需要空間大小為500M日×365日=182500M年 寬表結(jié)構(gòu)：需要空間大小3372M年 比較：寬表結(jié)構(gòu)的存儲方式大小是傳統(tǒng)結(jié)構(gòu)的1.8。 另外使用寬表進行存儲，還具有提高處理速度、增強查詢性能的作用。3  設(shè)計索引策略 數(shù)據(jù)倉庫的數(shù)據(jù)量很大，因而需要對數(shù)據(jù)的存取路徑進行仔細地設(shè)計和選擇。由于數(shù)據(jù)倉庫的數(shù)據(jù)一般很少更新，所以可以設(shè)計索引結(jié)構(gòu)來提高數(shù)據(jù)存取效率。在數(shù)據(jù)倉庫中，設(shè)計人員可以考慮對各個數(shù)據(jù)存儲建立專用的索引和復雜的索引，以獲取較高的存取效率，雖然建立它們需要付出一定的代價，但建立后一般不需要過多的維護。 數(shù)據(jù)倉庫中

41、的表通常要比聯(lián)機事務處理系統(tǒng)(OLTP)中的表建立更多的索引，表中應用的最大索引數(shù)應與表格的規(guī)模成正比。數(shù)據(jù)倉庫是個只讀的環(huán)境，建立索引可以取得靈活性，對性能極為有利。但是表若有很多索引，那么數(shù)據(jù)加載時間就會延長，因此索引的建立需要進行綜合的考慮。在建立索引時，可以按照索引使用的頻率由高到低逐步添加，直到某一索引加入后，使數(shù)據(jù)加載或重組表的時間過長時，就結(jié)束索引的添加。 最初，一般都是按主關(guān)鍵字和大多數(shù)外部關(guān)鍵字建立索引，通常不要添加很多的其他索引。在表建立大量的索引后，對表進行分析等具體使用時，可能需要許多索引，這會導致表的維護時間也隨之增加。如果從主關(guān)鍵字和外部關(guān)鍵字著手建立索引，并按照需

42、要添加其他索引，就會避免首先建立大量的索引帶來的后果。如果表格過大，而且需要另外增加索引，那么可以將表進行分割處理。如果一個表中所有用到的列都在索引文件中，就不必訪問事實表，只要訪問索引就可以達到訪問數(shù)據(jù)的目的，以此來減少I/O操作。3.1 選擇正確的索引類型 索引是一個單獨的、物理的數(shù)據(jù)庫結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。 索引與表一樣，需要占用磁盤空間，但在索引里的數(shù)據(jù)存放形式與表里的數(shù)據(jù)存放形式不一樣。在理解索引時，可以想象一本書，其中書的內(nèi)容就相當于表里的數(shù)據(jù)，而書前面的目錄就相當于該表的索引。通常情況下，索引所占用的

43、磁盤空間要比表要小的多，其主要作用是為了加快對數(shù)據(jù)的搜索速度，也可以用來保證數(shù)據(jù)的唯一性。 索引作為一種可選的數(shù)據(jù)結(jié)構(gòu)，你可以選擇為某個表創(chuàng)建索引，也可以不創(chuàng)建。一旦創(chuàng)建了索引，就意味著數(shù)據(jù)庫系統(tǒng)對表進行DML（包括INSERT、UPDATE、DELETE）時，必須處理額外的工作量（也就是對索引結(jié)構(gòu)的維護）以及存儲方面的開銷。在數(shù)據(jù)庫設(shè)計過程中，需要根據(jù)實際業(yè)務需求和數(shù)據(jù)特點，考慮創(chuàng)建索引所帶來的查詢性能方面的提高，與引起的額外的開銷相比，是否值得。故數(shù)據(jù)庫表中的索引絕對不是多多益善。具體來說，在索引建立上有如下建議。 B樹索引 缺省的和最常用的表列索引類型是B樹(或標準)索引，這是執(zhí)行標準的

44、索引創(chuàng)建語句時需要使用的索引類型。它是有索引節(jié)點的有序樹，每一個節(jié)點包括一個或多個索引項，每個索引項對應于表中的一行，它包含兩個元素：行的索引列值(或數(shù)值集)與行的RowID(或物理磁盤位置)。B樹索引為表中的每行包含一項除非行的索引項是空值。當使用B樹索引時，數(shù)據(jù)庫管理系統(tǒng)順著索引節(jié)點樹向下查找有查詢選擇條件相匹配的索引值。當它找到匹配時數(shù)據(jù)庫管理系統(tǒng)使用相應的ROWlD來定位和從磁盤中讀取相關(guān)的表行數(shù)據(jù)。 B樹索引并不是對所有類型的應用程序和所有類型的表列都適合的。通常B樹索引最適合用于不斷插入、更新和刪除數(shù)據(jù)的OLTP應用程序。在這種環(huán)境中，B樹索引最適合于包含許多相對于列中鍵值的總數(shù)來

45、說的不同值的關(guān)鍵列。表中的主鍵和次鍵是有B樹索引的列的最好的例子。 謹慎使用反向鍵索引（1）為什么要使用反向鍵索引 反向鍵索引是一種特殊類型的B樹索引，在索引基于含有序數(shù)的列時使非常有用的，如果一個傳統(tǒng)的B樹索引基于一個含有這種數(shù)據(jù)的列，往往會產(chǎn)生許多級。圖 理想B樹索引與非正常B樹索引 如上左圖所示，B樹的所有非葉子結(jié)點的左右子樹的結(jié)點數(shù)目均保持差不多（平衡），那么B樹的搜索性能逼近二分查找。但如果插入索引列的數(shù)據(jù)是嚴格的序列時，普通B樹索引就可能形成如上右圖所示的結(jié)構(gòu)，此時它的搜索性能已經(jīng)是線性的了。 但B樹索引有4級以上的深度就會降低性能，因此反向鍵索引更適合這種類型，反向鍵索引通過簡單

46、的反象被索引的列中的數(shù)據(jù)來解決問題，他首先反向每個列鍵值的字節(jié)，然后在反向后的新數(shù)據(jù)上進行索引，而新數(shù)據(jù)在值的范圍上的分布通常比原來的有序數(shù)更均勻。  如1000,10001,10011,10111,1100經(jīng)過反向后的值將是0001,1001,1101,0011。顯然經(jīng)過位反向處理的有序數(shù)據(jù)變得比較隨機了，這樣所得到的索引樹就比較對稱，從而提高表的查詢性能。（2）數(shù)據(jù)倉庫中并適用 但反向鍵索引有它局限性，由于鍵的真實值發(fā)生了方向，雖然在上述情況下，反向鍵索引可以大大提高INSERT、UPDATE等操作的性能，但對于需要進行范圍搜索的場合不一定使用，而數(shù)據(jù)倉庫大多數(shù)情況下正是此類應用

47、。 建立位圖索引（1）位圖索引原理 在基數(shù)小的欄位上要善于使用位圖索引，基數(shù)是位圖索引中的一個基本的定義，它是指數(shù)據(jù)庫表中某個字段內(nèi)容中不重復的數(shù)值。如在員工信息表中的性別字段，一般就只有男、女兩個值，因此其基數(shù)為2；婚姻狀況字段的話，則其只有已婚、未婚、離婚三種狀態(tài)，其基數(shù)就為3；民族一覽內(nèi)也只存在有限的取值。 對于要查詢基數(shù)小的字段，如現(xiàn)在用戶想查找所有婚姻狀況為”已婚”的”女性”時，利用位圖索引可以提高查詢的效率。這主要是因為標準索引是通過在索引中保存排序過的索引列以及對應的ROWID來實現(xiàn)的。若我們在基數(shù)小的列上建立標準索引的話，則其會返回大量的記錄。 而當我們在創(chuàng)建位圖索引的時候，通

48、常地數(shù)據(jù)庫管理系統(tǒng)（如Oracle）會對整個表進行掃描，并且會為索引列的每個取值建立一個位圖。若內(nèi)容相同，則在位圖上會以一個相同的數(shù)字表示。此時，若這個字段的基數(shù)比較小的話，則若需要實現(xiàn)對整個字段的查詢的話，效率就會非常的高。因為此時，數(shù)據(jù)庫只要位圖中數(shù)字相同的內(nèi)容找出來即可。 除了在數(shù)據(jù)表某列基數(shù)比較小的情況下，采用位圖索引外，我們往往在一些特殊的情況下，也會建議采用位圖索引。最常見的情況是，在Where限制條件中，若我們多次采用AND或者OR條件時，也建議采用位圖索引。因為當一個查詢引用了一些部署了位圖索引的列的時候，這些位圖可以很方便的與AND或者Or 運算符操作結(jié)合以快速的找出用戶所需

49、要的記錄。（2）使用位圖索引的場合 在數(shù)據(jù)庫設(shè)置中，一般只有在三種情況下才采用位圖索引： 列的基數(shù)比較小，而有可能需要根據(jù)這些字段的內(nèi)容查找相關(guān)的記錄。 在條件語句中，用到了AND或者OR運算符的時候（除了這兩種情況外，最好能夠采用其他適合的索引）； 需要用到NULL作為查詢的限制條件，位圖索引會記錄相關(guān)的NULL值列信息。因為標準查詢一般情況下，會忽略所有的NULL值列。也就是說，若需要查詢“所有沒有身份證號碼”的員工的信息的時候，標準索引并不能夠起到加速查詢速度的作用。此時，需要采用位圖索引。（3）與B樹索引的比較 假設(shè)銀行某理財產(chǎn)品存在5種類型的業(yè)務：A、B、C、D、E，在數(shù)據(jù)庫中為該類

50、型建立位圖索引：如果某行包含一個特定值那么位圖在此位置上包含一個1位，否則它包含一個0位，如下圖所示。圖 位圖索引 假設(shè)產(chǎn)品表中有一千萬行記錄，并假設(shè)B樹索引中的每個索引項有10個字節(jié)，那么一個B樹索引將會需要： 一千萬索引項×每項10字節(jié)=100MB； 一個位圖索引大約需要： (一千萬行×每行1位×5個位圖)每字節(jié)8位=625MB； 在這種情況下，位圖索引不僅節(jié)省存儲，而且讀取速度必然要比B樹索引快得多。（4）注意事項 不是在條件語句中包含運算符的時候，采用位圖索引都能夠提供比較高的效率。一般來說，只有AND 或者OR運算符的時候，位圖索引才會比較具有優(yōu)勢。若此

51、時用戶采用大于號或者不等于號作為條件語句中的限制條件的時候，則往往采用標準索引具有更大的優(yōu)勢。 使用組合索引 組合索引又叫復合索引，是同時包含兩個或兩個以上列的索引。如果一列數(shù)據(jù)的信息不能單獨提供較高的選擇性，組合索引將會非常有用，可以有效的縮小搜索范圍。 吉林銀行使用Oracle數(shù)據(jù)庫作為數(shù)據(jù)倉庫，在組合索引的使用方面，Oracle有以下特點： 當使用基于規(guī)則的優(yōu)化器(RBO)時，只有當組合索引的前導列出現(xiàn)在SQL語句的where子句中時，才會使用到該索引； 在使用Oracle9i之前的基于成本的優(yōu)化器(CBO)時， 只有當組合索引的前導列出現(xiàn)在SQL語句的where子句中時，才可能會使用到

52、該索引，這取決于優(yōu)化器計算的使用索引的成本和使用全表掃描的成本，Oracle會自動選擇成本低的訪問路徑(請見下面的測試1和測試2)； 從Oracle9i起，Oracle引入了一種新的索引掃描方式索引跳躍掃描(index skip scan)，這種掃描方式只有基于成本的優(yōu)化器(CBO)才能使用。這樣，當SQL語句的where子句中即使沒有組合索引的前導列，并且索引跳躍掃描的成本低于其他掃描方式的成本時，Oracle就會使用該方式掃描組合索引； Oracle優(yōu)化器有時會做出錯誤的選擇，因為它再“聰明”，也不如我們SQL語句編寫人員更清楚表中數(shù)據(jù)的分布，在這種情況下，通過使用提示(hint)，我們可

53、以幫助Oracle優(yōu)化器作出更好的選擇。 如上所述，雖然Oracle 9i以后引入的跳躍式掃描索引訪問方法增強了優(yōu)化器在使用組合索引時的選擇，但是您應該謹慎地選擇索引中的列順序。一般來說，索引的第一列應該是最有可能在WHERE子句中使用的列，并且也是索引中最具選擇性的列。 聚集索引與非聚集索引 聚集索引確定表中數(shù)據(jù)的物理順序。聚集索引類似于電話簿，后者按姓氏排列數(shù)據(jù)。由于聚集索引規(guī)定數(shù)據(jù)在表中的物理存儲順序，因此一個表只能包含一個聚集索引。但該索引可以包含多個列(組合索引)，就像電話簿按姓氏和名字進行組織一樣。 表 聚集索引的使用場合動作描述使用聚集索引使用非聚集索引列經(jīng)常被分組排序應應返回某范圍內(nèi)的數(shù)據(jù)應不應一個或極少不同值不應不應小數(shù)目的不同值應不應大數(shù)目的不同值不應應頻繁更新的列不應應外鍵列應應主鍵列應應頻繁修改索引列不應應 如上表所示，為聚集索引的使用場合，聚集索引對于那些經(jīng)常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。例如，如果應用程序執(zhí)行的一個查詢經(jīng)常檢索某一日期范圍內(nèi)的記錄，則使用聚集索引