使用不同方式向MySQL插入数据的性能比较

通过多种不同方式，向MySQL的一个table插入10000条记录，测试其所花费的时间成本。

环境

MySQL

~ mysql --version
mysql  Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

MyBatis

• mybatis-3.5.9.jar

DB

• repo

mysql> use repo
Database changed

Table

• t2

mysql> describe t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1    | int        | YES  |     | NULL    |       |
| c2    | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

结论

	测试方式	说明	时间（ms）
1	AutoCommit true	每insert一条记录就commit一次	11735
2	AutoCommit false	insert 10000条记录，只commit一次	3862
3	MyBatis batch	使用batch insert，只commit一次	2006
4	MySQL batch (literal)	用一个SQL来insert 10000条记录，使用literal直接创建好SQL字符串	365
5	MySQL batch (foreach)	用一个SQL来insert 10000条记录，利用MyBatis的foreach构建SQL	737

注：MySQL我不太清楚，不过对于Db2，如果一个事务太大，容易导致Db2错误 -964 （transaction log full），所以，也并不能一味的增加事务内容。解决办法是分批次，比如每插入1000条记录提交一次。当然， -964 错误跟transaction log的大小，记录本身大小，DB的并发量等条件也都有关系，因此没法确定最佳值，只能凭（pai）经（nao）验（dai）给出一个值。

代码

• MyMapper.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="dao.MyMapper">
    <insert id="insert">
        insert into t2 (c1, c2) values (111, 'abcde')
    </insert>

    <insert id="insert2">
        insert into t2 (c1, c2) values ${values}
    </insert>

    <insert id="insert3">
        insert into t2 (c1, c2) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.c1}, #{item.c2})
        </foreach>
    </insert>
</mapper>

• MyMapper.java

package dao;

import org.apache.ibatis.annotations.Param;

public interface MyMapper {
    int insert();

    int insert2(String values);

    int insert3(List<MyObject> list);
}

方法1（AutoCommit为true）

package testPackage;

import dao.MyMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class TestAutocommitTrue {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        long start = System.currentTimeMillis();

        try {
            MyMapper mapper = sqlSession.getMapper(MyMapper.class);

            for (int i=0; i<10000; i++) {
                mapper.insert();
            }

            sqlSession.commit();
        } finally {
            sqlSession.close();
        }

        long end = System.currentTimeMillis();

        System.out.println("time = " + (end - start) + "ms");
    }
}

运行程序，结果如下：

time = 11735ms

方法2（AutoCommit为false）

代码同上，唯一区别是把 sqlSessionFactory.openSession() 的参数由 true 改为 false 或者直接去掉参数（default值就是false）。

运行程序，结果如下：

time = 3862ms

方法3（MyBatis batch）

代码同上，唯一区别是把 sqlSessionFactory.openSession() 的参数改为 ExecutorType.BATCH。

运行程序，结果如下：

time = 2006ms

方法4（MySQL batch）

package testPackage;

import dao.MyMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class TestMysqlBatch {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

        long start = System.currentTimeMillis();

        try {
            MyMapper mapper = sqlSession.getMapper(MyMapper.class);

            String values = Stream.generate(() -> "(111, 'abcde')").limit(10000).collect(Collectors.joining(","));

            mapper.insert2(values);

            sqlSession.commit();
        } finally {
            sqlSession.close();
        }

        long end = System.currentTimeMillis();

        System.out.println("time = " + (end - start) + "ms");
    }
}

运行程序，结果如下：

time = 365ms

本例是直接在Java代码里创建了一个大字符串 (111, 'abcde'),(111, 'abcde'),...... ，然后用字符串替换的方式（ ${} ），替换到SQL语句里面（注意不是 #{} ）。

方法5（MySQL batch）

接上文。

MyBatis也支持foreach循环，不过需要传入一个集合（collection）参数，由MyBatis来拼接成完整的SQL。

package testPackage;

import dao.MyMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import pojo.MyObject;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class TestMysqlBatch2 {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

        List<MyObject> list = Stream.generate(() -> new MyObject(111, "abcde")).limit(10000).collect(Collectors.toList());

        long start = System.currentTimeMillis();

        try {
            MyMapper mapper = sqlSession.getMapper(MyMapper.class);

            mapper.insert3(list);

            sqlSession.commit();
        } finally {
            sqlSession.close();
        }

        long end = System.currentTimeMillis();

        System.out.println("time = " + (end - start) + "ms");
    }
}

运行程序，结果如下：

time = 737ms

该方法相比上一个方法（使用literal的SQL语句）时间成本稍高一些，预计原因如下：

• MyBatis通过foreach迭代来动态生成SQL语句
• 使用了 ${} ，也就是有10000个 ? ，然后运行期动态set值（SQL只运行一次，PreparedStatement的优势没有体现）