Оптимизация экспорта миллионов данных в Excel

задняя часть
Оптимизация экспорта миллионов данных в Excel

помещение

Эта статья не является заголовком, далее будет проанализировано, как оптимизировать миллионы данных на примере моделирования.Excelэкспорт.

Служба запроса данных и экспорта данных, за обслуживание которой отвечает автор, является относительно древним одноточечным приложением.После последней облачной миграции оно было расширено до развертывания с двумя узлами, но было обнаружено, что служба часто экспортировалась из-за к большому количеству данных.Full GC, что приводит к зависанию приложения и невозможности отвечать на внешние запросы. По какой-то причине сервис может только"распределять2GBмаксимальная куча памяти", следующие оптимизации основаны на этом ограничении памяти кучи. Просмотрев конфигурацию службы, журналы иAPMНаходятся две проблемы:

  1. Добавлен сценарий запускаCMSпараметры, используяCMSСборщик, алгоритм сбора очень чувствителен к памяти, а экспорт больших пакетов данных легко восполняет старость в одно мгновение.Full GCВстречается часто.
  2. При экспорте данных используется метод запроса всех целевых данных за один раз и записи их в поток.Большое количество запрашиваемых объектов находится в памяти кучи и непосредственно заполняет всю кучу.

Что касается вопроса 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. 关闭输出流
}

Пример выглядит следующим образом:

@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();
}

для разных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
@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;
                });
    }
}

написать класс обслуживанияOrderService:

@Data
public 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
@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());
    }
}

Наконец напишите контроллер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Ряд):

резюме

В этой статье подробно анализируется оптимизация производительности при экспорте больших объемов данных с акцентом на оптимизацию памяти. Эта схема реализует экспорт больших пакетов данных в приемлемом диапазоне эффективности при условии, что они занимают как можно меньше памяти. Это решение многократного использования, и аналогичные идеи дизайна также можно применять к другим полям или сценариям, не ограничиваясь экспортом данных.

в текстеdemoАдрес склада проекта: г.

  • Github: https://GitHub.com/zhaojun-export-bold/spring-boot-gui/tree/master/eat10086-excel-export

(Конец этой статьи c-2-d e-a-20200711 20:27)

Категории