Mybatis Plus batch insert performance optimization, very practical!

Click on the “Java base” above, select “Set as star”

Be a positive person, not a positive waste person!

Update articles every day at 14:00, lose a million bits of hair every day…

Source code boutique column

  • Original | Java 2021 Super God Road, very liver~

  • An open source project with detailed annotations in Chinese

  • RPC framework Dubbo source code analysis

  • Network application framework Netty source code analysis

  • Message middleware RocketMQ source code analysis

  • Database middleware Sharding-JDBC and MyCAT source code analysis

  • Job Scheduling Middleware Elastic-Job Source Code Analysis

  • Distributed transaction middleware TCC-Transaction source code analysis

  • Eureka and Hystrix source code analysis

  • Java Concurrency Source Code

Source: blog.csdn.net/weixin_42194695/

article/details/126349842

  • This article mainly focuses on how to improve the efficiency of batch insertion.

  • Next, the real batch insertion is realized through the SQL injector

    • default sql injector

    • Inherit DefaultSqlInjector custom sql injector

    • Inject a custom sql injector into the Mybatis container

    • Inherit BaseMapper to add custom methods

    • The corresponding mapper layer interface inherits the above custom mapper

    • add batch insert

  • The next most important test performance

b3e1712cf21479bba4ad6454024c64b1.png

Background: The background of the Internet of Things platform, the sensor acquisition frequency has reached 1000Hz, and more than 100 tables have been divided out, but mysql is still fried. At present, the amount of data in a single table is around 1,000, and the data is pulled from Kafka for asynchronous batch insertion. The amount of data inserted is 1,500 each time. There is no problem during the test. As a result, the Kafka server hangs up immediately after going online. The server has accumulated tens of gigabytes of data, and then looked at the production environment log, and found that the last single batch insertion time was fixed at more than 10 seconds, or even more than 20 seconds, and Kafka directly kicked the consumer out of the consumer group… so the Kafka message kept going Without consumption, the total weight caused Kafka data to accumulate and hang up…

Under such circumstances: The solution adopted is nothing more than sub-database and sub-table, reducing the amount of data in a single table, reducing the pressure on the database; improving the efficiency of batch insertion and increasing the consumption speed of consumers.

This article mainly focuses on how to improve the efficiency of batch insertion.

The batch insertion method of mybatisplus used: saveBatch(), I have seen online saying that adding rewriteBatchedStatements=true to the jdbc url path can only be done at the bottom of mysql Turn on real batch insert mode.

Ensure that the driver version 5.1.13 or later can achieve high-performance batch insertion. By default, the MySQL JDBC driver ignores the executeBatch() statement, breaks up a group of SQL statements we expect to be executed in batches, and sends them to the MySQL database one by one. Batch insertion is actually a single insertion, directly resulting in lower performance. Only when the rewriteBatchedStatements parameter is set to true, the driver will execute SQL in batches for you. In addition, this option is valid for INSERT/UPDATE/DELETE.

But I have added it before, and the data table is currently not indexed. Even if it is 1500 batches inserted under the data volume of 1000 to w, it is impossible to consume 20 seconds, so the contradiction is transferred to the saveBatch method. Use Version: V3.4.3.4

View source code:

public boolean saveBatch(Collection<T> entityList, int batchSize) {
     String sqlStatement = this. getSqlStatement(SqlMethod. INSERT_ONE);
     return this. executeBatch(entityList, batchSize, (sqlSession, entity) -> {
         sqlSession.insert(sqlStatement, entity);
     });
 }
protected <E> boolean executeBatch(Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
        return SqlHelper. executeBatch(this. entityClass, this. log, list, batchSize, consumer);
    }
