在這里插入圖片描述

概述

線上系統(tǒng)出現(xiàn)文件無法成功導出，或者導出導致虛擬機崩潰等情況隙笆。為保證系統(tǒng)穩(wěn)定和功能正常，需對導出功能做一輪整體優(yōu)化撑柔，以及整理一些可進一步優(yōu)化的點。
初始導出流程如下

在這里插入圖片描述

原因分析

1剪决、業(yè)務數(shù)據(jù)處理異常
出現(xiàn)比較少，測試的正常操作難以提前發(fā)現(xiàn)昼捍，系統(tǒng)運行過程中，產(chǎn)生了特定數(shù)據(jù)可能就會出現(xiàn)的bug
2肢扯、數(shù)據(jù)量大導致內(nèi)存溢出
目前系統(tǒng)最大數(shù)據(jù)量導出為單表3百萬行妒茬，60列蔚晨，全部加載到內(nèi)存中極易導致OOM
3、并發(fā)操作導致內(nèi)存溢出
與2中類似铭腕，實質還是數(shù)據(jù)量大的問題。由于是并行處理累舷，因此同時存在CPU瓶頸問題。

解決方案

針對上述問題被盈，在java應用層做了一些優(yōu)化措施。對于業(yè)務處理異常只怎，跟蹤log能夠比較快速的定位問題和解決。本質還是數(shù)據(jù)的輸入規(guī)范問題身堡，由于產(chǎn)生的數(shù)據(jù)不符合預期而導致的bug，可適當增加數(shù)據(jù)輸入校驗，或數(shù)據(jù)庫表字段約束汞扎。在此不詳談。主要討論大數(shù)據(jù)量和并發(fā)導致的問題解決澈魄。
直接的原因是虛擬機堆無剩余空間分配給程序即將加載的全部數(shù)據(jù)
具體措施：
方案1：物理機內(nèi)存足夠的情況下，可適當調大最大虛擬機堆空間一忱，如增加啟動參數(shù)-Xmx100G
優(yōu)點：操作簡單直接谭确，在最大數(shù)據(jù)有一定預期的時候能夠應付大部分情況。
缺點：對物理機配置要求較高逐哈，超過虛擬堆最大值的數(shù)據(jù)量依然無法處理
方案2：數(shù)據(jù)化整為零，分批處理
優(yōu)點：無論多大數(shù)量級數(shù)據(jù)都可以處理
缺點：需要對整個導出流程的步驟進行調整適配昂秃，存在一定復雜度
綜合考慮，選擇方案2肠骆，因此以數(shù)據(jù)流向來對整個流程進行梳理優(yōu)化

流程環(huán)節(jié)改進

數(shù)據(jù)庫到應用程序

有兩種策略：

• 分頁，主要采用limit方式
• 流式處理

考慮到分頁在數(shù)據(jù)量較大時蚀腿，后續(xù)分頁查詢較慢，舍棄莉钙，選擇流式處理
如下使用的是應用層jdbcTemplate的流式處理方案，對結果集的逐行處理磁玉，避免內(nèi)存溢出

public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException {
  query(psc, new RowCallbackHandlerResultSetExtractor(rch));
}

mybatis同樣可以實現(xiàn)，但由于目前使用的系統(tǒng)使用mybatis版本較低不支持蚊伞，而升級代價較大，暫未修改

應用程序到文件

文件類型

• xls是Microsoft Excel2007前excel的文件存儲格式时迫，實現(xiàn)原理是基于微軟的ole db是微軟com組件的一種實現(xiàn)，本質上也是一個微型數(shù)據(jù)庫别垮，由于微軟的東西很多不開源，另外也已經(jīng)被淘汰，了解它的細節(jié)意義不大毁靶，底層的編程都是基于微軟的com組件去開發(fā)的。
• xlsx是Microsoft Excel2007后excel的文件存儲格式预吆，實現(xiàn)是基于openXml和zip技術。這種存儲簡單拐叉，安全傳輸方便，同時處理數(shù)據(jù)也變的簡單凤瘦。
• csv 我們可以理解為純文本文件，可以被excel打開蔬芥。他的格式非常簡單，解析起來和解析文本文件一樣笔诵。

