一次大量数据更新的性能优化调研
问题 工作中需要同步一些数据，大概接近百万条的量级，更新时间非常慢，需要7个多小时，更新的频率是每周一次。随着数据量的一步步增加，时间也越来越多，逐渐成为一个风险因子，于是想到要尝试做一些优化，降低同步时间。
分析 经过调查，需要同步的是TABLE_A，同步的过程可以简化表述为两步：
- Call API_B to get updated value.
- Update records in DB.
- API call costs: ~350ms.
- DB update costs: ~20ms.
实际使用了一个大小为10的线程池，100h除以10的话，就是10h，再算上一些冗余的不需要更新的数据（估计20%左右），理论计算时间和实际时间是在一个数量级上的。
所以，可以从两个方面着手优化，一是优化调用API的时间，二是优化DB写数据的时间。
解决 - API 分析API调用的代码，看到是一条一条调用的，使用了
Object callAPI(Object input)这个函数。首先，做一个压力测试，看下callAPI()这个方法每秒可以处理多少请求。ExecutorService executor = Executors.newFixedThreadPool(100);
for (int i=0;
i<100;
i++) {
executor.submit(() -> log.info(callAPI(input)));
}开100个线程，每个线程请求100次，总计10000个request。根据总耗时，计算出
callAPI()可以处理请求约50个/秒。这个速度显然已经满足不了我们的系统了。联系上游API开发部门，经过协商，短期来看，可以为我们配置单独的机器来提高生产环境的query速度。从长远来看，
- 需要联合开发一个batch query的接口，即
List，减少调用的次数，节约中间环节的开销。 - 精简输出Object的参数。原先对象内有30+个参数，有些字段还很长。但是并不是每个参数都有变化的。改进后，输出只需要传回变化的字段就可以了，减少网络传输的时间。
以下是模拟测试DB update的部分，单次更新：
@Test
public void updateOneByOne() {
// initialize
Environment.initialize("qa", "test_batch_update");
System.out.println("INI DONE, start count time.");
// update
long start = System.currentTimeMillis();
for (int i=0;
i批量更新：
@Test
public void updateByBatch() {
Environment.initialize("qa", "test_batch_update");
System.out.println("INI DONE, start count time.");
// prep obj
List dummyObjList = new ArrayList<>();
for (int i=0;
i dummyObjList) {
int [] updateCounts = testBatchUpdateJdbcTemplate.batchUpdate(
UPDATE_SQL_WITH_PARAM,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
preparedStatement.setString(1, dummyObjList.get(i).desc1);
preparedStatement.setInt(2, dummyObjList.get(i).id);
}
@Override
public int getBatchSize() {
return dummyObjList.size();
}
}
);
return updateCounts;
} Spring XML config:
测试的结果是：对于500条数据，单次更新耗时370s，批量更新耗时9s。批量更新快了约40倍。
这里的一个坑是：不同数据库，比如生产环境v.s.开发环境，有可能性能表现是不一样的。比如上面的（简化后的）例子是在开发环境下的测试结果，1条数据单次更新大概耗时1.3s，而在生产环境的实际数据是20ms。这时候做优化需要找准baseline，不然搞半天发现用了新方法竟然还比生产环境的旧方法慢，其实是开发的数据库本身相对更慢而已。
总结 最终，解决的方案是：一方面，联系上游API部门开发新接口。另一方面，使用batch update优化DB写入。
题外话 在解决这个问题的过程中，还看了许多其它的文章，有一篇谈到了Oracle改进sql，update之前先做一个排序，让数据尽可能靠近。（Oracle大数据量更新方法）
另外，还看了一篇，讲到了修改
rewriteBatchedStatements参数，解决Spring jdbcTemplate batch update无效的问题。（jdbcTemplate.batchUpdate批量执行性能差解决）虽然最终看下来和本题无关，但还是挺有收获的。
【一次大量数据更新的性能优化调研】转载于:https://www.cnblogs.com/maxstack/p/10719447.html
推荐阅读
- Docker应用:容器间通信与Mariadb数据库主从复制
- 【故障公告】周五下午的一次突发故障
- 我要我们在一起（二）
- 洱海不是海，，人群没有你
- 使用协程爬取网页，计算网页数据大小
- 我的拖延症如何控制了我，又一次
- Java|Java基础——数组
- Python数据分析(一)（Matplotlib使用）
- Jsr303做前端数据校验
- 跟身体谈恋爱