public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
    Assert.isFalse(batchSize < 1, "batchSize must not be less than one", new Object[0]);
    return !CollectionUtils.isEmpty(list) & amp; & amp; executeBatch(entityClass, log, (sqlSession) -> {
        int size = list. size();
        int i = 1;

        for(Iterator var6 = list.iterator(); var6.hasNext(); + + i) {
            E element = var6. next();
            consumer. accept(sqlSession, element);
            if (i % batchSize == 0 || i == size) {
                sqlSession. flushStatements();
            }
        }

    });
}

Finally came to the executeBatch() method, you can see that this is obviously inserted in a loop one by one, through sqlSession.flushStatements() divide the insert statements one by one Batches are submitted, and it is the same sqlSession, which has a certain performance improvement compared to traversing the collection loop insert, but this is not a real batch insert at the sql level.

After consulting the relevant documents, I found that mybatisPlus provides a sql injector, and we can customize the method to meet the actual development needs of the business.

sql injection organ network

  • https://baomidou.com/pages/42ea4a/

Official example of sql injector

  • https://gitee.com/baomidou/mybatis-plus-samples/tree/master/mybatis-plus-sample-deluxe

The default injectable methods provided under the core package of mybtisPlus are:

4513bc6178f6ca8da61ba7d47a22dd41.png

Under the extension package, mybatisPlus also provides us with an extensible injectable method:

36e68bbfeb43042208d8fa3643fc7330.png

  • AlwaysUpdateSomeColumnById : Update each field according to the Id, the full update does not ignore the null field, and solve the problem that the updateById in mybatis-plus will automatically ignore the null value field in the entity by default and not update;

  • InsertBatchSomeColumn : Real batch insert, realize batch insert through single SQL insert statement;

  • Upsert : Update or insert, judge whether to perform update or delete according to the unique constraint, which is equivalent to providing support for insert on duplicate key update.

It can be found that mybatisPlus has already provided the InsertBatchSomeColumn method, we only need to add this method to our sql injector.

public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
    KeyGenerator keyGenerator = NoKeyGenerator. INSTANCE;
    SqlMethod sqlMethod = SqlMethod. INSERT_ONE;
    List<TableFieldInfo> fieldList = tableInfo. getFieldList();
    String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(true, false) + this.filterTableFieldInfo(fieldList, this.predicate, TableFieldInfo::getInsertSqlColumn, "");
    //----------------------------------splice batch insert statement--------- --------------------------------
    String columnScript = "(" + insertSqlColumn. substring(0, insertSqlColumn. length() - 1) + ")";
    String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(true, "et.", false) + this.filterTableFieldInfo(fieldList, this.predicate, (i) -> {
        return i.getInsertSqlProperty("et.");
    }, "");
    insertSqlProperty = "(" + insertSqlProperty. substring(0, insertSqlProperty. length() - 1) + ")";
    String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", (String)null, "et", ",");
    //------------------------------------------------ ------------------------------------------
    String keyProperty = null;
    String keyColumn = null;
    if (tableInfo. havePK()) {
        if (tableInfo. getIdType() == IdType. AUTO) {
            keyGenerator = Jdbc3KeyGenerator. INSTANCE;
            keyProperty = tableInfo. getKeyProperty();
            keyColumn = tableInfo. getKeyColumn();
        } else if (null != tableInfo. getKeySequence()) {
            keyGenerator = TableInfoHelper.genKeyGenerator(this.getMethod(sqlMethod), tableInfo, this.builderAssistant);
            keyProperty = tableInfo. getKeyProperty();
            keyColumn = tableInfo. getKeyColumn();
        }
    }

    String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
    SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
    return this.addInsertMappedStatement(mapperClass, modelClass, this.getMethod(sqlMethod), sqlSource, (KeyGenerator)keyGenerator, keyProperty, keyColumn);
}

Background management system + user applet based on Spring Boot + MyBatis Plus + Vue & amp; Element, supports RBAC dynamic permissions, multi-tenancy, data permissions, workflow, three-party login, payment, SMS, mall and other functions

  • Project address: https://github.com/YunaiV/ruoyi-vue-pro

  • Video tutorial: https://doc.iocoder.cn/video/