一般業(yè)務數(shù)據(jù)導出選擇xlsx
excel的sheet存在一個行數(shù)上限值，超過該值的數(shù)據(jù)需要分sheet甚至分不同excel文件導出
.xls格式excel建議：每個sheet寫入60000條數(shù)據(jù)乎婿，每個excel寫入300000條數(shù)據(jù)街佑，即5個sheet

private void updateContext(EasyExportContext context) {
  int fileIdx = context.rowIdx/MAX_PER_FILE;
  int sheetIdx = (context.rowIdx%MAX_PER_FILE)/MAX_PER_SHEET;
  if (fileIdx > context.fileIdx) {
    context.excelWriter.finish();
    String fileName = context.fileNameOrg+"_"+(fileIdx+1)+".xls";
    context.fileList.add(fileName);
    context.excelWriter = EasyExcel.write(WebConstant.THREAD_TOB_EXPORT_URL + fileName, context.clazz).build();
    context.writeSheet = EasyExcel.writerSheet(0, "" + 0).build();
    context.fileIdx = fileIdx;
    context.sheetIdx = 0;
  } else if (sheetIdx > context.sheetIdx) {
    context.writeSheet = EasyExcel.writerSheet(sheetIdx, "" + sheetIdx).build();
    context.sheetIdx = sheetIdx;
  }
}

poi

poi是java操作excel的一個主要工具庫，并在版本更新中做了許多優(yōu)化舆乔，如xlsx底層使用xml存儲，占用內(nèi)存會比較大希俩，在3.8版本之后，提供了SXSSFWorkbook來優(yōu)化寫性能颜武。其原理是可以定義一個window size（默認100），生成Excel期間只在內(nèi)存維持window size那么多的行數(shù)Row鳞上，超時window size時會把之前行Row寫到一個臨時文件并且remove釋放掉，這樣就可以達到釋放內(nèi)存的效果篙议。 SXSSFSheet在創(chuàng)建Row時會判斷并刷盤怠硼、釋放超過window size的Row移怯。
POI沒有像XLSX那樣對XLS的寫做出性能的優(yōu)化香璃，原因是：

1. 官方認為XLS的不像XLSX那樣占內(nèi)存
2. XLS一個Sheet最多也只能有65535行數(shù)據(jù)

POI對導入分為3種模式舟误，用戶模式User Model，事件模式Event Model嵌溢，還有Event User Model。

• 用戶模式（User Model）就類似于dom方式的解析赖草，是一種high level api，給人快速疚顷、方便開發(fā)用的禁偎。缺點是一次性將文件讀入內(nèi)存腿堤，構建一顆Dom樹如暖。并且在POI對Excel的抽象中，每一行盒至，每一個單元格都是一個對象。當文件大枷遂，數(shù)據(jù)量多的時候對內(nèi)存的占用可想而知。 用戶模式就是類似用 WorkbookFactory.create(inputStream)酒唉，poi 會把整個文件一次性解析，生成全部的Sheet痪伦，Row，Cell以及對象网沾，如果導入文件數(shù)據(jù)量大的話，也很可能會導致OOM辉哥。
• 事件模式（Event Model）就是SAX解析。Event Model使用的方式是邊讀取邊解析，并且不會將這些數(shù)據(jù)封裝成Row匆篓，Cell這樣的對象。而都只是普通的數(shù)字或者是字符串鸦概。并且這些解析出來的對象是不需要一直駐留在內(nèi)存中，而是解析完使用后就可以回收窗市。所以相比于User Model，Event Model更節(jié)省內(nèi)存咨察，效率也更。但是作為代價福青，相比User Model功能更少，門檻也要高一些无午。我們需要去學習Excel存儲數(shù)據(jù)的各個Xml中每個標簽，標簽中的屬性的含義酣衷，然后對解析代碼進行設計次泽。
• User Event Model也是采用流式解析，但是不同于Event Model意荤，POI基于Event Model為我們封裝了一層。我們不再面對Element的事件編程,而是面向StartRow玖像，EndRow，Cell等事件編程御铃。而提供的數(shù)據(jù)，也不再像之前是原始數(shù)據(jù)上真，而是全部格式化好，方便開發(fā)者開箱即用睡互。大大簡化了我們的開發(fā)效率陵像。

easyexcel

