1.背景環境 本文章來自最近做的項目模塊的思考和總結,主要講思路不涉及過多的基礎和實現細節。 需求:統計出來納稅人名稱、行業、近一年業務量(辦稅服務廳、電子稅務局、自助渠道),近一年業務量top5(辦稅服務廳、電子稅務局、自助渠道)、近一年納稅金額、近一年申報數、近一年用票數。支持根據所屬稅務機關分 ...
1.背景環境
本文章來自最近做的項目模塊的思考和總結,主要講思路不涉及過多的基礎和實現細節。
需求:統計出來納稅人名稱、行業、近一年業務量(辦稅服務廳、電子稅務局、自助渠道),近一年業務量top5(辦稅服務廳、電子稅務局、自助渠道)、近一年納稅金額、近一年申報數、近一年用票數。支持根據所屬稅務機關分頁查詢。
看上去業務不複雜,但是數據來自多個系統,數據量很大。來來畫個示意圖展示下數據來源的複雜程度:
數據涉及5個廠商,資料庫採用oracle,涉及幾十張表,其中納稅人信息生產環境下有380多萬,更不用說其他業務表的數據量有多大了,並且還需要分組,統計,排序。此時此刻心情如下:
2.實現方案
2.1 視圖(失敗的方案)
由於項目時間關係,想法很簡單先採用視圖,先實現再說。(其實在做的時候就有不詳的預感,感覺這種方案不行)。於是開乾,在實現的過程中我用到的
關鍵技術點有:
oracle wm_concat(column)函數實現查詢相同id欄位,內容以逗號分隔
select id, wmsys.wm_concat(欄位名)欄位別名 from table group by id
Oracle分組查詢取每組排序後的前N條記錄
SELECT *
FROM (
SELECT 分組的欄位名, ROW_NUMBER() OVER(PARTITION BY 分組的欄位名
ORDER BY 排序的欄位名) AS RN FROM 表名
)
WHERE RN <= 10 得到分組後,數據的前幾條
count、sum、group by 、join、dblink等等
生產環境下驗證結果
測試環境還好,生產環境打開視圖好久查不出來數據,臨時表空間暴增30g. 來看下現場的執行計劃
冷靜分析
- 資料庫是其他廠商的,我們沒有許可權去建索引,只給了查詢許可權。
- 大量的hash join,這些操作都在記憶體中,記憶體不足會把臨時計算結果放到磁碟導致臨時表空間暴增。
- 分組、排序類的特別耗時。
- 問題的本質也就是類似於數據結構的:時間複雜度和空間複雜度。 通俗點說你願意拿空間換時間還是時間換空間?
- 大道至簡、分而治之。當人類面臨負責問題和巨大工程的時候,都喜歡切成一小塊一小塊的去處理,問題就迎刃而解。
2.2 定時任務彙總(備選方案1)
接著上文,其實我們可以提前把數據加工好,插入彙總表,不用每次用戶查詢的時候去計算就好了。
技術實現關鍵點:
- 可以用spring +quarter 定時任務
- oracle中定時任務
以上在彙總的過程中必須註意一次拉取小批量數據加工。
由於時間緊急,定時任務需要開發代碼,數據量大,數據批次需要處理等缺點放棄了
2.3 物理視圖(選用方案2)
因為有比較多的查詢彙總,考慮到速度,最後選擇了物理視圖方案。下麵簡單介紹下物理視圖。
物化視圖也是種視圖。Oracle的物化視圖是包括一個查詢結果的資料庫對像,它是遠程數據的的本地副本,或者用來生成基於數據表求和的彙總表。物化視圖存儲基於遠程表的數據,也可以稱為快照。
物化視圖可以查詢表,視圖和其它的物化視圖。
特點:
(1) 物化視圖在某種意義上說就是一個物理表(而且不僅僅是一個物理表),這通過其可以被user_tables查詢出來,而得到確認;
(2) 物化視圖也是一種段(segment),所以其有自己的物理存儲屬性;
(3) 物化視圖會占用資料庫磁碟空間,這點從user_segment的查詢結果,可以得到佐證;
創建語句:create materialized view mv_name as select * from table_name
創建過程一波三折
把方案一種的視圖sql改稱物理視圖,到生產環境下創建。尼瑪又出狀況了
一個sql執行了8個小時,居然失敗了,怎麼辦?
冷靜分析
- 仔細看sql,去掉了不必要的關聯查詢。
- 拆分物理視圖,一個拆三,分而治之。
最後在3個小時左右,成功創建了5個物理視圖。
又出狀況、一波四折
**
測試庫是11.2.0.1.0的,WMSYS.WM_CONCAT( )函數返回的是varchar類型,而正式庫是11.2.0.4.0的,返回的是CLOB類型的。為了相容,所以解決辦法是:TO_CHAR(WMSYS.WM_CONCAT(param )); 只要用to_char()函數轉換一下就可以了。。。
好吧,重新來過,最後在3個小時左右,成功創建了5個物理視圖。
2.4 hadoop(做夢的方案,殺雞蔫用牛刀)
據說PB級別的數據,才上hadoop。為了賣弄一下我也懂點大數據技術(畢竟也讀過幾本書),簡單的列一下實現思路:
0.搭建hadoop平臺
1.sqoop導入數據到hive
2.利用hive進行分析
3.sqoop把分析結果導入Oracle彙總表
4.持續運維
為什麼不採用的原因:
1.數據量遠遠不夠
2.客戶是否給你那麼多機器來組集群。
3.公司缺乏相關技術的開發和運維,成本代價高。
3.結論
- 大道至簡、分而治之
- 思路總比問題多,不拋棄不放棄。
總結不易歡迎在看或轉發,更多精彩關註微信公眾號【lovepythoncn】