Next, the real batch insertion will be realized through the SQL injector

Default sql injector

public class DefaultSqlInjector extends AbstractSqlInjector {
    public DefaultSqlInjector() {
    }

    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        if (tableInfo. havePK()) {
            return (List)Stream.of(new Insert(), new Delete(), new DeleteByMap(), new DeleteById(), new DeleteBatchByIds(), new Update(), new UpdateById(), new SelectById(), new SelectBatchByIds (), new SelectByMap(), new SelectCount(), new SelectMaps(), new SelectMapsPage(), new SelectObjs(), new SelectList(), new SelectPage()).collect(Collectors.toList());
        } else {
            this.logger.warn(String.format("%s ,Not found @TableId annotation, Cannot use Mybatis-Plus 'xxById' Method.", tableInfo.getEntityType()));
            return (List)Stream.of(new Insert(), new Delete(), new DeleteByMap(), new Update(), new SelectByMap(), new SelectCount(), new SelectMaps(), new SelectMapsPage(), new SelectObjs (), new SelectList(), new SelectPage()). collect(Collectors. toList());
        }
    }
}

Inherit DefaultSqlInjectorcustom sql injector

/**
 * @author zhmsky
 * @date 2022/8/15 15:13
 */
public class MySqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super. getMethodList(mapperClass);
        //Fields that are automatically populated when updating, no need to insert values
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }
}

Inject a custom sql injector into the Mybatis container

/**
 * @author zhmsky
 * @date 2022/8/15 15:15
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MySqlInjector sqlInjector() {
        return new MySqlInjector();
    }
}

Inherit BaseMapper to add custom methods

/**
 * @author zhmsky
 * @date 2022/8/15 15:17
 */
public interface CommonMapper<T> extends BaseMapper<T> {
    /**
     * Real batch insert
     * @param entityList
     * @return
     */
    int insertBatchSomeColumn(List<T> entityList);
}

The corresponding mapper layer interface inherits the above custom mapper

/*
 * @author zhmsky
 * @since 2021-12-01
 */
@Mapper
public interface UserMapper extends CommonMapper<User> {

}

Finally, directly call the insertBatchSomeColumn() method of UserMapper to achieve real batch insertion.

@Test
void contextLoads() {

    for (int i = 0; i < 5; i ++ ) {
        User user = new User();
        user.setAge(10);
        user.setUsername("zhmsky");
        user.setEmail("[email protected]");
        userList. add(user);
    }
    long l = System. currentTimeMillis();
    userMapper.insertBatchSomeColumn(userList);
    long l1 = System. currentTimeMillis();
    System.out.println("-------------------:" + (l1-l));
    userList. clear();
}

View the log output information and observe the executed sql statement,

a6a3e31a12939af57c275b33118bb2fb.png

It is found that this is the real batch insert at the sql level.

However, it is not over here. The insertBatchSomeColumn method officially provided by mybatisPlus does not support batch insertion, that is, how many are directly inserted all at once, which may cause the final sql splicing statement to be very long. It exceeds the limit of mysql, so we have to implement a batch batch insert method similar to saveBatch.

Add batch insert

Imitate the original saveBatch method:

* @author zhmsky
 * @since 2021-12-01
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Override
    @Transactional(rollbackFor = {Exception. class})
    public boolean saveBatch(Collection<User> entityList, int batchSize) {
        try {
            int size = entityList. size();
            int idxLimit = Math.min(batchSize, size);
            int i = 1;
            //Save the data set submitted by a single batch
            List<User> oneBatchList = new ArrayList<>();
            for (Iterator<User> var7 = entityList.iterator(); var7.hasNext(); + + i) {
                User element = var7. next();
                oneBatchList. add(element);
                if (i == idxLimit) {
                    baseMapper.insertBatchSomeColumn(oneBatchList);
                    //The collection data needs to be cleared after each submission
                    oneBatchList. clear();
                    idxLimit = Math.min(idxLimit + batchSize, size);
                }
            }
        } catch (Exception e) {
            log. error("saveBatch fail", e);
            return false;
        }
        return true;
    }
}

