Mybatis批处理效率

前言： 搭建mybatis测试环境

application.yml

注意: url后面必须加上 rewriteBatchedStatements=true 才能开启batch模式

# 数据源配置
spring:
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
    username: root
    password: 1511
    type: org.springframework.jdbc.datasource.DriverManagerDataSource
mybatis:
  type-aliases-package: com.dzw.domain
  mapper-locations: classpath*:mapper/*Mapper.xml
  #executor-type: batch #批处理
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

entity.Text

public class Text {

    private int id;
    private String name;
    private int sex;
    private String text;
}

mapper.TextMapper

public interface TextMapper {
    public void insert(Text text);

    void insertFor(List<Text> list);

    List<Text> findAll();
}

service

public interface TextService {
        public void insert(List<Text> list);
        List<Text> findAll();

        void insertFor(List<Text> list);

}

serviceImpl

@Service
public class TextServiceImpl implements TextService {

    @Autowired
    private TextMapper textMapper;

    @Override
    public void insert(List<Text> list) {

    }

    @Override
    public List<Text> findAll() {
        return textMapper.findAll();
    }

    @Override
    public void insertFor(List<Text> list) {
        textMapper.insertFor(list);
    }
}

DemoApplication

@SpringBootApplication
@MapperScan(basePackages = "com.dzw.mapper")
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

TextMapper.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.dzw.mapper.TextMapper">
    <insert id="insert" parameterType="com.dzw.domain.Text" useGeneratedKeys="true">
        insert into text(name,sex,text) value(#{name},#{sex},#{text})
    </insert>

    <insert id="insertFor" parameterType="list" useGeneratedKeys="true">
        insert into text(name,sex,text) value
        <foreach collection="list" item="text"  separator=",">
            (#{text.name},#{text.sex},#{text.text})
        </foreach>

    </insert>

    <select id="findAll" resultType="com.dzw.domain.Text">
        select * from text
    </select>
</mapper>

text.sql

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50733
 Source Host           : localhost:3306
 Source Schema         : demo

 Target Server Type    : MySQL
 Target Server Version : 50733
 File Encoding         : 65001

 Date: 28/05/2022 15:24:50
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for text
-- ----------------------------
DROP TABLE IF EXISTS `text`;
CREATE TABLE `text`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` tinyint(4) NULL DEFAULT NULL,
  `text` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 91008 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

测试数据： 10000条数据插入时间对比

1.batch模式

@Autowired
private SqlSessionFactory sqlSessionFactory;

@Test
void contextLoads() {
    //批量处理效率
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH); //批处理模式
    TextMapper mapper = sqlSession.getMapper(TextMapper.class);
    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        Text text = new Text();
        text.setName(String.valueOf(i));
        text.setSex(1);
        text.setText(String.valueOf(i));
        mapper.insert(text);
    }
    /**
     * Batch模式下 flushStatements方法 实际就是执行 BatchExecutor.doFlushStatements方法
     * 该方法会刷新statement
     */
    sqlSession.flushStatements();
    System.out.println(System.currentTimeMillis()-start); // 885  860  853

}

2.foreach模式

@Autowired
private TextService textService;

@Test
void insertFor() {
    //foreach标签批量处理效率
    long start = System.currentTimeMillis();
    ArrayList<Text> texts = new ArrayList<>();
    for (int i = 0; i < 10000; i++) {
        Text text = new Text();
        text.setName(String.valueOf(i));
        text.setSex(1);
        text.setText(String.valueOf(i));
        texts.add(text);

        if(texts.size()>=10000){
            textService.insertFor(texts);
            texts.clear();
        }
    }

    /**
     *  500 -> 1396 1396 1379
     *  2000 -> 1096 1052 1091
     *  10000 -> 1032 1006 973
     */
    System.out.println(System.currentTimeMillis()-start);


}

3.总结

###  mybatis batch模式和foreach标签 进行批量插入操作时 ，耗时对比

 batch 三次插入1w条数据耗时
        	885  860  853 平均：866

 foreach 三次插入1w条数据耗时
        	500 1313 1288              平均：1292
        	2000 -> 1096 1052 1091     平均：1079
        	10000 -> 1032 1006 973     平均：1003
 注意： 由于foreach时拼接sql数据，该方法会存在语句长度限制，有可能会抛出Could not execute JDBC batch update异常
      	  my.ini中修改以下配置
         		 max_allowed_packet=1M
          		net_buffer_length=2K
 对比：
    		1292/866 = 1.49
      	batch插入速度比foreach要快很多。

  结论： foreach标签对比batch模式 效率慢了0.5，java代码进行for循环批量插入就不测试了，肯定要慢几十倍

我在网上看了很多文章，都是foreach效率比batch高，可能时jdbc url没加上 rewriteBatchedStatements=true的原因，如有错误请各位斧正。