0x01 测试环境
MySQL 5.7
Innodb默认参数
CentOS7
JDK1.8
内网测试环境
0x02 代码
public static DbConnectionBroker myBroker = null;
static {
try {
myBroker = new DbConnectionBroker("com.mysql.jdbc.Driver", "jdbc:mysql://10.237.81.111:3307/test_batch?characterEncoding=utf-8",
"user", "password", 2, 4, 0.01);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 初始化测试环境
*
* @throws SQLException 异常时抛出
*/
public static void init() throws SQLException {
Connection conn = myBroker.getConnection();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.addBatch("DROP TABLE IF EXISTS tuser1");
stmt.addBatch("CREATE TABLE tuser1 (\n" + " id bigint(20) NOT NULL AUTO_INCREMENT,\n"
+ " name varchar(12) DEFAULT NULL,\n"
+ " PRIMARY KEY (id)\n" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
stmt.executeBatch();
conn.commit();
myBroker.freeConnection(conn);
}
/**
* 批处理执行预处理SQL测试
*
* @param m 批次
* @param n 每批数量
* @throws Exception 异常时抛出
*/
public static void testInsertBatch(int m, int n, int base) throws Exception {
init(); // 初始化环境
Long start = System.currentTimeMillis();
String sql = "" + "insert into test_batch.tuser1\n" + " (id, name)\n" + "values\n"
+ " (?, ?)";
for (int i = 0; i < m; i++) {
// 从池中获取连接
Connection conn = myBroker.getConnection();
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int j = 0; j < n; j++) {
pstmt.setInt(1, base + i*n + j + 1);
pstmt.setString(2, UUID.randomUUID().toString().substring(0, 10));
// 加入批处理
pstmt.addBatch();
}
pstmt.executeBatch(); // 执行批处理
conn.commit();
// pstmt.clearBatch(); //清理批处理
pstmt.close();
myBroker.freeConnection(conn); // 连接归池
}
Long end = System.currentTimeMillis();
System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start)/1000 + "秒！");
}
public static void main(String[] args) throws Exception {
init();
Long start = System.currentTimeMillis();
testInsertBatch(1, 10000, 10000);
testInsertBatch(10, 1000, 20000);
testInsertBatch(25, 400, 30000);
testInsertBatch(40, 250, 40000);
testInsertBatch(50, 200, 50000);
testInsertBatch(100, 100, 60000);
testInsertBatch(200, 50, 70000);
testInsertBatch(250, 40, 80000);
testInsertBatch(500, 20, 90000);
testInsertBatch(1000, 10, 100000);
testInsertBatch(2500, 4, 110000);
testInsertBatch(5000, 2, 120000);
testInsertBatch(10000, 1, 130000);
Long end1 = System.currentTimeMillis();
System.out.println("测试过程结束，全部测试耗时：" + (end1 - start) / 1000f + "秒！");
}
为了排除主键自增带来的影响，计算主键插入。（实际经过测试，主键自增并没有对测试结果带来影响）
排除连接数带来的影响。（实际经过测试，连接数设置为50并没有对测试结果带来影响）
批量执行1*10000=10000条Insert操作，共耗时：12.497秒！
批量执行10*1000=10000条Insert操作，共耗时：12.781秒！
批量执行25*400=10000条Insert操作，共耗时：13.208秒！
批量执行40*250=10000条Insert操作，共耗时：13.865秒！
批量执行50*200=10000条Insert操作，共耗时：13.167秒！
批量执行100*100=10000条Insert操作，共耗时：13.449秒！
批量执行200*50=10000条Insert操作，共耗时：14.124秒！
批量执行250*40=10000条Insert操作，共耗时：15.229秒！
批量执行500*20=10000条Insert操作，共耗时：17.536秒！
批量执行1000*10=10000条Insert操作，共耗时：19.895秒！
批量执行2500*4=10000条Insert操作，共耗时：34.188秒！
批量执行5000*2=10000条Insert操作，共耗时：48.145秒！
批量执行10000*1=10000条Insert操作，共耗时：89.579秒！
测试过程结束，全部测试耗时：319.918秒！
0X03 测试结果
从测试结果来看，在批次不多的情况下，性能差距不大，但是在批次增多的情况下，性能极具下降，当退化为单条发送的时候，性能等同于单条发送。
0x04 补充
public static void testInsertBatch(int m, int n, int base) throws Exception {
...
// 从池中获取连接
Connection conn = myBroker.getConnection();
conn.setAutoCommit(false);
for (int i = 0; i < m; i++) {
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int k = 0; k < n; k++) {
pstmt.setInt(1, base + i*n + k + 1);
pstmt.setString(2, UUID.randomUUID().toString().substring(0, 10));
// 加入批处理
pstmt.addBatch();
}
pstmt.executeBatch(); // 执行批处理
// pstmt.clearBatch(); //清理批处理
pstmt.close();
}
conn.commit();
myBroker.freeConnection(conn); // 连接归池
...
}
将testInsertBatch替换为这段代码后测试的结果如下：
批量执行1*10000=10000条Insert操作，共耗时：16.738秒！
批量执行10*1000=10000条Insert操作，共耗时：15.838秒！
批量执行25*400=10000条Insert操作，共耗时：15.459秒！
批量执行40*250=10000条Insert操作，共耗时：16.383秒！
批量执行50*200=10000条Insert操作，共耗时：18.104秒！
批量执行100*100=10000条Insert操作，共耗时：16.474秒！
批量执行200*50=10000条Insert操作，共耗时：16.634秒！
批量执行250*40=10000条Insert操作，共耗时：18.752秒！
批量执行500*20=10000条Insert操作，共耗时：17.555秒！
批量执行1000*10=10000条Insert操作，共耗时：17.695秒！
批量执行2500*4=10000条Insert操作，共耗时：18.476秒！
批量执行5000*2=10000条Insert操作，共耗时：21.47秒！
批量执行10000*1=10000条Insert操作，共耗时：26.197秒！
结果显示batch的结果的影响并不是很大，真正对性能的影响是commit的次数，提高commit的频率的优点在于降低内存消耗和减少锁的竞争，所以在commit的粒度上的选取，需要一个权衡。
0x05 后续优化
接入hikari连接池的时候，推荐开启参数rewriteBatchedStatements(默认不开启)，这个参数是开启批处理模式。
before:
batchDelete(10条记录) => 发送10次delete 请求
batchUpdate(10条记录) => 发送10次update 请求
batchInsert(10条记录) => 发送10次insert 请求
after:
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，jdbc驱动所做的事就是把多条sql语句累积起来再一次性发出去；而对于insert，jdbc驱动则会把多条insert语句合成一条insert语句，然后再发出去。（”This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements”）
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {
if (canRewriteAsMultiValueInsertAtSqlLevel()) {
return executeBatchedInserts(batchTimeout);
}
if (!this.batchHasPlainStatements && this.batchedArgs != null && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
return executePreparedBatchAsMultiStatement(batchTimeout);
}
}
return executeBatchSerially(batchTimeout);
开启这个参数后测试结果如下：
单条执行10000条Insert操作，共耗时：16.563秒！
批量执行1*10000=10000条Insert操作，共耗时：0.994秒！
批量执行10*1000=10000条Insert操作，共耗时：1.388秒！
批量执行25*400=10000条Insert操作，共耗时：1.231秒！
批量执行40*250=10000条Insert操作，共耗时：0.559秒！
批量执行50*200=10000条Insert操作，共耗时：0.568秒！
批量执行100*100=10000条Insert操作，共耗时：0.451秒！
批量执行200*50=10000条Insert操作，共耗时：0.583秒！
批量执行250*40=10000条Insert操作，共耗时：0.673秒！
批量执行500*20=10000条Insert操作，共耗时：1.192秒！