我们在许多项目中都会遇到对数据库进行批量插入或更新的操作。现分别采用以下几种方法进行测试:(1)代码中循环逐条新增(2)采用开启batch模式(3)使用Mybatis-foreach标签拼接sql执行(4)通过begin end将SQL语句拼接执行。下面将通过本地连接达梦数据库的方式,测试四种方式之间的差异。
环境介绍:
软件 | 版本 |
---|---|
DM数据库 | 1-1-126-20.09.04-126608-ENT |
IDEA | IntelliJ IDEA 2020.2.2 x64 |
JDK | 1.8 |
Maven | apache-maven-3.6.1 |
Spring Boot | 2.3.7-RELEASE |
mybatis | mybatis-3.5.6 |
表结构:
CREATE TABLE "TEST"."BATCH_INSERT_TEST1"
(
"ID" INT NOT NULL,
"NAME" VARCHAR(50),
"INFO01" NUMBER(22,10),
"INFO02" VARCHAR(50),
CLUSTER PRIMARY KEY("ID"));
-
循环插入代码
public String Insert() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.SIMPLE,false);
BatchMapper batchMapper = sqlSession.getMapper(BatchMapper.class);
Long start = System.currentTimeMillis();
try {
Batch role;
for (int i = 0; i < 500; i++) {
role = new Batch();
role.setId(i);
role.setName("name" + i);
batchMapper.insert(role);
//提交后无法回滚(单条提交)
sqlSession.commit();
//清理缓存,防止溢出
sqlSession.clearCache();
}
} catch (Exception e) {
//没有提交的数据可以回滚
sqlSession.rollback();
} finally{
sqlSession.close();
}
System.out.println("业务代码循环插入500条数据耗时={" + (System.currentTimeMillis() - start)+"}");
return "sussess";
}
对应的XML配置
<insert id="insert" parameterType="com.dameng.batch_demo.model.Batch" >
insert into TEST.BATCH_INSERT_TEST1 (id,name)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
</insert>
-
开启batch模式
public String batchInsert() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
BatchMapper batchMapper1 = sqlSession.getMapper(BatchMapper.class);
Long start = System.currentTimeMillis();
Batch role;
for (int i = 0; i < 500; i++) {
role = new Batch();
role.setId(i);
role.setName("name" + i);
batchMapper1.insert(role);
}
sqlSession.commit();
System.out.println("(使用批处理)业务代码循环插入500条数据耗时={" + (System.currentTimeMillis() - start)+"}");
return "sussess";
}
注:对应的XML配置和循环插入一样。
Mybatis有三种基本的Executor执行器:
Simple、Reuse、Batch。
Simple:每执行一次update或select,就开启一个Statement对象,用完立刻关闭Statement对象。
Reuse:执行update或select,以sql作为key查找Statement对象,存在就使用,不存在就创建,用完后,不关闭Statement对象,而是放置于Map内,供下一次使用。简言之,就是重复使用Statement对象。
Batch:执行update(没有select,JDBC批处理不支持select),将所有sql都添加到批处理中(addBatch()),等待统一执行(executeBatch()),它缓存了多个Statement对象,每个Statement对象都是addBatch()完毕后,等待逐一执行executeBatch()批处理。与JDBC批处理相同。
-
foreach标签拼接sql
public String insert() {
SqlSession sqlSession = sqlSessionFactory.openSession();
BatchMapper batchMapper2 = sqlSession.getMapper(BatchMapper.class);
Long start = System.currentTimeMillis();
List<Batch> list = new ArrayList<>();
Batch role;
for (int i = 1; i <= 1000000; i++) {
role = new Batch();
role.setId(i);
role.setName("name" + i);
list.add(role);
}
if (null != list && list.size() > 0) {
int pointsDataLimit = 15000;//限制条数
Integer size = list.size();
//判断是否有必要分批
if (pointsDataLimit<size) {
int part = size / pointsDataLimit;//分批数
System.out.println("共有 : " + size + "条,!" + " 分为 :" + part + "批");
//
for (int i = 0; i < part; i++) {
List<Batch> listPage = list.subList(0, pointsDataLimit);
//System.out.println(listPage);
batchMapper2.insertBatch(listPage);
sqlSession.commit();
sqlSession.clearCache();
list.subList(0, pointsDataLimit).clear();
}
if (!list.isEmpty()) {
//System.out.println(list);//表示最后剩下的数据
batchMapper2.insertBatch(list);
sqlSession.commit();
sqlSession.clearCache();
}
} else {
System.out.println(list);
}
} else {
System.out.println("没有数据!!!");
}
sqlSession.close();
System.out.println("sql使用foreach方式循环插入{1000000}条数据耗时={" + (System.currentTimeMillis() - start) + "}");
return "sussess";
}
注意:达梦数据库的参数做了最大个数限制,当超过了32767个时会报错如下,所以需要进行分批处理。
### Cause: dm.jdbc.driver.DMException: 第200002 行附近出现错误:
超过最大参数个数(32767)] with root cause
对应的XML配置
<insert id="insertBatch" parameterType="com.dameng.batch_demo.model.Batch" >
insert into TEST.BATCH_INSERT_TEST1 (id, name)
values
<foreach collection="list" item="batch" separator=",">
(#{batch.id,jdbcType=INTEGER}, #{batch.name,jdbcType=VARCHAR})
</foreach>
</insert>
-
begin end按500条SQL语句进行拼接
public String beginend() {
SqlSession sqlSession = sqlSessionFactory.openSession();
BatchMapper batchMapper = sqlSession.getMapper(BatchMapper.class);
Long start = System.currentTimeMillis();
//List<String> sql = new ArrayList();//定义一个list
String insert_sql = "insert into TEST.BATCH_INSERT_TEST1 (id, name) values (";
String insert_sql2 = "";
// String sql;
for (int i = 1; i <= 1000000; i++) {
String sql1 = insert_sql + i + " ," + "'name" + i + "');";
for (int j = i; j <= i; j++) {
insert_sql2 = insert_sql2 + sql1;
}
if (i % 500 == 0){
batchMapper.beginend(insert_sql2);
sqlSession.commit();
sqlSession.clearCache();
insert_sql2 = "";
}
}
//batchMapper.beginend(insert_sql2);
System.out.println("beginend拼接SQL插入1000000条数据耗时={" + (System.currentTimeMillis() - start) + "}");
return "sussess";
}
对应的XML配置
<insert id="beginend" useGeneratedKeys="false" parameterType="com.dameng.batch_demo.model.Batch" >
begin
${sql} //该执行语句在代码中拼接
commit;
end
</insert>
结果比较
毫秒 | 业务代码循环 | batch模式 | foreach标签拼接 | begin end拼接 |
---|---|---|---|---|
500 | 33 | 36 | 41 | 30 |
1000 | 617 | 39 | 53 | 46 |
10000 | 4043 | 428 | 362 | 421 |
100000 | 27682 | 18432 | 2069 | 3822 |
500000 | 230463 | 922125 | 12489 | 17273 |
1000000 | 409247 | 5369012 | 18530 | 34767 |
总结:通过测试的结果数据进行对比,可以看出采用foreach标签拼接sql的方式是最理想的。但需要注意的是参数最大值有限制,需要进行分批处理。
更多资讯请上达梦技术社区了解:达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心