EasyExcel是阿里巴巴開源的庫寇壳，底層基于poi醒颖，主要解決了poi框架使用復雜壳炎，sax解析模式不容易操作，數(shù)據(jù)量大起來容易OOM匿辩，解決了POI并發(fā)的bug。主要解決方式：通過解壓文件的方式加載铲球，一行一行的加載，并且拋棄樣式字體等不重要的數(shù)據(jù)稼病，降低內(nèi)存的占用。
EasyExcel優(yōu)勢

• 注解式自定義操作然走。
• 輸入輸出簡單，提供輸入輸出過程的接口
• 支持一定程度的單元格合并等靈活化操作

excel與類映射丰刊，靠注解實現(xiàn)

@Data
@ColumnWidth(25)
public class CardExport {
    
    @ExcelProperty("編號")
    private String id;

    @ExcelProperty("接入電話")
    private String phone;

    @ExcelProperty("ICCID")
    private String iccidMark;

    @ExcelProperty("IMEI")
    private String imei;

    @ExcelProperty("開卡公司")
    private String accountName;
    
}

• @ExcelProperty 指定當前字段對應excel中的那一列增拥。可以根據(jù)名字或者Index去匹配掌栅。當然也可以不寫，默認第一個字段就是index=0猾封，以此類推。千萬注意晌缘，要么全部不寫，要么全部用index磷箕，要么全部用名字去匹配。千萬別三個混著用岳枷，除非你非常了解源代碼中三個混著用怎么去排序的呜叫。
• @ExcelIgnore 默認所有字段都會和excel去匹配，加了這個注解會忽略該字段
• @DateTimeFormat 日期轉換朱庆，用String去接收excel日期格式的數(shù)據(jù)會調用這個注解。里面的value參照java.text.SimpleDateFormat
• @NumberFormat 數(shù)字轉換娱颊，用String去接收excel數(shù)字格式的數(shù)據(jù)會調用這個注解。里面的value參照java.text.DecimalFormat
• @ExcelIgnoreUnannotated默認不加ExcelProperty 的注解的都會參與讀寫,加了不會參與

讀Excel

/**
 * 最簡單的讀
 * <p>1. 創(chuàng)建excel對應的實體對象 參照{@link DemoData}
 * <p>2. 由于默認一行行的讀取excel维蒙，所以需要創(chuàng)建excel一行一行的回調監(jiān)聽器果覆，參照{@link DemoDataListener}
 * <p>3. 直接讀即可
 */
@Test
public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 這里 需要指定讀用哪個class去讀颅痊，然后讀取第一個sheet 文件流會自動關閉
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

寫Excel

/**
 * 最簡單的寫
 * <p>1. 創(chuàng)建excel對應的實體對象 參照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
 * <p>2. 直接寫即可
 */
@Test
public void simpleWrite() {
    String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
    // 這里 需要指定寫用哪個class去讀局待，然后寫到第一個sheet，名字為模板 然后文件流會自動關閉
    // 如果這里想使用03 則 傳入excelType參數(shù)即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

web上傳钳榨、下載

/**
* 文件下載（失敗了會返回一個有部分數(shù)據(jù)的Excel）
* 1. 創(chuàng)建excel對應的實體對象 參照{@link  DownloadData} 
* 2. 設置返回的 參數(shù)
* 3. 直接寫，這里注意薛耻，finish的時候會自動關閉OutputStream,當然你外面再關閉流問題不大
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 這里URLEncoder.encode可以防止中文亂碼
    String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
}

/**
 * 文件上傳
 * <p>1. 創(chuàng)建excel對應的實體對象 參照{@link UploadData}
 * <p>2. 由于默認一行行的讀取excel，所以需要創(chuàng)建excel一行一行的回調監(jiān)聽器饼齿，參照{@link UploadDataListener}
 * <p>3. 直接讀即可
 */
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
    return "success";
}

文件到客戶端

應用程序讀取文件，傳輸給用戶缕溉，為流式傳輸，不會占用過多內(nèi)存证鸥，一般不會導致OOM

體驗優(yōu)化

當一個操作需要很長時間響應時，體驗會很差枉层，導出也是如此，下面討論如何避免用戶長時間的等待鸟蜡。主要從兩個方面：縮短時間、異步導出

縮短時間

要縮短時間就要分析在哪一塊花費的時間過長矩欠，做針對性的優(yōu)化悠夯，通常瓶頸在下面兩個地方

