優化方案：ETL的過程原理和數據倉庫建設

2024-07-21 02:43:37

字體：大中小

來源：轉載

供稿：網友

這篇論壇文章（賽迪網技術社區）根據筆者多年的數據倉庫實施經驗，同時結合ETL的過程原理和數據倉庫建設方法歸納總結了以下優化的方案，詳細內容請讀者參考下文：

1.引言

數據倉庫建設中的ETL(Extract, Transform, Load)是數據抽取、轉換和裝載到模型的過程，整個過程基本是通過控制用SQL語句編寫的存儲過程和函數的方式來實現對數據的直接操作，SQL語句的效率將直接影響到數據倉庫后臺的性能。

目前，國內的大中型企業基本都具有四年以上計算機信息系統應用經驗，積累了大量可分析的業務數據，這些信息系統中的數據需要通過搭建數據倉庫平臺才能得到科學的分析，這也是近幾年數據倉庫系統建設成為IT領域熱門話題的原因。

2.優化的思路分析

數據倉庫ETL過程的主要特點是：面對海量的數據進行抽?。环謺r段對大批量數據進行刪除、更新和插入操作；面對異常的數據進行規則化的清洗；大量的分析模型重算工作；有特定的過程處理時間規律性，一般整個ETL過程需要在每天的零點開始到6點之前完成。所以，針對ETL過程的優化主要是結合數據倉庫自身的特點，抓住需要優化的主要方面，針對不同的情況從如何采用高效的SQL入手來進行。

優化的實例分析

目前數據倉庫建設中的后臺數據庫大部分采用Oracle，以下的SQL采用Oracle的語法來說明，所有的測試在Oracle9i環境中通過，但其優化的方法和原理同樣適合除Oracle之外的其他數據庫。

3.1索引的正確使用

在海量數據表中，基本每個表都有一個或多個的索引來保證高效的查詢，在ETL過程中的索引需要遵循以下使用原則：

(1) 當插入的數據為數據表中的記錄數量10%以上時, 首先需要刪除該表的索引來提高數據的插入效率，當數據全部插入后再建立索引。

(2) 避免在索引列上使用函數或計算，在WHERE子句中，如果索引列是函數的一部分，優化器將不使用索引而使用全表掃描。舉例:

低效： SELECT * ROM DEPT WHERE SAL * 12 > 25000;

高效： SELECT * FROM DEPT WHERE SAL > 25000/12;

(3) 避免在索引列上使用NOT和”!=” ，索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當數據庫遇到NOT和”!=”時，就會停止使用索引轉而執行全表掃描。

(4) 索引列上用>=替代>

高效： SELECT * FROM EMP WHERE DEPTNO >=4

低效： SELECT * FROM EMP WHERE DEPTNO >3

兩者的區別在于，前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。

(5) 函數的列啟用索引方法，如果一定要對使用函數的列啟用索引，Oracle9i以上版本新的功能：基于函數的索引(Function-Based Index)是一個較好的方案，但該類型索引的缺點是只能針對某個函數來建立和使用該函數。

CREATE INDEX EMP_I ON EMP (UPPER( ENAME)); 　　　　　

SELECT * FROM EMP WHERE UPPER(ENAME) = ‘BLACKSNAIL’;

3.2 游標的正確使用

當在海量數據表中進行數據的刪除、更新和插入操作時，用游標處理的效率是最慢的方式，但它在ETL過程中的使用又必不可少，而且使用有著及其重要的地位，所以游標的正確使用尤為重要。

對數據倉庫維表的數據進行維護時，因為需要保證維表ID的一致性，所以采用游標的是數據維護完整性的最好方式。由于它的效率低，如果按照普通的方式將無法處理大數據量的維表數據維護（一般是指10萬條記錄以上的維表），以下是處理這種情況的有效方式：

(1) 在數據抽取的源表中使用時間戳，這樣每天的維表數據維護只針對更新日期為最新時間的數據來進行，大大減少需要維護的數據記錄數。

(2) 在INSERT和UPDATE維表時都加上一個條件來過濾維表中已經存在的記錄，實例為：

INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)

(3) 使用顯式的游標(CURSORs) ，因為使用隱式的游標將會執行兩次操作，第一次檢索記錄，第二次檢查TOO MANY ROWS 這個EXCEPTION，而顯式游標不執行第二次操作。

3.3數據抽取和上載時的SQL優化

◆3.3.1 WHERE子句中的連接順序

ORACLE采用自下而上的順序解析WHERE子句，根據這個原理，表之間的連接必須寫在其它WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在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’;

◆3.3.2 刪除全表時用TRUNCATE替代DELETE

當DELETE刪除表中的記錄時，有回滾段(rollback segments ) 用來存放可以被恢復的信息，而當運用TRUNCATE時，回滾段不再存放任何可被恢復的信息，所以執行時間也會很短。同時需要注意TRUNCATE只在刪除全表時適用，因為TRUNCATE是DDL而不是DML。

◆3.3.3 盡量多使用COMMIT

ETL中同一個過程的數據操作步驟很多，數據倉庫采用的是數據抽取后分析模型重算的原理，所以對數據的COMMIT不像業務系統為保證數據的完整和一致性而需要某個操作過程全部完成才能進行，只要有可能就在程序中對每個DELETE、INSERT和UPDATE操作盡量多使用COMMIT, 這樣系統性能會因為COMMIT所釋放的資源而大大提高。

◆3.3.4 用EXISTS替代IN

在許多基于基礎表的查詢中，為了滿足一個條件往往需要對另一個表進行聯接，例如在ETL過程寫數據到模型時經常需要關聯10個左右的維表，在這種情況下，使用EXISTS而不用IN將提高查詢的效率。

◆3.3.5 用NOT EXISTS替代NOT IN

子查詢中，NOT IN子句將執行一個內部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因為它對子查詢中的表執行了一個全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。

◆3.3.6 優化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

◆3.3.7 有條件的使用UNION-ALL 替換UNION

ETL過程針對多表連接操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所連接的各個表中無主關鍵字相同的記錄，因為UNION ALL 將重復輸出兩個結果集合中相同記錄。

當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以UNION-ALL的方式被合并，然后在輸出最終結果前進行排序。如果用UNION ALL替代UNION，這樣排序就不是必要了，效率就會因此得到提高3-5倍

◆ 3.3.8 分離表和索引