preface

Recently, xiaobian hands a bunch of projects, really very busy, a bunch of batch operations every day, update, import, add, delete, the company uses Mybatis-Plus operation SQL, used Mybatis-Plus partners must know that he has a lot of API for us to use, really cool, no longer need to write so many tedious SQL statements, SaveBatch is a batch insert function of Plus. We must have used it in our daily work. Let’s take a case to enter today’s topic.

RewriteBatchedStatements parameter

RewriteBatchedStatements parameter should be added to the URL of MySQL JDBC connection, and the driver version of 5.1.13 or above should be guaranteed to achieve high performance batch insert. By default, the MySQL JDBC driver ignores the executeBatch() statement, breaking up a set of SQL statements that we expect to be executed in batches and sending them to the MySQL database one at a time. A batch insert is actually a single insert, resulting in poor performance. If you set rewriteBatchedStatements to true, the driver will batch execute SQL for you. This option also works for INSERT, UPDATE, and DELETE

RewriteBatchedStatements =true:

2. Add employee information in batches

1. Insert saveBatch in batches

We loop 10,000 times, loading each instance employee object into the employee collection (List), then we call the saveBatch method of Mybatis Plus, passing in the List, and then we calculate the current function execution time at the beginning and end of the method.

@PostMapping("/addBath")
@ResponseBody
public CommonResult<Employee> addBath(a){
    long startTime = System.currentTimeMillis();
    List<Employee> list = new ArrayList<>();
    // Add 10000 employee data in a batch
    for (int i = 0; i < 10000; i++) {
        Employee employee = new Employee();
        employee.setName("DT test"+i);
        employee.setAge(20);
        employee.setSalary(9000D);
        employee.setDepartmentId(i);
        list.add(employee);
    }
    boolean batch = employeeService.saveBatch(list);
    if(batch){
        long endTime = System.currentTimeMillis();
        System.out.println("Function execution time:" + (endTime - startTime) + "ms");
        return CommonResult.success();
    }
    return CommonResult.error();
}
Copy the code

To make the test more detailed, I added this method several times. Here is the time of recording each time:

Add 10,000 employee data in a batch, and the test results are as follows:

The first time :(more than 2 seconds)

Second time :(nearly 2 seconds)

Third time :(nearly 2 seconds)

About 10 thousand pieces of data were added in about 2 seconds. At this time, we added a large number of 100 thousand pieces and tested again:

Add 100,000 employee data in batches, and the test results are as follows:

The first time :(19.341 seconds)

The second time :(18.298 seconds)

Immediately I was silly, 100,000 data batch to add about 20 seconds, this if add another 100,000, that does not crash, so I will find a solution, finally lock a database connection attribute rewriteBatchedStatements, below we will add the attribute to try the speed and passion.

2. Set rewriteBatchedStatements= True to batch insert

Let’s add rewriteBatchedStatements=true to the database connection and test the batch adding time.

rewriteBatchedStatements=true
Copy the code

Add 10,000 employee data in a batch, and the test results are as follows:

Qualitative leap ah! Cow force, you can see that the speed of batch processing is very powerful.

10000 data: 2s –>>> 0.5s

Add 100,000 employee data in batches, and the test results are as follows:

The effect is stunned?? Just take off.

10000 data: 20s –>>> 5s

conclusion

So, if you want to verify whether rewriteBatchedStatements have worked in your system, remember to use the larger batch, the above is my summary of this time, if there is a better, or more professional remember to leave your advice ~, please pay attention to more good articles: https://blog.csdn.net/qq_41107231?spm=1001.2014.3001.5343