Spring Hibernate JPA中Aircraft表findAll()查询缓慢及多子查询问题解决

我有两个实体类Aircraft和Operator，二者是Operator到Aircraft的一对多关联关系。调用JPA Repository对约1万行数据的Operator表执行findAll()仅需几秒，速度较快；但对约6.5万行数据的Aircraft表执行findAll()有时需数分钟才能完成。

同时我发现，对Operator执行findAll()仅生成1条Hibernate SELECT语句，而对Aircraft执行时，除了1条Aircraft表的SELECT语句外，还会生成大量带where operator0_.id=?条件的Operator表SELECT子查询，这并非预期行为。

该findAll()由Controller通过GET请求调用Service执行，查询结果用于将Aircraft对象映射至包含operator名称的AircraftDTO对象。

请问如何解决该问题，提升Aircraft表findAll()的查询速度？

注：部分无关类属性已移除

Aircraft实体类 #

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Aircraft implements Serializable {
    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "aircraft_sequence"
    )
    @SequenceGenerator(
            name = "aircraft_sequence",
            allocationSize = 1
    )
    @Column(nullable = false, updatable = false)
    private Long id;
    @ManyToOne
    @JoinColumn(name="operator_id", nullable=false)
    private Operator operator;
    private String registration;
}

Operator实体类 #

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Operator implements Serializable {
    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "operator_sequence"
    )
    @SequenceGenerator(
            name = "operator_sequence",
            allocationSize = 1
    )
    @Column(nullable = false, updatable = false)
    private Long id;
    private String name;
    @OneToMany(fetch = FetchType.LAZY, mappedBy="operator")
    @JsonIgnore
    private Set<Aircraft> aircraft;
}

Repository接口 #

AircraftRepository #

public interface AircraftRepository extends JpaRepository<Aircraft, Long> {
}

OperatorRepository #

public interface OperatorRepository extends JpaRepository<Operator, Long> {
}

Hibernate执行日志 #

Hibernate: select aircraft0_.id as id1_0_, aircraft0_.operator_id as operator7_0_, aircraft0_.registration as registra5_0_ from aircraft aircraft0_
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
...and so on

你碰到的是典型的N+1查询问题：先查所有Aircraft（1次查询），映射DTO时每个Aircraft都要加载关联的Operator，触发N次单独查询，6.5万条数据就会产生6.5万次额外请求，速度自然慢。下面是几种解决办法，按推荐优先级排序：

1. 使用Fetch Join一次性关联查询 #

在AircraftRepository里添加自定义查询，用JOIN FETCH一次性加载Aircraft和对应的Operator，彻底避免N+1：

public interface AircraftRepository extends JpaRepository<Aircraft, Long> {
    @Query("SELECT a FROM Aircraft a JOIN FETCH a.operator")
    List<Aircraft> findAllWithOperator();
}

之后Service里调用这个方法代替findAll()，Hibernate只会生成一条带JOIN的SELECT语句，一次性拉取所有数据。

如果担心6.5万条数据一次性加载到内存压力大，可以结合分页：

@Query("SELECT a FROM Aircraft a JOIN FETCH a.operator")
Page<Aircraft> findAllWithOperator(Pageable pageable);

2. DTO投影直接查询所需字段 #

既然最终要映射到带operator名称的AircraftDTO，直接在查询时只取需要的字段，不用加载整个实体，性能最优：

public interface AircraftRepository extends JpaRepository<Aircraft, Long> {
    // 这里要替换成你的AircraftDTO全类名，且DTO要有对应构造方法
    @Query("SELECT new com.yourpackage.dto.AircraftDTO(a.id, a.registration, o.name) FROM Aircraft a JOIN a.operator o")
    List<AircraftDTO> findAllAircraftDTO();
}

3. 修改@ManyToOne的FetchType为EAGER（不推荐） #

可以给Aircraft里的@ManyToOne加上fetch = FetchType.EAGER：

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name="operator_id", nullable=false)
private Operator operator;

这种方式会让Hibernate查询Aircraft时自动关联Operator，但不够灵活——所有查询Aircraft的操作都会默认加载Operator，可能在不需要的场景下造成不必要的性能开销，所以优先用前两种方法。

4. 开启Hibernate批量抓取（辅助优化） #

如果上面的方法已经解决问题，这个可以作为补充优化。在配置文件里添加：

# 批量抓取关联的Operator，每次抓50条
hibernate.default_batch_fetch_size=50

这样Hibernate会把多个Operator查询合并成where id in (?, ?, ...)的形式，减少查询次数，但还是不如Fetch Join高效。

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