Easy Excel 高效導(dǎo)出 30w 數(shù)據(jù)至 Excel 文件的實戰(zhàn)指南

作者：寫代碼的SharkChili 2025-02-12 07:27:52

本文的需求是將一個百萬數(shù)據(jù)量MySQL8的數(shù)據(jù)導(dǎo)出到excel的功能，經(jīng)查閱資料并結(jié)合實際場景需求整理出這樣一套比較精簡且使用的導(dǎo)出方案。

最近easy excel停止維護，筆者也看到自己早期寫的關(guān)于基于easy excel導(dǎo)出的文章，于是打算重新整理梳理一下，當初對于這個工具類的使用技巧。

文章主脈絡(luò)如下，筆者會通過一個簡單的需求案例提出設(shè)計思路和需要解決的問題點，然后結(jié)合一段完整的代碼示例供讀者參考，從而幫助讀者更好的解決這類問題。

一、簡述需求

為了更好演示筆者的方案，這里給出一個演示的需求，該需求是要求導(dǎo)出一個用戶表的數(shù)據(jù)，該數(shù)據(jù)表是一張用戶表，包含id和name，該用戶表數(shù)據(jù)量在300w左右，以自增id作為主鍵，而功能要求我們在一分鐘之內(nèi)完成百萬數(shù)據(jù)導(dǎo)出到excel。需要注意的是，我們導(dǎo)出的excel格式為xlsx，它的每一個sheet只能容納100w的數(shù)據(jù)，這也就意味著我們的數(shù)據(jù)必須以100w作為批次寫到不同的sheet中。

CREATE TABLE `t_user` (
  `id` bigint NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `count` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

二、設(shè)計思路

1. 三個問題

我們先來說說需要解決的問題:

如果一次性查詢300w左右的數(shù)據(jù)可能會占據(jù)大量的內(nèi)存，如果對象字段很多的情況下，很可能出現(xiàn)內(nèi)存溢出，我們要如何解決？
每個excel文件都有sheet，并且每個sheet只能容納100w左右的數(shù)據(jù)，對于這個問題我們要如何解決？
數(shù)據(jù)寫入到excel時，有沒有合適的工具推薦？

2. 解決方案

(1) 解決大表查詢問題

對于問題1我們兩套解決方案：方案1是采用分頁查詢的方式進行查詢，參考自己堆內(nèi)存的配置推算每次分頁查詢的數(shù)據(jù)量。因為問題1采用了分頁查詢，我們完全可以通過分頁查詢的次數(shù)推算出一個sheet寫入了多少數(shù)據(jù)，例如我們每次分頁查詢50w的數(shù)據(jù)，那么每兩次就可以視為一個sheet寫滿了，我們就可以創(chuàng)建一個新的sheet寫入數(shù)據(jù)。

這里需要注意一點，因為我們分頁查詢面對的是百萬級別的數(shù)據(jù)，所以隨著分頁的推進勢必出現(xiàn)深分頁導(dǎo)致查詢效率勢降低，所以為了提高分頁查詢的效率，我們可以利用查詢數(shù)據(jù)有序的特性，通過id作為偏移進行分頁查詢。

例如我們第一次分頁查詢的sql語句為:

select * from t_user limit 500000 ;

假如我們不以id作為索引，那么第二次的分頁查詢sql則是:

select * from t_user limit 500000,500000 ;

查看該查詢執(zhí)行計劃，可以看到該查詢一次性查詢到幾乎全表的數(shù)據(jù)，并且還走了全秒掃描性能可想而知:

id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra|
--+-----------+------+----------+----+-------------+---+-------+---+-------+--------+-----+
 1|SIMPLE     |t_user|          |ALL |             |   |       |   |2993040|   100.0|     |

因為我們的數(shù)據(jù)表是id自增的，所以我們查詢的時候完全可以基于該特性通過上一次查詢到的id作為篩選條件進行分頁查詢。

所以我們的分頁查詢可直接改為:

select * from t_user where id > 500000 limit 500000 ;

再次查看執(zhí)行計劃可以發(fā)現(xiàn)該查詢?yōu)榉秶樵儯樵兊降臄?shù)據(jù)量也少了很多，性能顯著提升:

