批量update實(shí)現(xiàn)方案全面解析與最佳實(shí)踐，帶你掌握到底怎么批量更新最快、性能最高
1.概述
在當(dāng)今應(yīng)用開(kāi)發(fā)中，數(shù)據(jù)操作是底層基礎(chǔ)，批量更新是實(shí)際開(kāi)發(fā)中一個(gè)常見(jiàn)的操作，同時(shí)也是一個(gè)性能瓶頸點(diǎn)。有多種批量更新的實(shí)現(xiàn)方式，但不同的方案在性能、可維護(hù)性和數(shù)據(jù)庫(kù)兼容性等方面差異顯著。本文將基于MyBatis全面剖析各種批量更新方案的實(shí)現(xiàn)原理、性能表現(xiàn)和適用場(chǎng)景，幫助開(kāi)發(fā)者做出合理的技術(shù)選型，從而實(shí)現(xiàn)性能最高的更新。
2.準(zhǔn)備工作
這里我們還是以用戶(hù)表tb_user為示例，并且基于上面總結(jié)快速插入了500多萬(wàn)條數(shù)據(jù)：
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`user_no` varchar(255) NOT NULL COMMENT '編號(hào)',
`name` varchar(255) DEFAULT NULL COMMENT '昵稱(chēng)',
`email` varchar(255) DEFAULT NULL COMMENT '郵箱',
`phone` varchar(255) NOT NULL COMMENT '手機(jī)號(hào)',
`gender` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性別 0：男生 1：女生',
`birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '出生日期',
`is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標(biāo)志 0：否 1：是',
`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
`update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
`creator` bigint(20) DEFAULT NULL COMMENT '創(chuàng)建人',
`updater` bigint(20) DEFAULT NULL COMMENT '更新人',
`address` varchar(1024) DEFAULT NULL COMMENT '地址',
`role_id` varchar(100) DEFAULT NULL COMMENT '角色id',
`hobby` varchar(255) DEFAULT NULL COMMENT '愛(ài)好',
`remark` varchar(255) DEFAULT NULL COMMENT '個(gè)人說(shuō)明',
`org_id` bigint(20) NOT NULL COMMENT '公司id',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_user_no` (`user_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5201026 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;當(dāng)同樣更新100條數(shù)據(jù)時(shí)，小表(幾千條)和大表(幾百萬(wàn)條)使用相同的批量更新方式，執(zhí)行效率會(huì)有差異，差異程度取決于多個(gè)因素
效率不會(huì)完全相同，但差異可能不明顯，主要因?yàn)椋?/p>
- 數(shù)據(jù)定位成本：大表可能需要更多I/O來(lái)定位記錄
- 索引結(jié)構(gòu)差異：大表的索引層級(jí)可能更深
- 內(nèi)存緩存影響：小表更可能完全緩存在內(nèi)存中
所以我這里為了更能突出區(qū)別不同批量更新方案的執(zhí)行效率，選擇了對(duì)大表進(jìn)行批量更新10000條數(shù)據(jù)來(lái)示例。當(dāng)然了執(zhí)行效率還與MySQL服務(wù)的配置有關(guān)，配置2核2G和4核8G肯定是不一樣的。
3.批量更新實(shí)現(xiàn)方案
這里我先查出10000條數(shù)據(jù)，更新user的name,gender,address等字段
public List<User> listUsers() {
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.select(User::getId, User::getName);
queryWrapper.ge(User::getId, 10000L).lt(User::getId, 20000L);
List<User> users = userDAO.selectList(queryWrapper);
users.forEach(user -> {
user.setName(user.getName() + "1");
user.setAddress("杭州" + user.getId());
user.setGender(user.getId() % 2 == 0 ? 1 : 0);
user.setUpdateTime(new Date());
});
return users;
}3.1 循環(huán)單條更新
這種方式最簡(jiǎn)單，直接看代碼：
@Test
public void testBatchUpdateByFor() {
List<User> users = listUsers();
long start = System.currentTimeMillis();
users.forEach(user -> {
userDAO.updateById(user);
});
long end = System.currentTimeMillis();
System.out.println("執(zhí)行時(shí)長(zhǎng)：" + (end - start) + "ms");
}執(zhí)行SQL部分如下：
c.p.b.e.mybatis.dao.UserDAO.updateById : ==> Preparing: UPDATE tb_user SET gender=?, address=?, name=?, update_time=?, updater=? WHERE id=?
c.p.b.e.mybatis.dao.UserDAO.updateById : ==> Parameters: 1(Integer), 杭州19998(String), 羅百夜1(String), 2025-07-08 11:08:23.588(Timestamp), null, 19998(Long)
c.p.b.e.mybatis.dao.UserDAO.updateById : <== Updates: 1
c.p.b.e.mybatis.dao.UserDAO.updateById : ==> Preparing: UPDATE tb_user SET gender=?, address=?, name=?, update_time=?, updater=? WHERE id=?
c.p.b.e.mybatis.dao.UserDAO.updateById : ==> Parameters: 0(Integer), 杭州19999(String), 張七土1(String), 2025-07-08 11:08:23.588(Timestamp), null, 19999(Long)
c.p.b.e.mybatis.dao.UserDAO.updateById : <== Updates: 1可以看出是一條一條提交執(zhí)行的。
執(zhí)行時(shí)長(zhǎng)：3846ms
這種方式產(chǎn)生N條獨(dú)立SQL語(yǔ)句，網(wǎng)絡(luò)IO次數(shù)與數(shù)據(jù)量成正比，性能很差，在平時(shí)開(kāi)發(fā)中幾乎不能使用，當(dāng)然如果是操作小表小批量數(shù)據(jù)，也問(wèn)題不大，但最好別這么寫(xiě)，顯得代碼水平不行，同時(shí)這種方式也是代碼性能提升方式經(jīng)常提到一大問(wèn)題點(diǎn)：for循環(huán)里面單條操作SQL語(yǔ)句，這種方式寫(xiě)了就有性能問(wèn)題~~~
3.2 foreach多條SQL
這種方式需要通過(guò)XML寫(xiě)SQL語(yǔ)句實(shí)現(xiàn)
public interface UserDAO extends BaseMapperX<User> {
int batchUpdateByForeach(@Param("userList") List<User> userList);
}XML配置如下：
<update id="batchUpdateByForeach">
<foreach collection="userList" item="u" separator=";">
UPDATE tb_user
SET
update_time = now()
<if test="u.name != null">
,name = #{u.name}
</if>
<if test="u.address != null">
,address = #{u.address}
</if>
<if test="u.gender != null">
,gender = #{u.gender}
</if>
WHERE id = #{u.id}
</foreach>
</update>測(cè)試代碼：
@Test
public void testBatchUpdateByForeach() {
List<User> users = listUsers();
long start = System.currentTimeMillis();
// 分批處理
List<List<User>> splitList = CollUtil.split(users, 500);
splitList.forEach(userList -> {
userDAO.batchUpdateByForeach(userList);
});
long end = System.currentTimeMillis();
System.out.println("執(zhí)行時(shí)長(zhǎng)：" + (end - start) + "ms");
}這里我只給出了3條數(shù)據(jù)的更新SQL，500條全給出來(lái)太多了。
c.p.b.e.m.d.U.batchUpdateByForeach : ==> Preparing: UPDATE tb_user SET update_time = now() ,name = ? ,address = ? ,gender = ? WHERE id = ? ; UPDATE tb_user SET update_time = now() ,name = ? ,address = ? ,gender = ? WHERE id = ? ; UPDATE tb_user SET update_time = now() ,name = ? ,address = ? ,gender = ? WHERE id = ? ;
c.p.b.e.m.d.U.batchUpdateByForeach : ==> Parameters: 王十金1111(String), 杭州13000(String), 1(Integer), 13000(Long), 楊一月1111(String), 杭州13001(String), 0(Integer), 13001(Long), 周六云1111(String), 杭州13002(String), 1(Integer), 13002(Long)
2025-07-08T13:55:41.618+08:00 DEBUG 53878 --- [plasticene-boot-mybatis-example] [ main] c.p.b.e.m.d.U.batchUpdateByForeach : <== Updates: 1可以看出是單次請(qǐng)求包含多條SQL語(yǔ)句，但本質(zhì)上每條數(shù)據(jù)都是單獨(dú)執(zhí)行更新的
執(zhí)行時(shí)長(zhǎng)：1417ms
3.3 CASE WHEN表達(dá)式
直接看XML配置里面寫(xiě)的SQL語(yǔ)句：
<update id="batchUpdateByCaseWhen">
UPDATE tb_user
SET
update_time=now(),
name = CASE
<foreach collection="userList" item="item">
WHEN id = #{item.id} AND #{item.name} IS NOT NULL THEN #{item.name}
</foreach>
ELSE name
END,
address = CASE
<foreach collection="userList" item="item">
WHEN id = #{item.id} AND #{item.address} IS NOT NULL THEN #{item.address}
</foreach>
ELSE address
END,
gender = CASE
<foreach collection="userList" item="item">
WHEN id = #{item.id} AND #{item.gender} IS NOT NULL THEN #{item.gender}
</foreach>
ELSE gender
END
WHERE id IN
<foreach collection="userList" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>測(cè)試代碼：
@Test
public void testBatchUpdateByCaseWhen() {
List<User> users = listUsers();
long start = System.currentTimeMillis();
// 分批處理
List<List<User>> splitList = CollUtil.split(users, 500);
for (List<User> userList : splitList) {
userDAO.batchUpdateByCaseWhen(userList);
}
long end = System.currentTimeMillis();
System.out.println("執(zhí)行時(shí)長(zhǎng)：" + (end - start) + "ms");
}這里就不給出控制臺(tái)的輸出的SQL語(yǔ)句了，太長(zhǎng)了，大家自行執(zhí)行查看
執(zhí)行時(shí)長(zhǎng)：988ms
真正的單SQL批量操作，性能很好，但要注意防止SQL語(yǔ)句長(zhǎng)度超過(guò)限制。
3.4 ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE是MySQL特有語(yǔ)法，批量插入，遇到主鍵/唯一鍵沖突時(shí)轉(zhuǎn)為更新。
<insert id="batchUpdateOnDuplicate">
INSERT INTO tb_user(user_no, name, phone, address, gender, org_id) VALUES
<foreach collection="userList" item="item" separator=",">
(#{item.userNo}, #{item.name}, #{item.phone}, #{item.address}, #{item.gender}, #{item.orgId})
</foreach>
ON DUPLICATE KEY UPDATE
name=VALUES(name), org_id=VALUES(org_id)
</insert>測(cè)試代碼：
@Test
public void testBatchUpdateOnDuplicate() {
List<User> users = listUsers();
long start = System.currentTimeMillis();
// 分批處理
List<List<User>> splitList = CollUtil.split(users, 500);
for (List<User> userList : splitList) {
userDAO.batchUpdateOnDuplicate(userList);
}
long end = System.currentTimeMillis();
System.out.println("執(zhí)行時(shí)長(zhǎng)：" + (end - start) + "ms");
}執(zhí)行時(shí)長(zhǎng)：1080ms
3.5 REPLACE INTO
replace into與on duplicate key update在一定程度上都能實(shí)現(xiàn)無(wú)記錄時(shí)插入，有記錄時(shí)更新。其判斷都是根據(jù)主鍵/唯一鍵是否存在，但是replace into實(shí)現(xiàn)更新的方式是先刪除在插入，這就會(huì)產(chǎn)生兩個(gè)binlog，可能導(dǎo)致消費(fèi)binlog出問(wèn)題，同時(shí)這種更新如果是唯一鍵沖突，那么先刪后插會(huì)導(dǎo)致主鍵變了，如果之前的主鍵id有在其他表關(guān)聯(lián)使用，這種更新是很危險(xiǎn)的。
<insert id="batchUpdateReplace">
REPLACE INTO tb_user(user_no, name, phone, address, gender, org_id) VALUES
<foreach collection="userList" item="item" separator=",">
(#{item.userNo}, #{item.name}, #{item.phone}, #{item.address}, #{item.gender}, #{item.orgId})
</foreach>
</insert>測(cè)試代碼：
@Test
public void testBatchUpdateReplace() {
List<User> users = listUsers();
long start = System.currentTimeMillis();
// 分批處理
List<List<User>> splitList = CollUtil.split(users, 500);
for (List<User> userList : splitList) {
userDAO.batchUpdateReplace(userList);
}
long end = System.currentTimeMillis();
System.out.println("執(zhí)行時(shí)長(zhǎng)：" + (end - start) + "ms");
}執(zhí)行時(shí)長(zhǎng)：6705ms
3.6 通過(guò)MyBatis-Plus批量更新
直接看代碼：
@Test
public void testBatchUpdateByMybatisPlus() {
List<User> users = listUsers();
long start = System.currentTimeMillis();
userDAO.updateById(users, 500);
long end = System.currentTimeMillis();
System.out.println("執(zhí)行時(shí)長(zhǎng)：" + (end - start) + "ms");
}執(zhí)行時(shí)長(zhǎng)：1730ms
4.性能對(duì)比表格
方案 | 1萬(wàn)條耗時(shí) | 網(wǎng)絡(luò)IO次數(shù) | SQL解析次數(shù) | 適用數(shù)據(jù)量 | 數(shù)據(jù)庫(kù)兼容性 |
for循環(huán)單條更新 | 3.-4.s | N | N | <100 | 全兼容 |
foreach多條SQL | 1-2s | 1 | N | 100-5000 | 需配置 |
mybaits-plus | 1-2s | 1 | 1 | 100-5000 | 全兼容 |
CASE WHEN | 0.5-1s | 1 | 1 | >1000 | 全兼容 |
ON DUPLICATE KEY UPDATE | 0.5-1s | 1 | 1 | >1000 | MySQL only |
replace into | 4-7s | 1 | N | 100-3000 | 全兼容 |
除了for循環(huán)單條更新不推薦之外，其他方式我個(gè)人感覺(jué)都可以選擇，可以根據(jù)具體場(chǎng)景選擇具體方式。追求極致性能首選case when
如果存在做更新，沒(méi)有就插入實(shí)現(xiàn)方案首選ON DUPLICATE KEY UPDATE，因?yàn)閞eplace into操作可能存在問(wèn)題，具體看上面敘述，當(dāng)然了MyBatis-Plus提供了saveOrUpdateBatch可以操作小批量數(shù)據(jù)，因?yàn)樗讓邮莊or循環(huán)單條操作實(shí)現(xiàn)的，比較慢。
5.總結(jié)
批量更新方案的選擇需要綜合考慮數(shù)據(jù)庫(kù)類(lèi)型、數(shù)據(jù)量大小、系統(tǒng)架構(gòu)要求和團(tuán)隊(duì)技術(shù)棧等因素。對(duì)于大多數(shù)MySQL應(yīng)用場(chǎng)景，ON DUPLICATE KEY UPDATE方案提供了最佳的性能和可維護(hù)性平衡。而在需要多數(shù)據(jù)庫(kù)支持的場(chǎng)景中，CASE WHEN表達(dá)式則是更為通用的選擇。無(wú)論采用哪種方案，都應(yīng)該結(jié)合分批次處理、連接參數(shù)優(yōu)化和適當(dāng)?shù)谋O(jiān)控手段，才能在實(shí)際生產(chǎn)環(huán)境中獲得理想的性能表現(xiàn)。
