Mysql批量插入数据问题解决和优化
一、问题描述
项目中mysql批量插入大概50000左右数据,使用事务和批量,但是速度依旧很慢,大约60s左右,迫切希望改进这个问题。
二、问题原因
尽管是批量addBatch()但是,mysql却默认没有执行批量,时间还是一条一条添加导致速度非常慢。解决办法:链接mysql字符串添加 rewriteBatchedStatements=true
三、解决和验证
1.测试代码
(1)新建测试表语句
create table mysqltest(
id INT,
name char(10)
)
create table mysqltest1(
id INT,
name char(10)
)
(2)Mysql链接工厂类
package bulk.mysql;
import java.sql.Connection;
import java.sql.DriverManager;
public class MySQLConnections {
private String driver = "";
private String dbURL = "";
private String user = "";
private String password = "";
private static MySQLConnections connection = null;
private MySQLConnections() throws Exception {
driver = "com.mysql.jdbc.Driver";
dbURL = "jdbc:mysql://ip:3306/test?rewriteBatchedStatements=true";
user = "root";
password = "passwd";
System.out.println("dbURL:" + dbURL);
}
public static Connection getConnection() {
Connection conn = null;
if (connection == null) {
try {
connection = new MySQLConnections();
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
try {
Class.forName(connection.driver);
conn = DriverManager.getConnection(connection.dbURL,
connection.user, connection.password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
(3)Mysql更新类
package bulk.mysql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class MySQLUpdate {
static Connection con = MySQLConnections.getConnection();
static PreparedStatement stmt = null;
public static int executeInsert() throws SQLException {
int i = 0;
//设置批量处理的数量
int batchSize = 5000;
stmt = con.prepareStatement("insert into mysqltest (id,name) "
+ "values (?,?)");
// 关闭事务自动提交 ,这一行必须加上
con.setAutoCommit(false);
for (int j = 0; j < 50005; j++){
++i;
stmt.setInt(1, j);
stmt.setString(2, "name");
stmt.addBatch();
if ( i % batchSize == 0 ) {
stmt.executeBatch();
con.commit();
}
}
if ( i % batchSize != 0 ) {
stmt.executeBatch();
con.commit();
}
return i;
}
public static void executeInsert2() throws SQLException {
// 关闭事务自动提交 ,这一行必须加上
con.setAutoCommit(false);
stmt = con.prepareStatement("insert into mysqltest (id,name) "
+ "values (?,?)");
for (int j = 0; j < 50002; j++){
stmt.setInt(1, j);
stmt.setString(2, "name");
stmt.addBatch();
}
stmt.executeBatch();
con.commit();
stmt.close();
con.close();
}
}
(4)Mysql测试类
package bulk.mysql;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) throws SQLException {
long begin1 = System.currentTimeMillis();
MySQLUpdate.executeInsert();
long end1 = System.currentTimeMillis();
System.out.println("程序运行时间为:" + (end1 - begin1));
long begin2 = System.currentTimeMillis();
MySQLUpdate.executeInsert2();
long end2 = System.currentTimeMillis();
System.out.println("程序运行时间为:" + (end2 - begin2));
}
}
2.测试链接字符串不同配置
(1)为dbURL = “jdbc:mysql://ip:3306/test”;时
运行结果
程序测试插入时间
程序运行时间为:62095
程序运行时间为:60129
(2)为dbURL = “jdbc:mysql://ip:3306/test?rewriteBatchedStatements=true”;时
运行结果
程序运行时间为:2181
程序运行时间为:419
3.结论
优化了一百倍左右,这个真的很夸张了,而且确认最好一次性提交