1. 數(shù)據(jù)庫sql查詢慢
2. 網(wǎng)絡傳輸慢

其他如內(nèi)存不夠頻繁gc躺坟，cpu性能不足等目前看相較之下影響比較小沦补，暫不考慮咪橙。
數(shù)據(jù)庫層面的優(yōu)化也就是sql調優(yōu)和庫表結構優(yōu)化，sql一般需要走索引美侦，不需要的字段不要查。
網(wǎng)絡層面優(yōu)化菠剩，通過公式
數(shù)據(jù)量/帶寬=傳輸時間
可知：減小數(shù)據(jù)量，增大帶寬即可縮短傳輸時間
帶寬優(yōu)化增強：
1具壮、數(shù)據(jù)流轉盡量全部在局域網(wǎng)之中
2、使用OSS等云產(chǎn)品提供給用戶下載導出文件
減小數(shù)據(jù)量：
文件壓縮后傳輸棺妓，用戶側解壓

異步導出

即在后臺靜默導出，導出完成通知給客戶怜跑，如下。

優(yōu)化后架構：

在這里插入圖片描述

文件壓縮

if (context.fileList.size() > 1) {
    //壓縮文件
    String zipName = DateUtil.getYMDHMSFormatter()+"_"+(int)((Math.random()*9+1)*100000)+".zip";
    List<File> srcFiles = new ArrayList<>();
    for (String name : context.fileList) {
        srcFiles.add(new File(WebConstant.THREAD_TOB_EXPORT_URL + name));
    }
    logger.info("{}開始壓縮", taskId);
    long startTime = System.currentTimeMillis();

    try {
        ZipUtil.zip(srcFiles, new File(WebConstant.THREAD_TOB_EXPORT_URL + zipName));
    } catch (Exception e) {
        logger.error("{}壓縮異常", taskId);
        throw new RuntimeException(e);
    }
    long endTime = System.currentTimeMillis();
    logger.info("{}結束壓縮,耗時：{}s", taskId, (endTime-startTime)/1000);
    //刪除多余文件
    for (String name : context.fileList) {
        new File(WebConstant.THREAD_TOB_EXPORT_URL + name).delete();
    }
    msg.setFileName(zipName);
}

上傳OSS

public static String localFirstUpload(String path, String fileName, String bucket, boolean isInternalNet) throws Exception {
    // 創(chuàng)建OSSClient實例峡眶。
    if (localFirstClient == null) {
        String endPoint = END_POINT;
        if (isInternalNet) {
            endPoint = LOCAL_END_POINT;
        }
        localFirstClient = new OSSClientBuilder().build(endPoint, ACCESS_KEY, SECRET_KEY);
    }
    InputStream inputStream = new FileInputStream(path);
    // 依次填寫B(tài)ucket名稱（例如examplebucket）和Object完整路徑（例如exampledir/exampleobject.txt）。Object完整路徑中不能包含Bucket名稱幌陕。
    localFirstClient.putObject(bucket, fileName, inputStream);

    return "https://"+bucket+".oss-cn-hangzhou.aliyuncs.com/"+fileName;
}

解決并發(fā)問題

1诵姜、異步
增加任務狀態(tài)表，處理完成時更新棚唆，并提供下載地址
2、排隊
將后來的導出任務線程阻塞宵凌，并控制并發(fā)量，等待優(yōu)先任務處理完瞎惫，再處理后續(xù)任務译株，目前是通過Semaphore實現(xiàn)最大并行導出數(shù)量

private static final Semaphore LIMIT_THREAD = new Semaphore(TASK_NUM);
public void doExport() {
    try {
        LIMIT_THREAD.acquire();
    } catch (InterruptedException e) {
        throw new RuntimeException("導出人數(shù)過多挺益，請稍后再試");
    }
    try {
        //處理業(yè)務代碼
    } catch (RuntimeException e) {
        throw e;
    } finally {
        LIMIT_THREAD.release();
    }
}

其他建議：增加導出進度狀態(tài)歉糜、完全異步導出
優(yōu)點：
無需占用線程資源
可檢測重復導出望众，重復導出只導一次
系統(tǒng)重啟后可恢復

其他導出方式

前端生成excel

sheetJs介紹
讀取：

