使用MySQL的Batch批量处理,
JDBC驱动版本需要5.1.13或以上
测试使用的JDBC驱动版本:mysql-connector-java-5.1.30-bin
测试表结构如下:
CREATE TABLE test (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL
) ENGINE=InnoDB
首先使用普通的方式插入100万条数据,使用时间81948毫秒
程序如下:
程序如下:
rewriteBatchedStatements=true
开启MySQL的查询日志general_log,发现如下SQL
INSERT INTO test
VALUES (11, 'test'), (12, 'test'), (13, 'test')......
相对Oracle的批量处理,MySQL需要JDBC参数显式开启,并且对于JDBC驱动的版本也有要求。
JDBC驱动版本需要5.1.13或以上
测试使用的JDBC驱动版本:mysql-connector-java-5.1.30-bin
测试表结构如下:
CREATE TABLE test (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL
) ENGINE=InnoDB
首先使用普通的方式插入100万条数据,使用时间81948毫秒
程序如下:
public class Test {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
long start = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/xx",
"xx", "xx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection
.prepareStatement("insert into test values(?,?)");
for (int i = 0; i < 1000000; i++) {
cmd.setInt(1, i);
cmd.setString(2, "test");
cmd.executeUpdate();
}
connection.commit();
cmd.close();
connection.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
}
使用批量处理,仅用7189毫秒,提升效果非常明显。public static void main(String[] args) throws ClassNotFoundException,
SQLException {
long start = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/xx",
"xx", "xx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection
.prepareStatement("insert into test values(?,?)");
for (int i = 0; i < 1000000; i++) {
cmd.setInt(1, i);
cmd.setString(2, "test");
cmd.executeUpdate();
}
connection.commit();
cmd.close();
connection.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
}
程序如下:
public class Test {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
long start = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/xx?rewriteBatchedStatements=true",
"xx", "xx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection
.prepareStatement("insert into test values(?,?)");
for (int i = 0; i < 1000000; i++) {
cmd.setInt(1, i);
cmd.setString(2, "test");
cmd.addBatch();
if(i%1000==0){
cmd.executeBatch();
}
}
cmd.executeBatch();
connection.commit();
cmd.close();
connection.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
}
与Oracle不同的是,需要添加下面的参数,才可以使用批量处理,否则还是使用逐条处理的方式。public static void main(String[] args) throws ClassNotFoundException,
SQLException {
long start = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/xx?rewriteBatchedStatements=true",
"xx", "xx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection
.prepareStatement("insert into test values(?,?)");
for (int i = 0; i < 1000000; i++) {
cmd.setInt(1, i);
cmd.setString(2, "test");
cmd.addBatch();
if(i%1000==0){
cmd.executeBatch();
}
}
cmd.executeBatch();
connection.commit();
cmd.close();
connection.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
}
rewriteBatchedStatements=true
开启MySQL的查询日志general_log,发现如下SQL
INSERT INTO test
VALUES (11, 'test'), (12, 'test'), (13, 'test')......
相对Oracle的批量处理,MySQL需要JDBC参数显式开启,并且对于JDBC驱动的版本也有要求。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1151785/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1151785/