侧边栏壁纸
博主头像
Epoch

Java开发、Python爬虫、微服务、分布式、前端

  • 累计撰写 93 篇文章
  • 累计创建 110 个标签
  • 累计收到 8 条评论
标签搜索

目 录CONTENT

文章目录
SpringBoot 数据库

Mybatis中批处理详细讲解

Epoch
Epoch
2021-09-22 / 0 评论 / 0 点赞 / 627 阅读 / 2,865 字 / 正在检测是否收录...

Mybatis–批量插入

1.同样都是批量插入为什么有区别？

2.同样插入20w条数据，插入的效率如何？

3.批量提交sql中，sqlSessionFactory.openSession(false)和sqlSessionFactory.openSession(ExecutorType.BATCH)区别在哪里

预备工作

1.springboot-yml配置

# 数据库配置
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&rewriteBatchedStatements=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
# mybatis
mybatis:
  type-aliases-package: com.xmaven.test.entity
  mapper-locations: classpath:mapper/*.xml

2.实体类

package com.xmaven.test.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

/**
 * @Author: Ambition
 * @Description TODO
 * @Date: 2021/9/22 12:57 下午
 * @Version 1.0
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Accessors(chain = true)
public class Student {
    
    private Integer id;
    
    private String name;
    
    private Integer sex;
    
}

3.mapper.xml文件编写

<?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.xmaven.test.mapper.StudentMapper">

  <insert id="insert" parameterType="com.xmaven.test.entity.Student">
    insert into student (name, sex)
    values (#{name}, #{sex})
  </insert>

  <insert id="batchInsert" parameterType="java.util.List">
    insert into student (name, sex)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.name}, #{item.sex})
    </foreach>
  </insert>
</mapper>

4.StudentMapper.java文件

package com.xmaven.test.mapper;

import com.xmaven.test.entity.Student;
import java.util.List;
import org.springframework.stereotype.Repository;

/**
 * @Author: Ambition
 * @Description TODO
 * @Date: 2021/9/22 1:10 下午
 * @Version 1.0
 */
@Repository
public interface StudentMapper {
    
    /**
     * 单条插入
     *
     * @param student
     * @return
     */
    public int insert(Student student);
    
    /**
     * foreach 插入
     *
     * @param list
     * @return
     */
    public int batchInsert(List<Student> list);
}

5.数据准备工作

private List<Student> assemblyData(int count) {
    List<Student> list = new ArrayList<>();
    Student student = new Student();
    for (int i = 0; i < count; i++) {
        student.setName("小王" + "-" + i).setSex(i);
        list.add(student);
    }
    return list;
}

N1-多次执行单条SQL语句

package com.xmaven.test.Mybaits;

import com.xmaven.test.entity.Student;
import com.xmaven.test.mapper.StudentMapper;
import java.util.ArrayList;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

/**
 * @Author: Ambition
 * @Description TODO
 * @Date: 2021/9/22 12:57 下午
 * @Version 1.0
 */
@SpringBootTest
@Slf4j
public class MybatisTest {
    
    private List<Student> list = new ArrayList<>();
    
    @Autowired
    private StudentMapper studentMapper;
    
    
    private List<Student> assemblyData(int count) {
        List<Student> list = new ArrayList<>();
        Student student = new Student();
        for (int i = 0; i < count; i++) {
            student.setName("小王" + "-" + i).setSex(i);
            list.add(student);
        }
        return list;
    }
    
    @Test
    public void insert() {
        list = assemblyData(200000);
        Long start = System.currentTimeMillis();
        for (Student student : list) {
            studentMapper.insert(student);
        }
        Long end = System.currentTimeMillis();
        log.info("insert 耗时：" + (end - start) + " ms");
    }
}

耗时计算：

2021-09-22 14:18:17.225  INFO 58997 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-09-22 14:18:17.832  INFO 58997 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2021-09-22 14:37:48.021  INFO 58997 --- [           main] com.xmaven.test.Mybaits.MybatisTest      : insert 耗时：1170841 ms

image-20210922152509078

