性能优化：Excel批量导入几十万用户数据至数据库？

最新推荐文章于 2023-02-12 22:55:12 发布
最新推荐文章于 2023-02-12 22:55:12 发布
阅读量1.7k 收藏 17
点赞数 2
分类专栏： 性能优化 文章标签： java 数据库 spring poi
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ABLiZU/article/details/107562453
版权

如何实现：Excel批量导入几十万用户数据至数据库？

需求描述

生产项目当中管理员需要使用**Excel将大量的用户信息导入至数据库**当中。现网反馈导入速度过慢，需对原有导入逻辑进行代码优化。

问题描述

走读原有项目代码，定位到导入速度过慢问题归为如下几类：
  1. Excel文件读取：读取采用旧版本的POI实现，逐行进行数据合法性校验，降低文件读取速度；
  2. 数据校验：用户信息唯一性判断，用户所属用户组信息判断等数据校验时，数据库查询次数过多（导入50w用户就需要查询50w次数据库）；
  3. 数据入库：逐行插入数据库，与数据库交互次数过多；

解决方案

1.采用开源项目EasyExcel进行数据文件读取和错误文件生成，读取速度效果明显，支持xls、xlsx文件自动识别。
在这里插入图片描述
2.优化原有数据校验逻辑，将需要判断的数据加载至内存，在内存中完成导入数据的校验工作。
3.采用并行批量导入数据库的方式，加快入库速度。

代码实现

1. 导入相关依赖

	    <!-- poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>
		
		<!-- easyexcel -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.2.6</version>
		</dependency>

2. 实现监听器

