問題描述

C保險公司業務系統中，團體保險明細查詢速度很慢。查詢時輸入保單號，要返回團體保單包含的所有被保險人的信息。較小的保單，包含1萬個被保險人，返回頁面需要等待7.5分鐘。較大的保單，包含100萬被保險人，返回頁面等待了4個小時沒有出來。

團體保險明細比較大，分兩個數據庫保存。每個團體保單的數據，在兩個庫中都有可能出現。數據庫是Oracle，SQL語句共163行，如下圖：

分析解決

面對性能問題，需要仔細分析數據和計算的特征，定位性能關鍵點，通過改變數據的存儲方式和計算方法逐步優化。

第一步，確認需求前提。團體保險明細查詢是應用系統中的一個功能，需要查詢最新數據。如果采用ETL定時將數據導出計算的方式，不能滿足這個要求。因此，還是要想辦法從數據庫取數、庫外計算，來優化性能。

第二步，了解業務需求特征。團體保險明細數據存放在兩個數據庫db1、db2，每個數據庫都有兩個表m1、m2。這四個表在查詢時要合并查詢結果，我們統一稱為團體保險明細表。

四個團體保險明細表有所不同，但是都可以查詢出主要字段：保單號、保險成員號、批改次數、業務編號1、業務編號2、業務標志，還有姓名、性別年齡等個人信息

“批改”是針對保險合同的調整，系統將調整后的最新保險明細也保存在團體保險明細表中，不會修改原保險明細，保留軌跡。在數據中通過“批改次數”字段體現。查詢時，要查詢批改次數最大的一次，也就是最新的數據。

明細數據中還有一部分是無效數據。要看業務編號1和業務標志連接成的字符串是否在無效集合中。無效集合是指：同一個保單號的數據中，批改次數小于9，并且業務標志為D或者U時，業務編號2和字母A連接成的字符串形成的集合。如果業務編號1和業務標志連接成的字符串出現在無效集合中，這條記錄就是無效的記錄，要舍棄掉。

第三步，梳理研究計算過程。SQL雖然比較長，但是可以分成幾個部分。第一部分是兩個數據庫的4個團體保險明細表，各自按照保單號查詢需要的數據，再用union合并在一起。第二部分是條件過濾，包括去掉無效數據和另外幾個簡單的條件。第三部分是用窗口函數row_number() OVER(PARTITION BY 保險成員號 ORDER BY 批改次數 desc)，查找批改次數最大的明細記錄。

第一部分單獨執行時，返回的結果數據量是幾萬到幾百萬，全部返回的時間比較長。如果用數據庫JDBC游標的話，很快就能返回部分數據，比如幾秒就可以返回幾千條。

第二部分，單獨從數據庫中取得無效集合只需要幾秒，而且返回結果數據量不大，可以全內存。

但是，第一部分和第二部分合并執行的時候，速度就變得很慢，即使是游標方式分批返回，也還是很慢。如果再加上第三部分，就更慢了。

第四步，設計呈現方案。根據SQL分段執行的情況，確定采用流式大報表的方式實現提速，原理如下圖：

從數據庫取數和呈現采用兩個異步線程，取數線程發出 SQL 后不斷取出數據經過復雜計算后，緩存到本地。再由呈現線程從本地緩存中獲取數據進行顯示。這樣，已經取出并緩存的數據就能快速呈現，不再有等待感。

第五步，設計計算過程優化方案。我們考慮將取數和計算分三段實現。

第一段，上面說的第一部分SQL加上按照保險成員號和批改次數降序排序之后，用數據庫JDBC游標依然能夠快速分批取出部分數據。加上排序，可以在分批取出數據時，保證一個保險成員的數據相鄰取出，在后續第三段中，就能夠快速找到批改次數最大的最新數據。

第二段，我們將這個保單的無效集合一次性取出到內存中，對第一段分批取出的數據進行過濾，計算出符合條件的有效明細。無效數據并不多，不會過濾掉太多的明細數據。

