Mysql百万级数据导出,你会吗?

2023年 12月 8日 64.0k 0

背景

小李今天去面试

面试官:我看了你的简历,里面提到,你做过excel表格的数据导入到数据库,也做过数据库的数据导出到excel,你能讲讲你是怎么做的吗?用到些什么技术?有没有遇到什么难点?

小李:我们使用poi组件,导入:提前定义好excel表格的模板,填充好数据后,读取excel的表格数据,然后写入到数据库。导入:从数据库读取数据,然后写入到excel,然后生成文件,返回给前端,其中没遇到什么问题,性能挺好

面试官:如果数据量比较大,比如10w、100w,你想想,会遇到什么问题?

小李:这......,吧啦吧啦说了一堆

面试官:今天就到这,后面联系

问题分析

对于excel的读取、写入,实际工作中经常使用,当数据量小的时候,一切都那么丝滑,如果随着系统运行,数据越来越多,性能问题就体现了,具体有什么问题呢?

应用的内存

数据量上来以后,大量的数据读取在内存里面操作,如果数据库字段很多,内存占用就很会大,对于Java应用来说,会产生大量的GC,影响整个系统的吞吐量,严重的内存溢出,甚至宕机

数据库

持续的对数据库操作,也会造成数据库很大的压力

数据导出: 从数据库查询数据,你是一次全部查出来?还是分页查询?分页查询随着页数的越来越深,是否有性能的问题,这些都会给数据库造成很大的压力

数据导入: 从excel读取大量数据后,对数据库的插入操作,你是单条插入,还是批量插入,批量多少合适,这些都要考虑,不同策略性能也不一样

同步&异步

导入导出,选择是同步,还是异步,对系统至关重要

同步

点击导入导出,一直等待系统给我提示成功还是失败,如果数据少的情况,效果杠杠的,没什么问题。如果数据量非常大,比如10w以上,数据处理时间过长,人为的以为系统出了问题,再次刷新点击,上个任务没完成,新的任务又来,人为造成并发,还是数据的大量读写,瞬间数据库崩掉,系统也挂掉。

异步

点击导入导出,后端插一条导出导出的任务,异步执行,提示用户,导出导入任务已经在执行,同时记录下任务,让用户去任务列表看执行的情况。这样避免长时间等待,让用户误以为失败了,持续点击,造成并发。

异步还能实现限流,每种任务类型,当前只能允许多少人使用。比如:商品导出,最多允许三个任务同时执行,用户点击导出,查询任务列表,如果有在执行的任务大于三个,就提示用户当前有任务在运行,请稍后。

image.png

技术选型

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。

特点:

  • 快速 快速的读取excel中的数据。
  • 简洁 映射excel和实体类,让代码变的更加简洁
  • 大文件 在读写大文件的时候使用磁盘做缓存,更加的节约内存

项目地址:easyexcel

场景

pom文件

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

Excel数据导入到数据库

10w数据的excel,一个sheet放入1w数据,一个sheet一个任务进行处理,可以使用线程池,提交到线程池进行处理

技术点:

  • 数据插入操作要批量,杜绝一条数据一次提交到数据,造成数据库的压力。
  • 多线程,一个线程读取一个sheet
  • 异步 防止响应过慢,用户误判系统出错,多次重复操作上传。用户点击上传,立马返回提示用户,数据在导入,请前往任务列表查看任务进度

我这里演示,就创建两个线程处理,代码如下:


/**
 * @BelongsProject: demo4
 * @BelongsPackage: com.example.demo
 * @Author: kb
 * @CreateTime: 2023-12-06  17:45
 * @Description: TODO
 * @Version: 1.0
 */

package com.example.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

/**
 * @ClassName DataDeal
 * @Description TODO
 * @Author Jiangnan Cui
 * @Date 2023/12/6 17:45
 * @Version 1.0
 */
@Slf4j
public class DataDealImport implements Runnable{
    private String sheet;

    private String fileName;

    DataDealImport(String fileName,String sheet){
        this.sheet = sheet;
        this.fileName = fileName;
    }

    @Override
    public void run() {
        log.info("{},开始执行任务",sheet);
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
            /**
             * 单次缓存的数据量
             */
            public static final int BATCH_COUNT = 100;
            /**
             *临时存储
             */
            private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(DemoData data, AnalysisContext context) {
                log.info("读取到一条数据{}", JSON.toJSONString(data));
                /**
                 * 校验数据通过,添加到缓存里面,如果不符合的,可以日志记录,或者写新建一个excel,添加一列,失败的原因
                 *
                 */
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                saveData();
            }

            /**
             * 加上存储数据库
             */
            private void saveData() {
                log.info("{}条数据,开始存储数据库!",cachedDataList.size());
                //批量插入到数据库
                log.info("存储数据库成功!");
            }
        }).sheet(sheet).doRead();

    }

    public static void main(String[] args) {
        DataDealImport dataDeal = new DataDealImport("demo.xlsx","sheet1");
        Thread t1 = new Thread(dataDeal);

        t1.start();

        DataDealImport dataDeal2 = new DataDealImport("demo.xlsx","sheet2");
        Thread t2= new Thread(dataDeal2);

        t2.start();
    }
}

