一、前言

前两天做了一个导入的功能，导入开始的时候非常慢，导入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<>();
  10.     for (FreshStudentAndStudentModel freshStudentAndStudentModel : list) {
  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);
  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);
  41.     if (enResult > 0 && stuResult > 0 && allResult) {
  42.         return 10;
  43.     }
  44.     return -10;
  45.     }

Mapper.xml

  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<>();
  10.         for (FreshStudentAndStudentModel freshStudentAndStudentModel : list) {
  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);
  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.         }
  38.         int c = 100;
  39.         int b = enrollStudentEntityList.size() / c;
  40.         int d = enrollStudentEntityList.size() % c;
  42.         int enResult = 0;
  43.         int stuResult = 0;
  44.         boolean allResult = false;
  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.         }
  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<>();
  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);
  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.         });
  38.         int nThreads = 50;
  40.         int size = enrollStudentEntityList.size();
  41.         ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
  42.         List<Future<Integer>> futures = new ArrayList<Future<Integer>>(nThreads);
  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));
  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 建立了固定的线程数，然后根据线程数目进行分组，批量依次导入。一方面可以缓解数据库的压力，另一个面线程数目多了，一定程度会提高程序运行的时间。缺点就是要看服务器的配置，如果配置好的话就可以开多点线程，配置差的话就开小点。

参考：https://mp.weixin.qq.com/s/mm4JhJiD-WpmJiwT9qSPUw