ETL過程原理和數(shù)據(jù)倉(cāng)庫(kù)建設(shè)的優(yōu)化
引言
數(shù)據(jù)倉(cāng)庫(kù)建設(shè)中的ETL(Extract, Transform, Load)是數(shù)據(jù)抽取、轉(zhuǎn)換和裝載到模型的過程，整個(gè)過程基本是通過控制用SQL語句編寫的存儲(chǔ)過程和函數(shù)的方式來實(shí)現(xiàn)對(duì)數(shù)據(jù)的直接操作，SQL語句的效率將直接影響到數(shù)據(jù)倉(cāng)庫(kù)后臺(tái)的性能。
目前，國(guó)內(nèi)的大中型企業(yè)基本都具有四年以上計(jì)算機(jī)信息系統(tǒng)應(yīng)用經(jīng)驗(yàn)，積累了大量可分析的業(yè)務(wù)數(shù)據(jù)，這些信息系統(tǒng)中的數(shù)據(jù)需要通過搭建數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)才能得到科學(xué)的分析，這也是近幾年數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)建設(shè)成為IT領(lǐng)域熱門話題的原因。
優(yōu)化的思路分析
數(shù)據(jù)倉(cāng)庫(kù)ETL過程的主要特點(diǎn)是：面對(duì)海量的數(shù)據(jù)進(jìn)行抽取；分時(shí)段對(duì)大批量數(shù)據(jù)進(jìn)行刪除、更新和插入操作；面對(duì)異常的數(shù)據(jù)進(jìn)行規(guī)則化的清洗；大量的分析模型重算工作；有特定的過程處理時(shí)間規(guī)律性，一般整個(gè)ETL過程需要在每天的零點(diǎn)開始到6點(diǎn)之前完成。所以，針對(duì)ETL過程的優(yōu)化主要是結(jié)合數(shù)據(jù)倉(cāng)庫(kù)自身的特點(diǎn)，抓住需要優(yōu)化的主要方面，針對(duì)不同的情況從如何采用高效的SQL入手來進(jìn)行。
優(yōu)化的實(shí)例分析
目前數(shù)據(jù)倉(cāng)庫(kù)建設(shè)中的后臺(tái)數(shù)據(jù)庫(kù)大部分采用Oracle，以下的SQL采用Oracle的語法來說明，所有的測(cè)試在Oracle9i環(huán)境中通過，但其優(yōu)化的方法和原理同樣適合除Oracle之外的其他數(shù)據(jù)庫(kù)。
1.索引的正確使用
在海量數(shù)據(jù)表中，基本每個(gè)表都有一個(gè)或多個(gè)的索引來保證高效的查詢，在ETL過程中的索引需要遵循以下使用原則：
a.當(dāng)插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量10%以上時(shí), 首先需要?jiǎng)h除該表的索引來提高數(shù)據(jù)的插入效率，當(dāng)數(shù)據(jù)全部插入后再建立索引。
b.避免在索引列上使用函數(shù)或計(jì)算，在WHERE子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用全表掃描。舉例:
低效： SELECT * ROM DEPT WHERE SAL * 12 > 25000;
高效： SELECT * FROM DEPT WHERE SAL > 25000/12;
c.避免在索引列上使用NOT和”!=” ， 索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當(dāng)數(shù)據(jù)庫(kù)遇到NOT和”!=”時(shí)，就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。
d.索引列上用>=替代>
高效： SELECT * FROM EMP WHERE DEPTNO >=4
低效： SELECT * FROM EMP WHERE DEPTNO >3
兩者的區(qū)別在于，前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄。
e.函數(shù)的列啟用索引方法，如果一定要對(duì)使用函數(shù)的列啟用索引，Oracle9i以上版本新的功能：基于函數(shù)的索引(Function-Based Index)是一個(gè)較好的方案，但該類型索引的缺點(diǎn)是只能針對(duì)某個(gè)函數(shù)來建立和使用該函數(shù)。
CREATE INDEX EMP_I ON EMP (UPPER( ENAME));
SELECT * FROM EMP WHERE UPPER(ENAME) = ‘BLACKSNAIL’;
2.游標(biāo)的正確使用
當(dāng)在海量數(shù)據(jù)表中進(jìn)行數(shù)據(jù)的刪除、更新和插入操作時(shí)，用游標(biāo)處理的效率是最慢的方式，但它在ETL過程中的使用又必不可少，而且使用有著及其重要的地位，所以游標(biāo)的正確使用尤為重要。
對(duì)數(shù)據(jù)倉(cāng)庫(kù)維表的數(shù)據(jù)進(jìn)行維護(hù)時(shí)，因?yàn)樾枰ＷC維表ID的一致性，所以采用游標(biāo)的是數(shù)據(jù)維護(hù)完整性的最好方式。由于它的效率低，如果按照普通的方式將無法處理大數(shù)據(jù)量的維表數(shù)據(jù)維護(hù)（一般是指10萬條記錄以上的維表），以下是處理這種情況的有效方式：
a.在數(shù)據(jù)抽取的源表中使用時(shí)間戳，這樣每天的維表數(shù)據(jù)維護(hù)只針對(duì)更新日期為最新時(shí)間的數(shù)據(jù)來進(jìn)行，大大減少需要維護(hù)的數(shù)據(jù)記錄數(shù)。
b.在INSERT和UPDATE維表時(shí)都加上一個(gè)條件來過濾維表中已經(jīng)存在的記錄，實(shí)例為：
INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)
c.使用顯式的游標(biāo)(CURSORs) ，因?yàn)槭褂秒[式的游標(biāo)將會(huì)執(zhí)行兩次操作，第一次檢索記錄，第二次檢查TOO MANY ROWS 這個(gè)EXCEPTION，而顯式游標(biāo)不執(zhí)行第二次操作。
#p#
3.數(shù)據(jù)抽取和上載時(shí)的SQL優(yōu)化
a.WHERE子句中的連接順序
ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在其它WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。
低效：SELECT * FROM EMP E WHERE SAL > 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’;
b.刪除全表時(shí)用TRUNCATE替代DELETE
當(dāng)DELETE刪除表中的記錄時(shí)，有回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息，而當(dāng)運(yùn)用TRUNCATE時(shí)，回滾段不再存放任何可被恢復(fù)的信息，所以執(zhí)行時(shí)間也會(huì)很短。同時(shí)需要注意TRUNCATE只在刪除全表時(shí)適用，因?yàn)門RUNCATE是DDL而不是DML。
c.盡量多使用COMMIT
ETL中同一個(gè)過程的數(shù)據(jù)操作步驟很多，數(shù)據(jù)倉(cāng)庫(kù)采用的是數(shù)據(jù)抽取后分析模型重算的原理，所以對(duì)數(shù)據(jù)的COMMIT不像業(yè)務(wù)系統(tǒng)為保證數(shù)據(jù)的完整和一致性而需要某個(gè)操作過程全部完成才能進(jìn)行，只要有可能就在程序中對(duì)每個(gè)DELETE、INSERT和UPDATE操作盡量多使用COMMIT, 這樣系統(tǒng)性能會(huì)因?yàn)镃OMMIT所釋放的資源而大大提高。
d.用EXISTS替代IN
在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接，例如在ETL過程寫數(shù)據(jù)到模型時(shí)經(jīng)常需要關(guān)聯(lián)10個(gè)左右的維表，在這種情況下，使用EXISTS而不用IN將提高查詢的效率。
e.用NOT EXISTS替代NOT IN
子查詢中，NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。
f.優(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 JOB
g.有條件的使用UNION-ALL 替換UNION
ETL過程針對(duì)多表連接操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所連接的各個(gè)表中無主關(guān)鍵字相同的記錄，因?yàn)閁NION ALL 將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄。
當(dāng)SQL語句需要UNION兩個(gè)查詢結(jié)果集合時(shí)，這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并，然后在輸出最終結(jié)果前進(jìn)行排序。如果用UNION ALL替代UNION，這樣排序就不是必要了，效率就會(huì)因此得到提高3-5倍
h.分離表和索引
總是將你的表和索引建立在不同的表空間內(nèi)，決不要將不屬于ORACLE內(nèi)部系統(tǒng)的對(duì)象存放到SYSTEM表空間里。同時(shí)確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上
【編輯推薦】
