/**
* mysql批处理性能比较,总50000数据量 不加批处理(testNoBatch),单条处理,共计63秒
* 加批处理,连接字符串不加&rewriteBatchedStatements=true,共计38秒
* 加批处理,连接字符串加&rewriteBatchedStatements=true,共计2秒
*
* 加了rewriteBatchedStatements虽然性能提升显著,但也有不好的地方,就是如果批处理中有一条失败了,那么后面的都会失败。
* 而不加这个参数,他会一直执行,后面正确数据也会执行。 例:table A(a varchar(10)) 现在批处理以下三条 insert
* A(a)(’1’); insert A(a)(’22222222222222222’); insert A(a)(’eeeee’);
* 两种批处理都会报错,报错后继续提交(conn.commit()),
* 加了参数后只能插入一条,而不加能插入2条。另外加了之后,插入顺序也与addBatch顺序不一定一样。插入成功的那一条有可能为1,也可能为eeeee
*
* @author chengzhong
*
*/
public class TestBatchURL {
// private static Log log=LogFactory.getLogger(TestBatch.class);
/**
* @param args
*/
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
// &rewriteBatchedStatements=true
Connection conn = DriverManager
.getConnection("jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF8", "root", "");
// conn.setAutoCommit(false);
conn.createStatement().execute("truncate table test");
// testNoBatch(); 63秒
try {
// testBatch(conn);//2秒
testBatchNoParam(conn);// 38秒
} catch (Exception e) {
e.printStackTrace();
}
}
private static void testNoBatch(Connection conn) throws Exception {
Statement stm = conn.createStatement();
List sqls = new ArrayList();
Calendar begin = Calendar.getInstance();
for (int i = 0; i < 50000; i++) {
// stm.addBatch("insert into test(a)values(’1’)");
stm.execute("insert into test(a)values(’1’)");
}
System.out.println((Calendar.getInstance().getTimeInMillis() - begin.getTimeInMillis()) / 1000 + "秒");
;
conn.close();
}
private static void testBatch(Connection conn) throws Exception {
conn.setAutoCommit(false);
Statement stm = conn.createStatement();
List sqls = new ArrayList();
Calendar begin = Calendar.getInstance();
for (int i = 0; i < 50000; i++) {
stm.addBatch("insert into test(a)values(’1’)");
if (i % 10000 == 0) {
stm.executeBatch();
conn.commit();
}
}
stm.executeBatch();
conn.commit();
System.out.println((Calendar.getInstance().getTimeInMillis() - begin.getTimeInMillis()) / 1000 + "秒");
;
conn.close();
}
private static void testBatchNoParam(Connection conn) throws Exception {
conn.setAutoCommit(false);
Statement stm = conn.createStatement();
List sqls = new ArrayList();
Calendar begin = Calendar.getInstance();
for (int i = 0; i < 50000; i++) {
stm.addBatch("insert into test(a)values(’1’)");
if (i % 10000 == 0) {
stm.executeBatch();
conn.commit();
}
}
stm.executeBatch();
conn.commit();
System.out.println((Calendar.getInstance().getTimeInMillis() - begin.getTimeInMillis()) / 1000 + "秒");
;
conn.close();
}
}
2018/6/12 测试
测试环境 mac ,ssd硬盘,mysql-5.7.21,jdk1.8
无批处理: 355秒
不加&rewriteBatchedStatements=true 如上10000批处理一次:4秒
加了上面参数,10000批处理一次:2秒
再加了一种插入方法:
private static void insertValues(Connection conn) throws Exception {
Calendar begin = Calendar.getInstance();
Statement stm = conn.createStatement();
for(int j=0;j<5;j++) {
StringBuffer sb=new StringBuffer();
for(int i=0;i<10000;i++) {
sb.append("('1')");
if(i!=(10000-1)) {
sb.append(",");
}
}
stm.execute("insert into test(a) values "+sb.toString());
}
System.out.println((Calendar.getInstance().getTimeInMillis() - begin.getTimeInMillis()) + "毫秒");
}上面代码用时:209毫秒
加上手动commit,每10000 commit一次:236毫秒
去掉&rewriteBatchedStatements=true连接参数与上面时间差不多。
目前我知道的,这种方式是最快的。可参考: