java mysql 高性能批量处理设置rewriteBatchedStatements

深藏阁楼爱情的钟 2022-06-07 13:36 328阅读 0赞

昨天研究了一下mysql的批处理，最初发现很慢

10000条 每次1000 243秒  
50000条 每次500条 1295秒

代码如下：

**\[java\]** [view plain][] [copy][view plain]

[print][view plain] [?][view plain]

1.  **package** main; 
2.  
3.  **import** java.sql.\*; 
4.  
5.  **public****class** JdbcStreaming \{ 
6.  **public****static****void** main(String\[\] args)\{ 
7.  **try** \{ 
8.   Connection conn = **null**; 
9.   Class.forName("com.mysql.jdbc.Driver").newInstance(); // MYSQL驱动
10.  conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test\_deletable?useUnicode=true&characterEncoding=UTF-8", 
11. "mysql", "mysql"); // 链接本地MYSQL
12. **int** batchSize = 50000; 
13. **int** count = 5000000; 
14.  conn.setAutoCommit(**false**);//设置自动提交为false
15.  PreparedStatement ps = conn.prepareStatement("insert into test (id) values (?)"); 
16.  Long t1 = System.currentTimeMillis(); 
17.  System.out.println("===================="); 
18. **for** (**long** i = 1; i < count; i++) \{ 
19.  ps.setLong(1, i);//设置第一个参数的值为i
20.  ps.addBatch();//将该条记录添加到批处理中
21. **if** (i% batchSize == 0) \{ 
22.  ps.executeBatch();//执行批处理
23.  conn.commit();//提交
24.  System.out.println(i+":添加"\+batchSize+"条"); 
25.  \} 
26.  \} 
27. **if** ((count+1) % batchSize != 0) \{ 
28.  ps.executeBatch(); 
29.  conn.commit(); 
30.  \} 
31.  ps.close(); 
32.  Long t2 = System.currentTimeMillis(); 
33.  System.out.println(count+"条 每次"\+batchSize+"条 "\+(t2-t1)/1000\+"秒"); 
34.  \} **catch** (Exception e) \{ 
35. // TODO: handle exception
36.  e.printStackTrace(); 
37.  \} 
38.  \} 
39. \}

很明显，1万条数据，需要执行243秒。

这个代码的性能很低，首先排除该表的复杂性（该表只有两个字段，id为自增主键），二、排除逻辑复杂性（从代码可以看出，这段代码只是简单的把i值赋给id）

后来又调整了很多参数，但都没有找到根本原因，执行时间会有稍微提高或者没什么变化。

最后，添加参数rewriteBatchedStatements=true

即：

**\[java\]** [view plain][] [copy][view plain]

[print][view plain] [?][view plain]

1.  conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test\_deletable?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8", 
2.  "mysql", "mysql"); // 链接本地MYSQL

执行时间提高100倍以上

50000条 每次500条 7秒

后续修改批处理大小，得到执行时间不一样。

5000000条 每次500条 849秒

5000000条 每次5000条 179秒

5000000条 每次50000条 90秒

5000000条 每次100000条 211秒

可以看出来，批处理大小的设置，也是影响执行时间的一个重要原因，但并不是设置的越大越好，或者越小越好。

下面介绍一下rewriteBatchedStatements

**MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。**

**MySQL Jdbc驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，直接造成较低的性能。  
  
只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL (jdbc:mysql://ip:port/db?rewriteBatchedStatements=true)。**

另外，有人说rewriteBatchedStatements只对INSERT有效，有人说它对UPDATE/DELETE也有效。为此我做了一些实验（详见下文），结论是： 这个选项对INSERT/UPDATE/DELETE都有效，只不过对INSERT它为会预先重排一下SQL语句。  
  
  
实验记录：未打开rewriteBatchedStatements时  
  
  
未打开rewriteBatchedStatements时，根据wireshark嗅探出的mysql报文可以看出，  
 batchDelete(10条记录) => 发送10次delete 请求  
 batchUpdate(10条记录) => 发送10次update 请求  
 batchInsert(10条记录) => 发送10次insert 请求  
  
也就是说，batchXXX()的确不起作用  
  
实验记录：打开了rewriteBatchedStatements后  
  
  
打开rewriteBatchedStatements后，根据wireshark嗅探出的mysql报文可以看出  
  
 batchDelete(10条记录) => 发送一次请求，内容为”delete from t where id = 1; delete from t where id = 2; delete from t where id = 3; ….”  
 batchUpdate(10条记录) => 发送一次请求，内容为”update t set … where id = 1; update t set … where id = 2; update t set … where id = 3 …”  
 batchInsert(10条记录) => 发送一次请求，内容为”insert into t (…) values (…) , (…), (…)”  
  
对delete和update，驱动所做的事就是把多条sql语句累积起来再一次性发出去；而对于insert，驱动则会把多条sql语句重写成一条风格很酷的sql语句，然后再发出去。 官方文档说，这种insert写法可以提高性能（”This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements”）  
  
一个注意事项  
  
  
需要注意的是，即使rewriteBatchedStatements=true, batchDelete()和batchUpdate()也不一定会走批量：  当batchSize <= 3时，驱动会宁愿一条一条地执行SQL 。所以，如果你想验证rewriteBatchedStatements在你的系统里是否已经生效，记得要使用较大的batch.

![Center][]

[view plain]: http://blog.csdn.net/tolcf/article/details/52102849#
[Center]: /images/20220607/b64715545c8344c9a01cedce5409d63f.png