性能问题，而调用batchUpdate由java更新多行mysql 8

Question

我在mysql 8上使用java处理多行batchUpdate时遇到了性能问题。

表中有超过400,000行，我试图通过它的id更新列。

sql如下:update table_name set status='status' where id = 1.

我使用batchUpdate每次更新10,000行。

列"id"有一个索引，参数也设置为jdbcUrl:

rewriteBatchedStatements=true

java代码为:

String sql = "update table_name set status='status' where id=?";
try(Connection conn = ds.getConnection();PreparedStatement ps = conn.prepareStatement(sql)){
for(int i=0;i<idList.size();i++){
ps.setInt(1,idList.get(i));
ps.addBatch();
if((i%10000==0&&i>0)||i==idList.size()-1){
System.out.println("now we execute from "+(i-10000)+" to "+i);
long s = System.currentTimeMillis();
ps.executeBatch();
System.out.println("execute from "+(i-10000)+" to "+i+" took "+(System.currentTimeMillis()-s));
}
}
}catch(Exception ex){
ex.printStackTrace();
}

通过执行这段代码，我发现它非常慢。更新10,000条记录大约需要60到90秒。

但是当我再次调用这段代码时，我发现只有在这些行更新一次的情况下，才需要1秒来完成这项工作。

我只是想知道为什么表演如此不同。是由某种缓存机制引起的吗?

当性能不佳时，我检查了innodb状态，发现更新操作正在等待handler commit。所以我理解为什么它很慢。但为什么我再运行一次就快多了?

该现象仅在我不使用transaction时发生。一旦交易被添加到代码中，它总是需要大约1秒。

Answer 1

UPDATE必须保存每行的旧副本。它尝试将其保存在一个日志文件中，但是如果日志文件填满了，它将以较慢的方式使用实际的表进行保存。我觉得批量生产100到1000个更安全。

此外，所有二级索引都排队等待稍后存储到磁盘。(别担心，即使系统崩溃也不会丢失它们。)

10,000已经进入了"收益递减"状态，所以涨到那么高没有任何优势。正如上面提到的，一个潜在的缺点。

当UPDATE不改变任何值时，它可以运行得更快。

改变一个表的整个列就像是一个设计缺陷。也许时间戳可以让您发现"状态"。在任何后续的SELECT?这可能会完全消除大的UPDATE。或者status可以存储在另一个表中?