N2-foreach 拼接SQL

测试代码

@Test
public void insertBatchTwo() {
    list = assemblyData(200000);
    Long start = System.currentTimeMillis();
    studentMapper.batchInsert(list);
    Long end = System.currentTimeMillis();
    log.info("insertBatchTwo 耗时：" + (end - start) + " ms");
}

测试耗时：

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@134ff8f8]
2021-09-22 14:44:06.485  INFO 71023 --- [           main] com.xmaven.test.Mybaits.MybatisTest      : insertBatchTwo 耗时：15269 ms
2021-09-22 14:44:06.535  INFO 71023 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2021-09-22 14:44:06.550  INFO 71023 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

image-20210922144700964

N3- SqlSessionFactory中false(默认不自动提交)

@Autowired
private SqlSessionFactory sqlSessionFactory;

测试代码

@Test
public void insertBatchThree() {
    list = assemblyData(200000);
    Long start = System.currentTimeMillis();
    // 设置sqlsession不自动提交  默认自动提交
    SqlSession sqlSession = sqlSessionFactory.openSession(false);
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    for (int i = 0, length = list.size(); i < length; i++) {
        mapper.insert(list.get(i));
        //每20000条提交一次防止内存溢出
        if (i % 20000 == 19999) {
            sqlSession.commit();
            sqlSession.clearCache();
        }
    }
    sqlSession.commit();
    sqlSession.clearCache();
    Long end = System.currentTimeMillis();
    log.info("insertBatchThree 耗时：" + (end - start) + " ms");
}

测试耗时：

2021-09-22 15:13:42.825  INFO 75097 --- [           main] com.xmaven.test.Mybaits.MybatisTest      : insertBatchThree 耗时：1276221 ms
2021-09-22 15:13:42.904  INFO 75097 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2021-09-22 15:13:42.917  INFO 75097 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

image-20210922151458047

N4- SqlSessionFactory中ExecutorType.BATCH(批处理模式)

@Test
public void insertBatchFour() {
    list = assemblyData(200000);
    Long start = System.currentTimeMillis();
    // 打开批处理
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    for (int i = 0, length = list.size(); i < length; i++) {
        mapper.insert(list.get(i));
        //每20000条提交一次防止内存溢出
        if (i % 20000 == 19999) {
            sqlSession.commit();
            sqlSession.clearCache();
        }
    }
    sqlSession.commit();
    sqlSession.clearCache();
    Long end = System.currentTimeMillis();
    log.info("insertBatchThree 耗时：" + (end - start) + " ms");
}

测试耗时：

==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
==> Parameters: 小王-199999(String), 199999(Integer)
2021-09-22 15:16:10.445  INFO 86184 --- [           main] com.xmaven.test.Mybaits.MybatisTest      : insertBatchThree 耗时：5035 ms
2021-09-22 15:16:10.467  INFO 86184 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2021-09-22 15:16:10.480  INFO 86184 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

image-20210922151850324

总结

  1. 循环插入单条数据虽然效率极低，但是代码量极少，数据量较小时可以使用，但是数据量较大禁止使用，效率太低了；

  2. foreach 拼接sql的方式，使用时有大段的xml和sql语句要写，很容易出错，虽然效率尚可，但是真正应对大量数据的时候，依旧无法使用，所以不推荐使用；

  3. 批处理执行是有大数据量插入时推荐的做法，使用起来也比较方便。

    解析：

    由于sqlSessionFactory.openSession(false),默认还是SIMPLE

image-20210922153159351

并且他插入数据的时候已经进行对表中的数据更新了！ExecutorType.SIMPLE: 这个执行器类型不做特殊的事情。它为每个语句的执行创建一个新的预处理语句。

image-20210922154005145

SimpleExecutor通过类名可以看出，它是一个简单的执行类，并不会做一些处理就执行sql。（每执行一次update或select，就开启一个Statement对象，用完立刻关闭Statement对象）源码及分析如下：

image-20210922154740162

/**
 *    Copyright 2009-2019 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package org.apache.ibatis.executor;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.List;

import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.transaction.Transaction;

/**
 * @author Clinton Begin
 */
