转自http://blog.itpub.net/29254281/viewspace-1151785/
使用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毫秒
程序如下:
- 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);
- }
- }
程序如下:
- 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);
- }
- }
rewriteBatchedStatements=true
开启MySQL的查询日志general_log,发现如下SQL
INSERT INTO test
VALUES (11, 'test'), (12, 'test'), (13, 'test')......
相对Oracle的批量处理,MySQL需要JDBC参数显式开启,并且对于JDBC驱动的版本也有要求。