數據倉庫建設探索與實踐

廣發銀行東莞分行 胡小英 李 翔

1.前言

我行自業務主機上收總行后，為了滿足分行的本地的報表查詢和業務開展，通過將總行下發的數據存放在分行的Oracle數據庫中，進行2次開發。但由于Oracle數據庫的復雜性和以前建設倉促等原因，數據利用率、安全性不高，而且不能很好地滿足業務需要，于是我們決定重新搭建分行數據倉庫。

通過一段時間的摸索，我們最終決定采用兩臺SqlServer2008數據庫，一臺只存儲最新一年數據的生產庫，另一臺是存儲歷史數據的歷史庫。兩臺服務器上的數據在生產庫建立分區視圖統一對外暴露出來，外部幾乎不知道有歷史庫的存在，卻能夠查詢所有時間點的數據。以后如果歷史服務器負擔重，我們可以再多添加幾臺服務器，把數據稀釋到幾臺服務器上，這樣可以通過增加服務器實現資源的擴展。

2.數據倉庫建設過程

2.1 搭建生產庫和歷史庫服務器。

3臺機器均采用Windows Server 2008R2操作系統，內存8-16GB，CPU 2.93GHz*2，數據庫為Microsoft SQL Server2008R2SP2(64位)，兩者是基于微軟同一平臺開發的產品，配合起來更能發揮效果。EDS數據庫因承擔著大量的數據更新操作，使用簡單恢復模式，避免產生大量的日志拖慢更新數據及占用磁盤空間。

2.2 制定數據歸檔策略，實現數據歸檔功能，實現分區視圖重建功能。通過實現這兩個功能來簡化維護操作，其實即使沒有，也可以手工維護。

2.2.1 通過權衡和數據測試，確定歷史數據以分區表的形式存儲在歷史數據庫中，按照季度進行分區，流水數據分區字段為qsrq，時點數據分區字段為jsrq，數據庫會自行為數據組織存放位置，對于用戶而言是透明的，由于數據歸檔時涉及的表多，數據多，通過自動歸檔的存儲過程(可制定歸檔日期)，只要加入到每天的調度中，就可以在每天晚上自動歸檔數據(例如每天晚上自動歸檔三個月前的數據)。經過測試一旦單表的歸檔記錄數超過一千萬(可能和系統內存有關)，通過存儲過程來歸檔要花費超過半小時的時間(存儲過程不能多線程，2千8百萬數據用了1個小時)，考慮到100多張表的歸檔排隊，時間會大大延長，一般情況下像“歷史資料表”，“科目日記表”，“會計分錄表”等表的記錄數增長很快，所以每年或每季度的間隔方式做歸檔不太好。目前78張表一個季度的數據大概25G，考慮到新增的48張CBS表，以及其他為加快查詢而新建的索引等，即使按照每季度歸檔數據的方式，生產機的數據容量預計可以控制在100G以內。

2.2.2 另外在實踐中每次歸檔的時候必須根據qsrq或jsrq重建約束，目前還不能實現自動化，所以放棄了一部分性能，不做分區視圖，直接使用普通視圖，經過測試查詢時間所受的影響有限(分區表的優勢抵消了大部分影響)。

2.3 選擇一個合理的初始時間點，從EDS備份的文件中依次上傳數據到最新日期并持續上送數據，保證數據最新。

我們選擇2009-12-04作為起始時點，將EDS下發的數據上傳到EDS生產數據庫。上傳情況良好，78個文件的上傳時間保持在10分鐘左右完成，

2.4 數據遷移，將初始時間點之前的流水表的歷史數據遷移到歷史庫，至此數據已全部遷入新的數據庫體系。

2.5 制定數據訪問規則，以用戶為單位做只讀授權，不論誰，要訪問業務數據，必須申請用戶并指定要訪問的數據，數據庫管理員增加用戶(或修改用戶)進行授權訪問，不再允許任何其他形式對EDS數據(包括接收的dat文件)的訪問(在整改后)，也不允許開放諸如sa等特權用戶。

創建的用戶包括數據庫管理員用戶；管理用戶ids(映射到EDS,HIS,IDS數據庫的public,db_owner角色；用于對EDS,HIS,IDS三個庫的數據管理或數據維護，例如增刪存儲過程，增刪表，視圖，同義詞，訪問授權等)；數據上傳操作用戶uploader(映射到EDS數據庫的public,db_owner角色；用于EDS數據上傳更新操作，EDS上傳程序專用)；數據讀取角色dbreader(用于外部程序使用，映射到IDS,EDS,HIS數據庫的public角色；對dbreader用戶的授權原則：統一在IDS數據庫下進行授權訪問，即只對dbreader開放IDS數據庫的對象訪問權限，比如表/視圖/同義詞的SELECT權限，或者存儲過程和函數的執行權限，按照最小原則開放)。

2.6 對一些常見的費時查詢進行測試。

案例一：查詢綜合業務系統中客戶名包含“三”(like‘%三%’)的所有客戶的活期存款賬號，戶名以及這些賬號在2010年2月18日時點余額。

分析：以上查詢使用到三個視圖，“客戶資料表”為生產機當前時點的客戶資料數據(在視圖中內置了條件29991231 between qsrq and jsrq)，“客戶狀況表”為生產機當前時點的活期賬號資料檔(在視圖中內置了條件29991231 between qsrq and jsrq)，“客戶余額表”為活期賬號余額檔的聯合視圖(通過20100218 between qsrq and jsrq這個條件獲得20100218時點切片數據)，可以看到查詢時間為10秒，物理讀取0次，這個結果還是在沒有對客戶資料檔的CUSNA1做索引的情況下得出的。我們目前在Oracle首先就無法進行時點切片數據查詢，即無法查詢任意時點的余額。

案例二：查詢一戶通系統客戶名含有“李”的所有客戶的一戶通卡號，戶名，賬號，子賬號以及20100209當日的余額。

分析：通過三個聯合視圖，查找20100 209的時點切片數據進行聯合查詢，獲得超過10萬條記錄，耗時17秒，沒有對PIFNAM字段建立索引。

案例三：模糊查詢一戶通系統證件號441900%760507023%的客戶的卡號，戶名，賬號，子賬號以及20100118當日的余額。

分析：同上例，從三個聯合視圖進行查詢，得到77條記錄，耗時7秒，未對PIFCER字段建立索引。

3.總結

(1)以上案例使用的是目前較為常見的查詢，而且都是模糊查詢，理論上來說應該是非常耗時的，但實際的測試表現確實很優秀。

(2)數據庫的瓶頸在于IO讀寫。1)內存越大，越能避免IO讀寫，得到的性能越高；2)Raid陣列使一組物理磁盤的讀寫條帶化，提高了IO讀寫效率；3)分區表使數據庫能夠快速屏蔽掉不需要的數據，并在多個分區進行并行查詢加快速度。以上案例的表現已超出我的預期。

當中運用的一些方法參考了現有的考核系統，提高了效率，只要EDS有數，就可以很方便的通過添加函數和存儲過程來進行加工，生成需要的數據或報表，使得對數據的分析和查詢更為方便快捷。下一步我們將對現有應用系統進行改造，逐步將原來建立在Oracle數據庫中的應用遷移到這個新的平臺上去。