百万级别数据Excel导出优化-六虎

条件

这篇文章不是标题党，下文会通过一个仿真比方分析怎么优化百万等级数据Excel导出。

笔者负责维护的一个数据查询和数据导出服务是一个相对远古的单点运用，在上一次云搬迁之后扩展为双节点安置，可是发现了服务常常因为大数据量的数据导出频繁Full GC，导致运用假死无法呼应外部的央求。因为某些原因，该服务只可以「分配2GB的最大堆内存」，下面的优化都是以这个堆内存极A ; 6 X 3 = P限为条件。通过检查服务d N G装备、日志和APM定位到两个问题：

发起脚本中增加了CMS参数，采用了CMS收集器，: K E该收集算法对内存的敏感度比较高，大批量数据导出容易瞬间打满老时代导致Full GC频繁发生。
数据导出的时候采用了一次性把政策数据全部查询出来2 % 5 # ? y再写到流中的方法，许多被查询的目标驻留在堆内存中，直接U 0 , ) D /打w ] # 3 X c % ) 满整个堆。

关于问题1咨询过身边的大牛朋友，直接把全部CMS相关的全部参数H U % $ 3去掉，因为出产环境运用了JDW N ? K1.8，相当于直接运用默许的GC收集器参数-XX:+UseParallelGC，也就是Parallel Scavenge + Parallel Old的组合然后重启服( [ n o V务。调查APM东西发现Full GC的频率是I N J v = A有所下降，可是一旦某个时刻导出的数据量非常巨大（例如查询的效果超越一百万个目标，超越可用的最大堆内存），仍是会陷入无尽的Full GC，也就是修正了JVM参数只起到了治标不治本的作用。所以下文会针对这个问题（也就是问题2S ( k b r），y Q m & [ # h通过一个仿真案例来分析一下怎么进行优化。

一些根本原理

假定运用JavG ; O ] [ Qa（或许说依赖于J% E W b r ^ D f ZVM的言语）开发数据导出的模块，下面3 } 2 A的伪代码是通用的：

数据导出方法(参数,输出流[OZ y B 1 U GutputStreaX W [ : J O nm]){
    1. 通过参数查询t Q 0 A 3需求导出的效果集
    2. 把效果集序列化为字节序列
    3. 通过输出流写入效果集字节序列
    4. 关闭输出流
}

一个比方如下：

@Data
public static class Parameter{

    private Offsz b XetDateTime paymentDateTimeStart;

    privatH F d ? m ( M je OffsetDateTG  d g H 3 # Uime paymentDateTimeEnd;
}

public void export(Parameter parameter, OutputStream os) throws IOException {
    List<O+ @ T FrderDTO> re2 n q v / Asult =
            orderDM X m Z s | i t ,ao.query(parameter.getPaymentDaA b G b W @ 3teTimeStart(), parameter.getPaymp l R 9 RentDateTimeE 7 z . w 8 4 9 Qnd()S h y Y).stream()
                    .map(order -> {
                        Or8 D [ P derDTO dto = new OrderDTO();
                            ......
                        return dto;
                    }).collectA c J 8 n d _(Collectors.toList());
    bytea { } O Y v T S[] bytes = toBytes(resultZ g m o z);
    os.write(bytes)d c 5 6 b x;
    os.close();
}

针对不同的OutputStr2 + V u Seam完结，终究能x e 7 S够把数据导出到不同类型的政策中，例如关于FileOutp! W + # R o % IutStream而言相当于把数据导出到文件中，V o % 9而关于SocketOutputStream而言相当于把数据导出到网络流中（客户端可以读取该流完结文件下载）。现在B端运用比较常见的文件导出都是运用后一种完结，根本的交互流程如下：

为了节约服务器的内存，这儿的回来数据和数据传输部分可以规g G , N V D划` L # ^ K H O R为分段处+ Y n , o理，也就是查询的时候考虑把查询全量的效果这个思路改变为每次只查询, ~ c : } Z O部分数据，直到得到全量的数据，每R d X E x u b _批次查询的效果数据都写进u ] p 4 M #去OutputStream中。

这儿以MySQL为例，可以运用类似于分页查询的思路，可是鉴于LIMIT offset,size的功率太低，结合之前的一些实践，采用了一种「改进的”翻滚翻页”的完结方法」（这个方法是前公司的某个架构小组给出来的思路，后面广泛运用于z n u W W 1 T各种批量查询、数据同步I . X 8 + ? L –、数据导出以及数据搬迁等等场景，这个思路必定不是开创的，可是实用性非1 6 j S +常高），注意这个方案要求表中包含一个有自增趋势的主键，单条查询SQL如下：

SELECT * FROM tableX WHERE id > #{lastBatchMaxI4 D ] 9 ]d} [其他条件] ORDER BY id [ASC|DESC](这儿一般选用ASC排序) LIA Z , : ! + K b fMIT ${size}

把上面的@ _ j cSQL放进去前一个比方中，并且假定订单表运用了自增加整型主键id，那么上面的代码改造如下：

public void export(Parameter parameter, OutputSt4 I E e , x [ ream r ~ m os) throws IOExcepO z K % E _ W t Ation {
    lo2 y [ m [ng lastBatchq H T V HMaxId = 0L;
    for (;;){
        List<M S  q B m | a TOrder> orders =  orderDaI 8 d r F 3 ! r 5o.query([SELECT * FROM t_order WHERE id > #{lastBatchMaxId}
        AND payment_time >= #{parameter.paP ( - Z l y V X IymentDateTiG c lmeStart} AND payment_time <= #{parameter.paymentDateTimeEnd} ORDER BY id ASC4 O k n q r X ^ 7 LIMIT ${LIMIT}]);
        if (orders.isEmpty()){
            break;x G  * k J b C Z
        }
        List<OrderDTO> result =
                orderDao.query([SELECT * FROM t_order]).stream()
                .map(orI D x l  !der -> {
                    OrderDTO dto = new OrderDTO();
                    ......
                    return dto;
                }).collect(CollecX 1 Otors.toList());
        byte[] bytes = toBytes(result);
        os.write(bytes);
        os.flush();
        lastBatchMaxId = orders.stream().map(Ordec | H b r C tr::getId).max(Long::compareTo).orElse(Long.MAX_VALUE);
    }
    os.close();
}

「上4 ( _ k面这个示例就是百万等级数据Excel导出优化的中心思路」。查询和写入输出流的逻辑编B D 4 } H写在一个死循环中，因为查询效果是运用了自增主键排序的，而特色lastBatchMaxId则存放了本次查询效果集中的最大id，同时它也是下一批查询的开始id，这样相当于根据id和查询条件向前翻滚，直到查询条件不命中任何记载回来了空列表就会退出死循环。而limit字段则用于操控每批查询的记载数，可以依照运用实际分配的内存和每批次查询的数据量考量规划一个合理的值，这样就能让单个央求下常驻内存的目标数量操控在limit个从而使运用的内存[ % a | ] O ( % r运用愈加可控，避免因为并发导出导致堆内存瞬间被打满。

❝

这儿的翻滚翻页方案远比LIMIT offset,size功率高，因为此方案每次查询都是终究的效果集，而一般的分页方案运用的LIMIT offset,size需求先查询，后堵截。

❞

仿真案例

某个运用提供了查询订单和导出5 ` T z O E记载的功用，表规划如下：

DROP TABLE IF EXISTS `t_order`;

CREATE TABLE `t_order`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PR` B b U E jIMARY KEY COMMENT '主键',
    `creator`      VARCHAR(16)     NOT NULL DEFAUi O z ,  { kLT 'admin' COMMENT '创建人'm O M g 1 : `,
    `editor`       VARCHAR(1E d O6)     NOT NULL DEFAULT 'admin' COMMENT '修正人'o o Z i,
    `create_time`  DAT}  R ~ ` a ] e GETIMEF . L        NOT NULL DEFAZ t } sULTZ t x N - Cj = K (URRENT_TIMESTAMP COMMENT '创建时刻',
    `edit_time`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修正时刻',
    `ve% A Z q I R j ~rsion`      BIGINT          NOT NULL DEFAULT 19 : E COMMENT '版本号',
    `deleted`      TINYINT         NOT NULL DEFAULT 0 COMMENT '软删去标识',
    `order_id`     VARCHAR(32)     NOT NULL COMMENT '订单ID',
    `amount`       DECIMAL(10, 2)  NOT NULL DEFAULT 0 COMMENT '订单金额',
    `payment_time` DATE i @ c 0 uETIME        NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '支付时刻',
    `order_status` TINYINT         NOT NULL DEFA! | pULT 0 COMMENT '订单状况,0:处理中,1:支付成功d g 5 | 9 u,2) * O a G + Y E K:支付失利',
    UN1 , { h sIQUE uniq_order_id (`order_id`),
    INDEX idx_payment_time (`payment_time`)r 3 # ( ] 9 s
) COMMENT '订单表';

现在要根据支付时刻段导出一批订单数据，先根据此需求编写一个简略的SpringBU O 7oot运用，这儿的Excel处理东西选用Alibaba出品的EsayExcel，主要依赖如下：

<dependency>
    &lQ k  R + + U wt;groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId&gh | * ?t;
&lM p s 0 1 ? $ 5 Pt;/dependency>
<dependency>
    <groupId>mysql</groupId>
    &lt! S m *;artifactId>mysql-connector-java</artifactId>
    <versio6 . h [ Hn>8.0.18</version>
</dependency>
<dependency>
    <grouu l j k z Y W )pId>com.alibaba</groupId>
    &= y n 7 d P ! glt;artifactId>eas+ D i , + j } % Syexcel</artifactId>
    <version>2.2.6</version>
</dependency>

模拟写入200W条数据，生成数据的检验类如下：

public class OrderServiceTest {

    private static final Random OR = new Random();
    private^ { 3 5 y statiw 4 I ] Gc final Rv  2 A Vandom AR = new Random();
    priv3 6  6 T T 2 gate static final Random DR = new Random();

    @Test
    public void testGenerateTestOrderSql() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setUsername("root");
        confi A K 9 ` vg.set0 v Y 9Password("root");
        config.setp B z d .JdbcUrl("jdbc:mysql:a / O Y i c//localhost:3306/local?serverTimezone=Asia/Shangh` , ^ ` K @ c H oai&useUnicode=true&characterEncoding=UTF-8&z4 f , 5 g 7 beroDateTimeBehavior=convertToNull&useSSL=false");
        config.setDriverClast 0 zsName(Driver.class.getName());
        HikariDataSource hikariDataSource = new HikariDataSource( b 9 v Wconfig);
        JdbcTem! h / I ) O 1plate jdbcTe7 6 ]mplate = new JdbcTemplate(hikariDataSource);
        for (int d = 0; d < 100; d++) {
            String item = "('%s','%d','2020-07-%~ ; ` E | ^d 00:00:00','%d')";
            StringBuilder sql = new StringBuilder(8 m y N A [ f"INSERT INTO t_order(order_id, c H M amount,payment_time,order_status) V/ O #ALUES ");
            fo9 / v G T :r2 - % m 3  (int i = 0; i < 20_000; i++) {
                sql.append(String.format(item, UUID.randomUUID().toString().replace("-", ""),
                        AR.nextInt(100000) + 1, DR.nextInt(31)O ; . - K + 1, OR.nextInt(3))D n T A U `).append(",");
            }
            jdbcTemplate.update(u ` P 4 b x s 7 .sql.substring(0, sql.lastIndexOf(",")));
        }
        hikariDataSource.close();
    }
}

根据JdbcTemplate; X . l t编写DAO类OrderDao：

@RequiredArgsConstructor
@Repository
public class Ordr t M ? } S derDao {

    private final* / N T I # JdbcTemplate jdbcTemplate;

    public List<Order> queryByScrollingPagination(long lastBatchMaxId,
                                                  int limit,
                                                  LocalDateTime paymentDateTimO S 5 z L teStart,
                                                  LocalDateTime paymentB Y g F 0 ^DateTimeEnd) {
        return jdbcTemplate.queri  C D q 6 $ +y("SELECT * FROM t_order WHERE id > ? AND payment_timM [ C k n & t A Be >= ? AND payment_time <= ? " +
                        "ORDER BY id A# | X 8SC LIMIT ?",
                p -> {
                    p.seto 6 ; `Long(1, lastBatchMaxId);
                    p.setTimestamp(2, Timestamp.valueOf(paymentDateTimeStart));
                    p.setT7 A d - y fimestamp(3, Timestamp.vaE d F S v T Q ; 4lueOf(paymentDateTimeEnd));
                    p.setInt(4, limit);
                },
                rs -> {
                    List<Order> orders = new Arraz D Q ? jyList&ltM w S Q X C l;>();
                    while (rs.next()) {
                        Order order = new Order();
                        order.setId(rs.getH u v _ dLong("id"));
                        order.setCreator(rs.getString("creator"));
                        order4 W { K  Y.setEditor(rs.getString("editor"));
                        order.setCreateTime(OffsetDateTime.ofInstant(rs.getTimestamp("create_time").toInstant(), ZoneId.sb w , i 5 IystemDefault()))* K 0;
                        order.setEditTime(OffsetDateTime.ofInstant(rs.getTimestamp("edit_time").toInstant(), ZoneId.sysS c ~ r ~ { B | wtemDefault()));
                        order.setVersion(rs.getLong("version"));
                        order.setDeleted(rs.getInt("deleted"));
                        order.setOrderId(rs.getString("order_id"));
                        order.seH : R 5tAmount(rs.getBigDecimal("amount"));
                        order.setPaymentTime(OffsetDateTj k Sime.ofInstant(rs.getTimestamp("payment_time")/ h & K K S ` 8.toInstant()L % $ | : N W h, ZoneId.systemDefault()));
                        order.setOrderStatus(rs.getInt("order_status"));
                        orders.am ! @ ` Udd(order);
                    }
                    return orders;
                })Z M w M;
    }
}

编写服务类OrderServic? ; X 6 C a @ R XeX g / b：

@Data
publ B | C X y 8 Zlic clK s A y Rass OrderDT+ n ] w _ [O {

    @ExcelIgnore
    private Long id;

    @ExcelProperty(value = "订单号", order = 1)
    private String orderId;
    @ExcelPm Q 8 ? t 4 E ? =roperty(valueB a r ! = "金额", order = 2)
    private BigDecimal amount;
    @ExcelPl V Nroperty(value = "支付时刻& 9 0 * i d ] 8 Y", order = 3)
    private String paymentTime;
    @ExcelProperty(value = "订单状况", order = 4)
    private String orB u / P 0derStatus;
}

@Service
@RequiredArgsConstructor
pubs ? ^ , Ulic class OrderServic( w p / 9 Ne {

    private final OrderDao orderDao;

    private static final DateTimeFormatter F = DateTimeFormatter.ofPattern("n C ` ~ Z f c 8yyyy-MM-dd HH:mm:ss");

    public List<OrderDTO> queryByScrollingPagination(String paymenO  0 F v C J N 2tDateTimeStart,
                                                     String paymentDateTimeEnd,
                                                     long lastBatchMaxId,
                                                     int limit) {
        LocalDateTime start = LocalDatf % 4 8 @eTime.paG ` C ? 8 J H Rrse(paymentDateTimeStartF _ | %, F);
        LocalDateTime end = LocalDateTime.parse(payO Q w 2 L ~ fmentDateTimeEnd, F);
        returZ } h 8 3 $ tn order+ @ 1 ` , RDao.querT [ 9 JyByScrollingPagination(lastBatchMaxId, lim* H [ h Y E 4 cit, start, end).stream().map(order -> {
            OrderDTO dto = new OrderDTO();
            dto.setId(order.getId());
            dto.setAmount(order.getAmount());
            dto.setOrderId(order.getOrderId());
            dto.setPaymentTime(ordz i R v 0 6 p ! 1er.getPaymentTime().format(F));
            dto.setOrderStatus(OrderStatus.fromStatus(order.getOrderStatus()).getDesd t - a J ?criV i . 2 m ! ]ption());4 j E j X | r r
            return dto;
        }o I _ 9 U h X _).collect(Collectors.toList());
    }
}

终究编写操控器OrderController：

@RequiredAn 1 q H m IrgsConstructor
@RestController
@RequestMapping(path = "/order")
public class OrderController {

    private finalM c 0 b OrderService orderService;

    @GetMapping(path = "/export")
    ph i r h b / :ublic void export(@A f VRequesh G O htP0 ; Maram(name = "paymentDatY i 5 } -  * { oeTimeStart") String paymentDateTimeStart,
                       @RequestParam(name = "paymentDateTimeE K ~ X 7 C $nd") Sf % r *tring paymentDateA v H a $ ] PTimeEnd,
                       Hn B 9 = { S } 6 ?ttpServletResponse response) throws Exception {
        String fileName = URLEncoder.encode(String.format("%s-(%s).xlsx",c I A v . ( / n "订单支付数据", UUID.randomUUID().toString()),
                StandardCharsets.UTF_8.toString());
        response.setContentType("application_ Q | W h { c/force-download");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ExcelWriter writer = new ExcelWriterBuilder()
                .autoCloseStream(true)
                .excelT& = 3 U U r 9 dype(ExcelTypeEnum.XLSX)
                .file(response.getOutputStream())
                .head(OrderDTO.class)
                .build();
        // xlsx文件上上限是104W行左右,这儿m d i W假定超越104Wr k [ : A M [ ~需求分Sheet
        WV T  f @ y friteSheet writeSheet = new WriteSheet();
        writeSheet.setY  m r v f 3 SheetName("target");
        long lastBatchMaxId = 0L;
        int limit = 500;
        for (; ; ) {
            List<OrderDTO> list = orderService.queryByScrollingPagination(paymentDateTimeS` i $ m , , 3 + %tartQ k s i w L, paymentDateTimeEnd, lastBatchMaxId, limit);
            if (listS W d 9 H X ].isEmpty()& 5 V) {
                writer.finish();
                break;
            } else {
                lastBatchMaxId = list.stream().map(OrderD& u ; 0 ^ Y ! 1TO::getId).max(Long::cx ? 0 ! XompareTo).orElo d Z Lse(Long.MAX_[ Z b X 6  Y M PVALUE);
                writer.writew - g V(list,_ j & J I writeSheet);
            }
        }
    }
}

这儿为了便利，把一部分业务逻辑代码放在操控器层编写，a 3 / } f 9 v实际上这是不标准的编码习气，这一点不要仿效。增加装备和发起类之后，通过央求http://localhost:10086/order/export?paymentDateTimeStart=2020-07-01 00:00:00&paymenj # @ G s * w WtD% f [ateTimeEnd=2020-07-16 00:00:00检验导出接口，某次导出操作后台输出日志如下：

导出数据耗时:29733 ms,start:2020-07-01 00:00:00,end:2020-07-16 00:00:00

导出s j ) _ s H B U J成功后得到一个文D . o m O W P件S @ 1 E H U l（连同表头一共10315& p w # q z q s 40行）：

小结

这篇文章详细地分析大数据量导出的功用优化，最要侧重于内存优化。该方案完结了在尽可能少占用内存的条件下，在功率可以承受的范围内进行大批量的数据导出。这是一个可复用 ; N : T 0 5 +的方案，类似的规划思路也可以运用于其他领域或许场景，不局限于数据导出。

文中demo项目的库房地址是：

Github：https://github.com/zjcscut/spring-bos k 9 ; 2 eot-guide/tree/master/ch10086-excel-exporG P % Rt

（本文完 c-2-d e-a-20200711 20:27 PM）

技术大众号《Throwable文摘》（id：throwU 6 I y V 5 + yable-doge），不定期推送笔者原创技术文章[ y } f y L F（绝不抄袭或许转载）：

本文运用 md2 ~ A ^nice 排版

百万级别数据Excel导出优化

条件

一些根本原理

仿真案例

小结

相关文章

Seata 分布式事务 XA 与 AT 全面解析

代码优雅之道——断言 + 统一异常处理

Kotlin 技术月报 | 2023 年 5 月

云计算必备的基础设施即代码工具集

作者信息