MySQL批量插入4千万条数据,想问下各位大佬还有没有优化的可能

2024-01-13 17:06

阅读1700

2024-01-13 17:06

阅读 1700

目前有15个txt文件，前14个都有300万行插入语句，插入语句示例如下

INSERT INTO sys_user(id,username,nickname,gender,password,avatar,mobile,status,email,is_deleted,content) VALUES(null,'15664152532','激动的篮球',1,'123456','http://','15664152532',1,'15664152532@qq.com',0,'null');

INSERT INTO sys_user(id,username,nickname,gender,password,avatar,mobile,status,email,is_deleted,content) VALUES(null,'18209582100','跳跃的汽车',1,'123456','http://','18209582100',1,'18209582100@qq.com',0,'null');

INSERT INTO sys_user(id,username,nickname,gender,password,avatar,mobile,status,email,is_deleted,content) VALUES(null,'15278230034','暴躁的啤酒',1,'123456','http://','15278230034',1,'15278230034@qq.com',0,'null');

我采用了3个线程同时处理三个txt文件，900万条数据一共花了2个小时，

private static void batchInsert(String filePath) throws SQLException {

Connection connection = null;

Statement statement = null;

try {

// 连接到MySQL数据库

connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);

// 关闭自动提交，提高性能

connection.setAutoCommit(false);

// 从txt文件中读取插入语句

List<String> insertStatements = readInsertStatementsFromTxt(filePath);

// 使用 Statement，不支持动态参数设置

statement = connection.createStatement();

int i = 0;

// 插入数据

for (String sql : insertStatements) {

i++;

// 添加到批处理

statement.addBatch(sql);

//每1000条提交一次并清空

if (i % 100000 == 0) {

// 执行批处理

statement.executeBatch();

connection.commit();

statement.clearBatch();

}

statement.executeBatch();

connection.commit();

System.out.println(filePath + insertStatements.size() + "Data inserted successfully.");

} catch (SQLException | IOException e) {

e.printStackTrace();

} finally {

// 关闭资源

try {

if (statement != null) {

statement.close();

}

if (connection != null) {

connection.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

public static void main(String[] args) throws SQLException {

long start = System.currentTimeMillis();

ExecutorService executor = Executors.newFixedThreadPool(3);

//Future<?> future1 = executor.submit(() -> {

// try {

// batchInsert("sql.txt");

// System.out.println("sql文件写入成功");

// } catch (Exception e) {

// throw new RuntimeException(e);

// }

//});

//Future<?> future2 = executor.submit(() -> {

// try {

// batchInsert("sql1.txt");

// System.out.println("sql1文件写入成功");

// } catch (Exception e) {

// throw new RuntimeException(e);

// }

//});

//Future<?> future3 = executor.submit(() -> {

// try {

// batchInsert("sql2.txt");

// System.out.println("sql2文件写入成功");

// } catch (Exception e) {

// throw new RuntimeException(e);

// }

//});

Future<?> future4 = executor.submit(() -> {

try {

batchInsert("sql3.txt");

} catch (Exception e) {

throw new RuntimeException(e);

}

});

Future<?> future5 = executor.submit(() -> {

try {

batchInsert("sql4.txt");

} catch (Exception e) {

throw new RuntimeException(e);

}

});

Future<?> future6 = executor.submit(() -> {

try {

batchInsert("sql5.txt");

} catch (Exception e) {

throw new RuntimeException(e);

}

});

try {

//future1.get();

//future2.get();

//future3.get();

future4.get();

future5.get();

future6.get();

System.out.println("所有任务执行完毕");

} catch (InterruptedException | ExecutionException e) {

System.err.println("An error occurred while executing tasks: " + e.getMessage());

e.printStackTrace();

}

System.out.println("合计用时：" + (System.currentTimeMillis() - start));

}