How to Speed Up MySQL Batch Imports with MyBatis: From List to Multithreading

Introduction

The author initially faced a slow import process where inserting 20,000 records took more than a minute. By progressively optimizing the approach—direct list insertion, grouped insertion, and multithreaded batch insertion—the total time was reduced to under ten seconds.

1. Direct List Insertion into MySQL

Using MyBatis batch operations, the method

@Transactional(rollbackFor = Exception.class) public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo)

converts each model into StudentEntity, EnrollStudentEntity, and AllusersEntity, then inserts the three lists with a single insertAll call for each DAO.

@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) {
        // copy properties, set IDs, operators, etc.
        // add to the three lists
    }
    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;
}

The corresponding Mapper.xml uses a foreach to build a single INSERT statement with many value tuples.

<insert id="insertAll" parameterType="com.dmsdbj.itoo.basicInfo.entity.EnrollStudentEntity">
    INSERT INTO tb_enroll_student (id, remark, ... , update_time)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.id}, #{item.remark}, ... , now(), now())
    </foreach>
</insert>

This approach can hit MySQL's max_allowed_packet limit (default 4 MB), causing the error Packet for query is too large. Increasing the server variable (e.g., max_allowed_packet=64M) or restarting MySQL resolves the issue, but it is not a scalable solution.

2. Grouped List Insertion

To avoid the packet size problem, the list is split into smaller batches (e.g., 100 records per batch). Each batch is inserted sequentially using the same DAO methods.

@Transactional(rollbackFor = Exception.class)
public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
    // ...prepare entity lists as before...
    int batchSize = 100;
    int batchCount = enrollStudentEntityList.size() / batchSize;
    int remainder = enrollStudentEntityList.size() % batchSize;
    for (int i = batchSize; i <= batchSize * batchCount; i += batchSize) {
        enrollStudentDao.insertAll(enrollStudentEntityList.subList(i - batchSize, i));
        studentDao.insertAll(studentEntityList.subList(i - batchSize, i));
        allusersFacade.insertUserList(allusersEntityList.subList(i - batchSize, i));
    }
    if (remainder != 0) {
        // insert the remaining records
    }
    // return result
}

This reduces the risk of packet overflow but still performs many sequential database calls, which may increase overall execution time.

3. Multithreaded Batch Insertion

The final optimization creates a fixed thread pool (e.g., 50 threads). The entity lists are divided equally among the threads, and each thread executes its batch insert concurrently.

@Transactional(rollbackFor = Exception.class)
public int addFreshStudentsNew(List<FreshStudentAndStudentModel> list, String schoolNo) {
    if (list == null || list.isEmpty()) return 0;
    // build entity lists as before
    int nThreads = 50;
    int size = enrollStudentEntityList.size();
    ExecutorService executor = Executors.newFixedThreadPool(nThreads);
    List<Future<Integer>> futures = new ArrayList<>();
    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> partAllusers = allusersEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
        Callable<Integer> task = () -> {
            studentSave.saveStudent(partEnroll, partStudent, partAllusers);
            return 1;
        };
        futures.add(executor.submit(task));
    }
    executor.shutdown();
    if (!futures.isEmpty()) return 10;
    return -10;
}

This method alleviates database pressure by parallelizing inserts, but the optimal thread count depends on server resources; too many threads may cause contention or longer total runtime.

Conclusion

By moving from a naïve list‑to‑MySQL insertion to grouped batches and finally to multithreaded batch processing, the import time was dramatically reduced. Adjusting max_allowed_packet and tuning thread count are essential for stable, high‑performance bulk imports.