這篇文章不是標(biāo)題黨，下文會(huì)通過一個(gè)仿真例子分析如何優(yōu)化百萬級(jí)別數(shù)據(jù)Excel導(dǎo)出。
筆者負(fù)責(zé)維護(hù)的一個(gè)數(shù)據(jù)查詢和數(shù)據(jù)導(dǎo)出服務(wù)是一個(gè)相對(duì)遠(yuǎn)古的單點(diǎn)應(yīng)用，在上一次云遷移之后擴(kuò)展為雙節(jié)點(diǎn)部署，但是發(fā)現(xiàn)了服務(wù)經(jīng)常因?yàn)榇髷?shù)據(jù)量的數(shù)據(jù)導(dǎo)出頻繁Full GC，導(dǎo)致應(yīng)用假死無法響應(yīng)外部的請(qǐng)求。因?yàn)槟承┰?，該服?wù)只能夠「分配2GB的最大堆內(nèi)存」，下面的優(yōu)化都是以這個(gè)堆內(nèi)存極限為前提。通過查看服務(wù)配置、日志和APM定位到兩個(gè)問題：
- 啟動(dòng)腳本中添加了CMS參數(shù)，采用了CMS收集器，該收集算法對(duì)內(nèi)存的敏感度比較高，大批量數(shù)據(jù)導(dǎo)出容易瞬間打滿老年代導(dǎo)致Full GC頻繁發(fā)生。
- 數(shù)據(jù)導(dǎo)出的時(shí)候采用了一次性把目標(biāo)數(shù)據(jù)全部查詢出來再寫到流中的方式，大量被查詢的對(duì)象駐留在堆內(nèi)存中，直接打滿整個(gè)堆。
對(duì)于問題1咨詢過身邊的大牛朋友，直接把所有CMS相關(guān)的所有參數(shù)去掉，由于生產(chǎn)環(huán)境使用了JDK1.8，相當(dāng)于直接使用默認(rèn)的GC收集器參數(shù)-XX:+UseParallelGC，也就是Parallel Scavenge + Parallel Old的組合然后重啟服務(wù)。觀察APM工具發(fā)現(xiàn)Full GC的頻率是有所下降，但是一旦某個(gè)時(shí)刻導(dǎo)出的數(shù)據(jù)量十分巨大（例如查詢的結(jié)果超過一百萬個(gè)對(duì)象，超越可用的最大堆內(nèi)存），還是會(huì)陷入無盡的Full GC，也就是修改了JVM參數(shù)只起到了治標(biāo)不治本的作用。所以下文會(huì)針對(duì)這個(gè)問題（也就是問題2），通過一個(gè)仿真案例來分析一下如何進(jìn)行優(yōu)化。
一些基本原理
如果使用Java（或者說依賴于JVM的語言）開發(fā)數(shù)據(jù)導(dǎo)出的模塊，下面的偽代碼是通用的：
數(shù)據(jù)導(dǎo)出方法(參數(shù),輸出流[OutputStream]){ 1. 通過參數(shù)查詢需要導(dǎo)出的結(jié)果集 2. 把結(jié)果集序列化為字節(jié)序列 3. 通過輸出流寫入結(jié)果集字節(jié)序列 4. 關(guān)閉輸出流 } 復(fù)制代碼
一個(gè)例子如下：
@Data public 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(); } 復(fù)制代碼
針對(duì)不同的OutputStream實(shí)現(xiàn)，最終可以把數(shù)據(jù)導(dǎo)出到不同類型的目標(biāo)中，例如對(duì)于FileOutputStream而言相當(dāng)于把數(shù)據(jù)導(dǎo)出到文件中，而對(duì)于SocketOutputStream而言相當(dāng)于把數(shù)據(jù)導(dǎo)出到網(wǎng)絡(luò)流中（客戶端可以讀取該流實(shí)現(xiàn)文件下載）。目前B端應(yīng)用比較常見的文件導(dǎo)出都是使用后一種實(shí)現(xiàn)，基本的交互流程如下：
為了節(jié)省服務(wù)器的內(nèi)存，這里的返回?cái)?shù)據(jù)和數(shù)據(jù)傳輸部分可以設(shè)計(jì)為分段處理，也就是查詢的時(shí)候考慮把查詢?nèi)康慕Y(jié)果這個(gè)思路改變?yōu)槊看沃徊樵儾糠謹(jǐn)?shù)據(jù)，直到得到全量的數(shù)據(jù)，每批次查詢的結(jié)果數(shù)據(jù)都寫進(jìn)去OutputStream中。
這里以MySQL為例，可以使用類似于分頁查詢的思路，但是鑒于LIMIT offset,size的效率太低，結(jié)合之前的一些實(shí)踐，采用了一種「改良的"滾動(dòng)翻頁"的實(shí)現(xiàn)方式」（這個(gè)方式是前公司的某個(gè)架構(gòu)小組給出來的思路，后面廣泛應(yīng)用于各種批量查詢、數(shù)據(jù)同步、數(shù)據(jù)導(dǎo)出以及數(shù)據(jù)遷移等等場(chǎng)景，這個(gè)思路肯定不是首創(chuàng)的，但是實(shí)用性十分高），注意這個(gè)方案要求表中包含一個(gè)有自增趨勢(shì)的主鍵，單條查詢SQL如下：
SELECT * FROM tableX WHERE id > #{lastBatchMaxId} [其他條件] ORDER BY id [ASC|DESC](這里一般選用ASC排序) LIMIT ${size} 復(fù)制代碼
把上面的SQL放進(jìn)去前一個(gè)例子中，并且假設(shè)訂單表使用了自增長(zhǎng)整型主鍵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(); } 復(fù)制代碼
「上面這個(gè)示例就是百萬級(jí)別數(shù)據(jù)Excel導(dǎo)出優(yōu)化的核心思路」。查詢和寫入輸出流的邏輯編寫在一個(gè)死循環(huán)中，因?yàn)椴樵兘Y(jié)果是使用了自增主鍵排序的，而屬性lastBatchMaxId則存放了本次查詢結(jié)果集中的最大id，同時(shí)它也是下一批查詢的起始id，這樣相當(dāng)于基于id和查詢條件向前滾動(dòng)，直到查詢條件不命中任何記錄返回了空列表就會(huì)退出死循環(huán)。而limit字段則用于控制每批查詢的記錄數(shù)，可以按照應(yīng)用實(shí)際分配的內(nèi)存和每批次查詢的數(shù)據(jù)量考量設(shè)計(jì)一個(gè)合理的值，這樣就能讓單個(gè)請(qǐng)求下常駐內(nèi)存的對(duì)象數(shù)量控制在limit個(gè)從而使應(yīng)用的內(nèi)存使用更加可控，避免因?yàn)椴l(fā)導(dǎo)出導(dǎo)致堆內(nèi)存瞬間被打滿。
?這里的滾動(dòng)翻頁方案遠(yuǎn)比LIMIT offset,size效率高，因?yàn)榇朔桨该看尾樵兌际亲罱K的結(jié)果集，而一般的分頁方案使用的LIMIT offset,size需要先查詢，后截?cái)唷?/p> ?
仿真案例
某個(gè)應(yīng)用提供了查詢訂單和導(dǎo)出記錄的功能，表設(shè)計(jì)如下：
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 '創(chuàng)建人', `editor` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '修改人', `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間', `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本號(hào)', `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '軟刪除標(biāo)識(shí)', `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 '支付時(shí)間', `order_status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài),0:處理中,1:支付成功,2:支付失敗', UNIQUE uniq_order_id (`order_id`), INDEX idx_payment_time (`payment_time`) ) COMMENT '訂單表'; 復(fù)制代碼
現(xiàn)在要基于支付時(shí)間段導(dǎo)出一批訂單數(shù)據(jù)，先基于此需求編寫一個(gè)簡(jiǎn)單的SpringBoot應(yīng)用，這里的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> 復(fù)制代碼
模擬寫入200W條數(shù)據(jù)，生成數(shù)據(jù)的測(cè)試類如下：
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(); } } 復(fù)制代碼
基于JdbcTemplate編寫DAO類OrderDao：
@RequiredArgsConstructor @Repository public 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; }); } } 復(fù)制代碼
編寫服務(wù)類OrderService：
@Data public class OrderDTO { @ExcelIgnore private Long id; @ExcelProperty(value = "訂單號(hào)", order = 1) private String orderId; @ExcelProperty(value = "金額", order = 2) private BigDecimal amount; @ExcelProperty(value = "支付時(shí)間", order = 3) private String paymentTime; @ExcelProperty(value = "訂單狀態(tài)", order = 4) private String orderStatus; } @Service @RequiredArgsConstructor public 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()); } } 復(fù)制代碼
最后編寫控制器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", "訂單支付數(shù)據(jù)", 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); } } } } 復(fù)制代碼
這里為了方便，把一部分業(yè)務(wù)邏輯代碼放在控制器層編寫，實(shí)際上這是不規(guī)范的編碼習(xí)慣，這一點(diǎn)不要效仿。添加配置和啟動(dòng)類之后，通過請(qǐng)求http://localhost:10086/order/export?paymentDateTimeStart=2020-07-01 00:00:00&paymentDateTimeEnd=2020-07-16 00:00:00測(cè)試導(dǎo)出接口，某次導(dǎo)出操作后臺(tái)輸出日志如下：
導(dǎo)出數(shù)據(jù)耗時(shí):29733 ms,start:2020-07-01 00:00:00,end:2020-07-16 00:00:00 復(fù)制代碼
導(dǎo)出成功后得到一個(gè)文件（連同表頭一共1031540行）：
小結(jié)
這篇文章詳細(xì)地分析大數(shù)據(jù)量導(dǎo)出的性能優(yōu)化，最要側(cè)重于內(nèi)存優(yōu)化。該方案實(shí)現(xiàn)了在盡可能少占用內(nèi)存的前提下，在效率可以接受的范圍內(nèi)進(jìn)行大批量的數(shù)據(jù)導(dǎo)出。這是一個(gè)可復(fù)用的方案，類似的設(shè)計(jì)思路也可以應(yīng)用于其他領(lǐng)域或者場(chǎng)景，不局限于數(shù)據(jù)導(dǎo)出。
