mysql 批量操作优化（转一）

mysql 批量操作优化（转1）

?? }
?? try {
??? con = DriverManager.getConnection(host, user, passwd);
??? con.setAutoCommit(false);???
??? StringBuilder sql = new StringBuilder("");
??? long start = System.currentTimeMillis();
??? int j = 0;
??? for (int i = 0; i < 1000; i++) {
???? sql = new StringBuilder("");
???? sql
?????? .append("insert into person (id,name,sex,phone,orgId) values");
???? for (int k = 0; k < 1000; k++) {
????? if (k == 999) {
?????? sql.append("('").append(i * 1000 + k + 1).append("',")
???????? .append("'这条记录是第=").append(i + 1)
???????? .append("',' ").append("',' ").append("','1')");
????? } else {
?????? sql.append("('").append(i * 1000 + k + 1).append("',")
???????? .append("'这条记录是第=").append(i + 1)
???????? .append("',' ").append("',' ")
???????? .append("','1'),");
????? }
???? }????
???? PreparedStatement p = con.prepareStatement(sql.toString());
???? p.executeUpdate();
???? p.addBatch();// 将一组参数添加到此 PreparedStatement 对象的批处理命令中。????
???? j++;
???? if (j == 2) {
????? con.commit(); // 每2K条提交一次
????? j = 0;
????? System.out.println("index=" + i);
???? }
???? if (i % 200 == 0) {
????? p.clearBatch();// 每20w条清理一下批处理
???? }
???? con.commit();
???? p.close();
??? }
??? s.close();
??? con.close();
??? long end = System.currentTimeMillis();
??? System.out.println("manyInsert()执行时间为：" + (end - start) + "ms");

?? } catch (Exception e) {
??? e.printStackTrace();
?? }

}
}
总结：批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,如一次插入1000条，然后提交批处理，批量插入的引入使性能大大提高了。