public class SimpleExecutor extends BaseExecutor {

  public SimpleExecutor(Configuration configuration, Transaction transaction) {
    super(configuration, transaction);
  }

  @Override
  public int doUpdate(MappedStatement ms, Object parameter) throws SQLException {
    Statement stmt = null;
    try {
      // 获得配置      
      Configuration configuration = ms.getConfiguration();
      // 获得statementHandler里面有statement，来处理
      StatementHandler handler = configuration.newStatementHandler(this, ms, parameter, RowBounds.DEFAULT, null, null);
      stmt = prepareStatement(handler, ms.getStatementLog());
      // 最终是一个statement进行处理
      return handler.update(stmt);
    } finally {
      closeStatement(stmt);
    }
  }

  @Override
  public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
    Statement stmt = null;
    try {
      Configuration configuration = ms.getConfiguration();
      StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
      stmt = prepareStatement(handler, ms.getStatementLog());
      return handler.query(stmt, resultHandler);
    } finally {
      closeStatement(stmt);
    }
  }

  @Override
  protected <E> Cursor<E> doQueryCursor(MappedStatement ms, Object parameter, RowBounds rowBounds, BoundSql boundSql) throws SQLException {
    Configuration configuration = ms.getConfiguration();
    StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, null, boundSql);
    Statement stmt = prepareStatement(handler, ms.getStatementLog());
    Cursor<E> cursor = handler.queryCursor(stmt);
    stmt.closeOnCompletion();
    return cursor;
  }

  @Override
  public List<BatchResult> doFlushStatements(boolean isRollback) {
    return Collections.emptyList();
  }

  private Statement prepareStatement(StatementHandler handler, Log statementLog) throws SQLException {
    Statement stmt;
    Connection connection = getConnection(statementLog);
    stmt = handler.prepare(connection, transaction.getTimeout());
    // 将Statement转为PrepareStatement
    handler.parameterize(stmt);
    return stmt;
  }

}

所以每次执行一条插入语句就会执行update相当于我们默认的那种多次执行单条SQL语句

BatchExecutor : 通过批量操作来提高性能。（执行update（没有select，JDBC批处理不支持select），将所有sql都添加到批处理中（addBatch()），等待统一执行（executeBatch()），它缓存了多个Statement对象，每个Statement对象都是addBatch()完毕后，等待逐一执行executeBatch()批处理。与JDBC批处理相同。）

/**
 *    Copyright 2009-2019 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package org.apache.ibatis.executor;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.transaction.Transaction;

/**
 * @author Jeff Butler
 */
public class BatchExecutor extends BaseExecutor {

  public static final int BATCH_UPDATE_RETURN_VALUE = Integer.MIN_VALUE + 1002;

  private final List<Statement> statementList = new ArrayList<>();
  private final List<BatchResult> batchResultList = new ArrayList<>();
  private String currentSql;
  private MappedStatement currentStatement;

  public BatchExecutor(Configuration configuration, Transaction transaction) {
    super(configuration, transaction);
  }

  @Override
  public int doUpdate(MappedStatement ms, Object parameterObject) throws SQLException {
    final Configuration configuration = ms.getConfiguration(); //获得配置信息
    final StatementHandler handler = configuration.newStatementHandler(this, ms, parameterObject, RowBounds.DEFAULT, null, null);
    final BoundSql boundSql = handler.getBoundSql();
    final String sql = boundSql.getSql(); // 获得Sql语句
    final Statement stmt; // 如果sql语句等于当前sql MappedStatement 等于当前Map碰到Statement 
    if (sql.equals(currentSql) && ms.equals(currentStatement)) {
      int last = statementList.size() - 1;
      stmt = statementList.get(last); //获得最后一个      //有相同的MappedStatement和参数
      applyTransactionTimeout(stmt);
      handler.parameterize(stmt);//fix Issues 322
      BatchResult batchResult = batchResultList.get(last);
      batchResult.addParameterObject(parameterObject);
    } else {  //如果不存在就创建一个批处理操作
      Connection connection = getConnection(ms.getStatementLog());
      stmt = handler.prepare(connection, transaction.getTimeout());
      handler.parameterize(stmt);    //fix Issues 322
      currentSql = sql;
      currentStatement = ms;
      statementList.add(stmt);  //添加批量处理操作
      batchResultList.add(new BatchResult(ms, sql, parameterObject));
    }
    handler.batch(stmt);  //最终是调用jdbc的批处理操作
    return BATCH_UPDATE_RETURN_VALUE;
  }

