提升 MySQL 批量更新效率的底層原理與優化策略
作者：碼農SharkChili
? 如果網絡情況良好且 MySQL 連接池資源充分的情況下，筆者更推薦使用并行進行逐條更新。
近期進行項目優化梳理工作時，發現某些功能模塊進行MySQL數據庫批量更新操作比較耗時，對此筆者查閱相關資料比進行壓測后，得出最優解，遂以此文章記錄一下筆者的解決方案。
一、前置準備
為方便演示，筆者先說明一下本文進行實驗的數據表，對應的DDL語句如下，可以看到該表有一個自增的主鍵ID和9個字段以及一個日期字段：
CREATE TABLE `batch_insert_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fileid_1` varchar(100) DEFAULT NULL,
`fileid_2` varchar(100) DEFAULT NULL,
`fileid_3` varchar(100) DEFAULT NULL,
`fileid_4` varchar(100) DEFAULT NULL,
`fileid_5` varchar(100) DEFAULT NULL,
`fileid_6` varchar(100) DEFAULT NULL,
`fileid_7` varchar(100) DEFAULT NULL,
`fileid_8` varchar(100) DEFAULT NULL,
`fileid_9` varchar(100) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `batch_insert_test_create_date_IDX` (`create_date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19091237 DEFAULT CHARSET=utf8 COMMENT='測試批量插入，一行數據1k左右';特別注意，讀者在根據本文進行操作時需要對數據庫連接配置上追加如下兩個參數，否則優化方案不會生效：
&rewriteBatchedStatements=true&allowMultiQueries=true二、三種方案壓測實驗
1. 逐條更新
首先查看逐條更新的解決方案，筆者通過分頁查詢查詢大約3000條數據，然后逐條進行遍歷更新：
/**
* 使用foreach進行逐條插入
*/
@Test
public void foreachUpdate() {
//分頁查詢3k的數據
PageHelper.startPage(PAGE, SIZE);
List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
//逐條更新
StopWatch stopWatch = new StopWatch("foreachUpdate");
stopWatch.start();
for (BatchInsertTest insertTest : insertTestList) {
batchInsertTestMapper.updateByPrimaryKey(insertTest);
}
stopWatch.stop();
log.info("逐條更新完成,size:{},耗時:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
}對應耗時結果如下，可以看到耗時花費了1592ms，表現比較遜色，原因很簡單，每條數據操作時都涉及網絡IO，3000次串行的網絡IO+DB更新，執行效率自然上不去：
2025-01-10 09:07:02.920 INFO 19328 --- [ main] c.s.mapper.BatchInsertTestMapperTest : 逐條更新完成,size:3000,耗時:1592ms2. 并行運算
不知道讀者是否留意筆者上文所說的串行DB更新，既然串行的網絡IO會降低執行效率，那么我們并行更新呢？
所以筆者將代碼進行進一步的優化：
/**
* 使用并行流foreach進行逐條插入
*/
@Test
public void foreachParallelStreamUpdate() {
PageHelper.startPage(PAGE, SIZE);
List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
//采用并行流的方式進行并行更新
StopWatch stopWatch = new StopWatch("foreachUpdate");
stopWatch.start();
insertTestList.parallelStream()
.forEach(i -> {
batchInsertTestMapper.updateByPrimaryKey(i);
});
stopWatch.stop();
log.info("逐條更新完成,size:{},耗時:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
}可以看到3000條數據花費了357ms，執行效率還是很客觀的，但筆者認為這還不是最優解，原因很簡單，每次進行批量更新操作都需要進行多次網絡IO，如果在并發量非常大的場景，比較吃MySQL的連接池資源：
2025-01-10 09:07:00.789 INFO 19328 --- [ main] c.s.mapper.BatchInsertTestMapperTest : 逐條更新完成,size:3000,耗時:357ms3. foreach更新
我們再來看看mybatis內置的foreach語法的批量更新：
<update id="updateByPrimaryKeyForeach" parameterType="com.sharkChili.domain.BatchInsertTest">
<foreach collection="list" item="item" separator=";">
update batch_insert_test
set fileid_1 = #{item.fileid1,jdbcType=VARCHAR},
fileid_2 = #{item.fileid2,jdbcType=VARCHAR},
fileid_3 = #{item.fileid3,jdbcType=VARCHAR},
fileid_4 = #{item.fileid4,jdbcType=VARCHAR},
fileid_5 = #{item.fileid5,jdbcType=VARCHAR},
fileid_6 = #{item.fileid6,jdbcType=VARCHAR},
fileid_7 = #{item.fileid7,jdbcType=VARCHAR},
fileid_8 = #{item.fileid8,jdbcType=VARCHAR},
fileid_9 = #{item.fileid9,jdbcType=VARCHAR},
create_date = #{item.createDate,jdbcType=TIMESTAMP}
where id = #{item.id,jdbcType=INTEGER}
</foreach>
</update>對應測試代碼如下：
@Test
public void updateByPrimaryKeyForeach() {
PageHelper.startPage(PAGE, SIZE);
List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
StopWatch stopWatch = new StopWatch("updateByPrimaryKeyForeach");
stopWatch.start();
batchInsertTestMapper.updateByPrimaryKeyForeach(insertTestList);
stopWatch.stop();
log.info("使用updateByPrimaryKeyForeach更新完成,size:{},耗時:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
}耗時563ms左右，性能表現也還行，并且foreach操作會因為字符串拼接操導致Packet for query is too large (106,100,142 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.即提交的SQL執行數據包過大被拒絕的風險:
2025-01-10 09:10:57.592 INFO 18332 --- [ main] c.s.mapper.BatchInsertTestMapperTest : 使用updateByPrimaryKeyForeach更新完成,size:3000,耗時:563ms4. 批處理更新
筆者希望可以一批更新操作可以一個批次的進行提交，所以接下來介紹這種方案就是一次性組裝一批量的更新語句，然后一次性提交。
/**
* 使用批處理進行更新
*/
@Test
public void updateBatch() {
PageHelper.startPage(PAGE, SIZE);
List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
StopWatch stopWatch = new StopWatch("updateBatch");
stopWatch.start();
//創建一個進行批處理操作的sqlsession組裝一批更新語句
try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
BatchInsertTestMapper batchInsertTestMapper = sqlSession.getMapper(BatchInsertTestMapper.class);
insertTestList.parallelStream()
.forEach(i -> {
batchInsertTestMapper.updateByPrimaryKey(i);
});
//手動提交
sqlSession.commit();
stopWatch.stop();
} catch (Exception e) {
}
log.info("批處理更新完成,size:{},耗時:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
}最終更新耗時為1s左右，相較于上述方案相對遜色一些，但是網絡IO的開銷以及MySQL的連接池使用都減小了，綜合起來性價比還是蠻高的：
2024-02-22 23:25:05.265 INFO 18844 --- [ main] c.s.mapper.BatchInsertTestMapperTest : 批處理更新完成,size:3000,耗時:1566ms5. case-when更新
最后一種case-when更新，語法如下，猛的一看比較復雜，實際理解起來還是蠻簡單的，對每個字段進行set操作，例如:當id等于1時，fileid_1則取id為1的那條數據的值，通過case分支實現一條SQL批量更新多條數據：
update batch_insert_test
-- 當id=1 則設置fileid_1為aaa
set fileid_1=
when 1 then aaa
....其余同理
where id in (本次批處理的id列表)所以結合mybatis框架的語法，我們得出下面這樣一個SQL語句：
<update id="updateBatch" parameterType="java.util.List">
update batch_insert_test
set fileid_1=
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid1,jdbcType=VARCHAR}
</foreach>,
fileid_2 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid2,jdbcType=VARCHAR}
</foreach>,
fileid_3 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid3,jdbcType=VARCHAR}
</foreach>,
fileid_4 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid4,jdbcType=VARCHAR}
</foreach>,
fileid_5 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid5,jdbcType=VARCHAR}
</foreach>,
fileid_6 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid6,jdbcType=VARCHAR}
</foreach>,
fileid_7 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid7,jdbcType=VARCHAR}
</foreach>,
fileid_8 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid8,jdbcType=VARCHAR}
</foreach>,
fileid_9 =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.fileid9,jdbcType=VARCHAR}
</foreach>,
create_date=
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.createDate,jdbcType=TIMESTAMP}
</foreach>
where id in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.id,jdbcType=INTEGER}
</foreach>
</update>對應的Java代碼如下，比較簡單，筆者這里就不多做贅述了：
@Test
public void updateDateByWhenCase() {
PageHelper.startPage(PAGE, SIZE);
List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
StopWatch stopWatch = new StopWatch("updateBatch");
stopWatch.start();
batchInsertTestMapper.updateBatch(insertTestList);
stopWatch.stop();
log.info("使用when case更新完成,size:{},耗時:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
}最終可以看到耗時800毫秒左右，相較于批處理更加出色一些，而且網絡和連接池的開銷都是差不多的，但和foreach意義也可能存在數據包過大導致報錯的風險：
2025-01-10 09:17:06.878 INFO 16788 --- [ main] c.s.mapper.BatchInsertTestMapperTest : 使用when case更新完成,size:3000,耗時:738ms小結
以上便是筆者本次大量壓測后得出的解決方案，總結如下：
- 如果網絡情況良好且MySQL連接池資源充分的情況下，筆者更推薦使用并行進行逐條更新。
- 如果網絡情況不好或者MySQL資源緊張，筆者更推薦使用foreach更新，相較于同等一次性更新多條語句的when-case語法,它語法更簡單且執行性能更好一些。
- 當然如果一次性要更新比較大基數的數據，考慮到MySQL的傳輸packet size我們還是優先考慮批處理這個性能和穩定性處于折中的方案。
責任編輯：趙寧寧
來源：
寫代碼的SharkChili
