mysql 多线程 事务_jdbc批处理+手动事务+多线程实现81秒插入千万数据（多线程版)...

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

/**

* @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参数。