第三段，根據被保險人號是否改變，判斷是不是一個被保險人的第一條數據。因為明細數據按照被保險人和批改次數有序，所以當被保險人號改變的時候，第一條數據就是當前被保險人批改次數的最大值。這樣就起到了，和上面說到的窗口函數一樣的作用。

由于每個保險成員的數據量都不大，一般是最多十幾條數據（對應幾次到十幾次批改），而且無效數據并不多。所以第一部分分批取出的數據量不需要很多，就可以向前端批量返回數據了。這是流式大報表能夠快速展現的必要條件。

第六步，設計代碼實現方案。使用延遲游標的方法實現上述三個分段。延遲游標的原理是，先依次定義三個分段的游標計算，定義的時候并不真的執行計算，而是在三個分段都定義好之后再執行。延遲計算的好處是可以一次遍歷完成三個分段計算，不必生成中間結果占用空間，可以把查詢結果分批提交給前端去展現。

第三段游標計算比較復雜，需要用程序游標來實現。原理如下圖：

程序游標要做到被調用的時候，邊計算邊返回結果，這樣才能達到流式大報表的要求。

實際效果

根據計算特征擬定了優化方案后，需要選擇合適的工具來實現計算和展現的性能優化。直接使用Java當然可以實現，但編碼量過大，實現周期過長，容易出現代碼錯誤隱患，也很難調試和維護。而開源的集算器SPL語言提供上述所有的算法支持，包括延遲游標、游標有序分段取出、程序游標等機制，能夠讓我們用較少的代碼量快速實現這種個性化的計算。前端呈現需要支持流式大報表機制的報表工具，我們選擇了潤乾報表來實現。

僅僅經過1天時間的編程、調試和測試，就完成了性能優化的驗證，而且查詢的響應速度非常快。較小的保單，包含1萬個被保險人，原來返回頁面需要等待7.5分鐘，優化后的報表首頁只需要3秒即可展現出來。較大的保單，包含100萬被保險人，原來返回頁面等待了4個小時沒有出來，優化后的報表首頁僅7秒即可展現出來，響應速度提高了2000倍還多。

在編程難度方面，SPL做了大量封裝，提供了豐富的函數，內置了上述優化方案需要的基本算法和存儲機制。實際編寫的代碼很短，開發效率很高。上述取數的三段代碼只有這么幾行：

后記

解決性能優化難題，最重要的是設計出高性能的計算方案，有效降低計算復雜度，最終把速度提上去。因此，一方面要充分理解計算和數據的特征，另一方面也要熟知常見的高性能算法，才能因地制宜地設計出合理的優化方案。本次工作中用到的基本高性能算法，都可以從下面這門課程中找到：點擊這里學習性能優化課程（底部原文中可點擊鏈接），有興趣的同學可以參考。

很遺憾的是，當前業界主流大數據體系仍以關系數據庫為基礎，無論是傳統的MPP還是HADOOP體系以及新的一些技術，都在努力將編程接口向SQL靠攏。兼容SQL確實能讓用戶更容易上手，但受制于理論限制的SQL卻無法實現大多數高性能算法，眼睜睜地看著硬件資源被浪費，還沒有辦法改進。SQL不應是大數據計算的未來。

有了優化方案后，還要用好的程序語言來高效地實現這個算法。雖然常見的高級語言能夠實現大多數優化算法，但代碼過于冗長，開發效率過低，會嚴重影響程序的可維護性。開源SPL是個很好的選擇，它有足夠的算法底層支持，代碼能做到很簡潔，還提供了友好的可視化調試機制，能有效提高開發效率，以及降低維護成本。

對于本例中的報表呈現，還需要有能支持流式呈現的報表工具，這方面潤乾報表有獨特的優勢，不需要全部取出數據就可以開始呈現，也不依賴于數據庫分頁機制（這種方法可能造成數據不一致）就可以支持高速前后翻頁。這樣才能獲得業務用戶的良好體驗。