id|select_type|table |partitions|type |possible_keys|key    |key_len|ref|rows   |filtered|Extra      |
--+-----------+------+----------+-----+-------------+-------+-------+---+-------+--------+-----------+
 1|SIMPLE     |t_user|          |range|PRIMARY      |PRIMARY|8      |   |1496520|   100.0|Using where|

另外一種解決方案就是流式查詢，通過流式查詢將SQL語句直接提交給MySQL服務(wù)端，讓服務(wù)端按照客戶端程序接受程度不斷推送數(shù)據(jù)，然后我們的java程序每次收集50w的數(shù)據(jù)，再寫入到對應(yīng)的excel文件中：

(2) 選擇合適的導(dǎo)出工具

因為市面上比較多的excel導(dǎo)出工具，常見的就是Apache poi，但是它們的操作對于內(nèi)存的消耗非常嚴重，對于我們這種大數(shù)據(jù)量的寫入不是很友好，所以筆者更推薦使用阿里的EasyExcel，它對poi進行一定的封裝和優(yōu)化，同等數(shù)據(jù)量寫入使用的內(nèi)存更小。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>4.0.3</version>
</dependency>

解決上述問題之后，我們就可以說說代碼實現(xiàn)思路了，以本文示例來說，有一張用戶表有300w左右的數(shù)據(jù)，每次查詢時只需查詢id(4字節(jié))和name(10字節(jié))，按照64位的操作系統(tǒng)來說，一個user對象所占用的內(nèi)存大小為:

object header +pointer+id字段+name字段大小=8+8+4+10=30字節(jié)

因為java對象內(nèi)存大小需要16位對齊，需要補齊2個字節(jié)，所以實際大小為32字節(jié)，按照筆者對于堆內(nèi)存的配置，每次查詢50w條數(shù)據(jù)是允許的，所以每次從數(shù)據(jù)庫讀取數(shù)據(jù)并轉(zhuǎn)為java對象，也只需要32*500000/1024即15M內(nèi)存即可。

確定每次分頁查詢50w條數(shù)據(jù)之后，我們就需要確定一共需要查詢幾個分頁，然后就可以根據(jù)pageSize確定查詢的頁數(shù)。因為每次查詢50w條數(shù)據(jù)，所以每兩次完成分頁查詢和寫入基本上一個sheet就會滿了，這時候我們就需要創(chuàng)建一個新的sheet進行數(shù)據(jù)寫入了。

2. 思路小結(jié)

總結(jié)一下實現(xiàn)步驟:

查詢目標數(shù)據(jù)量大小。
根據(jù)每次分頁大小確定查詢頁數(shù)（或使用流式查詢）。
根據(jù)頁數(shù)大小進行遍歷，進行分頁查詢，并將數(shù)據(jù)寫入到文件中。
基于頁數(shù)確定sheet切換時機。

(1) 分頁查詢方案落地

以下便是筆者基于上述思路所實現(xiàn)的分頁查詢和導(dǎo)出的代碼:

