jdbc mysql设置rewriteBatchedStatements参数实现高性能批量处理 executeBatch返回值问题

一、摘要

利用jdbc预处理PreparedStatement.executeBatch可实现sql批处理，但是数据库层面是否真正实现批处理，不同数据库表现不一。以mysql为例，只有jdbcUrl设置了rewriteBatchedStatements=true参数，mysql驱动才会真正执行sql批处理，从而显著提高性能。但是一旦设置rewriteBatchedStatements=true后，PreparedStatement.executeBatch()的返回结果也会发生变化，为此代码需要特殊处理。

二、有无参数rewriteBatchedStatements比较

1、数据表 jdbc_student

CREATE TABLE `jdbc_student` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、没有rewriteBatchedStatements=true参数

java代码

public static void main(String[] args) {
	Connection conn = null;
	try {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String jdbcUrl = "jdbc:mysql://ip:3306/dbname?useUnicode=true&characterEncoding=utf-8";
		conn = DriverManager.getConnection(jdbcUrl,"user", "pwd");
		int batchSize = 5000;
		int count = 0;
		conn.setAutoCommit(false);	//设置自动提交为false
		PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
		for (int i = 1; i <= batchSize; i++) {
		    ps.setString(1, "name: " + i);	//设置第2个参数, name
		    ps.setInt(2, i % 30 + 10);		//设置第3个参数, age
		    ps.addBatch();		//将该条记录添加到批处理中
		}
		Long t1 = System.currentTimeMillis();
		System.out.println("开始执行: " + t1);
        int rows[] = ps.executeBatch();
        for(int row : rows) {
        	count += row;
        }
        conn.commit();	//提交
		ps.close();
		Long t2 = System.currentTimeMillis();
		System.out.println("执行结束：" + t2 + ", 耗时："+(t2-t1)/1000+"秒, batchRows: "+batchSize+"条, affectedRows: " + count);
		if(conn!=null) {
			conn.close();
		}
	} catch (Exception e) {
		e.printStackTrace();
		if(conn!=null) {
			try {
				conn.close();
			} catch (Exception ee) {
				//TODO
			}
		}
	}
}

输出结果

开始执行: 1540629124028
执行结束: 1540629189490, 耗时：65秒, batchRows: 5000条, affectedRows: 5000

3、设置rewriteBatchedStatements=true参数

java代码

public static void main(String[] args) {
	Connection conn = null;
	try {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String jdbcUrl = "jdbc:mysql://ip:3306/dbname?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8";
		conn = DriverManager.getConnection(jdbcUrl,"user", "pwd");
		int batchSize = 5000;
		int count = 0;
		conn.setAutoCommit(false);	//设置自动提交为false
		PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
		for (int i = 1; i <= batchSize; i++) {
		    ps.setString(1, "name: " + i);	//设置第2个参数, name
		    ps.setInt(2, i % 30 + 10);		//设置第3个参数, age
		    ps.addBatch();		//将该条记录添加到批处理中
		}
		Long t1 = System.currentTimeMillis();
		System.out.println("开始执行: " + t1);
        int rows[] = ps.executeBatch();
        for(int row : rows) {
        	count += row;
        }
        conn.commit();	//提交
		ps.close();
		Long t2 = System.currentTimeMillis();
		System.out.println("执行结束：" + t2 + ", 耗时："+(t2-t1)/1000+"秒, batchRows: "+batchSize+"条, affectedRows: " + count);
		if(conn!=null) {
			conn.close();
		}
	} catch (Exception e) {
		e.printStackTrace();
		if(conn!=null) {
			try {
				conn.close();
			} catch (Exception ee) {
				//TODO
			}
		}
	}
}

输出结果

开始执行: 1540629414786
执行结束: 1540629414931, 耗时：0秒, batchRows: 5000条, affectedRows: 25000000

三、executeBatch返回值处理

1、有无rewriteBatchedStatements参数，两者执行性能真是天壤之别：

无rewriteBatchedStatements参数，执行时间：65秒
有rewriteBatchedStatements参数，执行时间：不到1秒

2、executeBatch返回值：

无rewriteBatchedStatements参数，返回值：int[]累加，5000
有rewriteBatchedStatements参数，返回值：int[]累加，25000000

3、有rewriteBatchedStatements参数，executeBatch返回值解释：

1）设置rewriteBatchedStatements参数后，可以简单理解为数据库将5000条insert-sql语句打包成一条sql语句，执行这条sql语句返回总影响行数5000, 如：
INSERT INTO jdbc_student VALUES (‘55000’, ‘name: 1’, ‘11’), (‘55001’, ‘name: 1’, ‘11’), (‘55002’, ‘name: 2’, ‘12’),;
2）但是驱动executeBatch返回的是每条sql语句对应的影响记录数数组，即[5000,5000,5000,…,5000],所以累计就是5000*5000 = 25000000
3）update, delete, 返回的仍然是[1,1,1…1]无需特殊处理。

4、executeBatch返回值应用

以销售模块更新订单主细表为例，假设订单细表有10条行项目，分2张出库单出库，分别有不同人员操作（谁先操作不确定）：
1）第一张出库单出库4个订单行项目，出库后
订单细表行项目状态：已调度->已出库
订单主表状态：已调度->(行项目是否全部出库 ? ‘已出库’ : ‘部分出库’)
2）第二张出库单出库6个订单行项目，出库后
订单细表行项目状态：已调度->已出库
订单主表状态：已调度->(行项目是否全部出库 ? ‘已出库’ : ‘部分出库’)

sql批处理的封装，以java代码为例，假设调用sql批处理代码封装如下：

public int executeBatch(String sql, List<Map> paras) {
	//预处理sql：insert into jdbc_student (name, age) values (?,?)
	//paras,参数集合，[{name:"name1",age:11},{name:"name2",age:12},...]
	//返回影响记录条数
}

更新订单主细表状态代码逻辑：

• 开启事务
• 更新订单细表(状态, 操作时间, 操作人, 出库数量等): 已调度->已出库，jdbc批处理实现，判断executeBatch(sql, paras) == paras.size()
• 查询细表是否全部已出库？并更新主表状态
• 提交事务

以出库单1为例：

• 没rewriteBatchedStatements参数，executeBatch(sql, paras) == paras.size() == 4
• 有rewriteBatchedStatements参数，executeBatch(sql, paras) = 16，paras.size() = 4，两者不等，程序报错！

实际业务中，通常paras.size即executeBatch返回结果，因此判断是否相等修改如下即可：

executeBatch(sql, paras) == paras.size() || executeBatch(sql, paras) == paras.size() * paras.size()