如何优化Spring JPA+Hibernate一对多层级实体的DB更新性能

我们的数据库层吞吐量已达瓶颈。尽管负载不算极高，但发现即使应用和数据库连接池尚有空闲，也无法有效执行大量并发事务。当线程数超过某个阈值后，每个事务的执行速度会变慢，导致处理吞吐量基本保持恒定。目前我们将线程数设为10，这是当前最优配置。除了并发限制，我们认为整体数据库更新速度仍有优化空间。

我们有一个运行在Docker容器中的Spring Boot应用，使用Spring Data JPA和Hibernate，底层数据库为MariaDb。

核心数据模型包含First实体，它与Second实体是one-to-many关系，Second实体又与Third实体是one-to-many关系，无多对多关系。

业务流程如下：
数据流入时，通过Spring的@Transactional开启事务，获取First实体，更新First及其子实体的部分属性，有时会新增子实体。随后调用firstRepository.save并提交事务。调用save是因为First本身或其子实体可能是新创建的。
该操作由多个线程并发执行，每个线程处理独立的First实例。
业务逻辑耗时极短，大部分时间消耗在读取、保存及事务提交上，事务提交包含Spring @Transactional代理处理的所有相关操作。

目前我们无法避免读取完整的First实体，因为在更新First或其子实体后，需要将整个对象快照发送至下一个系统。

我们在First、Second、Third每个实体上都创建了多个索引，且通过外键约束维护实体关系。

为提升吞吐量，我们已尝试多种Hibernate与MariaDb相关的优化方案：

• 使用EhCache实现的Hibernate二级缓存
• 配置最大连接数为30的Hikari数据源，该连接池从未被完全占满
• MariaDb的连接池更大，为10000
• Hibernate的批量插入与更新
• 预加载子实体以减少数据库往返次数。由于我们需要所有子实体（即使未更新）来传递完整的First快照，懒加载无法带来收益
• 数据库引擎使用InnoDb，理论上只会锁定特定记录而非整张表

以上优化均有效果，但整体速度仍不理想——更新大型First及其子实体可能需要2-3秒，且并发线程数的限制并未因这些优化而改善。

我们无法理解为何无法进一步挖掘数据库层的潜力。当前First约有60万条记录，Second约2800万条，Third约1亿条。
平均每个First对应50个Second，每个Second对应4个Third。但我们通常仅同时活跃使用5000个First实例，大部分数据多数时间处于非活跃状态。

我们怀疑可能是Hikari或数据库配置错误，限制了实际可用连接，或导致数据库锁定了不必要的行，但不知如何诊断。

@Entity
@Table(name = "first", indexes = { 
        @Index(name = "blah1_inx", unique = false, columnList = "blah1Id"),
        @Index(name = "blah2_inx", unique = false, columnList = "blah1Id"),
        @Index(name = "blah3_inx", unique = false, columnList = "blah2Id"),
        @Index(name = "blah4_inx", unique = false, columnList = "blah3Id") })
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class First {

    @Id
    private Long id;

    @OneToMany(mappedBy = "parent", cascade = {CascadeType.ALL})
    @OrderBy("typeId asc")
    @Fetch(FetchMode.JOIN)
    @LazyCollection(LazyCollectionOption.FALSE)
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private Set<Second> second = new HashSet<Second>();

    /**
    There are 24 more columns, some of which are @Lob
    **/

}

@Entity
@Table(name = "second", indexes = { 
        @Index(name = "blah100_inx", unique = false, columnList = "blah100"),
        @Index(name = "blah101_inx", unique = false, columnList = "blah101"),
        @Index(name = "blah102_inx", unique = false, columnList = "blah102"),
        @Index(name = "blah103_inx", unique = false, columnList = "blah103") })
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Second {

    @Id
    private Long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "parentId", nullable = false, referencedColumnName = "id")
    @JsonIgnore
    private First parent;

    @OneToMany(mappedBy = "parent", cascade = {CascadeType.ALL})
    @OrderBy("line asc, caption  desc")
    @Fetch(FetchMode.JOIN)
    @LazyCollection(LazyCollectionOption.FALSE)
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private List<Third> third = new ArrayList<Third>();


    /**
    There are 11 more columns, none of which are @Lob
    **/
}

@Entity
@Table(name = "third", indexes = { 
        @Index(name = "blah200_inx", unique = false, columnList = "blah200"),
        @Index(name = "blah201_inx", unique = false, columnList = "blah201"),
        @Index(name = "blah202_inx", unique = false, columnList = "blah202"),
        @Index(name = "blah203_inx", unique = false, columnList = "blah203") })
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Third {

    @Id
    private Long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "parentId", nullable = false, referencedColumnName = "id")
    private Second parent;


    /**
    There are 16 more columns, none of which are @Lob
    **/
}

我们使用原生JpaRepository进行数据查询与保存：

public interface FirstRepository extends JpaRepository<First, Long> {
    ...
}

数据创建/更新逻辑：

@Transactional(
        propagation = Propagation.REQUIRES_NEW,
        rollbackFor = {Exception.class}
    )
    public First process(Long id) {
    firstRepository.findById(id)
                .orElse(new First(id));
        
    // update some of first's attributes, and some of attributes of first children
    // ...
    
    return firstRepository.save(first);
     
    }

• 数据库中所有表均使用InnoDb引擎，行格式为Dynamic
• First中的部分文本字段较大，类型为longtext
• 事务隔离级别为默认的REPEATABLE READ
• 未在Hibernate中使用显式锁定

application.properties配置：

spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.datasource.url=jdbc:mariadb://${DB_HOST}:3306/${DB_DATABASE}?useSSL=false

spring.datasource.hikari.maximumPoolSize=30
spring.datasource.hikari.maxLifetime=27000
#Hibernate l2 cache properties
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory

spring.jpa.hibernate.jdbc.batch_size=50
spring.jpa.hibernate.jdbc.batch_versioned_data=true
spring.jpa.hibernate.order_inserts=true
spring.jpa.hibernate.order_updates=true

部分相关数据库配置：

MariaDB  Ver 15.1
max_connections = 10000 (no idea why they set that much here)
expire_logs_days = 3
InnoDB settings:
innodb_buffer_pool_size = 32G

内容的提问来源于stack exchange，提问作者Vasil Lazarov