從系統報表頁面匯出20w條資料到本地只用了4秒，我是如何做到的

鉑賽東發表於2020-08-11

背景

最近有個學弟找到我，跟我描述了以下場景：

他們公司內部管理系統上有很多報表，報表資料都有分頁顯示，瀏覽的時候速度還可以。但是每個報表在匯出時間視窗稍微大一點的資料時，就異常緩慢，有時候多人一起匯出時還會出現堆溢位。

他知道是因為資料全部載入到jvm記憶體導致的堆溢位。所以只能對時間視窗做了限制。以避免因匯出過資料過大而引起的堆溢位。最終拍腦袋定下個限制為：匯出的資料時間視窗不能超過1個月。

雖然問題解決了，但是運營小姐姐不開心了，跑過來和學弟說，我要匯出一年的資料，難道要我匯出12次再手工合併起來嗎。學弟心想，這也是。系統是為人服務的，不能為了解決問題而改變其本質。

所以他想問我的問題是：有沒有什麼辦法可以從根本上解決這個問題。

所謂從根本上解決這個問題，他提出要達成2個條件

  • 比較快的匯出速度
  • 多人能並行下載資料集較大的資料

我聽完他的問題後，我想，他的這個問題估計很多其他童鞋在做web頁匯出資料的時候也肯定碰到過。很多人為了保持系統的穩定性，一般在匯出資料時都對匯出條數或者時間視窗作了限制。但需求方肯定更希望一次性匯出任意條件的資料集。

魚和熊掌能否兼得？

答案是可以的。

我堅定的和學弟說，大概7年前我做過一個下載中心的方案，20w資料的匯出大概4秒吧。。。支援多人同時線上匯出。。。

學弟聽完表情有些興奮，但是眉頭又一皺，說，能有這麼快，20w資料4秒？

為了給他做例子，我翻出了7年前的程式碼。。。花了一個晚上把核心程式碼抽出來，剝離乾淨，做成了一個下載中心的例子

超快下載方案演示

先不談技術，先看效果，(完整案例程式碼文末提供)

資料庫為mysql（理論上此套方案支援任何結構化資料庫），準備一張測試表t_person。表結構如下：

CREATE TABLE `t_person` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `age` int(11) default NULL,
  `address` varchar(50) default NULL,
  `mobile` varchar(20) default NULL,
  `email` varchar(50) default NULL,
  `company` varchar(50) default NULL,
  `title` varchar(50) default NULL,
  `create_time` datetime default NULL,
  PRIMARY KEY  (`id`)
);

一共9個欄位。我們先建立測試資料。

案例程式碼提供了一個簡單的頁面，點以下按鈕一次性可以建立5w條測試資料：

file

這裡我連續點了4下，很快就生成了20w條資料，這裡為了展示下資料的大致樣子，我直接跳轉到了最後一頁

file

然後點開下載大容量檔案，點選執行執行按鈕，開始下載t_person這張表裡的全部資料

file

點選執行按鈕之後，點下方重新整理按鈕，可以看到一條非同步下載記錄，狀態是P，表示pending狀態，不停重新整理重新整理按鈕，大概幾秒後，這一條記錄就變成S狀態了，表示Success

file

然後你就可以下載到本地，檔案大小大概31M左右

file

看到這裡，很多童鞋要疑惑了，這下載下來是csv？csv其實是文字檔案，用excel開啟會丟失格式和精度。這解決不了問題啊，我們要excel格式啊！！

其實稍微會一點excel技巧的童鞋，可以利用excel匯入資料這個功能，資料->匯入資料，根據提示一步步，當中只要選擇逗號分隔就可以了，關鍵列可以定義格式，10秒就能完成資料的匯入

file

你只要告訴運營小姐姐，根據這個步驟來完成excel的匯入就可以了。而且下載過的檔案，還可以反覆下。

是不是從本質上解決了下載大容量資料集的問題？

原理和核心程式碼

學弟聽到這裡，很興奮的說，這套方案能解決我這裡的痛點。快和我說說原理。

其實這套方案核心很簡單，只源於一個知識點，活用JdbcTemplate的這個介面：

@Override
public void query(String sql, @Nullable Object[] args, RowCallbackHandler rch) throws DataAccessException {
  query(sql, newArgPreparedStatementSetter(args), rch);
}

sql就是select * from t_person，RowCallbackHandler這個回撥介面是指每一條資料遍歷後要執行的回撥函式。現在貼出我自己的RowCallbackHandler的實現

private class CsvRowCallbackHandler implements RowCallbackHandler{

    private PrintWriter pw;

    public CsvRowCallbackHandler(PrintWriter pw){
        this.pw = pw;
    }

    public void processRow(ResultSet rs) throws SQLException {
        if (rs.isFirst()){
            rs.setFetchSize(500);
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++){
                if (i == rs.getMetaData().getColumnCount() - 1){
                    this.writeToFile(pw, rs.getMetaData().getColumnName(i+1), true);
                }else{
                    this.writeToFile(pw, rs.getMetaData().getColumnName(i+1), false);
                }
            }
        }else{
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++){
                if (i == rs.getMetaData().getColumnCount() - 1){
                    this.writeToFile(pw, rs.getObject(i+1), true);
                }else{
                    this.writeToFile(pw, rs.getObject(i+1), false);
                }
            }
        }
        pw.println();
    }

    private void writeToFile(PrintWriter pw, Object valueObj, boolean isLineEnd){
        ...
    }
}

這個CsvRowCallbackHandler做的事就是每次從資料庫取出500條，然後寫入伺服器上的本地檔案中，這樣，無論你這條sql查出來是20w條還是100w條，記憶體理論上只佔用500條資料的儲存空間。等檔案寫完了，我們要做的，只是從伺服器把這個生成好的檔案download到本地就可以了。

因為記憶體中不斷重新整理的只有500條資料的容量，所以，即便多執行緒下載的環境下。記憶體也不會因此而溢位。這樣，完美解決了多人下載的場景。

當然，太多並行下載雖然不會對記憶體造成溢位，但是會大量佔用IO資源。為此，我們還是要控制下多執行緒並行的數量，可以用執行緒池來提交作業

ExecutorService threadPool = Executors.newFixedThreadPool(5);

threadPool.submit(new Thread(){
    @Override
    public void run() {
    下載大資料集程式碼
  }
}

最後測試了下50w這樣子的person資料的下載，大概耗時9秒，100w的person資料，耗時19秒。這樣子的下載效率，應該可以滿足大部分公司的報表匯出需求吧。

最後

學弟拿到我的示例程式碼後，經過一個禮拜的修改後，上線了頁面匯出的新版本，所有的報表提交非同步作業，大家統一到下載中心去進行檢視和下載檔案。完美的解決了之前的2個痛點。

但最後學弟還有個疑問，為什麼不可以直接生成excel呢。也就是說在在RowCallbackHandler中持續往excel裡寫入資料呢？

我的回答是：

1.文字檔案流寫入比較快

2.excel檔案格式好像不支援流持續寫入，反正我是沒有試成功過。

我把剝離出來的案例整理了下，無償提供給大家，希望幫助到碰到類似場景的童鞋們。

關注作者

關注公眾號「元人部落」回覆”匯出案例“獲得以上完整的案例程式碼，直接可以執行起來，頁面上輸入http://127.0.0.1:8080就可以開啟文中案例的模擬頁面。

一個只做原創的技術科技分享號

file

相關文章