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的原因，如有错误请各位斧正。
