jdbc批处理+手动事务+多线程实现81秒插入1000万数据（多线程版)

• 现在来试试多线程能够多少秒钟插入千万数据

/**
 * @Author: guandezhi
 * @Date: 2019/4/13 15:35
 */
public class JdbcUtils {

    private static String url = "jdbc:mysql://localhost:3306/user?useSSL=false&rewriteBatchedStatements=true";
    private static String user = "root";
    private static String password = "root";

    private static ExecutorService threadPool = Executors.newFixedThreadPool(50);


    public static void executeBatch() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
            String sql = "insert into user(name, mobile) values(?,?)";
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            for (int j = 0; j < 100000; j++) {
                String mobile = "13356544556";
                Integer randNum = 0;
                Random random = new Random();
                for (int i = 0; i < 1000; i++) {
                    randNum = random.nextInt(10);
                }
                ps.setString(1, "官德志");
                ps.setString(2, mobile + String.valueOf(randNum));
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) throws InterruptedException {
        long beginTime = System.currentTimeMillis();
        CountDownLatch latch = new CountDownLatch(100);
        for (int i = 0; i < 100; i++) {
            threadPool.execute(() -> {
                try {
                    JdbcUtils.executeBatch();
                } catch (Exception e) {
                    System.out.println("插入数据异常");
                } finally {
                    latch.countDown();
                }
            });
        }
        latch.await();
        long endTime = System.currentTimeMillis();
        System.out.println("插入一千万数据用时：" + (endTime - beginTime) / 1000 + " 秒");
        threadPool.shutdown();
    }
}

• 测试一下

 

 

总结： 

      1.多线程确实比单线程快很多

      2.此处开启多线程容易造成OOM,需要合理的设置线程大小和JVM参数。