达梦数据库+mybatis批量入库效率测试测试

我们在许多项目中都会遇到对数据库进行批量插入或更新的操作。现分别采用以下几种方法进行测试：（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的方式是最理想的。但需要注意的是参数最大值有限制，需要进行分批处理。

  更多资讯请上达梦技术社区了解：达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心