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

select * from t_user limit 500000 ;

select * from t_user limit 500000,500000 ;

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

select * from t_user where id > 500000 limit 500000 ;

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|

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

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

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


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



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

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

                List<User> userList = null;
                //如果是第一次則直接進(jìn)行分頁(yè)查詢(xún)，反之基于上一次分頁(yè)查詢(xún)的分頁(yè)定位實(shí)際偏移量，篩選前n條數(shù)據(jù)以達(dá)到分頁(yè)效果
                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);//相當(dāng)于where id=1
                    userList = userMapper.selectList(wrapper);
                    PageHelper.startPage(0, queryCount, false);
                }


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

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

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

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

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

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

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


        // 創(chuàng)建writeSheet,用原子類(lèi)保證可以在lambda表達(dá)式中通過(guò)編譯
        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)出寫(xiě)入
                if (userListRef.get().size() % 1_0000 == 0) {
                    long writeStart = System.currentTimeMillis();
                    //寫(xiě)入到文件
                    excelWriter.write(userListRef.get(), writeSheetRef.get());
                    //清空列表內(nèi)部數(shù)據(jù)
                    userListRef.get().clear();
                    long writeEnd = System.currentTimeMillis();
                    log.info("本次寫(xiě)入耗時(shí):{}", writeEnd - writeStart);
                }

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

                    log.info("寫(xiě)滿(mǎn)一個(gè)sheet,切換到下一個(gè)sheet:{}", sheetName);
                }
            });

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

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