  @Override
  public <E> List<E> doQuery(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql)
      throws SQLException {
    Statement stmt = null;
    try {
      flushStatements();
      Configuration configuration = ms.getConfiguration();
      StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameterObject, rowBounds, resultHandler, boundSql);
      Connection connection = getConnection(ms.getStatementLog());
      stmt = handler.prepare(connection, transaction.getTimeout());
      handler.parameterize(stmt);
      return handler.query(stmt, resultHandler);
    } finally {
      closeStatement(stmt);
    }
  }

  @Override
  protected <E> Cursor<E> doQueryCursor(MappedStatement ms, Object parameter, RowBounds rowBounds, BoundSql boundSql) throws SQLException {
    flushStatements();
    Configuration configuration = ms.getConfiguration();
    StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, null, boundSql);
    Connection connection = getConnection(ms.getStatementLog());
    Statement stmt = handler.prepare(connection, transaction.getTimeout());
    handler.parameterize(stmt);
    Cursor<E> cursor = handler.queryCursor(stmt);
    stmt.closeOnCompletion();
    return cursor;
  }
	
  // 刷新Statement，记录执行次数
  @Override
  public List<BatchResult> doFlushStatements(boolean isRollback) throws SQLException {
    try {
      List<BatchResult> results = new ArrayList<>();
      if (isRollback) {
        return Collections.emptyList();
      }
      // 如果进行了批量处理
      for (int i = 0, n = statementList.size(); i < n; i++) {
        Statement stmt = statementList.get(i);
        applyTransactionTimeout(stmt);
        BatchResult batchResult = batchResultList.get(i);
        try {
          // 记录批量处理执行操作的条数
          batchResult.setUpdateCounts(stmt.executeBatch());
          MappedStatement ms = batchResult.getMappedStatement();
          // 参数对象集合
          List<Object> parameterObjects = batchResult.getParameterObjects();
          // 生成key
          KeyGenerator keyGenerator = ms.getKeyGenerator();
          if (Jdbc3KeyGenerator.class.equals(keyGenerator.getClass())) {
            Jdbc3KeyGenerator jdbc3KeyGenerator = (Jdbc3KeyGenerator) keyGenerator;
            jdbc3KeyGenerator.processBatch(ms, stmt, parameterObjects);
          } else if (!NoKeyGenerator.class.equals(keyGenerator.getClass())) { //issue #141
            for (Object parameter : parameterObjects) {
              keyGenerator.processAfter(this, ms, stmt, parameter);
            }
          }
          // Close statement to close cursor #1109
          closeStatement(stmt);
        } catch (BatchUpdateException e) {
          StringBuilder message = new StringBuilder();
          message.append(batchResult.getMappedStatement().getId())
              .append(" (batch index #")
              .append(i + 1)
              .append(")")
              .append(" failed.");
          if (i > 0) {
            message.append(" ")
                .append(i)
                .append(" prior sub executor(s) completed successfully, but will be rolled back.");
          }
          throw new BatchExecutorException(message.toString(), e, results, batchResult);
        }
        // 记录操作
        results.add(batchResult);
      }
      return results;
    } finally {
      for (Statement stmt : statementList) {
        closeStatement(stmt);
      }
      currentSql = null;
      statementList.clear();
      batchResultList.clear();
    }
  }

}
0
SpringBoot Mybatis 批处理
版权归属： Epoch
本文链接： https://www.xmaven.cn/archives/mybatis-zhong-pi-chu-li-xiang-xi-jiang-jie
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区