long start = System.currentTimeMillis();
        UserMapper userMapper = SpringUtil.getBean(UserMapper.class);
        //計算總的數(shù)據(jù)量
        int count = Math.toIntExact(userMapper.selectCount(Wrappers.emptyWrapper()));


        //獲取分頁總數(shù)
        int queryCount = 50_0000;
        int pageCount = count % queryCount == 0 ? count / queryCount : count / queryCount + 1;

        log.info("pageCount: {}", pageCount);

        //設(shè)置導(dǎo)出的文件名
        String fileName = "F://tmp/result.xlsx";
        //設(shè)置excel的sheet號碼
        int sheetNo = 1;
        //設(shè)置第一個sheet的名字
        String sheetName = "sheet-" + sheetNo;



        // 創(chuàng)建writeSheet
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
        //記錄每次分頁查詢的最大值
        Long maxId = null;

        //指定文件
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build()) {
            //寫入每一頁分頁查詢的數(shù)據(jù)
            for (int i = 1; i <= pageCount; i++) {
                // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)
                long queryStart = System.currentTimeMillis();

                List<User> userList = null;
                //如果是第一次則直接進行分頁查詢，反之基于上一次分頁查詢的分頁定位實際偏移量，篩選前n條數(shù)據(jù)以達到分頁效果
                PageHelper.startPage(1, queryCount, false);
                if (i == 1) {
                    userList = userMapper.selectList(Wrappers.emptyWrapper());
                } else if (maxId != null) {
                    QueryWrapper wrapper = new QueryWrapper();
                    wrapper.gt("id", maxId);//相當于where id=1
                    userList = userMapper.selectList(wrapper);
                    PageHelper.startPage(0, queryCount, false);
                }


                //更新下一次分頁查詢用的id
                if (CollUtil.isNotEmpty(userList)) {
                    maxId = userList.get(userList.size() - 1).getId();
                    log.info("maxId: {}", maxId);
                }

                long queryEnd = System.currentTimeMillis();
                log.info("數(shù)據(jù)大小:{},寫入sheet位置:{}，耗時:{}", userList.size(), sheetName, queryEnd - queryStart);

                long writeStart = System.currentTimeMillis();
                excelWriter.write(userList, writeSheet);

                long writeEnd = System.currentTimeMillis();
                log.info("本次寫入耗時:{}", writeEnd - writeStart);

                //如果% 2 == 0，則說明一個sheet寫入了50*2即100w的數(shù)據(jù)，需要創(chuàng)建新的sheet進行寫入
                if (i % 2 == 0) {
                    sheetName = "sheet-" + (++sheetNo);
                    writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
                    log.info("寫滿一個sheet,切換到下一個sheet:{}", sheetName);
                }
            }
        }
        long total = System.currentTimeMillis() - start;
        log.info("導(dǎo)出結(jié)束，總耗時:{}", total);

可能會有讀者好奇筆者這個50w的數(shù)值設(shè)計思路是什么，除了考慮避免OOM以外，還考慮到每個sheet只能寫入100w條的數(shù)據(jù)，為了方便通過分頁查詢的輪次確定當前寫入的數(shù)據(jù)量大小，筆者嘗試過20w、50w。最終在壓測結(jié)果上看出，50w讀寫耗時雖然是20w的2倍，但是IO次數(shù)卻不到20w查詢的二分之一，通過更少的IO操作獲得更好的執(zhí)行性能。

最終300w數(shù)據(jù)導(dǎo)出耗時大約35s，整體性能表現(xiàn)還是可以的：

(2) 使用流式編程導(dǎo)出(推薦)

對應(yīng)我們也給出流式編程的導(dǎo)出方案，筆者針對查詢語句做了流式編程的配置，通過這些配置保證MySQL服務(wù)端基于自己的迭代游標按照客戶端處理效率按照順序的數(shù)據(jù)流不斷傳輸給客戶端：

@Select("select * from user  ")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
    @ResultType(User.class)
    void selectListByStream(ResultHandler<User> handler);

關(guān)于流式查詢的更多內(nèi)容，建議讀者參考筆者寫的這篇文章：《MySQL 流式查詢的奧秘與應(yīng)用解析》

基于上述查詢語句，我們不斷拿到user對象，因為流式查詢避免的頻繁的IO分頁請求，所以真正的寫入瓶頸點在于寫入到excel文件中，所以筆者在流式聚合數(shù)據(jù)時是通過每1w條進行一次寫入，保持每100w切換一次sheet。

需要注意的是因為筆者的ResultHandler用的是lambda表達式，為了讓編譯器通過編譯所有的計數(shù)、sheet等修改操作都是通過原子類CAS完成的，具體讀者可以參考筆者的注釋：

