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

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

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

前言

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

需求簡(jiǎn)述

該數(shù)據(jù)表是一張用戶表，包含id和name，該用戶表數(shù)據(jù)量在300w左右，以自增id作為主鍵，而功能要求我們?cè)谝环昼娭畠?nèi)完成百萬(wàn)數(shù)據(jù)導(dǎo)出到excel。需要注意的是，我們導(dǎo)出的excel格式為xlsx，它的每一個(gè)sheet只能容納100w的數(shù)據(jù)，這也就意味著我們的數(shù)據(jù)必須以100w作為批次寫(xiě)到不同的sheet中。

實(shí)現(xiàn)思路

我們先來(lái)說(shuō)說(shuō)需要解決的問(wèn)題:

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

對(duì)于問(wèn)題1我們采用分頁(yè)查詢的方式進(jìn)行查詢，參考自己堆內(nèi)存的配置推算每次分頁(yè)查詢的數(shù)據(jù)量。因?yàn)閱?wèn)題1采用了分頁(yè)查詢，我們完全可以通過(guò)分頁(yè)查詢的次數(shù)推算出一個(gè)sheet寫(xiě)入了多少數(shù)據(jù)，例如我們每次分頁(yè)查詢50w的數(shù)據(jù)，那么每?jī)纱尉涂梢砸暈橐粋€(gè)sheet寫(xiě)滿了，我們就可以創(chuàng)建一個(gè)新的sheet寫(xiě)入數(shù)據(jù)。

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

例如我們第一次分頁(yè)查詢的sql語(yǔ)句為:

select * from t_user limit 500000 ;

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

 select * from t_user limit 500000,500000 ;

查看該查詢執(zhí)行計(jì)劃，可以看到該查詢一次性查詢到幾乎全表的數(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|     |

因?yàn)槲覀兊臄?shù)據(jù)表是id自增的，所以我們查詢的時(shí)候完全可以基于該特性通過(guò)上一次查詢到的id作為篩選條件進(jìn)行分頁(yè)查詢。

所以我們的分頁(yè)查詢可直接改為:

select * from t_user where id > 500000 limit 500000 ;

再次查看執(zhí)行計(jì)劃可以發(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|

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

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

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

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

確定每次分頁(yè)查詢50w條數(shù)據(jù)之后，我們就需要確定一共需要查詢幾個(gè)分頁(yè)，然后就可以根據(jù)pageSize確定查詢的頁(yè)數(shù)。
因?yàn)槊看尾樵?code>50w條數(shù)據(jù)，所以每?jī)纱瓮瓿煞猪?yè)查詢和寫(xiě)入基本上一個(gè)sheet就會(huì)滿了，這時(shí)候我們就需要?jiǎng)?chuàng)建一個(gè)新的sheet進(jìn)行數(shù)據(jù)寫(xiě)入了。

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

查詢目標(biāo)數(shù)據(jù)量大小。
根據(jù)每次分頁(yè)大小確定查詢頁(yè)數(shù)。
根據(jù)頁(yè)數(shù)大小進(jìn)行遍歷，進(jìn)行分頁(yè)查詢，并將數(shù)據(jù)寫(xiě)入到文件中。
基于頁(yè)數(shù)確定sheet切換時(shí)機(jī)。

代碼示例

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

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

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


        //獲取分頁(yè)總數(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號(hào)碼
        int sheetNo = 1;
        //設(shè)置第一個(gè)sheet的名字
        String sheetName = "sheet-" + sheetNo;


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

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


                //如果是第一次則直接進(jìn)行分頁(yè)查詢，反之基于上一次分頁(yè)查詢的分頁(yè)定位實(shí)際偏移量，篩選前n條數(shù)據(jù)以達(dá)到分頁(yè)效果
                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);
                //更新下一次分頁(yè)查詢用的id
                if (CollUtil.isNotEmpty(userList)) {
                    maxId = userList.get(userList.size() - 1).getId();
                }

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

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

                long writeEnd = System.currentTimeMillis();
                logger.info("本次寫(xiě)入耗時(shí):{}", writeEnd - writeStart);

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

    }

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

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

小結(jié)

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

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

您可能感興趣的文章: