Difference of Mybatis Two Different Batch Insertion Methods

2021-11-13 07:48:59
OfStack

Preface to the table of contents
Test
Spliced sql
batch insertion
Data comparison summary

Preface

In this paper, Mybatis is used for batch insertion, and the differences between two different insertion methods are compared.

Test

Bulk Insert Considerations:

1. Add the parameter allowMultiQueries=true when connecting to the database, which supports multi-statement execution and batch processing

2. Whether the database supports a large amount of data writing, set max_allowed_packet parameters to ensure the amount of data submitted in batches

Spliced sql


public void batchDemo() {
        long start = System.currentTimeMillis();
        List<User> list = new ArrayList<>();
        for (int i = 0; i < 5000; i++) {
            User user = new User();
            user.setId(UUID.randomUUID().toString());
            user.setName("feiyangyang");
            user.setPwd("feiyangyang");
            list.add(user);
        }
        userService.batchForeach(list);
        long end = System.currentTimeMillis();
        System.out.println("---------------" + (start - end) + "---------------");
    }


<insert id="batchForeach" parameterType="com.fyy.druid.entity.User">
    insert into
    user(id,`name`,pwd)
    values
    <foreach collection ="userList" item="user" separator =",">
        (#{user.id}, #{user.name}, #{user.pwd})
    </foreach>
</insert>

batch Insert


public void batchInsert() {
    long start = System.currentTimeMillis();
    SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH);
    UserService mapper = sqlSession.getMapper(UserService.class);

    for (int i = 0; i < 5000; i++) {
        User user = new User();
        user.setId(UUID.randomUUID().toString());
        user.setName("feiyangyang");
        user.setPwd("feiyangyang");
        mapper.batchInsert(user);
    }
    sqlSession.commit();
    long end = System.currentTimeMillis();
    System.out.println("---------------" + (start - end) + " ---------------");
}

Note: The batch mode also has its own problems. For example, during the Insert operation, there is no way to obtain the self-increasing id before the transaction is committed, which does not meet the business requirements in a certain type of situation

Data comparison

	拼接sql (ms)	batch插入 (ms)
500条	1744	639
2000条	26966	2473
5000条	173668	7382

Of Stack