long start = System.currentTimeMillis();
        UserMapper userMapper = SpringUtil.getBean(UserMapper.class);

        //設(shè)置導(dǎo)出的文件名
        String fileName = "F://tmp/result.xlsx";
        //設(shè)置excel的sheet號碼,用原子類保證可以在lambda表達式中通過編譯
        AtomicInteger sheetNo = new AtomicInteger(1);
        //設(shè)置第一個sheet的名字,用原子類保證可以在lambda表達式中通過編譯
        AtomicReference<String> sheetName = new AtomicReference<>("sheet-" + sheetNo.get());


        // 創(chuàng)建writeSheet,用原子類保證可以在lambda表達式中通過編譯
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo.get(), sheetName.get()).build();
        AtomicReference<WriteSheet> writeSheetRef = new AtomicReference<>(writeSheet);


        List<User> userList = new ArrayList<>();
        AtomicReference<List<User>> userListRef = new AtomicReference<>(userList);

        //記錄導(dǎo)出的size
        AtomicInteger atomicCount = new AtomicInteger(0);

        //指定文件
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build()) {


            userMapper.selectListByStream(res -> {
                //存入list中
                User user = res.getResultObject();
                userListRef.get().add(user);
                atomicCount.incrementAndGet();

                //50w執(zhí)行一次導(dǎo)出寫入
                if (userListRef.get().size() % 1_0000 == 0) {
                    long writeStart = System.currentTimeMillis();
                    //寫入到文件
                    excelWriter.write(userListRef.get(), writeSheetRef.get());
                    //清空列表內(nèi)部數(shù)據(jù)
                    userListRef.get().clear();
                    long writeEnd = System.currentTimeMillis();
                    log.info("本次寫入耗時:{}", writeEnd - writeStart);
                }

                //寫入100w條后，切換sheet
                if (atomicCount.get() % 100_0000 == 0) {
                    //自增sheetNo
                    sheetNo.incrementAndGet();
                    //修改sheetName
                    sheetName.set("sheet-" + sheetNo.get());
                    //寫入文件
                    writeSheetRef.set(EasyExcel.writerSheet(sheetNo.get(), sheetName.get()).build());

                    log.info("寫滿一個sheet,切換到下一個sheet:{}", sheetName);
                }
            });

            //檢查是否還有未寫入的文件
            if (CollectionUtil.isNotEmpty(userList)) {
                log.info("存在未寫入完成的數(shù)據(jù)，size:{}", userList.size());
                excelWriter.write(userList, writeSheetRef.get());
            }
            long total = System.currentTimeMillis() - start;
            log.info("導(dǎo)出結(jié)束，總耗時:{}", total);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

最終這種方案的執(zhí)行耗時在最好的情況下差不多30s左右，總的來說流式查詢天然內(nèi)存友好且游標式順序前進的特定，對于這種并發(fā)場景下的數(shù)據(jù)導(dǎo)出是非常友好的，所以這種方案也是筆者比較推薦的方案：

三、小結(jié)

以上便是筆者的百萬級別數(shù)據(jù)導(dǎo)出的落地方案，可以看出筆者針對分頁查詢導(dǎo)出的方案著重在分頁查詢大小和分頁查詢sql上進行重點優(yōu)化，通過平衡分頁查詢的數(shù)據(jù)量和IO次數(shù)找到合適的pageSize，再通過上一次分頁查詢結(jié)果定位下一次查詢的id作為where條件，避免分頁查詢時的全秒掃描以得到符合業(yè)務(wù)需求的高性能sql。

對于流式查詢，因為流式查詢的特定，筆者在優(yōu)化時更著重于找到寫入到文件這塊的耗時上，通過找到IO寫入的平衡點找到最佳寫入閾值，從而完成百萬級別數(shù)據(jù)的高效導(dǎo)出。

責(zé)任編輯：趙寧寧來源：寫代碼的SharkChili