java - Spring Boot JPA 尽管带有 MYSQL8 的 Hibernate 并未批量执行

Question

StackOverflow 上也有类似的问题，但这些都是通用答案，并非特定于问题中提到的此设置。

有没有办法让批量插入在 mysql8 数据库上的 Spring Boot JPA 和 Hibernate 中工作？

即使在应用程序属性文件中设置了以下属性；从 mysql 日志可以看出，插入 [customerjpa ] 表仅作为单个事务发生，而不是批量发生。

spring.jpa.properties.hibernate.jdbc.batch_size=2
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL57Dialect

但是通过纯 JDBC 批量插入可以按预期工作。 [customersJDBCCheck] 的插入就是这样。

2018-10-15T15:06:57.056430Z       240 Query     DROP TABLE  IF EXISTS customersJDBCCheck
2018-10-15T15:06:57.083438Z       240 Query     select @@session.transaction_read_only
2018-10-15T15:06:57.084438Z       240 Query     CREATE TABLE customersJDBCCheck(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))
2018-10-15T15:06:57.394531Z       240 Query     select @@session.transaction_read_only
2018-10-15T15:06:57.396532Z       240 Query     INSERT INTO customersJDBCCheck(first_name, last_name) VALUES ('John','Woo'),('Jeff','Dean'),('Josh','Bloch'),('Josh','Long')
2018-10-15T15:06:57.434543Z       240 Query     SELECT id, first_name, last_name FROM customersJDBCCheck WHERE first_name = 'Josh'
2018-10-15T15:06:57.444546Z       240 Query     select @@session.transaction_read_only
2018-10-15T15:06:57.446547Z       240 Query     DROP TABLE  IF EXISTS customersJDBCCheck
2018-10-15T15:06:57.585589Z       240 Query     SET autocommit=0
2018-10-15T15:06:57.636604Z       240 Query     insert into customerjpa (first_name, last_name) values ('Jack', 'Bauer')
2018-10-15T15:06:57.661612Z       240 Query     insert into customerjpa (first_name, last_name) values ('Chloe', 'O\'Brian')
2018-10-15T15:06:57.678617Z       240 Query     insert into customerjpa (first_name, last_name) values ('Kim', 'Bauer')
2018-10-15T15:06:57.688620Z       240 Query     insert into customerjpa (first_name, last_name) values ('Michelle', 'Dessler')
2018-10-15T15:06:57.710626Z       240 Query     commit
2018-10-15T15:06:57.710626Z       240 Query     SET autocommit=1

实体类

@Entity
class CustomerJPA {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;
    private String firstName;
    private String lastName;

    protected CustomerJPA() {
    }

