Java批量插入数据方法对比

一．环境

1. 数据库MySql 8.0.2
2. Maven+IDEA

二．方法配置

1. 目录结构

 

1. POM.xml依赖

<dependencies>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.13</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/junit/junit -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
</dependencies>

1. Mybatis配置mybati-config.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "<http://mybatis.org/dtd/mybatis-3-config.dtd>">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.20.10:3306/SCTest?useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true"/>
                <property name="username" value="root"/>
                <property name="password" value="17klop"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="com.sc.common.IInsertMapper"/>
    </mappers>
</configuration>

1. Mybatis Mapper代码(IInsertMapper)

package com.sc.common;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface IInsertMapper {
    @Select("insert into t_inserttest(IndexV)value(#{index})")
    void insertBatch(int index);
}

1. 实现类源代码

package com.sc.common;

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.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Random;

public class MySqlHelper {
    public MySqlHelper() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
    }

    //&rewriteBatchedStatements=true连接字符串必须添加这个配置，否则无法实现批量提交
    private static final String DB_URL = "jdbc:mysql://192.168.20.10:3306/SCTest?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&rewriteBatchedStatements=true";
    private static final String uid = "root";
    private static final String pwd = "17klop";
    private static final String insertSql="insert into t_inserttest(IndexV)value(?)";
    private static Connection connection = null;

    //1.测试连接
    public Boolean isConnect() throws Exception {
        try {
            connection = DriverManager.getConnection(DB_URL,uid,pwd);
            return true;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return false;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    //2.测试AutoCommit=true;
    //此次测试耗时11411ms
    public void BatchByAutoCommit()throws Exception {
        long start = System.currentTimeMillis();
        try{
            connection = DriverManager.getConnection(DB_URL,uid,pwd);
            PreparedStatement ps = null;
            ps=connection.prepareStatement(insertSql);
            Random random = new Random();
            for(int i=0;i<1000000;i++){
                ps.setObject(1,new Integer(random.nextInt(1000000)));
                ps.addBatch();
                //每1000条数据提交一次,防止出现内存溢出的情况;
                if (i % 999 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //最后的一次也需要执行提交
            ps.executeBatch();
            ps.clearBatch();
        }catch (Exception ex) {
            System.out.println(ex.getMessage());
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    //3.测试AutoCommit=false;
    //此次耗时14084ms,根据测试,未发现AutoCommit设置与否对测试插入耗时结果的影响
    public void BatchNoAutoCommit()throws Exception {
        long start = System.currentTimeMillis();
        try{
            connection = DriverManager.getConnection(DB_URL,uid,pwd);
            connection.setAutoCommit(false);
            PreparedStatement ps = null;
            ps=connection.prepareStatement(insertSql);
            Random random = new Random();
            for(int i=0;i<1000000;i++){
                ps.setObject(1,new Integer(random.nextInt(1000000)));
                ps.addBatch();
                //每1000条数据提交一次,防止出现内存溢出的情况;
                if (i % 999 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //最后的一次也需要执行提交
            ps.executeBatch();
            ps.clearBatch();
        }catch (Exception ex) {
            System.out.println(ex.getMessage());
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    //4.测试使用Mybatis批量提交
    //此方法耗时1202058ms合计20分钟(setAutoCommit(true))
    //此方法耗时422900ms合计7分钟(setAutoCommit(false)),速度提升了3倍
    public void BatchByMybatis() throws Exception{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.getConnection().setAutoCommit(false);
        // 获取UserMapper对象
        IInsertMapper iInsertMapper = sqlSession.getMapper(IInsertMapper.class);
        Random random = new Random();
        long start = System.currentTimeMillis();
        try {
            for (int i = 0; i < 1000000; i++) {
                iInsertMapper.insertBatch(new Integer(random.nextInt(1000000)));
                //每1000条数据提交一次,防止出现内存溢出的情况;
                if (i % 999 == 0) {
                    sqlSession.commit();
                    sqlSession.clearCache();
                }
            }
            sqlSession.commit();
            sqlSession.clearCache();
        }catch (Exception ex){
            sqlSession.rollback();
        }
        finally {
            sqlSession.close();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    //5.使用Mybatis实现原生的批量提交
    //此方法耗时14044ms
    public void BatchByMyBatisUseJDBC()throws Exception {

        long start = System.currentTimeMillis();
        try{
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            // 创建SqlSession对象
            SqlSession sqlSession = sqlSessionFactory.openSession();
            connection=sqlSession.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement ps = null;
            ps=connection.prepareStatement(insertSql);
            Random random = new Random();
            for(int i=0;i<1000000;i++){
                ps.setObject(1,new Integer(random.nextInt(1000000)));
                ps.addBatch();
                //每1000条数据提交一次,防止出现内存溢出的情况;
                if (i % 999 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //最后的一次也需要执行提交
            ps.executeBatch();
            ps.clearBatch();
            sqlSession.close();
        }catch (Exception ex) {
            System.out.println(ex.getMessage());
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    //6.使用Mybatis实现原生的批量提交多个字段
    //此方法第一次测试耗时27782ms,第二次测试耗时18376ms
    //测试结果得出,字段越多,插入的时间越长,相较于2个字段而言,10个字段的时间时1个字段的2倍
    public void BatchByMyBatisUseJDBCManyField()throws Exception {

        long start = System.currentTimeMillis();
        try{
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            // 创建SqlSession对象
            SqlSession sqlSession = sqlSessionFactory.openSession();
            connection=sqlSession.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement ps = null;
            ps=connection.prepareStatement("insert into t_inserttest(field1,field2,field3,field4,field5,field6,field7,field8,field9,field10)value(?,?,?,?,?,?,?,?,?,?)");
            Random random = new Random();
            for(int i=0;i<1000000;i++){
                ps.setObject(1,new Integer(random.nextInt(1000000)));
                ps.setObject(2,new Integer(random.nextInt(1000000)));
                ps.setObject(3,new Integer(random.nextInt(1000000)));
                ps.setObject(4,new Integer(random.nextInt(1000000)));
                ps.setObject(5,new Integer(random.nextInt(1000000)));
                ps.setObject(6,new Integer(random.nextInt(1000000)));
                ps.setObject(7,new Integer(random.nextInt(1000000)));
                ps.setObject(8,new Integer(random.nextInt(1000000)));
                ps.setObject(9,new Integer(random.nextInt(1000000)));
                ps.setObject(10,new Integer(random.nextInt(1000000)));
                ps.addBatch();
                //每1000条数据提交一次,防止出现内存溢出的情况;
                if (i % 999 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //最后的一次也需要执行提交
            ps.executeBatch();
            ps.clearBatch();
            sqlSession.close();
        }catch (Exception ex) {
            System.out.println(ex.getMessage());
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
}

1. 测试类源代码

package com.sc.test;

import com.sc.common.MySqlHelper;
import org.junit.Test;

public class TestMain {
    @Test
    public void testConnection(){
        MySqlHelper msh=new MySqlHelper();
        try {
            if (msh.isConnect()) {
                System.out.println("连接成功!");
            }else{
                System.out.println("连接失败!");
            }
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    @Test
    public void testByAutoCommit(){
        MySqlHelper msh=new MySqlHelper();
        try {
           msh.BatchByAutoCommit();
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    @Test
    public void testNoAutoCommit(){
        MySqlHelper msh=new MySqlHelper();
        try {
            msh.BatchNoAutoCommit();
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    @Test
    public void testMybatis(){
        MySqlHelper msh=new MySqlHelper();
        try {
            msh.BatchByMybatis();
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    @Test
    public void testMyBatisUseJDBC(){
        MySqlHelper msh=new MySqlHelper();
        try {
            msh.BatchByMyBatisUseJDBC();
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    @Test
    public void testMyBatisUserJDBCManyField(){
        MySqlHelper msh=new MySqlHelper();
        try {
            msh.BatchByMyBatisUseJDBCManyField();
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }
    }
}

1. 测试结果说明

a)     方法2、3使用JDBC直接实现批量插入，区别在于3中setAutoCommit=false;

根据实际测试情况，两者在插入1000W条数据的时候速度相差不是特别大。

b)    方法4使用Mybatis的批处理方法实现批量插入，分别进行了两种测试，即setAutoCommit=true和false的情况下，两者速度差距达到了3倍，但与直接使用JDBC进行批量插入速度相差依然很大

c)     方法5测试使用Mybatis直接调用JDBC进行批量插入，测试情况显示，与直接使用JDBC的速度差距不大。

d)    方法6测试在插入多个字段与单个字段的情况下两者的速度差距，经过两次测试，第一个10个字段的速度是单个字段速度的2倍；第二次测试速度相差不多，猜测是因为数据库缓存的原因

e)     总体测试过程中，数据库CPU使用率大幅度提升，因为使用1000条数据提交一次的方法，内存使用率有提升，但变化不是特别明显。实际开发过程中不建议大量使用批量插入的方法，避免对数据库造成过大的压力