package com.test.common.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.capitek.modules.mgmt.entity.ImportUser;
import com.capitek.modules.mgmt.entity.UserAutzEntity;
import com.capitek.modules.mgmt.entity.UserGroupsEntity;
import com.capitek.modules.mgmt.service.UserGroupsService;
import com.capitek.modules.sys.entity.SysUserEntity;
import com.capitek.modules.sys.service.SysUserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ImportUserDataListener extends AnalysisEventListener<ImportUser> {

    private Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 每隔规定数量存一次储数据库，然后清理list
     */
    private static final int BATCH_COUNT = 100000;
    private List<ImportUser> impUserlist = new ArrayList<ImportUser>();
    private List<ImportUser> impVaildUserlist = new ArrayList<ImportUser>();
    private List<ImportUser> impErrorUserlist = new ArrayList<ImportUser>();
    private List<UserAutzEntity> impUserAutzlist = new ArrayList<UserAutzEntity>();
    private List<String> permGroupIdList = new ArrayList<>();
    private Map<String, String> groupNameIdMap = new HashMap<String, String>();
    private Map<String, Integer> usernameRepeat = new HashMap<String, Integer>();
    private Map<String, Integer> ipAddrRepeat = new HashMap<String, Integer>();

    public ImportUserDataListener() {
    }

    /**
     * 每次创建Listener的时候需要把spring管理的类传进来
     */
    public ImportUserDataListener(UserGroupsService userGroupsService, SysUserService sysUserService) {
        //查詢所有的用戶組名称和用戶組ID，用于之后的数据校验
        List<UserGroupsEntity> groups = userGroupsService.queryAllGroupName();
        logger.info("查詢所有的用戶組名称和用戶組ID，用于之后的数据校验!");
        for (UserGroupsEntity ug : groups) {
            groupNameIdMap.put(ug.getGroupName(), ug.getGroupId());
        }
        //查询当前登录用户的用户组权限
        logger.info("查询当前登录用户的用户组权限!");
        SysUserEntity loginUser = ShiroUtils.getUserEntity();
        if(!loginUser.getUserId().equals(Constant.SUPER_ADMIN)){
            System.out.println("loginUser.getUserId():"+loginUser.getUserId());
            permGroupIdList = sysUserService.selectGroupIdList( loginUser.getUserId());
        }
    }

    /**
     * 每解析一条数据，调用此方法
     * 优化参考：解析数据的同时不要添加任何的判断逻辑，先将数据存入内存，之后进行数据筛选工作
     */
    @Override
    public void invoke(ImportUser user, AnalysisContext context) {
        //将合法有效的用户存入内存
        impUserlist.add(user);
    }

    /**
     * 所有数据解析完成之后会调用次方法
     * 优化建议：此处完成数据校验、数据入库、错误用户信息文件生成等工作
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        logger.info("数据读取完成！共读取数据：{}条。", impUserlist.size());
        logger.info("开始校验数据合法性！");
        /**
         * 遍历内存中的用户数据对象，进行必要的数据校验
         */
        for (ImportUser user : impUserlist) {
            //TODO:完善并检查属性判断逻辑是否完整
            //判断用户名
            if (user.getUserName() == null) {
                user.setErrorCause("用户名不能为空");
                impErrorUserlist.add(user);
                continue;
            }
            //判断导入文件中的用户名username是否重复
            if (usernameRepeat.containsKey(user.getUserName())) {
                user.setErrorCause("用户名重复，请重新填写");
                impErrorUserlist.add(user);
                continue;
            } else {
                usernameRepeat.put(user.getUserName(), 1);
            }
            //判断导入文件中的静态IP是否重复
            if (user.getFramedIpAddress() != null) {
                if (ipAddrRepeat.containsKey(user.getFramedIpAddress())) {
                    user.setErrorCause("静态IP重复，请重新填写");
                    impErrorUserlist.add(user);
                    continue;
                } else {
                    ipAddrRepeat.put(user.getFramedIpAddress(), 1);
                }
            }
            //判断导入的用户组是否存在
            if (user.getUserGroup() == null || user.getUserGroup().length() == 0) {
                user.setGroupId("default");//默认用户组
            } else {
                if (!groupNameIdMap.containsKey(user.getUserGroup())) {
                    user.setErrorCause("用户指定的组不存在");
                    impErrorUserlist.add(user);
                    continue;
                }
                user.setGroupId(groupNameIdMap.get(user.getUserGroup()));
            }
            //判断当前登录账户是否有权限插入填写的用户组
            if (!permGroupIdList.isEmpty() && !permGroupIdList.contains(user.getGroupId())) {
                user.setErrorCause("当前管理员没有对此用户组的操作权限");
                impErrorUserlist.add(user);
                continue;
            }
            //设置用户主键ID标识
            user.setUserId(java.util.UUID.randomUUID().toString().replace("-", ""));
            //存储通过数据校验的合法用户
            impVaildUserlist.add(user);
        }
        logger.info("数据合法性校验完毕！有效数据{}条，非法数据{}条.", impVaildUserlist.size(), impErrorUserlist.size());
        //并行批量插入数据库
        logger.info("开始插入数据");
        //注意此处使用了lambda语法，java8以上支持
        Insert2DBConsumer.insertData(impVaildUserlist(), ImportUserDataListener::insertUserToDBByJdbc);
        logger.info("数据成功入库");
        //生成导入错误文件
        if (!impErrorUserlist.isEmpty()) {
            recordErrors();
        }
        //TODO:清理集合中元素，释放内存
        impErrorUserlist.clear();
        impVaildUserlist.clear();
        impUserlist.clear();
        usernameRepeat.clear();
        ipAddrRepeat.clear();
        logger.info("释放批量导入临时占用内存，导入结束！");
    }

    /**
     * 利用原生JDBC批量存储至数据库
     */
    private void insertUserToDBByJdbc(List<ImportUser> impVaildUserlist) {
        // 利用JDBC批量导入数据库，注意主库与备库的事务控制
        Connection connection = null;
        PreparedStatement cmd = null;
        private final static String columnStr = "USER_ID,USER_NAME,GROUP_ID,USER_STATUS,CALLING_ID,USER_PASSWORD,CREATE_DATE,EXPIRE_DATE,"
            + "FULL_NAME,CONTACT_ADDR,CONTACT_POSTCODE,CONTACT_EMAIL,CONTACT_TEL,CONTACT_PHONE";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@192.16.31.25:1521:orcl1", "root", "root");
            connection.setAutoCommit(false);
            cmd = connection.prepareStatement("insert into USERS(" + columnStr + ") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            for(UsersEntity user: importUsers){
                try {
                    cmd.setString(1, user.getUserId());
                    cmd.setString(2, user.getUserName());
                    cmd.setString(3, user.getGroupId());
                    cmd.setInt(4, user.getUserStatus());
                    cmd.setString(5, user.getCallingId());
                    cmd.setString(6, user.getUserPassword());
                    cmd.setString(7, user.getCreateDate());
                    cmd.setString(8, user.getExpireDate());
                    cmd.setString(9, user.getFullName());
                    cmd.setString(10, user.getContactAddr());
                    cmd.setString(11, user.getContactPostcode());
                    cmd.setString(12, user.getContactEmail());
                    cmd.setString(13, user.getContactTel());
                    cmd.setString(14, user.getContactPhone());
                    cmd.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                connection.commit();
            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            // 关闭连接
            try {
                if (cmd != null) {
                    cmd.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        logger.info("存储数据库成功！");
    }

    /**
     * 生成错误文件
     */
    private void recordErrors() {
        logger.info("正在生成错误文件，无效数据共计{}条。"+impErrorUserlist.size());
        String fileName = "G:\\工作目录\\NMCP\\ErrorUsers" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ImportUser.class).sheet("模板").doWrite(impErrorUserlist);
        logger.info("错误文件生成结束！");
    }
}

3. 并行插入方法类

package com.test.common.utils;

import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

/**
 * 功能：利用并行流快速插入数据
 *
 */
public class Insert2DBConsumer {
    /**
     * 根据数据库性能调整分批次导入数据的数据条数
     */
    private final static int SIZE = 3000;

    /**
     * 调整并发线程数
     */
    static {
        System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "4");
    }

    /**
     * 分批次调用JDBC插入数据库
     *
     * @param list     插入的有效数据集合
     * @param consumer Java8消费型方法定义，直接使用 类名::方法名 引用的方式
     * @param <T>      插入的数据类型
     */
    public static <T> void insertData(List<T> list, Consumer<List<T>> consumer) {
        if (list == null || list.size() < 1) {
            return;
        }

        List<List<T>> streamList = new ArrayList<>();

        for (int i = 0; i < list.size(); i += SIZE) {
            int j = Math.min((i + SIZE), list.size());
            List<T> subList = list.subList(i, j);
            streamList.add(subList);
        }
        streamList.parallelStream().forEach(consumer);
    }
}

4. Excel文件读取

	/**
     * <p>1. 创建excel对应的实体对象 参照{@link ImportUser}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link IhmportUserDataListener}
     * <p>3. 直接读即可
     */
	String fileName = "F://usersdata20w.xlsx";
    // 需要指定读用ImportUser.class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, ImportUser.class, new ImportUserDataListener()).sheet().doRead();

最后说明

Mybatis也可以实现数据的批量插入方式，参考如下语法：

<insert id="insertList">
        insert into table(colom1, colom2)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            ( #{item.colom1}, #{item.colom2})
        </foreach>
</insert>

经过实际测试，导入相同量的数据，原生JDBC方式的导入速度较快与Mybatis的实现方式。

嘉木ii
关注
专栏目录
大数据量excel导入mysql_十万级百万级数据量的Excel文件导入并写入数据库
weixin_39578516的博客
01-19 2424
一.需求分析最近接到一个需求,导入十万级,甚至可能百万数据量的记录了车辆黑名单的Excel文件,借此机会分析下编码过程;首先将这个需求拆解,发现有三个比较复杂的问题:问题一:Excel文件导入后首先要被解析为存放对象的列表,数据量大的情况下可能会导致内存溢出,解析时间过长;问题二:插入数据库的时候,数据量大,写入的时间长问题三:要对数据库中的现有数据进项判断,不仅仅要做插入动作,还要将数据库的数据...
Spring boot开发从Excel表格导入大数据量（十万级/百万级）数据到数据库（MySQL）的工具
依然神话的博客
11-12 5302
首先创建一个项目，我这里项目名称为：utils-self 。可使用Spring Assistant 创建，如下图，选择 web、jdbc、mysql三个模块的依赖，点击next。 设置配置文件 application.properties，如下： server.port=8076 spring.datasource.url=jdbc:mysql://127.0.0.1:3306/li...
C#实现几十万级数据导出Excel及Excel各种操作实例
08-31
本篇文章主要介绍了C#实现几十万级数据导出Excel及Excel各种操作实例，这里整理了详细的代码,有需要的小伙伴可以参考下。
Excel30万数据导入SQL server数据库
Friendshiper的博客
07-16 329
public static void TransferData() { string sheetName = "a"; // string connectionString = "Data Source=.;Initial Catalog=Pujie;Integrated Security=True";//连接字符串 DataTab...
excel大量数据如何导入到oracle数据库中
11-27
excel大量数据如何导入到oracle数据库中
Mysql 批量插入大量数据的两种方案以及优缺点（分别是 5W 条数据和 10W 条数据）
qq_41824825的博客
03-18 1万+
Mysql 批量插入大量数据的两种方案以及优缺点（分别是 5W 条数据和 10W 条数据）
如何使用excel批量导入数据到数据库？看这一篇就够了！超详细
热门推荐
酷酷的馫博客
08-10 3万+
使用excel向数据库批量添加数据
Spring框架实现Excel批量导入数据
04-13
以上就是使用Spring框架实现Excel批量导入数据的基本步骤。通过这种方式，可以极大地提高数据录入的效率，降低手动操作的工作量，尤其适用于需要频繁处理大量数据的业务场景。在实际项目中，可以根据具体需求进行...
十万条数据从excel导入数据库方法思路
qq_36257411的博客
08-11 4155
项目经理给了一个15万条的excel数据让导入数据库用于测试。 然后我就直接用项目中的方法进行excel的导入，结果一秒钟才插入了两条。 经过一下午的百度以及和别人交流，从以下三个方面进行优化： 1.使用easyexcel代替poi，内存中可以存更多的数据，减少对excel的读取。 2.使用jdbc中的批量插入，减少对数据库的操作。 3.使用多线程，优化效率。 以下为代码: import java.math.BigDecimal; import com.alibaba.excel.annotation.E
C#.NET中如何批量插入大量数据到数据库中
09-03
在C#.NET中批量插入大量数据到数据库是一个常见的任务，特别是在处理诸如从Excel文件导入数据等场景时。这里，我们将探讨如何使用C#.NET高效地完成这个任务，并提供一个简单的示例来说明整个过程。 首先，我们需要...
一次性导入千万级数据到Mysql（附源码）
成长的足迹
05-19 3496
通过对比3种MySQL数据导入方式，分别对比万级、百万级、千万级导入性能，来实现如何高性能导入千万数据到MySQL。
数据库 一次插入几万条数据
anjing9345的博客
06-28 3408
最近做了一个功能， 操作的数据量特别大， 一次 往数据库 插入上万条数据。我用了 循环添加， 和EF封装的添加功能。 两次方式速度都非常慢，一万条数据都要近一分钟的时间。 在网上 查找资料后， 可以创建一个Datatable对象，然后把数据都赋值给这个Datatable对象的列。 然后Copy到数据库就行了。 下面这段代码是：实例化一个Datatable， 然后确定所...
300万数据导入导出优化方案，从80s优化到8s（实测）
程序员小明1024
09-28 3074
前景在项目开发中往往需要使用到数据的导入和导出，导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。写本文的背景是因为在工作中遇到了大数据的导入和导出，问题既然来了逃跑不如干掉它！！！只要这一次解决了，后期遇到同样的问题就好解决了。废话不多说，开始撸起来！！！1 传统POI的...
数据库百万级数据量的导入导出（excel）
最新发布
Ciao's blog
02-12 1697
数据库百万级数据量的导入导出（excel）
百万级Excel数据导入到Oracle数据库
y_yanghao的博客
03-18 3101
目的：将Excel数据导入到Oracle数据库中去（可以实现百万级数据），看了一下网上的，没有找到springboot+mybatis+Oracel实现的，只有自己写一个了。 首先要建立一个springboot项目，并连接到数据库。以前写过一个模板。springboot+Oracle模板 先来看看想要的做的模型 直接上代码： domain下的：peopel类 public class Peop...
POI导入大批量Excel数据，解决OOM问题（可支持几十万以上）
一茗道人 ~~ ~ 阿亮-的博客
10-09 2691
使用xlsx-streamer读取 .xlsx格式文件，可将部分数据按次缓存到内存中，已经读取的数据进行GC处理，解决OOM问题。只支持 Excel2007版本，不支持Excel2003版本。
Excel导入30万条数据和导出50万条数据方案
kangyuan520的专栏
05-06 5476
导入Excel30万数，导出50万Excel数据
关于EXCEL大批量数据导入校验，效率太慢的性能优化
Wicd的博客
04-12 1万+
下面的代码无法复制粘贴直接使用，主要记录一个性能优化的方法。 若有兴趣，可以认真阅读。 1.首先描述一下Excel导入的步骤 2.当Excel内有大批量的数据时，如何优化校验这个步骤。 我的处理方法是: * Excel性能优化方法 * 将Excel数据进行划分处理 * 1.根据某一列的属性,把Excel数据划分成...
高效方法：Excel数据批量导入MySQL数据库教程
在本文中，我们将深入探讨如何将Excel表格中的数据导入MySQL数据库，通过两种实用的方法实现数据迁移。第一种方法是手动操作，适合小型数据集且对数据库结构有清晰理解的情况。 **方法1：手动导入** 1. **数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值