performance issue while calling batchUpdate by java to update multiple rows on mysql 8

I met performance issue with batchUpdate for multiple rows on mysql 8 by using java.

There are more than 400,000 rows in the table , I was trying to update a col开发者_如何学Goumn by it's id.

The sql is like: update table_name set status='status' where id = 1.

I used batchUpdate to update 10,000 rows per time.

There is an index for the column "id", and the parameter was set to jdbcUrl too:

rewriteBatchedStatements=true

The java code is :

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();
        }

By executing this code, I found it was very slow. It took about 60 to 90 seconds to update 10,000 records.

But when I called this code again, I found it only took 1 second to finish the job ,only if these rows were updated once.

I just wonder why the performace is so different. Does it cause by some cache mechanism?

When the performance was poor, I checked the innodb status, and found the update operation was waiting for handler commit. So I understand why it is slow. But why it was much faster when I run it again?

The phenomenon only occurs when I am not using transaction. Once the transaction is added to the code, it always takes about 1 second.