JPA：即使使用FetchType.LAZY仍存在N+1查询问题

Question

我读过一些有关如何在JPA中解决n+1查询问题的文章，但是它们都没能解决我的问题。

当我尝试获取数据时，JPA会做出n+1个查询。

select owner0_.id as id1_1_, owner0_.created_at as created_2_1_, owner0_.updated_at as updated_3_1_, owner0_.name as name4_1_, owner0_.version as version5_1_ from owner owner0_

select cars0_.owner_id as owner_id6_0_0_, cars0_.id as id1_0_0_, cars0_.id as id1_0_1_, cars0_.created_at as created_2_0_1_, cars0_.updated_at as updated_3_0_1_, cars0_.license_no as license_4_0_1_, cars0_.owner_id as owner_id6_0_1_, cars0_.version as version5_0_1_ from car cars0_ where cars0_.owner_id=? [1]

select cars0_.owner_id as owner_id6_0_0_, cars0_.id as id1_0_0_, cars0_.id as id1_0_1_, cars0_.created_at as created_2_0_1_, cars0_.updated_at as updated_3_0_1_, cars0_.license_no as license_4_0_1_, cars0_.owner_id as owner_id6_0_1_, cars0_.version as version5_0_1_ from car cars0_ where cars0_.owner_id=? [2]

以下是代码片段：

@Entity
public class Owner extends BaseEntity implements EntityTransformer<OwnerDto> {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @Version
    private Long version;

    @OneToMany(mappedBy = "owner", fetch = FetchType.LAZY)
    private Set<Car> cars;

    @Override
    public OwnerDto convertToDto() {
        OwnerDto ownerDto = new OwnerDto();
        ownerDto.setId(this.getId());
        ownerDto.setName(this.getName());
        ownerDto.setVersion(this.getVersion());
        if (this.getCars() != null) ownerDto.setCars(this.getCars().stream().map(Car::convertToDto).collect(Collectors.toSet()));
        return ownerDto;
    }
}

我的Car类如下所示：

@Entity
public class Car {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String licenseNo;

    @Version
    private Integer version;

    @JoinColumn( name = "owner_id" )
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private Owner owner;
    @Override
    public CarDto convertToDto() {
        CarDto carDto = new CarDto();
        carDto.setId(this.getId());
        carDto.setLicenseNo(this.getLicenseNo());
        carDto.setVersion(this.getVersion());
        return carDto;
    }
}

OwnerService：

@Service
public class OwnerServiceImpl implements OwnerService {

    @Autowired
    OwnerRepository ownerRepository;

    @Override
    public List<Owner> findAll() {
        return ownerRepository.findAll();
    }
}

OwnerController：

@RestController
public class OwnerController {
    @Autowired
    private OwnerService ownerService;

    @GetMapping(value = "/owners", produces = "application/vnd.demo.api.v1+json")
    public ResponseEntity<List<OwnerDto>> findAll() {
        return ResponseEntity.ok(ownerService.findAll().stream().map(Owner::convertToDto).collect(Collectors.toList()));
    }
}

cURL：

curl -X POST \
  http://localhost:8080/owner \
  -H 'Accept: application/vnd.demo.api.v1+json' \
  -H 'Content-Type: application/json' \
  -H 'Host: localhost:8080' \
  -d '{
    "name": "pranay5"
}'

curl -X POST \
  http://localhost:8080/owner/5/car \
  -H 'Accept: application/vnd.demo.api.v1+json' \
  -H 'Content-Type: application/json' \
  -H 'Host: localhost:8080' \
  -d '{
    "licenseNo": "MSH-5555"
}'

代码有问题吗？

另外注意：当我设置@BatchSize（size = 5）而没有进行其他更改时，JPA只会向数据库发出两个查询。

select o_.id , o_.created_at, o_.updated_at, o_.name from owner o_ 

select c_.owner_id, c_.id, c_.created_at, c_.updated_at, c_.license_no, c_.owner_id, from car c_ where c_.owner_id in (?, ?, ?, ?, ?) [1,2,3,4,5] 

但我有疑问，为什么FetchType.LAZY会导致N+1个查询？

代码：https://github.com/pranayhere/exception-demo-mvn

- prranay

---

你没有展示你在哪里使用这段代码。如果你实际上正在迭代值，那么惰性获取预计会导致N+1。 - chrylis -cautiouslyoptimistic-

您有一个查询用于所有项目，并遍历它们以创建dto。在dto中，您访问集合，因为它是惰性的，所以需要发出选择。因此，实际上使集合变得惰性引入了N+1选择问题（所有Owners的1个和每个Owner的所有汽车的1个）。要么编写自定义查询来FETCH JOIN车辆，要么使“cars”属性EAGER，以便它将被加入到查询中。 - M. Deinum

你可能误解了 FetchType.LAZY 的作用。它不是解决 N+1问题 的方案，而是导致该问题的原因。最简单（但不是最优）的选择是切换到 FetchType.EAGER。如果您不想切换，则应使用实体图（示例在此处https://www.baeldung.com/spring-data-jpa-named-entity-graphs）或使用带有`FETCH JOIN`的查询，如上所述。 - Nikolai Shevchenko

是的，你们说得对。DTO查询导致了问题。@BatchSize(size = M) 是解决这个问题的适当方式吗？ - prranay

不是这样的。如前所述，要么将集合设置为FetchType.EAGER，要么编写自定义查询以进行FETCH JOIN集合（使其在此实例中变为eager）。调整@BatchSize不会解决这个问题。 - M. Deinum

Answer 1

实际问题在于您使用了OwnerRepository的默认findAll方法，并且将FetchType.LAZY设置为了懒加载。

由于在Owner::convertToDto中获取了汽车信息，Hibernate必须获取由于懒加载而未获取的汽车信息。

为避免额外的查询，请在OwnerRepository中创建一个新的JPA方法getAllBy，并使用EntityGraph在查询中主动获取汽车信息：

public class OwnerRepository extend JpaRepository<Owner, Long> {

    @EntityGraph(attributePaths = {
            "cars",
    })
    List<Owners> getAllBy();

}

然后在您的服务中使用它，而不是使用findAll。

- Ybri

Answer 2

我刚检查了你的代码，我认为问题在于 OwnerController：

@GetMapping(value = "/owners", produces = "application/vnd.demo.api.v1+json")
public ResponseEntity<List<OwnerDto>> findAll() {
    return ResponseEntity.ok(ownerService.findAll().stream().map(Owner::convertToDto).collect(Collectors.toList()));
}

Owner.convertToDto() 方法遍历 Owner.cars 字段。

- Cajova_Houba

---

我的关注点在于对数据库的查询。当我设置@BatchSize(size = 5)而不进行其他更改时，它只会向数据库发出两个查询：从owner o_选择o_.id，o_.created_at，o_.updated_at，o_.name从car c_中选择c_.owner_id，c_.id，c_.created_at，c_.updated_at，c_.license_no，c_.owner_id，并在其中（？、？、？、？、？）[1,2,3,4,5]中选择c_.owner_id - prranay