function handleFile(e) {
  var files = e.target.files, f = files[0];
  var reader = new FileReader();
  reader.onload = function(e) {
    var workbook = XLSX.read(e.target.result);

    /* DO SOMETHING WITH workbook HERE */
  };
  reader.readAsArrayBuffer(f);
}
input_dom_element.addEventListener('change', handleFile, false);

XLSX.utils.sheet_to_json(ws);

下載：

/* bookType can be any supported output type */
var wopts = { bookType:'xlsx', bookSST:false, type:'array' };

var wbout = XLSX.write(workbook,wopts);

/* the saveAs call downloads a file on the local machine */
saveAs(new Blob([wbout],{type:"application/octet-stream"}), "test.xlsx");

前端處理架構

在這里插入圖片描述

優(yōu)點：
后端無需額外提供導出接口烂翰，直接復用列表查詢接口
可直接使用前端編碼映射
部分性能消耗轉移到客戶端，減少服務器壓力

數(shù)據(jù)庫工具導出

在這里插入圖片描述

navicat

在這里插入圖片描述

mysqldump

mysqldump 命令是數(shù)據(jù)庫導出中使用最頻繁的一個工具踊兜，它可將數(shù)據(jù)庫中的數(shù)據(jù)備份成已 *.sql 結尾的文本文件，表結構和數(shù)據(jù)都會存儲在其中润文。mysqldump 命令的原理也很簡單，它先把需要備份的表結構查詢出來典蝌，然后生成一個 CREATE TABLE 'table' 語句，最后將表中所有記錄轉化成一條INSERT語句骏掀。可以把它理解為一個批量導出導入腳本截驮。數(shù)據(jù)導入時，按照規(guī)范語句導入數(shù)據(jù)际度，大幅減少奇怪的未知錯誤出現(xiàn)。
mysqldump 的基本命令：

$ mysqldump -u username -p database_name > data-dump.sql

• username 是數(shù)據(jù)庫的登錄名
• database_name 是需要導出的數(shù)據(jù)庫名稱
• data-dump.sql 是文件輸出目錄的文件
  輸入數(shù)據(jù)庫賬號的密碼執(zhí)行命令乖菱，如果執(zhí)行過程中，沒有任何錯誤窒所，那么命令行不會有任何輸出。

mysqldump 也可以分表備份吵取，比較常見的場景有

# 備份單個庫
$ mysqldump -uroot -p -R -E --single-transactio --databases [database_one] > database_one.sql
# 備份部分表
$ mysqldump -uroot -p --single-transaction [database_one] [table_one] [table_two] > database_table12.sql
# 排除某些表
$ mysqldump -uroot -p [database_one] --ignore-table=[database_one.table_one] --ignore-table=[database_one.table_two] > database_one.sql
# 只備份結構
$ mysqldump -uroot -p [database_one] --no-data > [database_one.defs].sql
# 只備份數(shù)據(jù)
$ mysqldump -uroot -p [database_one] --no-create-info > [database_one.data].sql

into outfile

使用 into outfile 命令導出 MySQL數(shù)據(jù)至 CSV / Excel

select * from users into outfile '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',';

FIELDS TERMINATED BY ',' 表示數(shù)據(jù)以 ','進行分隔。
導出后會顯示成功提示脯倒，CD 到導出目錄可看到 CSV 文件已導出实辑。

提示：into outfile 常見報錯

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

這是因為MySQL 配置了--secure-file-priv 限制了導出文件的存放位置藻丢♂悴ぃ可以使用以下命令來查看具體配置信息

show global variables like '%secure_file_priv%';

secure_file_priv 為 NULL 時，表示不允許導入或導出婿奔。 secure_file_priv 為路徑時（/var/lib/mysql-files/ ）時，表示只允許在路徑目錄中執(zhí)行问慎。 secure_file_priv 沒有值時，表示可在任意目錄的導入導出如叼。可以 my.cnf 或 my.ini配置以下語句笼恰，重啟 MySQL server 即可

secure_file_priv=''

優(yōu)點：
簡潔，直接
脫離原系統(tǒng)社证，與業(yè)務系統(tǒng)耦合小
缺點：
如果業(yè)務人員不熟悉sql和數(shù)據(jù)庫結構及編碼，就會大大增加開發(fā)人員工作量