ETL優化 （R2）

thamsyangsw發表於2014-03-21
優化
轉載地址：http://blog.csdn.net/jadeite/article/details/1837426 

在討論ETL優化之前我們應該首先討論的是當前程式的效能。首先要定義ETL過程的基線標準。在建立了基線標準的基礎上我們再來討論該如何對已有過程進行優化。
基線標準也應該包括標準情況下網路傳輸速度，硬碟讀取速度等等的硬體指標。在此之上測量多次ETL過程得到比較穩定的 資料量(M)/時間(s) 的比值作為基線標準。當然前提是在一定的範圍內資料量的增長和計算量的增長一定要呈線性關係。一種比較理想的假設就是每條資料都經過相同的計算/轉換過程。
建立基線的好處在於有了除錯以及優化的依據。最大程度地避免了人的主觀感覺所造成的錯誤判斷。
“經過優化，現在的ETL效能好了一些！”這種話應該換成“經過優化，現在效能比基線效能提升了1%”
 
當然我們最終的目的是進行ETL過程的優化。優化的方面包括索引的調整，資料抽取的並行處理，複雜運算的程式處理等手段。當然編寫出高效的SQL應該是首要的考慮因素了！
 
筆者當初經歷過一個專案。用的SQL SERVER自帶的DTS作為ETL工具。在專案設計階段沒有考慮ETL的效能問題。在開發過程中隨著實現的功能越來越多，演算法越來越複雜，需要處理的資料也是越來越多 每次ETL過程所需要的時間不斷增長。而專案本身需要每半小時同步一次資料。沒辦法減少需求和資料量，只好著手開始優化。
 
 
分析了一下整個ETL過程。發現大致可以分為兩類操作。一類是直接從資料來源拷貝資料，一類是從資料來源獲得原始資料在過程中計算再把結果存到結果資料庫中。
 
從資料量看直接拷貝的部分資料量比較小，需要計算的部分資料量較大。兩者比例大概 30% / 70%
 
 
我們首先從直接拷備的資料開始入手。先測試了一下網路環境，因為兩臺伺服器在一個區域網裡，網路很穩定所以只要能排除鎖表等原因的話應該沒有什麼優化的餘地了。
 
再看需要計算的部分。由於整個過程的邏輯比較複雜，資料量較大所以耗時較長。用掉了整個ETL過程 90% 時間。
 
 
 
1.       SQL 指令碼
SQL指令碼的優化是個很寬泛的話題。在ETL中我們需要主要的內容主要包括：
?儘量避免使用遊標進行資料操作
作過程式開發的人估計對遊標都很熟悉，很多程式設計師剛開始用SQL指令碼寫邏輯的時候很自然的使用遊標進行計算和操作。
但我建議在抽取/轉換/計算等等過程中都要儘可能地避免使用遊標。應該意識到用遊標遍歷一個1000條記錄的資料集就等於做了1000次select !
如果說在資料量小，或者完全沒有時間限制的情況下可以使用的話。那麼在大資料量，有嚴格的時間控制的情況下使用遊標絕對是程式設計師的噩夢！
筆者剛出道的時候就犯過類似錯誤。過程略去不說。結果是寫出來的ETL過程需要消耗盡兩個小時才能完成。這在當時是完全不能接受的，因為專案的設計是每半小時同步一次。而且我自己除錯起來也是十分痛苦！
後來經人指點修改了資料的計算方式，改用臨時表代替遊標，整個過程只需要不到三分鐘就完成了！
希望這樣血淋淋的教訓能夠警示後人，慎用遊標！
 
當然我們也不是說絕對不能用。在後來的專案中我們還是不時會用到。比如遍歷一個例項上的所有資料庫等操作。用遊標實現就顯得十分自然。
 
?SQL 指令碼儘量避免邏輯為“非”的條件
用“非”條件進行資料檢索的時候會使索引失效。這點相信大家都很清楚。類似的還有在用like的時候不要以 “%”開頭，不要使用關鍵字 “in”等等。
 
?不要在任何地方寫 SELECT * FROM TABLE
哪怕是確實需要所有的欄位，最好也一個一個地列出來。更便於維護/修改。而且大部分情況是我們不需要一張表裡的全部欄位。寫 “select *”是因為我們在偷懶！
 
?儘量避免使用Lfet join 和 Null
Left Join 效率低我想就不用多解釋了吧。其實任何大表的join操作都是應該避免的。如果一定要join可以考慮檢視索引(SQL SERVER) 物化檢視(Oracle)
Null 值是需要資料引擎單獨處理的內容。自然需要犧牲一部分效能。
……更多請參見SQL 優化的相關文章。
 
 
2.       調整索引
資料來源的索引我們很難控制，在這裡就不加討論了。我們只討論目的資料庫的索引問題。
總所周知索引的作用在於查詢時能更快的得到結果。但這是以降低插入，修改，刪除操作的效能為代價換來的。但目的表又不能沒有索引。
索引是一把雙刃劍，用得好就是提升效能的利器，而使用不當也會成為效能的殺手。
所以在我們需要在建立和使用索引時候格外注意。
如果需要插入的資料量很大，我們可以考慮在插入資料前先刪除索引，插入操作完畢後再建立索引。這樣避免了引擎在插入資料的同時維護索引，新建的索引也會更整齊連貫。
 
3.       資料抽取的並行化
並行的資料抽取主要包括從多個資料來源同時進行抽取操作，如果目的資料庫分了檔案組並且分佈在目的伺服器有多塊硬碟上的話效率會有較大提高。
 
4.       複雜運算的程式處理
有時從資料來源抽取的資料在進入資料倉儲之前要經過很複雜的邏輯運算。在這種時候我們可以考慮用程式的方式在ETL處理流程外進行處理，再把結果返回。畢竟用SQL指令碼寫邏輯很強的程式不如用.NET/JAVA來的順手。同時在程式計算的過程中還可以進行資料的傳輸。
 
但這種方式會給ETL整體的控制和維護造成負面影響，設計時要仔細考慮，權衡利弊。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26613085/viewspace-1126455/，如需轉載，請註明出處，否則將追究法律責任。

相關文章