java 下执行mysql 批量插入的几种方法及用时

方法1：

java code

conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_pass);
        pstmt = conn
                .preparestatement("insert into loadtest (id, data) values (?, ?)");
        for (int i = 1; i <= count; i++) {
            pstmt.clearparameters();
            pstmt.setint(1, i);
            pstmt.setstring(2, data);
            pstmt.execute();
        }

myisam：246.6秒、innodb：360.2秒

方法2： 使用事务，不自动commit

java code

conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_pass);
        conn.setautocommit(false);
        pstmt = conn
                .preparestatement("insert into loadtest (id, data) values (?, ?)");
        for (int i = 1; i <= count; i++) {
            pstmt.clearparameters();
            pstmt.setint(1, i);
            pstmt.setstring(2, data);
            pstmt.execute();
            if (i % commit_size == 0) {
                conn.commit();
            }
        }
        conn.commit();

innodb：31.5秒

方法3： executebatch

java code

conn = drivermanager.getconnection(jdbc_url
                + "?rewritebatchedstatements=true", jdbc_user, jdbc_pass);
        conn.setautocommit(false);
        pstmt = conn
                .preparestatement("insert into loadtest (id, data) values (?, ?)");
        for (int i = 1; i <= count; i += batch_size) {
            pstmt.clearbatch();
            for (int j = 0; j < batch_size; j++) {
                pstmt.setint(1, i + j);
                pstmt.setstring(2, data);
                pstmt.addbatch();
            }
            pstmt.executebatch();
            if ((i + batch_size - 1) % commit_size == 0) {
                conn.commit();
            }
        }
        conn.commit();

innodb：5.2秒

上面的使用时必须
1）rewritebatchedstatements=true
2）useserverprepstmts=true

方法4：先load再commit

java code

conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_pass);
        conn.setautocommit(false);
        pstmt = conn.preparestatement("load data local infile '' "
                + "into table loadtest fields terminated by ','");
        stringbuilder sb = new stringbuilder();
        for (int i = 1; i <= count; i++) {
            sb.append(i + "," + data + "\n");
            if (i % commit_size == 0) {
                inputstream is = new bytearrayinputstream(sb.tostring()
                        .getbytes());
                ((com.mysql.jdbc.statement) pstmt)
                        .setlocalinfileinputstream(is);
                pstmt.execute();
                conn.commit();
                sb.setlength(0);
            }
        }
        inputstream is = new bytearrayinputstream(sb.tostring().getbytes());
        ((com.mysql.jdbc.statement) pstmt).setlocalinfileinputstream(is);
        pstmt.execute();
        conn.commit();