Spring JPA+Hibernate在SaveAll()上的低性能

Question

我正在开发一个REST应用程序来加载CSV文件，将它们插入DB (MYSQL - mysqld Ver 5.7.32)，然后以JSON格式查询和显示记录。

问题是，当我使用JPA存储库SaveAll()方法试图持久化这些记录时，需要花费大量的时间( 500条记录的~25s)。

我搜索了解决方案，并发现了一些似乎解决了问题的配置更改，但它们都没有为我解决问题。我更改了logging.level.org.hibernate.SQL=DEBUG和spring.jpa.properties.hibernate.generate_statistics=true，以检查hibernate是如何工作的。

对于500个记录，不需要对application.properties进行任何进一步的更改，如下所示：

ID: GenerationType.AUTO / Id : 14:13:30-14:13:52 /插入: 14:13:53 / No批/ No URL /23 s

17529760 nanoseconds spent acquiring 501 JDBC connections;
11900589 nanoseconds spent releasing 500 JDBC connections;
201149299 nanoseconds spent preparing 1500 JDBC statements;
946268444 nanoseconds spent executing 1500 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
511195616 nanoseconds spent executing 1 flushes (flushing a total of 500 entities and 0 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

22用于获取和更新Id。

Hibernate对传递给SaveAll()方法的每个实体执行3次查询：

首先，它获得所需的ID：

1. 从hibernate_sequence中选择next_val作为id_val来更新
2. update hibernate_sequence set next_val=？next_val=?

在获得Id后：

1. 插入表.

如日志所示，Hibernate为500条记录执行1500条语句。

我试图按以下方式更改配置：

spring.jpa.properties.hibernate.jdbc.batch_size=30
spring.jpa.properties.hibernate.order_inserts=true

结果如下：

GenerationType.AUTO / ID: 14:23:30-14:23:52 /插入直至:14:23:53/批大小: 30，order_inserts=true / No URL /23

15672968 nanoseconds spent acquiring 501 JDBC connections;
13474276 nanoseconds spent releasing 500 JDBC connections;
116274083 nanoseconds spent preparing 1001 JDBC statements;
843429450 nanoseconds spent executing 1000 JDBC statements;
222390695 nanoseconds spent executing 17 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
346810255 nanoseconds spent executing 1 flushes (flushing a total of 500 entities and 0 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

语句减少到1000，执行了17批，但仍然需要22s才能获得ID，1s才能执行插入。

我将Id生成策略更改为序列，结果是相同的。此外，我在中添加了?reWriteBatchedInserts=true，以便在一条语句中插入多个实体，但hibernate仍然为每条记录生成1个插入：

2020-11-27 15:33:15.349 DEBUG 7061 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into enra (account_link_code_n, chanel_nam, contract_type_v, cra_ref_num_v, dms_verified_flag_v, id_type_v, msisdn_nsk, profile_type_v, registration_by, registration_date, report_date, shahkar_id, sim_category_code_v, status_code_v, user_code_n, version, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-11-27 15:33:15.349 DEBUG 7061 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into enra (account_link_code_n, chanel_nam, contract_type_v, cra_ref_num_v, dms_verified_flag_v, id_type_v, msisdn_nsk, profile_type_v, registration_by, registration_date, report_date, shahkar_id, sim_category_code_v, status_code_v, user_code_n, version, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-11-27 15:33:15.350 DEBUG 7061 --- [nio-8080-exec-1] org.hibernate.SQL                        : insert into enra (account_link_code_n, chanel_nam, contract_type_v, cra_ref_num_v, dms_verified_flag_v, id_type_v, msisdn_nsk, profile_type_v, registration_by, registration_date, report_date, shahkar_id, sim_category_code_v, status_code_v, user_code_n, version, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

我有40,000条记录的CSV文件，这在我的应用程序中造成了一个巨大的瓶颈。

我真的不知道我在配置中缺少了什么，但是如果我能够让hibernate批量获得和更新Id (hibernate_sequence)，那么时间消耗就会急剧下降。

任何帮助都是非常感谢的。

Answer 1

对于一般观众来说，问题是“一代类型”。由于MySQL不支持序列，hibernate使用了一个单独的表解决方案。在每一行执行读和写之前，性能就会下降。

解决方案：

@Id
@Column(nullable = false, updatable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)

它使用auto_increment特性，MySQL的首选选项。:-)