更新百万的mysql数据库的数据会慢吗

转载

mob6454cc73e9a6 2024-11-04 09:01:59

文章标签 更新百万的mysql数据库的数据会慢吗数据库 javascript java ViewUI 文章分类 MySQL 数据库

问题

工作中需要同步一些数据，大概接近百万条的量级，更新时间非常慢，需要7个多小时，更新的频率是每周一次。随着数据量的一步步增加，时间也越来越多，逐渐成为一个风险因子，于是想到要尝试做一些优化，降低同步时间。

分析

经过调查，需要同步的是TABLE_A，同步的过程可以简化表述为两步：

Call API_B to get updated value.
Update records in DB.

首先，检查log，看看这两个过程分别耗时多少。分析生产环境的log，发现，对于一条数据，

API call costs: ~350ms.
DB update costs: ~20ms.

做一个计算，100万条数据，假设每条同步需要0.4s，那么总耗时就是100h，和实际的6h不符。经过思考后解释是：还需要考虑线程和冗余数据。
实际使用了一个大小为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)));
}
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> callBatchAPI(List<Object> inputs)，减少调用的次数，节约中间环节的开销。
精简输出Object的参数。原先对象内有30+个参数，有些字段还很长。但是并不是每个参数都有变化的。改进后，输出只需要传回变化的字段就可以了，减少网络传输的时间。

解决 - DB

不难猜想到，DB也是一条一条调用更新的。优化的策略是batch update。

以下是模拟测试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<recordNum; i++) {
        jdbcTemplate.update(UPDATE_SQL_WITH_PARAM, "SP1_DUMMY_DESC1", i);
    }
    long end = System.currentTimeMillis();

    // count time
    String timeElapsed = DurationFormatUtils.formatPeriod(start, end, "H:mm:ss");
    System.out.println("ACTION DONE, updateOneByOne, elapsed: " + timeElapsed);
}
@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<recordNum; i++) {
        jdbcTemplate.update(UPDATE_SQL_WITH_PARAM, "SP1_DUMMY_DESC1", i);
    }
    long end = System.currentTimeMillis();

    // count time
    String timeElapsed = DurationFormatUtils.formatPeriod(start, end, "H:mm:ss");
    System.out.println("ACTION DONE, updateOneByOne, elapsed: " + timeElapsed);
}

批量更新：

@Test
public void updateByBatch() {
    Environment.initialize("qa", "test_batch_update");
    System.out.println("INI DONE, start count time.");

    // prep obj
    List<DummyObj> dummyObjList = new ArrayList<>();
    for (int i=0; i<recordNum; i++) {
        dummyObjList.add(new DummyObj(i, "BP2_DUMMY_NAME", "BP2_DUMMY_DESC1", "BP2_DUMMY_DESC2", "BP2_DUMMY_DESC3", "BP2_DUMMY_DESC4", "BP2_DUMMY_DESC5"));
    }

    // update
    long start = System.currentTimeMillis();
    this.batchUpdateObj(dummyObjList);
    long end = System.currentTimeMillis();

    // count time
    String timeElapsed = DurationFormatUtils.formatPeriod(start, end, "H:mm:ss");
    System.out.println("ACTION DONE, updateByBatch, time elapsed: " + timeElapsed);
}

private int[] batchUpdateObj(List<DummyObj> 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;
}
@Test
public void updateByBatch() {
    Environment.initialize("qa", "test_batch_update");
    System.out.println("INI DONE, start count time.");

    // prep obj
    List<DummyObj> dummyObjList = new ArrayList<>();
    for (int i=0; i<recordNum; i++) {
        dummyObjList.add(new DummyObj(i, "BP2_DUMMY_NAME", "BP2_DUMMY_DESC1", "BP2_DUMMY_DESC2", "BP2_DUMMY_DESC3", "BP2_DUMMY_DESC4", "BP2_DUMMY_DESC5"));
    }

    // update
    long start = System.currentTimeMillis();
    this.batchUpdateObj(dummyObjList);
    long end = System.currentTimeMillis();

    // count time
    String timeElapsed = DurationFormatUtils.formatPeriod(start, end, "H:mm:ss");
    System.out.println("ACTION DONE, updateByBatch, time elapsed: " + timeElapsed);
}

private int[] batchUpdateObj(List<DummyObj> 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:

<import resource="classpath:/datasource.xml" />

<bean id="batchUpdateTest" class="com.nomura.unity.udw.util.BatchUpdateTest">
    <property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
</bean>

测试的结果是：对于500条数据，单次更新耗时370s，批量更新耗时9s。批量更新快了约40倍。
这里的一个坑是：不同数据库，比如生产环境v.s.开发环境，有可能性能表现是不一样的。比如上面的（简化后的）例子是在开发环境下的测试结果，1条数据单次更新大概耗时1.3s，而在生产环境的实际数据是20ms。这时候做优化需要找准baseline，不然搞半天发现用了新方法竟然还比生产环境的旧方法慢，其实是开发的数据库本身相对更慢而已。