“This is the 7th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Before talking about the function optimization I have done, I said the optimization of address book synchronization. For details, see the optimization of address book synchronization efficiency. I mentioned using Mybatis to insert data in batches, and insert the upper limit of 10,000 data into the table at a time. The interviewer had some questions about the one-time insertion of 10,000 pieces of data, and thought that such a large number of data could not be inserted. However, I did succeed in this function, and I was not sure about the specific upper limit of the one-time insertion, so I found time to do the following experiment.

Firstly, I set up SpringBoot+Mybatis project test by myself, and the steps are as follows

1. Build test projects

Idea constructs SpringBoot+MyBatis project

Gitee code: https://gitee.com/AJiSun/SpringBoot-MyBatis

File->New->Project

Dependencies: select a mysql dependency if you don’t want to add it to poM

Add the required POM

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2. 0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
Copy the code

Then it is to create a new folder, create the required files, my directory structure is as follows

Configuration in application.yml

server:
port: 7070
spring:
application:
  name: ajisun-mybatis
datasource:
  driver-class-name: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3306/ajisun_mybatis? useUnicode=true&characterEncoding=utf-8&useSSL=false&useInformationSchema=true
  username: root
  password: root
​
mybatis:
mapperLocations: classpath:mapper/*.xml
typeAliasesPackage: com.ajisun.coding.ajisunmybatis.entity
 # Enable hump naming
configuration:
    map-underscore-to-camel-case: true
Copy the code

Add annotation @mapperscan to the startup class (mapper can be scanned at startup)

@SpringBootApplication
@MapperScan("com.ajisun.coding.ajisunmybatis.mapper")
public class AjisunMybatisApplication {
public static void main(String[] args) { SpringApplication.run(AjisunMybatisApplication.class, args); }}Copy the code

Content in an entity class

public class SyncEmployee implements Serializable {
   private Long syncId;
   private Long syncCode;
   private String employeeNum;
   privateString imageUrl; ./ / set/get omitted
}
Copy the code

Mapper. In Java

public interface SyncEmployeeMapper {
   /** * query list *@return* /
   List<SyncEmployee> selectList(a);
}
Copy the code

Mapper XML content

<? xml version="1.0" encoding="UTF-8"? > <! DOCTYPE mapper PUBLIC"- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
​
<mapper namespace="com.ajisun.coding.ajisunmybatis.mapper.SyncEmployeeMapper">
   <select id="selectList" resultType="com.ajisun.coding.ajisunmybatis.entity.SyncEmployee">
      SELECT * FROM sync_employee;
   </select>
</mapper>
Copy the code

Contents of the Interface Service class

public interface SyncEmployeeService {
   /** * query list *@return* /
   List<SyncEmployee> selectList(a);
}
Copy the code

The contents of the class that implements the Interface Service

The @service annotation indicates that the class is a Service and will be scanned by SpringBoot.

@Service
public class SyncEmployeeServiceImpl implements SyncEmployeeService {
​
   @Autowired
   private SyncEmployeeMapper syncEmployeeMapper;
​
   /** * query list *@return* /
   @Override
   public List<SyncEmployee> selectList(a) {
       return syncEmployeeMapper.selectList();
  }
Copy the code

Contents of the interface file controller

@RestController
@RequestMapping("/user")
public class SyncEmployeeController {
​
   @Autowired
   private SyncEmployeeService syncEmployeeService;
​
   @GetMapping("/list")
   public ResponseEntity<List<SyncEmployee>>  list(){
       returnResponseEntity.ok(syncEmployeeService.selectList()); }}Copy the code

At this point you can start the port is configured in the yml 7070, access interface is [get] HTTP: / / http://localhost:7070/user/list

2. Build data and test

1. The table structure
CREATE TABLE `ajisun_mybatis`.`sync_employee`  (
  `sync_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sync_code` bigint(30) NOT NULL COMMENT 'Synchronize batch number',
  `employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Employee Code',
  `name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Name of Employee',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'the tenant ID',
  `email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Email',
  `mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Mobile phone',
  `inter_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT International code,
  `code_mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'International code + Mobile number',
  `gender` tinyint(4) NULL DEFAULT NULL,
  `cid` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Identity code',
  `status` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Employee status',
  `enabled_flag` tinyint(1) NULL DEFAULT NULL COMMENT 'Enabled status',
  `entry_date` datetime(0) NULL DEFAULT NULL COMMENT 'Staff Entry Time',
  `birthday` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT Date of birth,
  `ldap_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'No Ldap user',
  `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'password',
  `image_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'avatar',
  `sync_status` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'the sync status',
  `sync_message` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'the sync information'.PRIMARY KEY (`sync_id`) USING BTREE,
  INDEX `sync_emp_n1`(`sync_code`, `tenant_id`) USING BTREE,
  INDEX `sync_emp_n2`(`email`, `sync_code`, `tenant_id`) USING BTREE,
  INDEX `sync_emp_n3`(`mobile`, `sync_code`, `tenant_id`) USING BTREE,
  INDEX `sync_emp_n4`(`employee_num`, `tenant_id`, `sync_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Employee Synchronization Intermediate Table' ROW_FORMAT = Dynamic;
Copy the code

Add method to springBoot-Mybatis project (reference code, here only the main places)

Mapper. XML batch insert code
<insert id="batchInsertData">
      insert into sync_employee (sync_id,sync_code,employee_num,name,tenant_id, email,mobile,inter_code,code_mobile,gender,cid,status,enabled_flag, entry_date,birthday,ldap_flag,image_url,sync_status,sync_message)
      values
       <foreach collection="syncEmployeeList" item="sync" separator=",">
          (
          #{sync.syncId},
          #{sync.syncCode},
          #{sync.employeeNum},
          #{sync.name},
          #{sync.tenantId},
          #{sync.email},
          #{sync.mobile},
          #{sync.interCode},
          #{sync.codeMobile},
          #{sync.gender},
          #{sync.cid},
          #{sync.status},
          #{sync.enabledFlag},
          #{sync.entryDate},
          #{sync.birthday},
          #{sync.ldapFlag},
          #{sync.imageUrl},
          0,
          #{sync.syncMessage}
          )
       </foreach>
   </insert>
Copy the code
3. Code in service (loop the amount of data needed)
@Override
   public void batchInsertData(Long syncCode) {
       List<SyncEmployee> syncEmployeeList = new ArrayList<SyncEmployee>();
       for (int i=10000; i<20000; i++){ SyncEmployee ee =new SyncEmployee();
           ee.setSyncId(Long.valueOf(i));
           ee.setSyncCode(syncCode);
           ee.setEmail(i+"@qq.com");
           ee.setMobile("121000"+String.valueOf(+i));
           ee.setCodeMobile(ee.getInterCode()+"-"+ee.getMobile());
           ee.setEmployeeNum("ajisun"+i);
           ee.setEnabledFlag(1);
           ee.setGender(i%2);
           ee.setImageUrl("Http://ajisun.com/'s head. PNG");
           ee.setName(i+"");
           ee.setLdapFlag(i%2);
           ee.setEntryDate(new Date());
           ee.setStatus("ON");
           ee.setTenantId(0L);
           ee.setBirthday("1900-01-01");
           ee.setSyncMessage("new data "+i);
           syncEmployeeList.add(ee);
      }
       long start = System.currentTimeMillis();
       syncEmployeeMapper.batchInsertData(syncEmployeeList);
       long end = System.currentTimeMillis();
       System.out.println(end-start);
  }
Copy the code
4. After preparations are complete, start the operation

The above method is used to construct 10,000 pieces of data at a time, and then batch insert through SQL

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (4,879,714 > 4,194,304). You can change this valueon the server by setting the 'max_allowed_packet' variable.
Copy the code

Error: Error: the size of 10000 data is larger than the limit of mysql, but there is no problem with the development environment (check the development environment and the value is really large)

Check the size of max_allowed_packet in the current environment. The default size of the following version is 4M

mysql> show global variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC) mysql > select 4194304/1024/1024; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4194304/1024/1024 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 4.00000000 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Make this parameter larger (10M)

mysql> set global max_allowed_packet = 1024*1024*10; Query OK, 0 rows affected (0.01sec)Copy the code

The set command is temporary and needs to open a new session to take effect, restart the SpringBoot service and invoke the interface. Max_allowed_packet = 1024* 1024*10 you can also modify the mysql configuration file to make it permanent.

You can also modify the mysql configuration file to make it permanent max_allowed_packet = 1024* 1024*10

Calling the interface again succeeds

Continue to increase the amount of operation data, modify the loop condition (30,000 data), the total size of the data is about 14M

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (14,637,233 > 10,485,760). You can change this value on the server by setting the 'max_allowed_packet' variable.
Copy the code

The same max_allowed_packet error occurred as above, and the size of the packet exceeded the value of max_allowed_packet.

Therefore, after testing, it can be determined that how much data Mybatis can insert depends on mysql’s max_allowed_packet size limit, rather than its own limit.

3. Summary

This paper focuses on testing whether Mybatis has an impact on the volume of data inserted in batches, and it is found that it does not. It is mainly the limitation of mysql itself on the received data volume, which is controlled by parameter max_allowed_packet.

However, it is important to pay attention to efficiency problems when using Mybatis large data volume batch insert. This method is only tested and not recommended.

I am Mr. Ji, with output force input and continue to learn, continue to share technology series articles, as well as the whole network worth collecting good articles, welcome to pay attention to or pay attention to the public number, to be a continuous growth of technical people.

4. Historical articles in the practical issues series

(Also check out the Nuggets section.)

1. Easy tools: slow SQL analysis pt-query-digest;

2. Easy to use: pt-online-schame-change;

3. How to prevent messages from being whoring;

4. Use double index skillfully to avoid the problem that es index does not exist;

5. If the problem cannot be reproduced locally, debug it online.

6. Optimize the data synchronization efficiency of the organizational structure and speed up by 100 times;