导出数据库数据到Excel

技术点

  • 异步 防止响应过慢,用户误判系统出错,多次点击导出。用户点击导出,立马返回提示用户,数据在导出中,请前往任务列表查看任务进度,并且下载 这个真的太重要了,我们系统因为这个,时不时就被干崩掉

  • 数据库的深度分页优化 在偏移量非常大的时候,也就是翻页到很靠后的页面时,查询速度会变得越来越慢。

mysql深度分页优化

一般分页

在系统中需要进行分页操作时,我们通常会使用 LIMIT 加上偏移量的方式实现,语法格式如下

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在有对应索引的情况下,这种方式一般效率还不错。但它存在一个让人头疼的问题,在偏移量非常大的时候,也就是翻页到很靠后的页面时,查询速度会变得越来越慢。

原因:MySQL 并不是跳过 OFFSET 行,而是取 OFFSET+N 行,然后放弃前 OFFSET 行,最后返回 N 行,当 OFFSET 特别大的时候,效率就非常的低下

解决方案

记录主键id位置,避免使用offset,导致深度分页效率低下
先获取第一页数据,记录id的位置

select * from T limit 10

假如id是连续不中断的,那查询第一页后,当前id的位置为10

第二页查询
select * from T where id >10 limit 10
第三页查询
select * from T where id >20 limit 10
第四页查询
select * from T where id >30 limit 10
第五页查询
...............

以此类推,无论查询到多少页,性能都会很好

代码示例:

public static void main(String[] args) {
        SpringApplication app = new SpringApplication(WebApplication.class);
        Environment env = app.run(args).getEnvironment();
        logger.info("启动成功!!");
        logger.info("地址: thttp://127.0.0.1:{}", env.getProperty("server.port"));

        TUserMapper userMapper = SpringUtil.getBean(TUserMapper.class);
        //计算总的数据量
        int count = (int) userMapper.countByExample(null);


        //获取分页总数
        int queryCount = 50_0000;
        int pageCount = count % queryCount == 0 ? count / queryCount : count / queryCount + 1;

        //设置导出的文件名
        String fileName = "result.xlsx";
        //设置excel的sheet号码
        int sheetNo = 1;
        //设置第一个sheet的名字
        String sheetName = "sheet-" + sheetNo;


        long start = System.currentTimeMillis();
        // 创建writeSheet
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
        //记录每次分页查询的最大值
        Long maxId = null;

        //指定文件
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, TUser.class).build()) {
            //写入每一页分页查询的数据
            for (int i = 1; i <= pageCount; i++) {
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                long queryStart = System.currentTimeMillis();
                TUserExample userExample = new TUserExample();


                //如果是第一次则直接进行分页查询,反之基于上一次分页查询的分页定位实际偏移量,筛选前n条数据以达到分页效果
                if (i == 1) {
                    PageHelper.startPage(i, queryCount, false);
                } else if (maxId != null) {
                    userExample.createCriteria().andIdGreaterThan(maxId);
                    PageHelper.startPage(0, queryCount, false);
                }


                List<TUser> userList = userMapper.selectByExample(userExample);
                //更新下一次分页查询用的id
                if (CollUtil.isNotEmpty(userList)) {
                    maxId = userList.get(userList.size() - 1).getId();
                }

                long queryEnd = System.currentTimeMillis();
                logger.info("数据大小:{},写入sheet位置:{},耗时:{}", userList.size(), sheetName, queryEnd - queryStart);

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

                long writeEnd = System.currentTimeMillis();
                logger.info("本次写入耗时:{}", writeEnd - writeStart);

                //如果% 2 == 0,则说明一个sheet写入了50*2即100w的数据,需要创建新的sheet进行写入
                if (i % 2 == 0) {
                    sheetName = "sheet-" + (++sheetNo);
                    writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();
                    logger.info("写满一个sheet,切换到下一个sheet:{}", sheetName);
                }
            }
        }
        long total = System.currentTimeMillis() - start;
        logger.info("导出结束,总耗时:{}", total);

    }

总结

  • 导入导出,经量异步处理
  • 数据库,分页的优化,一定要
  • 多线程加速,注意下:easyexcel的写入,不支持多线程,读是支持的

参考文章

基于EasyExcel实现百万级别数据导出

写作不易,刚好你看到,刚好对你有帮助,麻烦点点赞,有问题的留言讨论。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论