    public CustomerJPA(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    @Override
    public String toString() {
        return String.format(
                "Customer[id=%d, firstName='%s', lastName='%s']",
                id, firstName, lastName);
    }

}

Answer 1

使用 GenerationType.SEQUENCE 类型的标识符和 pooled-lo 优化器是此处的解决方案。

详分割析如下。

这里的要点是，如果您使用 IDENTITY 标识符生成器，​​Hibernate 会透明地禁用 JDBC 级别的插入批处理。 (引用:http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html)

一旦更改为 @GenerateValue(strategy = GenerationType.SEQUENCE) ，效率就会低得多，因为序列有多个选择和更新。但是插入语句作为批处理查询执行。
这将在幕后创建如下表。

2018-10-16T12:15:07.125581Z       561 Query     create table customerjpa (
       id bigint not null,
        first_name varchar(255),
        last_name varchar(255),
        primary key (id)
    ) engine=InnoDB
2018-10-16T12:15:07.301581Z       561 Query     SHOW WARNINGS
2018-10-16T12:15:07.302581Z       561 Query     select @@session.transaction_read_only
2018-10-16T12:15:07.303581Z       561 Query     create table hibernate_sequence (
       next_val bigint
    ) engine=InnoDB

获取Id值并执行批量更新的日志。

2018-10-16T08:58:56.016432Z       393 Query     select next_val as id_val from hibernate_sequence for update
2018-10-16T08:58:56.020432Z       393 Query     update hibernate_sequence set next_val= 41 where next_val=40
2018-10-16T08:58:56.037432Z       393 Query     commit
2018-10-16T08:58:56.038432Z       393 Query     SET autocommit=1
2018-10-16T08:58:56.059432Z       393 Query     SET autocommit=0
2018-10-16T08:58:56.060432Z       393 Query     select next_val as id_val from hibernate_sequence for update
2018-10-16T08:58:56.061432Z       393 Query     update hibernate_sequence set next_val= 42 where next_val=41
2018-10-16T08:58:56.074432Z       393 Query     commit
2018-10-16T08:58:56.075432Z       393 Query     SET autocommit=1
2018-10-16T08:58:56.076432Z       393 Query     SET autocommit=0
2018-10-16T08:58:56.077432Z       393 Query     select next_val as id_val from hibernate_sequence for update
2018-10-16T08:58:56.078432Z       393 Query     update hibernate_sequence set next_val= 43 where next_val=42
2018-10-16T08:58:56.085432Z       393 Query     commit
2018-10-16T08:58:56.086432Z       393 Query     SET autocommit=1
2018-10-16T08:58:56.086432Z       393 Query     SET autocommit=0
2018-10-16T08:58:56.087432Z       393 Query     select next_val as id_val from hibernate_sequence for update
2018-10-16T08:58:56.088432Z       393 Query     update hibernate_sequence set next_val= 44 where next_val=43
2018-10-16T08:58:56.093432Z       393 Query     commit
2018-10-16T08:58:56.093432Z       393 Query     SET autocommit=1
2018-10-16T08:58:56.128432Z       392 Query     select @@session.transaction_read_only
2018-10-16T08:58:56.129432Z       392 Query     insert into customerjpa (first_name, last_name, id) values ('Jack', 'Bauer', 40),('Chloe', 'O\'Brian', 41),('Kim', 'Bauer', 42),('Michelle', 'Dessler', 43)
2018-10-16T08:58:56.140432Z       392 Query     commit
2018-10-16T08:58:56.141432Z       392 Query     SET autocommit=1

即使创建批量更新 ID 生成也不是最佳的。它可以使用 pooled-lo 优化器进行优化(引用:https://vladmihalcea.com/hibernate-hidden-gem-the-pooled-lo-optimizer/)

@Id
@GenericGenerator(
        name = "sequenceGenerator",
        strategy = "enhanced-sequence",
        parameters = {
                @org.hibernate.annotations.Parameter(
                        name = "optimizer",
                        value = "pooled-lo"
                ),
                @org.hibernate.annotations.Parameter(
                        name = "initial_value",
                        value = "1"
                ),
                @org.hibernate.annotations.Parameter(
                        name = "increment_size",
                        value = "10"
                )
        }
)
@GeneratedValue(
        strategy = GenerationType.SEQUENCE,
        generator = "sequenceGenerator"
)
@Column(name = "id", unique = true,updatable = false, nullable = false)
private Long id;

优化后的批量插入可以在日志中看到如下。

2018-10-16T12:15:10.076581Z       564 Query     SET autocommit=0
2018-10-16T12:15:10.084581Z       564 Query     select next_val as id_val from hibernate_sequence for update
2018-10-16T12:15:10.086581Z       564 Query     update hibernate_sequence set next_val= 11 where next_val=1
2018-10-16T12:15:10.087581Z       564 Query     commit
2018-10-16T12:15:10.106581Z       564 Query     SET autocommit=1
2018-10-16T12:15:10.164581Z       563 Query     select @@session.transaction_read_only
2018-10-16T12:15:10.165581Z       563 Query     insert into customerjpa (first_name, last_name, id) values ('Jack', 'Bauer', 1),('Chloe', 'O\'Brian', 2),('Kim', 'Bauer', 3),('Michelle', 'Dessler', 4)
2018-10-16T12:15:10.169581Z       563 Query     commit