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

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

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

代码如下：

 
 

   
   

    
    

   
   
   
   

    
    

     
     package main;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     import java.sql.*;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     public 
     
     class JdbcStreaming {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
	
     
     public static void main(String[] args){
    
    
   
   

   
   

    
    

   
   
   
   

    
    
		
     
     try {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			Connection conn = 
     
     null;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			Class.forName(
     
     "com.mysql.jdbc.Driver").newInstance(); 
     
     // MYSQL驱动
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			conn = DriverManager.getConnection(
     
     "jdbc:mysql://127.0.0.1:3306/test_deletable?useUnicode=true&characterEncoding=UTF-8",
    
    
   
   

   
   

    
    

   
   
   
   

    
    
							
     
     "mysql", 
     
     "mysql"); 
     
     // 链接本地MYSQL
    
    
   
   

   
   

    
    

   
   
   
   

    
    
			
     
     int batchSize = 
     
     50000;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
			
     
     int count = 
     
     5000000;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			conn.setAutoCommit(
     
     false);
     
     //设置自动提交为false
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			PreparedStatement ps = conn.prepareStatement(
     
     "insert into test (id) values (?)");
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			Long t1 = System.currentTimeMillis();
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			System.out.println(
     
     "====================");
    
    
   
   

   
   

    
    

   
   
   
   

    
    
			
     
     for (
     
     long i = 
     
     1; i < count; i++) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			    ps.setLong(
     
     1, i);
     
     //设置第一个参数的值为i
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			    ps.addBatch();
     
     //将该条记录添加到批处理中
    
    
   
   

   
   

    
    

   
   
   
   

    
    
			    
     
     if (i% batchSize == 
     
     0) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			        ps.executeBatch();
     
     //执行批处理
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			        conn.commit();
     
     //提交
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			        System.out.println(i+
     
     ":添加"+batchSize+
     
     "条");
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			    }
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			}
    
    
   
   

   
   

    
    

   
   
   
   

    
    
			
     
     if ((count+
     
     1) % batchSize != 
     
     0) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			    ps.executeBatch();
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			    conn.commit();
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			}
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			ps.close();
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			Long t2 = System.currentTimeMillis();
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			System.out.println(count+
     
     "条    每次"+batchSize+
     
     "条   "+(t2-t1)/
     
     1000+
     
     "秒");
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     		} 
     
     catch (Exception e) {
    
    
   
   

   
   

    
    

   
   
   
   

    
    
			
     
     // TODO: handle exception
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     			e.printStackTrace();
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     		}
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	}
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     }
    
    
   
   
 
 

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

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

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

最后，添加参数rewriteBatchedStatements=true

即：

 
 

   
   

    
    

   
   
   
   

    
    

     
     conn = DriverManager.getConnection(
     
     "jdbc:mysql://127.0.0.1:3306/test_deletable?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8",
    
    
   
   

   
   

    
    

   
   
   
   

    
    
							
     
     "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.