Java MySQL批量插入与正常插入的区别

Question

我尝试通过批量插入来加快向mysql数据库中插入数据的速度。然而，当我运行以下代码时，两种方法得到相同的结果（时间为2分钟或173秒）。欢迎对此发表评论。

MySQLConn mysqlconn = new MySQLConn(db,username,pass);
    Connection conn =mysqlconn.getConnection();
    PreparedStatement ps = null;

    String query = "insert into table (column) values (?)";

    System.out.println("bulk insert started with "+10000 );
    long startTime = System.currentTimeMillis();

    try {
        ps = conn.prepareStatement(query);
        long start = System.currentTimeMillis();
        for(int i =0; i<10000;i++){

            ps.setString(1, "Name"+i);

            ps.addBatch();

            if(i%1000 == 0) ps.executeBatch();
        }
        ps.executeBatch();

        System.out.println("Time Taken="+(System.currentTimeMillis()-start));

    } catch (SQLException e) {
        e.printStackTrace();
    }

    System.out.println("End inserting data...");

    long stopTime = System.currentTimeMillis();
    long elapsedTime = (stopTime - startTime); 

    long days = TimeUnit.MILLISECONDS.toDays(elapsedTime);
    long hours = TimeUnit.MILLISECONDS.toHours(elapsedTime);
    long minutes = TimeUnit.MILLISECONDS.toMinutes(elapsedTime);
    long seconds = TimeUnit.MILLISECONDS.toSeconds(elapsedTime);


    System.out.println("Time elapsed: "+minutes+"min, or "+seconds+"sec");



    System.out.println("normal insert started with "+10000 );

    long startTimeNormal = System.currentTimeMillis();

    for(int i=0;i<10000;i++){ 
        String ins="Data"+i;
        Insert insert = new Insert(conn,ins,"db.table", "column" );
    }
    System.out.println("End inserting data...");

    long stopTimeNormal = System.currentTimeMillis();
    long elapsedTimeNormal = (stopTimeNormal - startTimeNormal); 

    long daysN = TimeUnit.MILLISECONDS.toDays(elapsedTime);
    long hoursN = TimeUnit.MILLISECONDS.toHours(elapsedTime);
    long minutesN = TimeUnit.MILLISECONDS.toMinutes(elapsedTime);
    long secondsN = TimeUnit.MILLISECONDS.toSeconds(elapsedTime);


    System.out.println("Time elapsed: "+minutesN+"min, or "+secondsN+"sec");

- John Fixon

---

2分钟和173秒不是“完全相同的”。 - TZHX

在这两种情况下，时间都是2分钟或173秒（2分钟是长分钟） N = TimeUnit.MILLISECONDS.toMinutes(elapsedTime); - John Fixon

Answer 1

您使用了addBatch和executeBatch，这很好，但您还需要将连接的autocommit设置为false，以实现更快的执行时间。

类似以下内容：

大致如下：

Connection conn = mysqlconn.getConnection();
conn.setAutoCommit(false); // here

PreparedStatement ps = null;

String query = "insert into table (column) values (?)";

System.out.println("bulk insert started with "+10000 );
long startTime = System.currentTimeMillis();

try {
    ps = conn.prepareStatement(query);
    long start = System.currentTimeMillis();
    for(int i =0; i<10000;i++){

        ps.setString(1, "Name"+i);

        ps.addBatch();

        if(i%1000 == 0) {
            ps.executeBatch();
            conn.commit(); // here
        }
    }
    ps.executeBatch();
    conn.commit(); // here

    System.out.println("Time Taken="+(System.currentTimeMillis()-start));

} catch (SQLException e) {
    e.printStackTrace();
}

- Uooo

---

哇喔喔喔喔喔喔喔喔喔喔喔喔喔喔！只需要1秒钟！！！是的，10,000个插入只需要1秒钟...谢谢！ - John Fixon

Answer 2

你需要将 autocommit 设置为 false

conn.setAutoCommit(false);

在每次执行executeBatch()后手动提交。

示例

- krstf