How to Speed Up MySQL Batch Inserts with MyBatis, Partitioning, and Multithreading

Preface

Initially a bulk import of 20,000 records took more than a minute; by gradually optimizing the approach—from inserting the whole list at once, to grouping the list, and finally to multithreaded batch insertion—the execution time was reduced to under ten seconds.

Insert list directly into MySQL

Using MyBatis batch insert operation:

@Transactional(rollbackFor = Exception.class)
public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
    if (list == null || list.isEmpty()) {
        return 0;
    }
    List<StudentEntity> studentEntityList = new LinkedList<>();
    List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
    List<AllusersEntity> allusersEntityList = new LinkedList<>();
    for (FreshStudentAndStudentModel model : list) {
        EnrollStudentEntity enroll = new EnrollStudentEntity();
        StudentEntity student = new StudentEntity();
        BeanUtils.copyProperties(model, student);
        BeanUtils.copyProperties(model, enroll);
        String operator = TenancyContext.UserID.get();
        String studentId = BaseUuidUtils.base58Uuid();
        enroll.setId(BaseUuidUtils.base58Uuid());
        enroll.setStudentId(studentId);
        enroll.setIdentityCardId(model.getIdCard());
        enroll.setOperator(operator);
        student.setId(studentId);
        student.setIdentityCardId(model.getIdCard());
        student.setOperator(operator);
        studentEntityList.add(student);
        enrollStudentEntityList.add(enroll);
        AllusersEntity all = new AllusersEntity();
        all.setId(enroll.getId());
        all.setUserCode(enroll.getNemtCode());
        all.setUserName(enroll.getName());
        all.setSchoolNo(schoolNo);
        all.setTelNum(enroll.getTelNum());
        all.setPassword(enroll.getNemtCode()); // password set to candidate number
        allusersEntityList.add(all);
    }
    int enResult = enrollStudentDao.insertAll(enrollStudentEntityList);
    int stuResult = studentDao.insertAll(studentEntityList);
    boolean allResult = allusersFacade.insertUserList(allusersEntityList);
    if (enResult > 0 && stuResult > 0 && allResult) {
        return 10;
    }
    return -10;
}

Mapper.xml snippet (generated by reverse engineering):

<insert id="insertAll" parameterType="com.dmsdbj.itoo.basicInfo.entity.EnrollStudentEntity">
    insert into tb_enroll_student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        id, remark, nEMT_aspiration, nEMT_code, nEMT_score, student_id,
        identity_card_id, level, major, name, nation, secondary_college,
        operator, sex, is_delete, account_address, native_place, original_place,
        used_name, pictrue, join_party_date, political_status, tel_num,
        is_registry, graduate_school, create_time, update_time
    </trim>
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id,jdbcType=VARCHAR}, #{item.remark,jdbcType=VARCHAR},
         #{item.nemtAspiration,jdbcType=VARCHAR}, #{item.nemtCode,jdbcType=VARCHAR},
         #{item.nemtScore,jdbcType=VARCHAR}, #{item.studentId,jdbcType=VARCHAR},
         #{item.identityCardId,jdbcType=VARCHAR}, #{item.level,jdbcType=VARCHAR},
         #{item.major,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR},
         #{item.nation,jdbcType=VARCHAR}, #{item.secondaryCollege,jdbcType=VARCHAR},
         #{item.operator,jdbcType=VARCHAR}, #{item.sex,jdbcType=VARCHAR}, 0,
         #{item.accountAddress,jdbcType=VARCHAR}, #{item.nativePlace,jdbcType=VARCHAR},
         #{item.originalPlace,jdbcType=VARCHAR}, #{item.usedName,jdbcType=VARCHAR},
         #{item.pictrue,jdbcType=VARCHAR}, #{item.joinPartyDate,jdbcType=VARCHAR},
         #{item.politicalStatus,jdbcType=VARCHAR}, #{item.telNum,jdbcType=VARCHAR},
         #{item.isRegistry,jdbcType=TINYINT}, #{item.graduateSchool,jdbcType=VARCHAR},
         now(), now())
    </foreach>
</insert>

The generated SQL concatenates many value tuples, which can exceed MySQL's default max_allowed_packet (4 MB). The error message "Packet for query is too large (6071393 > 4194304)" can be resolved by increasing the variable, e.g., max_allowed_packet=64M in my.ini and restarting MySQL, or by avoiding oversized batches.

Partition list and import into MySQL

The same MyBatis batch insert is used, but the list is split into fixed‑size groups (e.g., 100 records) and each group is inserted sequentially:

@Transactional(rollbackFor = Exception.class)
public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
    if (list == null || list.isEmpty()) return 0;
    List<StudentEntity> studentEntityList = new LinkedList<>();
    List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
    List<AllusersEntity> allusersEntityList = new LinkedList<>();
    // ...populate the three lists as in the previous method...
    int c = 100; // batch size
    int b = enrollStudentEntityList.size() / c;
    int d = enrollStudentEntityList.size() % c;
    int enResult = 0, stuResult = 0;
    boolean allResult = false;
    for (int e = c; e <= c * b; e += c) {
        enResult = enrollStudentDao.insertAll(enrollStudentEntityList.subList(e - c, e));
        stuResult = studentDao.insertAll(studentEntityList.subList(e - c, e));
        allResult = allusersFacade.insertUserList(allusersEntityList.subList(e - c, e));
    }
    if (d != 0) {
        enResult = enrollStudentDao.insertAll(enrollStudentEntityList.subList(c * b, enrollStudentEntityList.size()));
        stuResult = studentDao.insertAll(studentEntityList.subList(c * b, studentEntityList.size()));
        allResult = allusersFacade.insertUserList(allusersEntityList.subList(c * b, allusersEntityList.size()));
    }
    if (enResult > 0 && stuResult > 0 && allResult) return 10;
    return -10;
}

This avoids the packet‑size error but introduces extra loop overhead and may cause timeout if the number of groups is large.

Multithreaded batch import into MySQL

Data is divided according to the number of threads, and each partition is processed concurrently using a fixed‑size thread pool:

@Transactional(rollbackFor = Exception.class)
public int addFreshStudentsNew(List<FreshStudentAndStudentModel> list, String schoolNo) {
    if (list == null || list.isEmpty()) return 0;
    List<StudentEntity> studentEntityList = new LinkedList<>();
    List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
    List<AllusersEntity> allusersEntityList = new LinkedList<>();
    list.forEach(model -> {
        // populate entities as before and add to the three lists
    });
    int nThreads = 50;
    int size = enrollStudentEntityList.size();
    ExecutorService executor = Executors.newFixedThreadPool(nThreads);
    List<Future<Integer>> futures = new ArrayList<>(nThreads);
    for (int i = 0; i < nThreads; i++) {
        final List<EnrollStudentEntity> partEnroll = enrollStudentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
        final List<StudentEntity> partStudent = studentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
        final List<AllusersEntity> partAll = allusersEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
        Callable<Integer> task = () -> {
            studentSave.saveStudent(partEnroll, partStudent, partAll);
            return 1;
        };
        futures.add(executor.submit(task));
    }
    executor.shutdown();
    if (!futures.isEmpty()) return 10;
    return -10;
}

This approach reduces database pressure and can further shorten runtime, but the optimal thread count depends on server resources; too many threads may degrade performance.

Conclusion

By progressively applying direct batch insertion, grouped insertion, and multithreaded insertion, the bulk‑import performance improved dramatically, demonstrating practical techniques for handling large data loads in Java‑MyBatis‑MySQL projects.