使用EasyExcel實現(xiàn)百萬級別數(shù)據(jù)導(dǎo)出的代碼示例

更新時間：2023年12月04日 10:37:16 作者：shark_chili

近期需要開發(fā)一個將百萬數(shù)據(jù)量MySQL8的數(shù)據(jù)導(dǎo)出到excel的功能,所以本文講給大家介紹了基于EasyExcel實現(xiàn)百萬級別數(shù)據(jù)導(dǎo)出,文中通過代碼示例講解的非常詳細,需要的朋友可以參考下

前言

近期需要開發(fā)一個將百萬數(shù)據(jù)量MySQL8的數(shù)據(jù)導(dǎo)出到excel的功能，查閱相關(guān)資料后便整理了這篇實現(xiàn)方案供讀者參考。

需求簡述

該數(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中。

實現(xiàn)思路

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

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

對于問題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|

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

解決上述問題之后，我們就可以說說代碼實現(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ù)寫入了。

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

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

代碼示例

以下便是筆者基于上述思路所實現(xiàn)的代碼，查看日志也可以發(fā)現(xiàn)50w的數(shù)據(jù)查詢和寫入加起來只需6s。最終執(zhí)行耗時也只需45s。

public static void main(String[] args) {
        SpringApplication app = new SpringApplication(WebApplication.class);
        Environment env = app.run(args).getEnvironment();
        logger.info("啟動成功！！");
        logger.info("地址: \thttp://127.0.0.1:{}", env.getProperty("server.port"));

        TUserMapper userMapper = SpringUtil.getBean(TUserMapper.class);
        //計算總的數(shù)據(jù)量
        int count = (int) userMapper.countByExample(null);


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

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


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

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


                //如果是第一次則直接進行分頁查詢，反之基于上一次分頁查詢的分頁定位實際偏移量，篩選前n條數(shù)據(jù)以達到分頁效果
                if (i == 1) {
                    PageHelper.startPage(i, queryCount, false);
                } else if (maxId != null) {
                    userExample.createCriteria().andIdGreaterThan(maxId);
                    PageHelper.startPage(0, queryCount, false);
                }


                List<TUser> userList = userMapper.selectByExample(userExample);
                //更新下一次分頁查詢用的id
                if (CollUtil.isNotEmpty(userList)) {
                    maxId = userList.get(userList.size() - 1).getId();
                }

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

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

                long writeEnd = System.currentTimeMillis();
                logger.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();
                    logger.info("寫滿一個sheet,切換到下一個sheet:{}", sheetName);
                }
            }
        }
        long total = System.currentTimeMillis() - start;
        logger.info("導(dǎo)出結(jié)束，總耗時:{}", total);

    }

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

# 50w的讀寫耗時
com.sharkChili.webTemplate.config.WebApplication  :73   [32m                  [0;39m 數(shù)據(jù)大小:500000,寫入sheet位置:sheet-1，耗時:4719
2023-12-03 10:13:58.675 INFO  com.sharkChili.webTemplate.config.WebApplication  :78   [32m                  [0;39m 本次寫入耗時:2911
2023-12-03 10:14:02.517 INFO  com.sharkChili.webTemplate.config.WebApplication  :73   [32m                  [0;39m 數(shù)據(jù)大小:500000,寫入sheet位置:sheet-1，耗時:3841
2023-12-03 10:14:04.860 INFO  com.sharkChili.webTemplate.config.WebApplication  :78   [32m                  [0;39m 本次寫入耗時:2343

小結(jié)

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

以上就是使用EasyExcel實現(xiàn)百萬級別數(shù)據(jù)導(dǎo)出的代碼示例的詳細內(nèi)容，更多關(guān)于EasyExcel實現(xiàn)數(shù)據(jù)導(dǎo)出的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: