background

In the actual development process of the project, in order to improve efficiency, we often adopt batch processing. For example, when we need to insert or update business tables in batches, we often adopt batch processing to improve efficiency.

Environment set up

Technology stack

  • springboot
  • mysql
  • jdbctemplate

Maven rely on

<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> The < version > 2.4.3 < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <! -- Database driven, can be deleted according to their own needs, Mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql-connector-java.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency>  <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion>  </exclusions> </dependency>Copy the code

The configuration file

spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/test? autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezo ne=CTT&nullCatalogMeansCurrent=true username: root password: root hikari: maximum-pool-size: 10 minimum-idle: 5Copy the code

Database table structure

CREATE TABLE `cbsd_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SERIAL_NUMBER` varchar(50) CHARACTER SET latin1 NOT NULL,
  `cbsdid` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
  `msg` varchar(1000) CHARACTER SET latin1 DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `sas_state` char(1) CHARACTER SET latin1 DEFAULT NULL COMMENT '0-unregistered/1-registered/2-idl/3-granted/4-authorized/5-grant_suspended',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24001 DEFAULT CHARSET=utf8;
Copy the code

The test code

Unit test 2000 data volume

@SpringBootTest(classes= {SasDpApplication.class}) public class Test { @Autowired JdbcTemplate jdbcTemplate; @org.junit.jupiter.api.Test public void testNoBatch() { List<CbsdLog> logList = Lists.newArrayList(); for (int i = 0; i < 2000; i++) { CbsdLog log=new CbsdLog(); log.setCbsdid(""+i); log.setSerialNumber(""+i); log.setCreateTime(LocalDateTime.now()); logList.add(log); } Long startTime=System.currentTimeMillis(); System.out.println(" startTime "+startTime); this.jdbcTemplate.batchUpdate("insert into cbsd_log (cbsdid,SERIAL_NUMBER,time) values (? ,? ,?) ", logList, 1000, new ParameterizedPreparedStatementSetter<CbsdLog>() { @Override public void setValues(PreparedStatement ps, CbsdLog log) throws SQLException { ps.setString(1,log.getCbsdid()); ps.setString(2,log.getSerialNumber()); ps.setDate(3,new Date(new java.util.Date().getTime())); }}); Long endTime=System.currentTimeMillis(); System.out.println(" endTime "+endTime); System.out.println(" elapsed time "+(endtime-startTime)); }}Copy the code

Results:

Start time 1629863033966 11:43:53 2021-08-25. 4952-979 the INFO [main]. Com zaxxer. Hikari. HikariDataSource: HikariPool-1 - Starting... The 2021-08-25 11:43:54. 4952-497 the INFO [main] com. Zaxxer. Hikari. HikariDataSource: HikariPool - 1 - Start completed. End Time 1629863040136 Consumed time 6170Copy the code

Unit test 2000 data volume increased &rewriteBatchedStatements=true

Results:

Start time 1629863142794 11:45:42 2021-08-25. 10488-807 the INFO [main]. Com zaxxer. Hikari. HikariDataSource: HikariPool-1 - Starting... The 2021-08-25 11:45:43. 10488-071 the INFO [main] com. Zaxxer. Hikari. HikariDataSource: HikariPool - 1 - Start completed. End Time 1629863143228 Consumed time 434Copy the code

It can be clearly seen through the above mentioned right that the storage speed is improved

Unit test 20000 data volume

Results:

Start time 1629863295120 11:48:15 2021-08-25. 3776-132 the INFO [main]. Com zaxxer. Hikari. HikariDataSource: HikariPool-1 - Starting... The 2021-08-25 11:48:15. 3776-419 the INFO [main] com. Zaxxer. Hikari. HikariDataSource: HikariPool - 1 - Start completed. End Time 1629863351409 Consumed Time 56289Copy the code

Unit test 20000 data volume increased &rewriteBatchedStatements=true

Results:

Start time 1629863245142 11:47:25 2021-08-25. 9432-154 the INFO [main]. Com zaxxer. Hikari. HikariDataSource: HikariPool-1 - Starting... The 2021-08-25 11:47:25. 9432-404 the INFO [main] com. Zaxxer. Hikari. HikariDataSource: HikariPool - 1 - Start completed. End Time 1629863245908 Consumed time 766Copy the code

By comparing the above two groups, we can conclude that adding &rewritebatchedstatements =true to the database connection pool for batch processing will improve the execution efficiency

The principle of analysis

org.springframework.jdbc.core.JdbcTemplate

@Override public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException { if (logger.isDebugEnabled()) { logger.debug("Executing SQL batch update [" + sql + "] with a batch size of " + batchSize); } int[][] result = execute(sql, (PreparedStatementCallback<int[][]>) ps -> { List<int[]> rowsAffected = new ArrayList<>(); try { boolean batchSupported = JdbcUtils.supportsBatchUpdates(ps.getConnection()); int n = 0; for (T obj : batchArgs) { pss.setValues(ps, obj); n++; if (batchSupported) { ps.addBatch(); if (n % batchSize == 0 || n == batchArgs.size()) { if (logger.isTraceEnabled()) { int batchIdx = (n % batchSize == 0) ? n / batchSize : (n / batchSize) + 1; int items = n - ((n % batchSize == 0) ? n / batchSize - 1 : (n / batchSize)) * batchSize; logger.trace("Sending SQL batch update #" + batchIdx + " with " + items + " items"); } // Key: Execute rowsAffected. Add (ps.executeBatch()); } } else { int i = ps.executeUpdate(); rowsAffected.add(new int[] {i}); } } int[][] result1 = new int[rowsAffected.size()][]; for (int i = 0; i < result1.length; i++) { result1[i] = rowsAffected.get(i); } return result1; } finally { if (pss instanceof ParameterDisposer) { ((ParameterDisposer) pss).cleanupParameters(); }}}); Assert.state(result ! = null, "No result array"); return result; }Copy the code

com.mysql.cj.jdbc.ClientPreparedStatement#executeBatchInternal

@Override protected long[] executeBatchInternal() throws SQLException { synchronized (checkClosed().getConnectionMutex()) { if (this.connection.isReadOnly()) { throw new SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT); } if (this.query.getBatchedArgs() == null || this.query.getBatchedArgs().size() == 0) { return new long[0]; } // we timeout the entire batch, not individual statements int batchTimeout = getTimeoutInMillis(); setTimeoutInMillis(0); resetCancelledState(); try { statementBegins(); clearWarnings(); if (! this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) { if (((PreparedQuery<? >) enclosing query). GetParseInfo () canRewriteAsMultiValueInsertAtSqlLevel ()) {/ / if rewriteBatchedStatements to true for batch processing otherwise the serialization process  return executeBatchedInserts(batchTimeout); } if (! this.batchHasPlainStatements && this.query.getBatchedArgs() ! = null && this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) { return executePreparedBatchAsMultiStatement(batchTimeout); } } return executeBatchSerially(batchTimeout); } finally { this.query.getStatementExecuting().set(false); clearBatch(); }}}Copy the code