案例 百万数据批量插入比较

1、比较jpa、mybatis-plus、jdbc、load data infile插入数据效率（个人感觉jpa不是很好用，用 线程+单批次提交1000条数据 的方案应该也可以使用；后续没有针对jpa做过多测试）

2、load data infile语法测试

3、相关代码

测试

package com.xm;
import com.xm.entity.UserP;
import com.xm.task.JdbcInsert2Task;
import com.xm.task.JdbcInsertTask;
import com.xm.task.MybatisPlusInsert2Task;
import com.xm.task.MybatisPlusInsertTask;
import com.xm.util.CommonUtil;
import com.xm.util.DataSourceUtil;
import com.xm.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
/**
 * 串行批量插入
 */
@Slf4j
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class ThreadTests {
    /**
     * 并发线程池
     */
    protected static ExecutorService executorService;
    static {
        executorService = Executors.newFixedThreadPool(10);
    }
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    private final int[] totalArr = { 10000, 50000, 100000, 300000, 500000, 1000000 };
    /**
     * 总计数（更新下标进行批量测试）
     */
    private final int total = totalArr[5];
    /**
     * 单批次最大计数（误解分区，单批次固定执行1000条数据，而不应该在线程中再拆分批次 这样占用的连接并不会及时释放）
     */
    private final int batchSize = 50000;
    /**
     * 测试总次数（测试总条数 = total * count；耗时ms = 总耗时 / count）
     */
    private final int count = 10;//插入的总条数增加，插入速度将会越来越慢（测试平均值：先清表再测试）

    //jdbc sql内置拼接，串行批量插入10000条数据结束。result=true，耗时0.2138s
    //jdbc sql内置拼接，串行批量插入50000条数据结束。result=true，耗时0.5108s
    //jdbc sql内置拼接，串行批量插入100000条数据结束。result=true，耗时0.8939s
    //jdbc sql内置拼接，串行批量插入300000条数据结束。result=true，耗时2.7112s
    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时5.1734s
    //jdbc sql内置拼接，串行批量插入1000000条数据结束。result=true，耗时9.6879s

    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时6204/5892/5811/5806/5861/5712/6360/6477/5864/6108ms，平均耗时：6009.5，单批次最大计数10000，单批次批量提交1000
    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时6072ms，单批次最大计数50000，单批次批量提交1000
    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时7786ms，单批次最大计数100000，单批次批量提交1000
    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时5880/5978/5654/5780/6067/5814/5814/5662/6100/6118ms，平均耗时：5886.7，单批次最大计数10000，单批次批量提交5000
    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时6179ms，单批次最大计数50000，单批次批量提交5000
    //jdbc sql内置拼接，串行批量插入500000条数据结束。result=true，耗时7960ms，单批次最大计数100000，单批次批量提交5000
    /**
     * jdbc(sql内置拼接)
     */
    @Test
    void jdbc() throws InterruptedException {
        long start = System.currentTimeMillis();
        long other = 0;
        try {
            for (int i = 0; i < count; i++) {
                long begin = System.currentTimeMillis();
                List<UserP> users = CommonUtil.getUsers2(total, i, "jdbc sql内置拼接");
                other += System.currentTimeMillis() - begin;
                int commitSize = DataSourceUtil.getCommitSize();
                List<List<?>> chunks = StringUtil.groupBatchSize(users, commitSize);
                List<Future<String>> futures = new ArrayList<>();
                for (List<?> userList : chunks) {
                    Future<String> future = (Future<String>) executorService.submit(new JdbcInsertTask((List<UserP>) userList));
                    futures.add(future);
                }
                //查询总记录条数为0
                CommonUtil.waitFutures(futures);
                //查询总记录条数为total，表示执行结束，并立即清空表；为下次重置执行环境
//                long totalCount = this.count();
//                log.info("总记录数={}", totalCount);
                begin = System.currentTimeMillis();
                CommonUtil.clearTable();
                other += System.currentTimeMillis() - begin;
            }
        } catch (Exception e) {
            log.error("jdbc，串行批量插入数据异常！e={}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            long time = System.currentTimeMillis() - start - other;
            executorService.shutdown();
            boolean result = executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
            long time0 = DataSourceUtil.atomicLong.get();
            String before = StringUtil.formatDecimal((double) time0 / 1000L, 4, RoundingMode.HALF_UP);
            String after = StringUtil.formatDecimal((double) time / count / 1000L, 4, RoundingMode.HALF_UP);
            log.info("jdbc，串行批量插入{}*{}条数据结束。result={}，原耗时{}s，现耗时{}s", total, count, result, before, after);
        }
    }

    //jdbc2 sql手动拼接，串行批量插入10000条数据结束。result=true，耗时0.1968s
    //jdbc2 sql手动拼接，串行批量插入50000条数据结束。result=true，耗时0.5352s
    //jdbc2 sql手动拼接，串行批量插入100000条数据结束。result=true，耗时0.9192s
    //jdbc2 sql手动拼接，串行批量插入300000条数据结束。result=true，耗时2.3785s
    //jdbc2 sql手动拼接，串行批量插入500000条数据结束。result=true，耗时4.7409s
    //jdbc2 sql手动拼接，串行批量插入1000000条数据结束。result=true，耗时9.2848s
    /**
     * jdbc(sql手动拼接)
     */
    @Test
    void jdbc2() throws InterruptedException {
        long start = System.currentTimeMillis();
        long other = 0;
        try {
            for (int i = 0; i < count; i++) {
                long begin = System.currentTimeMillis();
                List<UserP> users = CommonUtil.getUsers2(total, i, "jdbc2 sql手动拼接");
                other += System.currentTimeMillis() - begin;
                int commitSize = DataSourceUtil.getCommitSize();
                List<List<?>> chunks = StringUtil.groupBatchSize(users, commitSize);
                List<Future<String>> futures = new ArrayList<>();
                for (List<?> userList : chunks) {
                    Future<String> future = (Future<String>) executorService.submit(new JdbcInsert2Task((List<UserP>) userList));
                    futures.add(future);
                }
                CommonUtil.waitFutures(futures);
                begin = System.currentTimeMillis();
                CommonUtil.clearTable();
                other += System.currentTimeMillis() - begin;
            }
        } catch (Exception e) {
            log.error("jdbc2，串行批量插入数据异常！e={}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            long time = System.currentTimeMillis() - start - other;
            executorService.shutdown();
            boolean result = executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
            long time0 = DataSourceUtil.atomicLong.get();
            String before = StringUtil.formatDecimal((double) time0 / 1000L, 4, RoundingMode.HALF_UP);
            String after = StringUtil.formatDecimal((double) time / count / 1000L, 4, RoundingMode.HALF_UP);
            log.info("jdbc2，串行批量插入{}*{}条数据结束。result={}，原耗时{}s，现耗时{}s", total, count, result, before, after);
        }
    }

    //mybatis-plus 逐个insert，串行批量插入10000条数据结束。result=true，耗时0.2984s
    //mybatis-plus 逐个insert，串行批量插入50000条数据结束。result=true，耗时0.7840s
    //mybatis-plus 逐个insert，串行批量插入100000条数据结束。result=true，耗时1.6001s
    //mybatis-plus 逐个insert，串行批量插入300000条数据结束。result=true，耗时4.0869s
    //mybatis-plus 逐个insert，串行批量插入500000条数据结束。result=true，耗时6.5886s
    //mybatis-plus 逐个insert，串行批量插入1000000条数据结束。result=true，耗时13.1609s
    /**
     * mybatis-plus(逐个insert)
     */
    @Test
    void mybatis_plus() throws InterruptedException {
        long start = System.currentTimeMillis();
        long other = 0;
        try {
            for (int i = 0; i < count; i++) {
                long begin = System.currentTimeMillis();
                List<UserP> users = CommonUtil.getUsers2(total, i, "mybatis-plus 逐个insert");
                other += System.currentTimeMillis() - begin;
                int commitSize = DataSourceUtil.getCommitSize();
                List<List<?>> chunks = StringUtil.groupBatchSize(users, commitSize);
                List<Future<String>> futures = new ArrayList<>();
                for (List<?> userList : chunks) {
                    Future<String> future = (Future<String>) executorService.submit(new MybatisPlusInsertTask((List<UserP>) userList, sqlSessionFactory));
                    futures.add(future);
                }
                CommonUtil.waitFutures(futures);
                begin = System.currentTimeMillis();
                CommonUtil.clearTable();
                other += System.currentTimeMillis() - begin;
            }
        } catch (Exception e) {
            log.error("mybatis-plus，串行批量插入数据异常！e={}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            long time = System.currentTimeMillis() - start - other;
            executorService.shutdown();
            boolean result = executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
            long time0 = DataSourceUtil.atomicLong.get();
            String before = StringUtil.formatDecimal((double) time0 / 1000L, 4, RoundingMode.HALF_UP);
            String after = StringUtil.formatDecimal((double) time / count / 1000L, 4, RoundingMode.HALF_UP);
            log.info("mybatis-plus，串行批量插入{}*{}条数据结束。result={}，原耗时{}s，现耗时{}s", total, count, result, before, after);
        }
    }

    //mybatis-plus2 批量batchAdd，串行批量插入10000条数据结束。result=true，耗时0.6944s
    //mybatis-plus2 批量batchAdd，串行批量插入50000条数据结束。result=true，耗时1.1751s
    //mybatis-plus2 批量batchAdd，串行批量插入100000条数据结束。result=true，耗时2.0128s
    //mybatis-plus2 批量batchAdd，串行批量插入300000条数据结束。result=true，耗时5.6418s
    //mybatis-plus2 批量batchAdd，串行批量插入500000条数据结束。result=true，耗时8.6080s
    //mybatis-plus2 批量batchAdd，串行批量插入1000000条数据结束。result=true，耗时16.4599s
    /**
     * mybatis-plus(批量batchAdd)
     */
    @Test
    void mybatis_plus2() throws InterruptedException {
        long start = System.currentTimeMillis();
        long other = 0;
        try {
            for (int i = 0; i < count; i++) {
                long begin = System.currentTimeMillis();
                List<UserP> users = CommonUtil.getUsers2(total, i, "mybatis-plus2 批量batchAdd");
                other += System.currentTimeMillis() - begin;
                int commitSize = DataSourceUtil.getCommitSize();
                List<List<?>> chunks = StringUtil.groupBatchSize(users, commitSize);
                List<Future<String>> futures = new ArrayList<>();
                for (List<?> userList : chunks) {
                    Future<String> future = (Future<String>) executorService.submit(new MybatisPlusInsert2Task((List<UserP>) userList, sqlSessionFactory));
                    futures.add(future);
                }
                CommonUtil.waitFutures(futures);
                begin = System.currentTimeMillis();
                CommonUtil.clearTable();
                other += System.currentTimeMillis() - begin;
            }
        } catch (Exception e) {
            log.error("mybatis-plus2，串行批量插入数据异常！e={}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            long time = System.currentTimeMillis() - start - other;
            executorService.shutdown();
            boolean result = executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
            long time0 = DataSourceUtil.atomicLong.get();
            String before = StringUtil.formatDecimal((double) time0 / 1000L, 4, RoundingMode.HALF_UP);
            String after = StringUtil.formatDecimal((double) time / count / 1000L, 4, RoundingMode.HALF_UP);
            log.info("mybatis-plus2，串行批量插入{}*{}条数据结束。result={}，原耗时{}s，现耗时{}s", total, count, result, before, after);
        }
    }
}

线程任务JdbcInsertTask

package com.xm.task;
import com.xm.entity.UserP;
import com.xm.util.DataSourceUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
/**
 * jdbc(sql内置拼接) 批量插入数据任务
 */
@Slf4j
public class JdbcInsertTask implements Runnable {
    private final List<UserP> users;
    public JdbcInsertTask(List<UserP> users) {
        this.users = users;
    }

    @Override
    public void run() {
        if(!CollectionUtils.isEmpty(users)) {
            long start = System.currentTimeMillis();
            //1、注册驱动
            //2、创建数据库连接
//            long start2 = System.currentTimeMillis();
//            conn = DataSourceUtil.getHikariConnection();
//            conn = DataSourceUtil.getC3p0Connection();
//            log.info("从数据库连接池获取连接，耗时{}ms", System.currentTimeMillis() - start2);
            try (Connection conn = DataSourceUtil.getHikariConnection();) {
                //3、开启事务/取消事务自动提交
                conn.setAutoCommit(false);
                //4、执行SQL 获取结果集
                String sql = "INSERT INTO `user` " +
                        "(`uuid`, `name`, `height`, `weight`, `age`, `bool`, `signature`, `delMark`, `createTime`, `updateTime`, `long_field`) " +
                        "VALUES " +
                        "(?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?)";
                try (PreparedStatement ps = conn.prepareStatement(sql)) {
                    for (UserP user : users) {
                        ps.setString(1, user.getUuid());
                        ps.setString(2, user.getName());
                        ps.setBigDecimal(3, user.getHeight());
                        ps.setBigDecimal(4, user.getWeight());
                        ps.setInt(5, user.getAge());
                        ps.setBoolean(6, user.getBool());
                        ps.setString(7, user.getSignature());
                        ps.setString(8, user.getCreateTimeStr());
                        ps.setString(9, user.getUpdateTimeStr());
                        ps.setString(10, user.getLongField());
                        ps.addBatch();
                    }
                    ps.executeBatch();
                    conn.commit();
                    ps.clearBatch();
                }
            } catch (SQLException e) {
                //5、关闭连接（try-with-resources 异常或结束后会自动关闭资源）
                log.error("JdbcInsertTask 批量插入数据异常！e={}", e.getMessage());
                throw new RuntimeException(e);
            } finally {
                int total = users.size();
                long time = System.currentTimeMillis() - start;
                long totalTime = DataSourceUtil.atomicLong.addAndGet(time);
                log.info("JdbcInsertTask 批量插入{}条数据，耗时{}ms，totalTime={}", total, time, totalTime);
            }
        }
    }
}

线程任务JdbcInsert2Task

package com.xm.task;
import com.xm.entity.UserP;
import com.xm.util.DataSourceUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
/**
 * jdbc(sql手动拼接) 批量插入数据任务
 */
@Slf4j
public class JdbcInsert2Task implements Runnable {
    private final List<UserP> users;
    public JdbcInsert2Task(List<UserP> users) {
        this.users = users;
    }

    @Override
    public void run() {
        if(!CollectionUtils.isEmpty(users)) {
            long start = System.currentTimeMillis();
            //1、注册驱动
            //2、创建数据库连接
            try (Connection conn = DataSourceUtil.getHikariConnection()) {
                //3、开启事务/取消事务自动提交
                conn.setAutoCommit(false);
                //4、执行SQL 获取结果集
                String sql = "INSERT INTO `user` " +
                        "(`uuid`, `name`, `height`, `weight`, `age`, `bool`, `signature`, `delMark`, `createTime`, `updateTime`, `long_field`) " +
                        "VALUES ";
                try (Statement statement = conn.createStatement()) {
                    StringBuilder sqlBuilder = new StringBuilder(sql);
                    for (UserP user : users) {
                        sqlBuilder.append("(");
                        sqlBuilder.append("'").append(user.getUuid()).append("'").append(", ")
                                .append("'").append(user.getName()).append("'").append(",")
                                .append(user.getHeight()).append(",")
                                .append(user.getWeight()).append(",")
                                .append(user.getAge()).append(",")
                                .append(user.getBool() ? 1 : 0).append(",")
                                .append("'").append(user.getSignature()).append("'").append(",")
                                .append(user.getDelMark()).append(",")
                                .append("'").append(user.getCreateTimeStr()).append("'").append(",")
                                .append("'").append(user.getUpdateTimeStr()).append("'").append(",")
                                .append("'").append("Hello jdbc！").append("'")
                        ;
                        sqlBuilder.append("),");
                    }
                    sqlBuilder.deleteCharAt(sqlBuilder.toString().length() - 1);
                    statement.executeUpdate(sqlBuilder.toString());
                    //一次性提交事务
                    conn.commit();
                }
            } catch (SQLException e) {
                //5、关闭连接（try-with-resources 异常或结束后会自动关闭资源）
                log.error("JdbcInsert2Task 批量插入数据异常！e={}", e.getMessage());
                throw new RuntimeException(e);
            } finally {
                int total = users.size();
                long time = System.currentTimeMillis() - start;
                long totalTime = DataSourceUtil.atomicLong.addAndGet(time);
                log.info("JdbcInsert2Task 批量插入{}条数据，耗时{}ms，totalTime={}", total, time, totalTime);
            }
        }
    }
}

线程任务MybatisPlusInsertTask

package com.xm.task;
import com.xm.entity.UserP;
import com.xm.mapper.UserMapper;
import com.xm.util.DataSourceUtil;
import com.xm.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.util.CollectionUtils;
import java.util.List;
/**
 * mybatis-plus(逐个insert) 批量插入数据任务
 */
@Slf4j
public class MybatisPlusInsertTask implements Runnable {
    private final List<UserP> users;
    private final SqlSessionFactory sqlSessionFactory;

    public MybatisPlusInsertTask(List<UserP> users, SqlSessionFactory sqlSessionFactory) {
        this.users = users;
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public void run() {
        if(!CollectionUtils.isEmpty(users)) {
            long start = System.currentTimeMillis();
            int total = users.size();
            //批量插入users
            try (SqlSession sqlSession = this.sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                for (UserP user : users) {
                    userMapper.insert(user);
                }
                sqlSession.commit();
            } catch (Exception e) {
                log.error("MybatisPlusInsertTask 批量插入数据异常！e={}", e.getMessage());
                throw new RuntimeException(e);
            } finally {
                long time = System.currentTimeMillis() - start;
                long totalTime = DataSourceUtil.atomicLong.addAndGet(time);
                log.info("MybatisPlusInsertTask 批量插入{}条数据，耗时{}ms，totalTime={}", total, time, totalTime);
            }
        }
    }
}

线程任务MybatisPlusInsert2Task

package com.xm.task;
import com.xm.entity.UserP;
import com.xm.mapper.UserMapper;
import com.xm.util.DataSourceUtil;
import com.xm.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.util.CollectionUtils;
import java.util.List;
/**
 * mybatis-plus(批量batchAdd) 批量插入数据任务
 */
@Slf4j
public class MybatisPlusInsert2Task implements Runnable {
    private final List<UserP> users;
    private final SqlSessionFactory sqlSessionFactory;

    public MybatisPlusInsert2Task(List<UserP> users, SqlSessionFactory sqlSessionFactory) {
        this.users = users;
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public void run() {
        if(!CollectionUtils.isEmpty(users)) {
            long start = System.currentTimeMillis();
            //批量插入users
            try (SqlSession sqlSession = this.sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                userMapper.batchAdd(users);
                sqlSession.commit();
            } catch (Exception e) {
                log.error("MybatisPlusInsertTask2 批量插入数据异常！e={}", e.getMessage());
                throw new RuntimeException(e);
            } finally {
                int total = users.size();
                long time = System.currentTimeMillis() - start;
                long totalTime = DataSourceUtil.atomicLong.addAndGet(time);
                log.info("MybatisPlusInsertTask2 批量插入{}条数据，耗时{}ms，totalTime={}", total, time, totalTime);
            }
        }
    }
}

注：测试有限，仅供参考。