這篇文章不是標題黨，下文會通過一個仿真例子分析如何優化百萬級別數據Excel導出。
筆者負責維護的一個數據查詢和數據導出服務是一個相對遠古的單點應用，在上一次雲遷移之後擴展為雙節點部署，但是發現了服務經常因為大數據量的數據導出頻繁Full GC，導致應用假死無法響應外部的請求。因為某些原因，該服務只能夠「分配2GB的最大堆內存」，下面的優化都是以這個堆內存極限為前提。通過查看服務配置、日誌和APM定位到兩個問題：
啟動腳本中添加了CMS參數，採用了CMS收集器，該收集算法對內存的敏感度比較高，大批量數據導出容易瞬間打滿老年代導致Full GC頻繁發生。數據導出的時候採用了一次性把目標數據全部查詢出來再寫到流中的方式，大量被查詢的對象駐留在堆內存中，直接打滿整個堆。對於問題1諮詢過身邊的大牛朋友，直接把所有CMS相關的所有參數去掉，由於生產環境使用了JDK1.8，相當於直接使用默認的GC收集器參數-XX:+UseParallelGC，也就是Parallel Scavenge + Parallel Old的組合然後重啟服務。觀察APM工具發現Full GC的頻率是有所下降，但是一旦某個時刻導出的數據量十分巨大（例如查詢的結果超過一百萬個對象，超越可用的最大堆內存），還是會陷入無盡的Full GC，也就是修改了JVM參數只起到了治標不治本的作用。所以下文會針對這個問題（也就是問題2），通過一個仿真案例來分析一下如何進行優化。
一些基本原理
如果使用Java（或者說依賴於JVM的語言）開發數據導出的模塊，下面的偽代碼是通用的：
數據導出方法(參數,輸出流[OutputStream]){ 1. 通過參數查詢需要導出的結果集 2. 把結果集序列化為字節序列 3. 通過輸出流寫入結果集字節序列 4. 關閉輸出流}複製代碼
一個例子如下：
@Datapublic static class Parameter{ private OffsetDateTime paymentDateTimeStart; private OffsetDateTime paymentDateTimeEnd;}public void export(Parameter parameter, OutputStream os) throws IOException { List<OrderDTO> result = orderDao.query(parameter.getPaymentDateTimeStart(), parameter.getPaymentDateTimeEnd()).stream() .map(order -> { OrderDTO dto = new OrderDTO(); ...... return dto; }).collect(Collectors.toList()); byte[] bytes = toBytes(result); os.write(bytes); os.close();}複製代碼
針對不同的OutputStream實現，最終可以把數據導出到不同類型的目標中，例如對於FileOutputStream而言相當於把數據導出到文件中，而對於SocketOutputStream而言相當於把數據導出到網絡流中（客戶端可以讀取該流實現文件下載）。目前B端應用比較常見的文件導出都是使用後一種實現，基本的交互流程如下：
為了節省伺服器的內存，這裡的返回數據和數據傳輸部分可以設計為分段處理，也就是查詢的時候考慮把查詢全量的結果這個思路改變為每次只查詢部分數據，直到得到全量的數據，每批次查詢的結果數據都寫進去OutputStream中。
這裡以MySQL為例，可以使用類似於分頁查詢的思路，但是鑑於LIMIT offset,size的效率太低，結合之前的一些實踐，採用了一種「改良的"滾動翻頁"的實現方式」（這個方式是前公司的某個架構小組給出來的思路，後面廣泛應用於各種批量查詢、數據同步、數據導出以及數據遷移等等場景，這個思路肯定不是首創的，但是實用性十分高），注意這個方案要求表中包含一個有自增趨勢的主鍵，單條查詢SQL如下：
SELECT * FROM tableX WHERE id > #{lastBatchMaxId} [其他條件] ORDER BY id [ASC|DESC](這裡一般選用ASC排序) LIMIT ${size}複製代碼
把上面的SQL放進去前一個例子中，並且假設訂單表使用了自增長整型主鍵id，那麼上面的代碼改造如下：
public void export(Parameter parameter, OutputStream os) throws IOException { long lastBatchMaxId = 0L; for (;;){ List<Order> orders = orderDao.query([SELECT * FROM t_order WHERE id > #{lastBatchMaxId} AND payment_time >= #{parameter.paymentDateTimeStart} AND payment_time <= #{parameter.paymentDateTimeEnd} ORDER BY id ASC LIMIT ${LIMIT}]); if (orders.isEmpty()){ break; } List<OrderDTO> result = orderDao.query([SELECT * FROM t_order]).stream() .map(order -> { OrderDTO dto = new OrderDTO(); ...... return dto; }).collect(Collectors.toList()); byte[] bytes = toBytes(result); os.write(bytes); os.flush(); lastBatchMaxId = orders.stream().map(Order::getId).max(Long::compareTo).orElse(Long.MAX_VALUE); } os.close();}複製代碼
「上面這個示例就是百萬級別數據Excel導出優化的核心思路」。查詢和寫入輸出流的邏輯編寫在一個死循環中，因為查詢結果是使用了自增主鍵排序的，而屬性lastBatchMaxId則存放了本次查詢結果集中的最大id，同時它也是下一批查詢的起始id，這樣相當於基於id和查詢條件向前滾動，直到查詢條件不命中任何記錄返回了空列表就會退出死循環。而limit欄位則用於控制每批查詢的記錄數，可以按照應用實際分配的內存和每批次查詢的數據量考量設計一個合理的值，這樣就能讓單個請求下常駐內存的對象數量控制在limit個從而使應用的內存使用更加可控，避免因為並發導出導致堆內存瞬間被打滿。
這裡的滾動翻頁方案遠比LIMIT offset,size效率高，因為此方案每次查詢都是最終的結果集，而一般的分頁方案使用的LIMIT offset,size需要先查詢，後截斷。
仿真案例
某個應用提供了查詢訂單和導出記錄的功能，表設計如下：
DROP TABLE IF EXISTS `t_order`;CREATE TABLE `t_order`( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵', `creator` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '創建人', `editor` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '修改人', `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間', `version` BIGINT NOT NULL DEFAULT 1 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` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '支付時間', `order_status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態,0:處理中,1:支付成功,2:支付失敗', UNIQUE uniq_order_id (`order_id`), INDEX idx_payment_time (`payment_time`)) COMMENT '訂單表';複製代碼
現在要基於支付時間段導出一批訂單數據，先基於此需求編寫一個簡單的SpringBoot應用，這裡的Excel處理工具選用Alibaba出品的EsayExcel，主要依賴如下：
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId></dependency><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.18</version></dependency><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version></dependency>複製代碼
模擬寫入200W條數據，生成數據的測試類如下：
public class OrderServiceTest { private static final Random OR = new Random(); private static final Random AR = new Random(); private static final Random DR = new Random(); @Test public void testGenerateTestOrderSql() throws Exception { HikariConfig config = new HikariConfig(); config.setUsername("root"); config.setPassword("root"); config.setJdbcUrl("jdbc:mysql://localhost:3306/local?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false"); config.setDriverClassName(Driver.class.getName()); HikariDataSource hikariDataSource = new HikariDataSource(config); JdbcTemplate jdbcTemplate = new JdbcTemplate(hikariDataSource); for (int d = 0; d < 100; d++) { String item = "('%s','%d','2020-07-%d 00:00:00','%d')"; StringBuilder sql = new StringBuilder("INSERT INTO t_order(order_id,amount,payment_time,order_status) VALUES "); for (int i = 0; i < 20_000; i++) { sql.append(String.format(item, UUID.randomUUID().toString().replace("-", ""), AR.nextInt(100000) + 1, DR.nextInt(31) + 1, OR.nextInt(3))).append(","); } jdbcTemplate.update(sql.substring(0, sql.lastIndexOf(","))); } hikariDataSource.close(); }}複製代碼
基於JdbcTemplate編寫DAO類OrderDao：
@RequiredArgsConstructor@Repositorypublic class OrderDao { private final JdbcTemplate jdbcTemplate; public List<Order> queryByScrollingPagination(long lastBatchMaxId, int limit, LocalDateTime paymentDateTimeStart, LocalDateTime paymentDateTimeEnd) { return jdbcTemplate.query("SELECT * FROM t_order WHERE id > ? AND payment_time >= ? AND payment_time <= ? " + "ORDER BY id ASC LIMIT ?", p -> { p.setLong(1, lastBatchMaxId); p.setTimestamp(2, Timestamp.valueOf(paymentDateTimeStart)); p.setTimestamp(3, Timestamp.valueOf(paymentDateTimeEnd)); p.setInt(4, limit); }, rs -> { List<Order> orders = new ArrayList<>(); while (rs.next()) { Order order = new Order(); order.setId(rs.getLong("id")); order.setCreator(rs.getString("creator")); order.setEditor(rs.getString("editor")); order.setCreateTime(OffsetDateTime.ofInstant(rs.getTimestamp("create_time").toInstant(), ZoneId.systemDefault())); order.setEditTime(OffsetDateTime.ofInstant(rs.getTimestamp("edit_time").toInstant(), ZoneId.systemDefault())); order.setVersion(rs.getLong("version")); order.setDeleted(rs.getInt("deleted")); order.setOrderId(rs.getString("order_id")); order.setAmount(rs.getBigDecimal("amount")); order.setPaymentTime(OffsetDateTime.ofInstant(rs.getTimestamp("payment_time").toInstant(), ZoneId.systemDefault())); order.setOrderStatus(rs.getInt("order_status")); orders.add(order); } return orders; }); }}複製代碼
編寫服務類OrderService：
@Datapublic class OrderDTO { @ExcelIgnore private Long id; @ExcelProperty(value = "訂單號", order = 1) private String orderId; @ExcelProperty(value = "金額", order = 2) private BigDecimal amount; @ExcelProperty(value = "支付時間", order = 3) private String paymentTime; @ExcelProperty(value = "訂單狀態", order = 4) private String orderStatus;}@Service@RequiredArgsConstructorpublic class OrderService { private final OrderDao orderDao; private static final DateTimeFormatter F = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); public List<OrderDTO> queryByScrollingPagination(String paymentDateTimeStart, String paymentDateTimeEnd, long lastBatchMaxId, int limit) { LocalDateTime start = LocalDateTime.parse(paymentDateTimeStart, F); LocalDateTime end = LocalDateTime.parse(paymentDateTimeEnd, F); return orderDao.queryByScrollingPagination(lastBatchMaxId, limit, start, end).stream().map(order -> { OrderDTO dto = new OrderDTO(); dto.setId(order.getId()); dto.setAmount(order.getAmount()); dto.setOrderId(order.getOrderId()); dto.setPaymentTime(order.getPaymentTime().format(F)); dto.setOrderStatus(OrderStatus.fromStatus(order.getOrderStatus()).getDescription()); return dto; }).collect(Collectors.toList()); }}複製代碼
最後編寫控制器OrderController：
@RequiredArgsConstructor@RestController@RequestMapping(path = "/order")public class OrderController { private final OrderService orderService; @GetMapping(path = "/export") public void export(@RequestParam(name = "paymentDateTimeStart") String paymentDateTimeStart, @RequestParam(name = "paymentDateTimeEnd") String paymentDateTimeEnd, HttpServletResponse response) throws Exception { String fileName = URLEncoder.encode(String.format("%s-(%s).xlsx", "訂單支付數據", UUID.randomUUID().toString()), StandardCharsets.UTF_8.toString()); response.setContentType("application/force-download"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); ExcelWriter writer = new ExcelWriterBuilder() .autoCloseStream(true) .excelType(ExcelTypeEnum.XLSX) .file(response.getOutputStream()) .head(OrderDTO.class) .build(); // xlsx文件上上限是104W行左右,這裡如果超過104W需要分Sheet WriteSheet writeSheet = new WriteSheet(); writeSheet.setSheetName("target"); long lastBatchMaxId = 0L; int limit = 500; for (; ; ) { List<OrderDTO> list = orderService.queryByScrollingPagination(paymentDateTimeStart, paymentDateTimeEnd, lastBatchMaxId, limit); if (list.isEmpty()) { writer.finish(); break; } else { lastBatchMaxId = list.stream().map(OrderDTO::getId).max(Long::compareTo).orElse(Long.MAX_VALUE); writer.write(list, writeSheet); } } }}複製代碼
這裡為了方便，把一部分業務邏輯代碼放在控制器層編寫，實際上這是不規範的編碼習慣，這一點不要效仿。添加配置和啟動類之後，通過請求http://localhost:10086/order/export?paymentDateTimeStart=2020-07-01 00:00:00&paymentDateTimeEnd=2020-07-16 00:00:00測試導出接口，某次導出操作後臺輸出日誌如下：
導出數據耗時:29733 ms,start:2020-07-01 00:00:00,end:2020-07-16 00:00:00複製代碼
導出成功後得到一個文件（連同表頭一共1031540行）：
小結
這篇文章詳細地分析大數據量導出的性能優化，最要側重於內存優化。該方案實現了在儘可能少佔用內存的前提下，在效率可以接受的範圍內進行大批量的數據導出。這是一個可復用的方案，類似的設計思路也可以應用於其他領域或者場景，不局限於數據導出。