Java Spring Boot批处理插入Show-SQL显示单独的插入物

Question

我正在使用Spring Boot，并试图通过在前端应用程序中上传Excel文件来批量插入成分。 Spring Boot API收集了一份成分列表，但插入非常慢。更新快速发展，但是插入非常慢。

什么

	？
在	这里
缺少	我

​

Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)

​

我

    15800 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    1316900 nanoseconds spent preparing 13 JDBC statements;
    144703300 nanoseconds spent executing 10 JDBC statements;
    9137380500 nanoseconds spent executing 110 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    9164292200 nanoseconds spent executing 1 flushes (flushing a total of 280 entities and 10 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

<

@Entity
@Getter
@Setter
@RequiredArgsConstructor
@ToString
@Where(clause = "is_active=1")
public class Ingredient {

    @Id
    private String id;

    @PrePersist
    public void prePersist() {
        if (this.id == null) {
            this.id = UUID.randomUUID().toString();
        }
    }

    @ManyToOne
    private IngredientCategory category;

    @OneToMany(mappedBy = "ingredient")
    @JsonIgnore
    private Set<IngredientAmount> ingredientAmounts;

    @Column(length = 200)
    @Size(max = 200)
    private String unit;

    @Column(length = 1000)
    @Size(max = 1000)
    private String name;
    private boolean isActive;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<IngredientProperty> properties;

}

st

@Entity
@Getter
@Setter
@RequiredArgsConstructor
@ToString
@Where(clause = "is_active=1")
public class IngredientProperty {
        @Id
    private String id;

    @PrePersist
    public void prePersist() {
        if (this.id == null) {
            this.id = UUID.randomUUID().toString();
        }
    }
    
    private String name;
    private String value;
    private String unit;
    private boolean isActive;
}

intredientController

    @PostMapping("/bulk")
    public ResponseEntity createOrUpdateInBulk(@RequestBody Set<Ingredient> ingredients) throws InterruptedException {
        return new ResponseEntity(this.ingredientService.updateAndCreateBulkIngredients(ingredients), HttpStatus.OK);
    }

intredientService

    public boolean updateAndCreateBulkIngredients(Set<Ingredient> toUpdateIngredients) {
        Set<Ingredient> toAddOrUpdateIngredients = new HashSet<>();
        for (int i = 0; i < toUpdateIngredients.size(); i++) {
            Ingredient ingredient = new ArrayList<>(toUpdateIngredients).get(i);
            Ingredient existing = this.ingredientRepository.getIngredientById(ingredient.getId());
            if (existing != null) {
                existing.getProperties().clear();
                existing.getProperties().addAll(ingredient.getProperties());
                existing.setUnit(ingredient.getUnit());
                toAddOrUpdateIngredients.add(existing);
            } else {
                toAddOrUpdateIngredients.add(ingredient);
            }
            if (i % batchSize == 0 && i > 0) {
                this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
                toAddOrUpdateIngredients.clear();
            }
        }
        if (toAddOrUpdateIngredients.size() > 0) {
            this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
            toAddOrUpdateIngredients.clear();
        }
        return true;
    }

application.properties包含以下内容：

spring.datasource.driverClassName= com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.jdbc.batch_size=5
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.generate_statistics=true

update

将以下内容添加到应用程序。专业大大提高了性能。我仍然可以看到插入物是单独完成的，但是现在每28个InfreDientProperty's现在需要800ms而不是8.5秒！！！

添加了以下内容：

spring.datasource.url=${DATASOURCE_URL:jdbc:mysql://PATH:PORT?cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true}

?cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true

这也改变了批次的数量：

    15900 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    171364400 nanoseconds spent preparing 13 JDBC statements;
    129932100 nanoseconds spent executing 10 JDBC statements;
    166547600 nanoseconds spent executing 3 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    229440900 nanoseconds spent executing 1 flushes (flushing a total of 280 entities and 10 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

新问题

这是我得到的最大性能吗？还是我可以做更多的事情来提高性能？