• 给你 2 万条数据，怎么快速导入到 MySQL？写得太好了...

    一、前言

    前两天做了一个导入的功能，导入开始的时候非常慢，导入2w条数据要1分多钟，后来一点一点的优化，从直接把list怼进Mysql中，到分配把list导入Mysql中，到多线程把list导入Mysql中。

    时间是一点一点的变少了。非常的爽，最后变成了10s以内。

    下面就展示一下过程。

    二、直接把list怼进Mysql

    使用mybatis的批量导入操作：

    1. @Transactional(rollbackFor = Exception.class)
    2. public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
    3.  if (list == null || list.isEmpty()) {
    4.   return 0;
    5.  }
    6.  List<StudentEntity> studentEntityList = new LinkedList<>();
    7.  List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
    8.  List<AllusersEntity> allusersEntityList = new LinkedList<>();
    9.  
    10.  for (FreshStudentAndStudentModel freshStudentAndStudentModel : list) {
    11.  
    12.   EnrollStudentEntity enrollStudentEntity = new EnrollStudentEntity();
    13.   StudentEntity studentEntity = new StudentEntity();
    14.   BeanUtils.copyProperties(freshStudentAndStudentModel, studentEntity);
    15.   BeanUtils.copyProperties(freshStudentAndStudentModel, enrollStudentEntity);
    16.   String operator = TenancyContext.UserID.get();
    17.   String studentId = BaseUuidUtils.base58Uuid();
    18.   enrollStudentEntity.setId(BaseUuidUtils.base58Uuid());
    19.   enrollStudentEntity.setStudentId(studentId);
    20.   enrollStudentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
    21.   enrollStudentEntity.setOperator(operator);
    22.   studentEntity.setId(studentId);
    23.   studentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
    24.   studentEntity.setOperator(operator);
    25.   studentEntityList.add(studentEntity);
    26.   enrollStudentEntityList.add(enrollStudentEntity);
    27.  
    28.   AllusersEntity allusersEntity = new AllusersEntity();
    29.   allusersEntity.setId(enrollStudentEntity.getId());
    30.   allusersEntity.setUserCode(enrollStudentEntity.getNemtCode());
    31.   allusersEntity.setUserName(enrollStudentEntity.getName());
    32.   allusersEntity.setSchoolNo(schoolNo);
    33.   allusersEntity.setTelNum(enrollStudentEntity.getTelNum());
    34.   allusersEntity.setPassword(enrollStudentEntity.getNemtCode()); //密码设置为考生号
    35.   allusersEntityList.add(allusersEntity);
    36.  }
    37.   enResult = enrollStudentDao.insertAll(enrollStudentEntityList);
    38.   stuResult = studentDao.insertAll(studentEntityList);
    39.   allResult = allusersFacade.insertUserList(allusersEntityList);
    40.  
    41.  if (enResult > 0 && stuResult > 0 && allResult) {
    42.   return 10;
    43.  }
    44.  return -10;
    45. }

    Mapper.

    1. <insert id="insertAll" parameterType="com.dmsdbj.itoo.basicInfo.entity.EnrollStudentEntity">
    2.  insert into tb_enroll_student
    3.  <trim prefix="(" suffix=")" suffixOverrides=",">
    4.    id, 
    5.    remark, 
    6.    nEMT_aspiration, 
    7.    nEMT_code, 
    8.    nEMT_score, 
    9.    student_id, 
    10.    identity_card_id, 
    11.    level, 
    12.    major, 
    13.    name, 
    14.    nation, 
    15.    secondary_college, 
    16.    operator, 
    17.    sex, 
    18.    is_delete, 
    19.    account_address, 
    20.    native_place, 
    21.    original_place, 
    22.    used_name, 
    23.    pictrue, 
    24.    join_party_date, 
    25.    political_status, 
    26.    tel_num, 
    27.    is_registry, 
    28.    graduate_school, 
    29.    create_time, 
    30.    update_time  </trim>  
    31.  values
    32.  <foreach collection="list" item="item" index="index" separator=",">
    33.  (
    34.    #{item.id,jdbcType=VARCHAR},
    35.    #{item.remark,jdbcType=VARCHAR},
    36.    #{item.nemtAspiration,jdbcType=VARCHAR},
    37.    #{item.nemtCode,jdbcType=VARCHAR},
    38.    #{item.nemtScore,jdbcType=VARCHAR},
    39.    #{item.studentId,jdbcType=VARCHAR},
    40.    #{item.identityCardId,jdbcType=VARCHAR},
    41.    #{item.level,jdbcType=VARCHAR},
    42.    #{item.major,jdbcType=VARCHAR},
    43.    #{item.name,jdbcType=VARCHAR},
    44.    #{item.nation,jdbcType=VARCHAR},
    45.    #{item.secondaryCollege,jdbcType=VARCHAR},
    46.    #{item.operator,jdbcType=VARCHAR},
    47.    #{item.sex,jdbcType=VARCHAR},
    48.    0,
    49.    #{item.accountAddress,jdbcType=VARCHAR},
    50.    #{item.nativePlace,jdbcType=VARCHAR},
    51.    #{item.originalPlace,jdbcType=VARCHAR},
    52.    #{item.usedName,jdbcType=VARCHAR},
    53.    #{item.pictrue,jdbcType=VARCHAR},
    54.    #{item.joinPartyDate,jdbcType=VARCHAR},
    55.    #{item.politicalStatus,jdbcType=VARCHAR},
    56.    #{item.telNum,jdbcType=VARCHAR},
    57.    #{item.isRegistry,jdbcType=TINYINT},
    58.    #{item.graduateSchool,jdbcType=VARCHAR},
    59.    now(),
    60.    now()  
    61.  ) 
    62.  </foreach>    
    63. </insert>

    代码说明：

    底层的mapper是通过逆向工程来生成的，批量插入如下，是拼接成类似：insert into tb_enroll_student（）values （），（）…….（）;

    这样的缺点是，数据库一般有一个默认的设置，就是每次sql操作的数据不能超过4M。这样插入，数据多的时候，数据库会报错Packet for query is too large (6071393 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.，虽然我们可以通过

    类似 修改 my.ini 加上 max_allowed_packet =67108864，67108864=64M，默认大小4194304 也就是4M

    修改完成之后要重启mysql服务，如果通过命令行修改就不用重启mysql服务。

    完成本次操作，但是我们不能保证项目单次最大的大小是多少，这样是有弊端的。所以可以考虑进行分组导入。

    三、分组把list导入Mysql中

    同样适用mybatis批量插入，区别是对每次的导入进行分组计算，然后分多次进行导入：

    1. @Transactional(rollbackFor = Exception.class)
    2. public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
    3.  if (list == null || list.isEmpty()) {
    4.   return 0;
    5.  }
    6.  List<StudentEntity> studentEntityList = new LinkedList<>();
    7.  List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
    8.  List<AllusersEntity> allusersEntityList = new LinkedList<>();
    9.  
    10.  for (FreshStudentAndStudentModel freshStudentAndStudentModel : list) {
    11.  
    12.   EnrollStudentEntity enrollStudentEntity = new EnrollStudentEntity();
    13.   StudentEntity studentEntity = new StudentEntity();
    14.   BeanUtils.copyProperties(freshStudentAndStudentModel, studentEntity);
    15.   BeanUtils.copyProperties(freshStudentAndStudentModel, enrollStudentEntity);
    16.   String operator = TenancyContext.UserID.get();
    17.   String studentId = BaseUuidUtils.base58Uuid();
    18.   enrollStudentEntity.setId(BaseUuidUtils.base58Uuid());
    19.   enrollStudentEntity.setStudentId(studentId);
    20.   enrollStudentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
    21.   enrollStudentEntity.setOperator(operator);
    22.   studentEntity.setId(studentId);
    23.   studentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
    24.   studentEntity.setOperator(operator);
    25.   studentEntityList.add(studentEntity);
    26.   enrollStudentEntityList.add(enrollStudentEntity);
    27.  
    28.   AllusersEntity allusersEntity = new AllusersEntity();
    29.   allusersEntity.setId(enrollStudentEntity.getId());
    30.   allusersEntity.setUserCode(enrollStudentEntity.getNemtCode());
    31.   allusersEntity.setUserName(enrollStudentEntity.getName());
    32.   allusersEntity.setSchoolNo(schoolNo);
    33.   allusersEntity.setTelNum(enrollStudentEntity.getTelNum());
    34.   allusersEntity.setPassword(enrollStudentEntity.getNemtCode()); //密码设置为考生号
    35.   allusersEntityList.add(allusersEntity);
    36.  }
    37.  
    38.  int c = 100;
    39.  int b = enrollStudentEntityList.size() / c;
    40.  int d = enrollStudentEntityList.size() % c;
    41.  
    42.  int enResult = 0;
    43.  int stuResult = 0;
    44.  boolean allResult = false;
    45.  
    46.  for (int e = c; e <= c * b; e = e + c) {
    47.   enResult = enrollStudentDao.insertAll(enrollStudentEntityList.subList(e - c, e));
    48.   stuResult = studentDao.insertAll(studentEntityList.subList(e - c, e));
    49.   allResult = allusersFacade.insertUserList(allusersEntityList.subList(e - c, e));
    50.  }
    51.  if (d != 0) {
    52.   enResult = enrollStudentDao.insertAll(enrollStudentEntityList.subList(c * b, enrollStudentEntityList.size()));
    53.   stuResult = studentDao.insertAll(studentEntityList.subList(c * b, studentEntityList.size()));
    54.   allResult = allusersFacade.insertUserList(allusersEntityList.subList(c * b, allusersEntityList.size()));
    55.  }
    56.  
    57.  if (enResult > 0 && stuResult > 0 && allResult) {
    58.   return 10;
    59.  }
    60.  return -10;
    61. }

    代码说明：

    这样操作，可以避免上面的错误，但是分多次插入，无形中就增加了操作实践，很容易超时。所以这种方法还是不值得提倡的。

    再次改进，使用多线程分批导入。

    四、多线程分批导入Mysql

    依然使用mybatis的批量导入，不同的是，根据线程数目进行分组，然后再建立多线程池，进行导入。

    1. @Transactional(rollbackFor = Exception.class)
    2. public int addFreshStudentsNew(List<FreshStudentAndStudentModel> list, String schoolNo) {
    3.  if (list == null || list.isEmpty()) {
    4.   return 0;
    5.  }
    6.  List<StudentEntity> studentEntityList = new LinkedList<>();
    7.  List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
    8.  List<AllusersEntity> allusersEntityList = new LinkedList<>();
    9.  
    10.  list.forEach(freshStudentAndStudentModel -> {
    11.   EnrollStudentEntity enrollStudentEntity = new EnrollStudentEntity();
    12.   StudentEntity studentEntity = new StudentEntity();
    13.   BeanUtils.copyProperties(freshStudentAndStudentModel, studentEntity);
    14.   BeanUtils.copyProperties(freshStudentAndStudentModel, enrollStudentEntity);
    15.   String operator = TenancyContext.UserID.get();
    16.   String studentId = BaseUuidUtils.base58Uuid();
    17.   enrollStudentEntity.setId(BaseUuidUtils.base58Uuid());
    18.   enrollStudentEntity.setStudentId(studentId);
    19.   enrollStudentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
    20.   enrollStudentEntity.setOperator(operator);
    21.   studentEntity.setId(studentId);
    22.   studentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
    23.   studentEntity.setOperator(operator);
    24.   studentEntityList.add(studentEntity);
    25.   enrollStudentEntityList.add(enrollStudentEntity);
    26.  
    27.   AllusersEntity allusersEntity = new AllusersEntity();
    28.   allusersEntity.setId(enrollStudentEntity.getId());
    29.   allusersEntity.setUserCode(enrollStudentEntity.getNemtCode());
    30.   allusersEntity.setUserName(enrollStudentEntity.getName());
    31.   allusersEntity.setSchoolNo(schoolNo);
    32.   allusersEntity.setTelNum(enrollStudentEntity.getTelNum());
    33.   allusersEntity.setPassword(enrollStudentEntity.getNemtCode()); //密码设置为考生号
    34.   allusersEntityList.add(allusersEntity);
    35.  });
    36.  
    37.  
    38.  int nThreads = 50;
    39.  
    40.  int size = enrollStudentEntityList.size();
    41.  ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
    42.  List<Future<Integer>> futures = new ArrayList<Future<Integer>>(nThreads);
    43.  
    44.  for (int i = 0; i < nThreads; i++) {
    45.   final List<EnrollStudentEntity> EnrollStudentEntityImputList = enrollStudentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
    46.   final List<StudentEntity> studentEntityImportList = studentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
    47.   final List<AllusersEntity> allusersEntityImportList = allusersEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
    48.  
    49.   Callable<Integer> task1 = () -> {
    50.   studentSave.saveStudent(EnrollStudentEntityImputList,studentEntityImportList,allusersEntityImportList);
    51.    return 1;
    52.   };
    53.   futures.add(executorService.submit(task1));
    54.  }
    55.  executorService.shutdown();
    56.  if (!futures.isEmpty() && futures != null) {
    57.   return 10;
    58.  }
    59.  return -10;
    60. }

    代码说明：

    上面是通过应用ExecutorService 建立了固定的线程数，然后根据线程数目进行分组，批量依次导入。一方面可以缓解数据库的压力，另一个面线程数目多了，一定程度会提高程序运行的时间。

    缺点就是要看服务器的配置，如果配置好的话就可以开多点线程，配置差的话就开小点。

    五、小结

    通过使用这个操作真是不断的提高了，项目使用技巧也是不错。

     
    小伙伴们有兴趣想了解内容和更多相关学习资料的请点赞收藏+评论转发+关注我，后面会有很多干货。
    我有一些面试题、架构、设计类资料可以说是程序员面试必备！所有资料都整理到网盘了，需要的话欢迎下载！私信我回复【07】即可免费获取

     

    原文出处：www.shaoqun.com/a/1601159.html

  • 相关阅读:
    【ROS进阶篇】第一讲 常用API介绍
    随想录一刷Day49——动态规划
    如何解决Maven依赖冲突？
    NAT模式LVS负载均衡群集部署
    数据看板是什么？
    基于Xml方式的Bean的配置-Bean的作用范围scope配置
    2007-2019年36家上市银行绿色信贷余额、绿色信贷占比、资产收益率、不良贷款率等数据
    数据结构--》解锁数据结构中树与二叉树的奥秘（二）
    从入门到精通Ansible Playbook，一篇就够了
    Python:Unittest框架快速入门：用例、断言、夹具、套件、HTML报告、ddt数据驱动
  • 原文地址：https://blog.csdn.net/wadfdhsajd/article/details/125296541