<think>
我们讨论的是在MyBatis中执行
批量更新操作，特别是当传入一个ID集合时如何高效地
更新记录。
高效
批量更新的关键点：
1
. 避免在循环中执行单条
更新（避免多次数据库往返）
2
. 使用IN子句时注意数据库对IN列表长度的限制（如Oracle限制1000，MySQL默认也有max_allowed_packet限制）
3
. 考虑使用批处理操作（JDBC批处理）来减少网络往返
以下是几种常见的实现方式：
方式一：使用IN子句（适合ID数量可控的情况）
- 使用MyBatis的foreach动态SQL生成IN列表
- 缺点：当ID数量很大时，生成的SQL会很长，可能超出数据库限制，且效率可能下降
方式二：使用临时表（适合ID数量很大的情况）
- 创建临时表，将ID
批量插入临时表，然后使用JOIN
更新
- 优点：避免了长SQL，数据库优化器可以更好地使用索引
- 缺点：需要额外的临时表操作
方式三：使用JDBC批处理（适合
更新不同值的情况，但这里我们
更新的是同一个值，所以不太适用）
- 但如果是
更新同一个值，使用IN子句更合适
由于
问题中
更新的是同一个值（tombstoneTime），我们主要考虑前两种方式。
根据
问题描述，我们选择方式一（当ID数量不大时）或方式二（当ID数量很大时）。
下面分别给出两种方案的实现：
方案一：使用IN子句（适合小
批量）
已经在之前的回答中给出，这里再优化一下：
为了防止传入空列表，我们添加一个判断。
方案二：使用临时表（适合大
批量）
步骤：
1
. 创建一个临时表（可以是会话级别的，事务结束后自动
删除）
2
. 将ID集合插入临时表
3
. 使用JOIN将临时表与原表关联进行
更新
4
. 删除临时表（如果是会话级别的，可以自动
删除，也可以手动
删除）
由于不同数据库创建临时表语法不同，这里以MySQL为例：
临时表方案代码示例：
在MyBatis中，我们需要执行多个SQL语句，所以需要在连接上允许执行多条语句（在连接字符串中添加`allowMultiQueries=true`）。
映射文件中的操作：
```xml
<update id="
batchUpdateWithTempTable">
<!-- 创建临时表 -->
CREATE TEMPORARY TABLE IF NOT EXISTS temp_ids (
id BIGINT PRIMARY KEY
) ENGINE=MEMORY;
<!-- 清空临时表（确保当前会话中之前的临时数据被清除） -->
DELETE FROM temp_ids;
<!--
批量插入ID到临时表 -->
INSERT INTO temp_ids (id) VALUES
<foreach collection="ids" item="id" separator=",">
(#{id})
</foreach>;
<!-- 执行
更新 -->
UPDATE apigc_api_assets ass
JOIN temp_ids tmp ON ass
.ID = tmp
.id
SET ass
.TOMBSTONE_TIME = #{tombstoneTime}
WHERE ass
.ASSETS_SOURCE = '1' <!-- 如果有其他条件可以加上 -->
AND ass
.TOMBSTONE_TIME = 0;
<!--
删除临时表（可选，因为临时表会在会话结束时自动
删除） -->
DROP TEMPORARY TABLE IF EXISTS temp_ids;
</update>
```
注意：由于这是一个包含多条语句的
更新，需要确保数据库连接允许执行多条语句（在MySQL连接字符串中添加参数`allowMultiQueries=true`）。
但是，在MyBatis中执行多条语句可能会带来安全风险（如SQL注入），且不是所有数据库都支持。因此，我们也可以将创建临时表和插入数据放在一个单独的调用中，然后执行
更新，最后再
删除临时表。但是这样就需要在同一个连接中，所以需要确保这些操作在同一个事务中。
另外，如果ID数量非常大，使用内存临时表可能会占用过多内存，可以将引擎改为InnoDB（但这样
速度会
慢一些）或者限制每次处理的ID数量（分批次）。
考虑到事务和连接的
问题，我们可以将整个操作放在一个事务中，并且使用同一个连接。在MyBatis中，可以通过在方法上添加`@Transactional`注解来实现。
但是，如果不想使用临时表，我们还可以使用分批次处理，即每次
更新一定数量的ID（比如每次1000个），这样也可以避免IN子句过长的
问题。
分批次
更新（方案三）：
在Service层进行分批次调用：
```java
@Transactional
public void
batchUpdateTombstoneTime(Date tombstoneTime, List<Long> ids) {
int batchSize = 1000;
for (int i = 0; i < ids
.size(); i += batchSize) {
int end = Math
.min(i + batchSize, ids
.size());
List<Long> batchIds = ids
.subList(i, end);
if (!batchIds
.isEmpty()) {
assetMapper
.batchUpdateTombstoneTime(tombstoneTime, batchIds);
}
}
}
```
然后在映射文件中使用IN子句
更新每个批次。
结论：
- 如果ID数量不大（比如小于1000），使用IN子句简单高效。
- 如果ID数量很大，使用分批次
更新（方案三）更为通用，且不依赖于临时表。
因此，我推荐使用分批次
更新，因为它避免了临时表的复杂性，同时也能处理大
批量数据。
下面给出完整的代码：
MyBatis映射文件（每个批次的
更新）：
```xml
<update id="
batchUpdateTombstoneTime">
UPDATE apigc_api_assets
SET TOMBSTONE_TIME = #{tombstoneTime}
WHERE ID IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
<!-- 防止空列表 -->
<if test="ids == null or ids
.size() == 0">
AND 1=0
</if>
<!-- 可以加上其他条件 -->
AND ASSETS_SOURCE = '1'
AND TOMBSTONE_TIME = 0
</update>
```
Service层分批处理：
```java
public void
batchUpdateTombstoneTime(Date tombstoneTime, List<Long> ids) {
int batchSize = 1000;
for (int fromIndex = 0; fromIndex < ids
.size(); fromIndex += batchSize) {
int toIndex = Math
.min(fromIndex + batchSize, ids
.size());
List<Long> batchList = ids
.subList(fromIndex, toIndex);
// 注意：subList返回的是视图，如果要在其他线程中使用需要复制
assetMapper
.batchUpdateTombstoneTime(tombstoneTime, batchList);
}
}
```
注意：如果使用MyBatis-
Spring，确保在Service方法上添加`@Transactional`，这样每个批次
更新都在同一个事务中。
如果
更新操作非常频繁，还可以考虑使用并行流（但要注意数据库连接池和事务管理）：
```java
// 使用并行流（需要小心事务和连接池）
List<List<Long>> partitions = Lists
.partition(ids, batchSize);
partitions
.parallelStream()
.forEach(batch -> {
// 注意：每个批次需要在一个独立的事务中，或者使用编程式事务
assetMapper
.batchUpdateTombstoneTime(tombstoneTime, batch);
});
```
但是并行
更新可能会增加数据库锁竞争，需要根据实际情况测试。
综上所述，分批次处理是较为通用和高效的做法。
最后，如果
更新操作非常频繁且数据量巨大，还可以考虑使用异步处理或消息队列来削峰填谷，但这已经超出了MyBatis的范围。
请根据实际情况选择合适的方式。
§§相关
问题§§:
1
. MyBatis中如何执行多条SQL语句（如创建临时表并
更新）？
2
. 在分批次
更新时，如何保证整个操作的事务一致性？
3
. 除了分批次
更新和临时表，还有哪些高效
批量更新的方法？
4
. 在大量数据
更新的情况下，如何避免对数据库性能造成太大影响？
</think># 高效
批量更新方案：基于ID集合的优化实现
我将为您提供多种高效实现
批量更新的方案，并详细解释每种方案的适用场景和实现细节。
## 方案一：MyBatis原生批处理（最佳实践）
```xml
<!-- 在Mapper XML文件中 -->
<update id="
batchUpdateByIds">
UPDATE apigc_api_assets
SET TOMBSTONE_TIME = #{tombstoneTime}
WHERE ID IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
AND TOMBSTONE_TIME = 0 <!-- 可选：添加其他条件 -->
</update>
```
Java服务层实现：
```java
public void
batchUpdateTombstoneTime(Date tombstoneTime, List<Long> ids) {
if (ids == null || ids
.isEmpty()) return;
// 分批处理，每批1000个ID
int batchSize = 1000;
int total = ids
.size();
int batches = (int) Math
.ceil((double) total / batchSize);
for (int i = 0; i < batches; i++) {
int fromIndex = i * batchSize;
int toIndex = Math
.min(fromIndex + batchSize, total);
List<Long> batchIds = ids
.subList(fromIndex, toIndex);
assetMapper
.batchUpdateByIds(tombstoneTime, batchIds);
// 添加短暂延迟，减轻数据库压力
try {
Thread
.sleep(50);
} catch (InterruptedException e) {
Thread
.currentThread()
.interrupt();
}
}
}
```
## 方案二：JDBC高效批处理（高性能方案）
```java
// Mapper接口
@Update("<script>" +
"UPDATE apigc_api_assets SET TOMBSTONE_TIME = #{tombstoneTime} WHERE ID IN " +
"<foreach item='id' collection='ids' open='(' separator=',' close=')'>" +
"#{id}" +
"</foreach>" +
"</script>")
void
batchUpdateByIds(@Param("tombstoneTime") Date tombstoneTime,
@Param("ids") List<Long> ids);
```
JDBC批处理服务层实现：
```java
public void jdbc
BatchUpdate(Date tombstoneTime, List<Long> ids) {
if (ids == null || ids
.isEmpty()) return;
String sql = "UPDATE apigc_api_assets SET TOMBSTONE_TIME = ? WHERE ID = ?";
try (Connection conn = dataSource
.getConnection();
PreparedStatement ps = conn
.prepareStatement(sql)) {
conn
.setAutoCommit(false); // 关闭自动提交
int batchSize = 1000;
int count = 0;
for (Long id : ids) {
ps
.setTimestamp(1, new Timestamp(tombstoneTime
.getTime()));
ps
.setLong(2, id);
ps
.addBatch();
if (++count % batchSize == 0) {
ps
.executeBatch();
conn
.commit();
}
}
// 执行剩余批次
if (count % batchSize != 0) {
ps
.executeBatch();
conn
.commit();
}
} catch (SQLException e) {
throw new DataAccessException("
批量更新失败", e);
}
}
```
## 方案三：临时表方案（超大数据量）
```java
public void
batchUpdateWithTempTable(Date tombstoneTime, List<Long> ids) {
if (ids == null || ids
.isEmpty()) return;
// 创建临时表
jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS temp_ids (id BIGINT PRIMARY KEY)");
//
批量插入ID到临时表
jdbcTemplate.batchUpdate("INSERT INTO temp_ids (id) VALUES (?)",
ids
.stream()
.map(id -> new Object[]{id})
.collect(Collectors
.toList()),
1000); // 每批1000条
// 执行
更新
String updateSql = "UPDATE apigc_api_assets a " +
"JOIN temp_ids t ON a
.ID = t
.id " +
"SET a
.TOMBSTONE_TIME = ? " +
"WHERE a
.TOMBSTONE_TIME = 0";
jdbcTemplate.update(updateSql, tombstoneTime);
// 清理临时表
jdbcTemplate.execute("DROP TEMPORARY TABLE IF EXISTS temp_ids");
}
```
## 方案四：
Spring Data JPA方案
```java
// 在Repository中定义
public interface AssetRepository extends JpaRepository<Asset, Long> {
@Modifying
@Query("UPDATE Asset a SET a
.tombstoneTime = :tombstoneTime " +
"WHERE a
.id IN :ids AND a
.tombstoneTime = 0")
int bulkUpdateTombstoneTime(@Param("tombstoneTime") Date tombstoneTime,
@Param("ids") List<Long> ids);
}
// 服务层
批量处理
public void
batchUpdateJpa(Date tombstoneTime, List<Long> ids) {
int batchSize = 1000;
int total = ids
.size();
int batches = (int) Math
.ceil((double) total / batchSize);
for (int i = 0; i < batches; i++) {
int from = i * batchSize;
int to = Math
.min(from + batchSize, total);
List<Long> batchIds = ids
.subList(from, to);
assetRepository
.bulkUpdateTombstoneTime(tombstoneTime, batchIds);
}
}
```
## 性能优化建议
1
. **索引优化**：
```sql
-- 确保ID列有索引
CREATE INDEX idx_assets_id ON apigc_api_assets(ID);
-- 如果经常使用TOMBSTONE_TIME查询
CREATE INDEX idx_tombstone_time ON apigc_api_assets(TOMBSTONE_TIME);
```
2
. **连接池配置**：
```properties
# application
.properties
spring.datasource
.hikari
.maximum-pool-size=20
spring.datasource
.hikari
.minimum-idle=5
spring.datasource
.hikari
.connection-timeout=30000
```
3
. **监控与调优**：
```java
public void
batchUpdateWithMetrics(Date tombstoneTime, List<Long> ids) {
Stopwatch stopwatch = Stopwatch
.createStarted();
int total = ids
.size();
//
... 执行批处理
...
long elapsed = stopwatch
.elapsed(TimeUnit
.MILLISECONDS);
log
.info("
批量更新 {} 条记录，耗时 {} 毫秒，平均 {} 条/秒",
total, elapsed, (total * 1000L) / elapsed);
}
```
## 方案选择指南
| 方案 | 适用场景 | 性能 | 复杂度 | 数据量上限 |
|------|----------|------|--------|------------|
| MyBatis批处理 | 常规
批量更新 | ★★★ | 低 | 10万级 |
| JDBC批处理 | 高性能需求 | ★★★★ | 中 | 百万级 |
| 临时表 | 超大数据量 | ★★★★ | 高 | 千万级 |
| JPA | JPA项目集成 | ★★ | 低 | 10万级 |
**推荐实践**：对于大多数场景，方案一（MyBatis分批次处理）是最佳选择，平衡了性能和实现复杂度。当遇到性能瓶颈时，可升级到方案二（JDBC批处理）。
本文介绍了一种提高MySQL数据库批量插入操作效率的方法。通过在数据库连接字符串中添加rewriteBatchedStatements参数，可以显著减少批量插入所需的时间。文章还对比了使用此参数前后的性能差异。
扫一扫
1364
到【灌水乐园】发言