test:

@Test
void contextLoads() {

    for (int i = 0; i < 20; i ++ ) {
        User user = new User();
        user.setAge(10);
        user.setUsername("zhmsky");
        user.setEmail("[email protected]");
        userList. add(user);
    }
    long l = System. currentTimeMillis();
    userService. saveBatch(userList, 10);
    long l1 = System. currentTimeMillis();
    System.out.println("-------------------:" + (l1-l));
    userList. clear();
}

Output result:

8d809549de80e50295855a27aaa37e3d.png

The batch insert is satisfied, and it’s time to call it a day.

Background management system + user applet based on Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & amp; Element, supporting RBAC dynamic permissions, multi-tenancy, data permissions, workflow, three-party login, payment, SMS, mall and other functions

  • Project address: https://github.com/YunaiV/yudao-cloud

  • Video tutorial: https://doc.iocoder.cn/video/

The next most important test performance

e0bf2b44bf3e4e31dae6b6bc50899d88.png

The amount of data in the current data table is more than 100w. On this basis, the original saveBatch (fake batch insert) and insertBatchSomeColumn (real batch insert) are used for performance comparison—-(jdbc Both open rewriteBatchedStatements):

The original fake bulk insert:

@Test
  void insert(){
      for (int i = 0; i < 50000; i ++ ) {
          User user = new User();
          user.setAge(10);
          user.setUsername("zhmsky");
          user.setEmail("[email protected]");
          userList. add(user);
      }
      long l = System. currentTimeMillis();
      userService. saveBatch(userList, 1000);
      long l1 = System. currentTimeMillis();
      System.out.println("The original saveBatch method takes time: " + (l1-l));
  }

15cc53822d743bec8d8c68847f20e1f7.png

Custom insertBatchSomeColumn:

@Test
void contextLoads() {

    for (int i = 0; i < 50000; i ++ ) {
        User user = new User();
        user.setAge(10);
        user.setUsername("zhmsky");
        user.setEmail("[email protected]");
        userList. add(user);
    }
    long l = System. currentTimeMillis();
    userService. saveBatch(userList, 1000);
    long l1 = System. currentTimeMillis();
    System.out.println("The custom insertBatchSomeColumn method takes time: " + (l1-l));
    userList. clear();
}

323e4e317bfc89f7e880eb0073672cd7.png

Inserting 50,000 pieces of data in batches, the custom batch insertion time in the real sense has been reduced by about 3 seconds, and using insertBatchSomeColum to insert 1500 pieces of data in batches takes 650 milliseconds, which is already quite fast

09ae1c6d949546606a74357eedc681a6.png

Welcome to join my knowledge planet, discuss architecture and exchange source code together. How to join, Long press the QR code below:

c76cfa5fa43f482ed9f9a4248d797f44.png

The source code has been updated on Knowledge Planet and the analysis is as follows:

7f8ad8c24b26215cf005b400a97a082b.jpeg

62bc13c8dd88ceff1d12ca1383f68cf1.jpeg

126d20f0fb284887895ee30030d321c4.jpeg

662884b5158cf2d64585bd59f48ea3b6.jpeg

The recently updated series “Introduction to Taro SpringBoot 2.X” has more than 101 articles, covering MyBatis, Redis, MongoDB, ES, sub-database and sub-table, read-write separation, SpringMVC, Webflux, permissions, WebSocket, Dubbo, RabbitMQ, RocketMQ , Kafka, performance testing, etc.

Provides a SpringBoot example with nearly 3W lines of code, and an e-commerce microservice project with more than 6W lines of code.

How to get it: Click “Looking“, follow the official account and reply to 666 to receive, more content will be provided one after another.

If the article is helpful, please read it and forward it.
Thank you for your support (*^__^*)