方法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();
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!