Hibernate使用JOIN FETCH仍出现N+1查询问题求助

问题描述 #

使用Hibernate查询包含子集合的Category实体时遇到N+1查询问题：获取带有子集合或存在父级的分类时，尽管在Repository中使用了JOIN FETCH关联查询childs集合，但Mapper将实体转换为CategoryApi时依然触发额外查询（共执行3次查询）。

实体类Category #

@EqualsAndHashCode(callSuper = true)
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Category extends AbstractJpaAudit {
    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false, unique = true)
    private String name;

    @Column(name = "description")
    private String description;

    @ManyToOne
    @JoinColumn(name = "fk_category_id")
    private Category category;

    @EqualsAndHashCode.Exclude
    @ToString.Exclude
    @OneToMany(mappedBy = "category", fetch = FetchType.LAZY)
    private List<Category> childs = new ArrayList<>();
}

Repository #

public interface CategoryRepository extends JpaRepository<Category, Long>, JpaSpecificationExecutor<Category> {
    @Query("SELECT c FROM Category c JOIN FETCH c.childs WHERE c.name = :name ")
    Optional<Category> findByName(final @Param("name") String name);
}

Service #

@Service
@RequiredArgsConstructor
public class CategoryService {

    private final CategoryEntityService categoryEntityService;
    private final CategoryMapper categoryMapper;
    private final AbstractSpecificationResolver<Category> specificationResolver;

    /**
     * Used to search for a specific category by name or throw exception if the category is not found.
     *
     * @param name of category
     * @return fetched {@link CategoryApi}
     */
    @Transactional(readOnly = true)
    public CategoryApi findByName(final String name) {
        return categoryMapper.toApi(categoryEntityService.findByName(name)
                                                         .orElseThrow(() -> new EntityNotFoundException("Category with name " + name + " does not exist")));
    }

    @Transactional(readOnly = true)
    public List<CategoryApi> findAll(final Map<String, Object> parameters) {
        return categoryEntityService.findAll(specificationResolver.resolveAll(parameters))
                                    .stream()
                                    .map(categoryMapper::toApi)
                                    .collect(Collectors.toList());
    }
}

Mapper #

@Override
public CategoryApi toApi(final Category category) {
    if (category == null) {
        return null;
    }

    return CategoryApi.builder()
                      .id(category.getId())
                      .parent(getWithoutChilds(category.getCategory()))
                      .description(category.getDescription())
                      .name(category.getName())
                      .childs(category.getChilds()
                                      .stream()
                                      .map(this::toApi)
                                      .collect(Collectors.toList()))
                      .build();
}

private CategoryApi getWithoutChilds(final Category category) {
    if (category == null) return null;

    return CategoryApi.builder()
                      .id(category.getId())
                      .parent(getWithoutChilds(category.getCategory()))
                      .description(category.getDescription())
                      .name(category.getName())
                      .build();
}

原因分析 #

• JOIN FETCH仅覆盖子集合，未处理父级关联：当前查询只JOIN FETCH c.childs，但Mapper中调用category.getCategory()获取父级分类，而@ManyToOne默认是LAZY加载策略，访问未加载的父级实体时会触发额外SELECT查询。
• 递归加载父级层级引发多次查询：getWithoutChilds方法递归获取父级的父级，每一层未预先加载的父级都会触发一次新的查询，这就是额外查询的来源。

解决方案 #

方案1：修改查询语句，同时加载父级关联 #

调整Repository的查询，加入父级的JOIN FETCH，如果需要递归加载多层父级，可结合Hibernate递归CTE（适用于Hibernate 5.1+）：

基础版：加载直接父级

@Query("SELECT c FROM Category c JOIN FETCH c.childs LEFT JOIN FETCH c.category WHERE c.name = :name ")
Optional<Category> findByName(final @Param("name") String name);

递归版：加载完整父级层级

@Query(value = "WITH RECURSIVE cat_tree AS (" +
               "    SELECT c.* FROM category c WHERE c.name = :name " +
               "    UNION ALL " +
               "    SELECT p.* FROM category p JOIN cat_tree ct ON p.id = ct.fk_category_id" +
               ") SELECT c FROM Category c JOIN FETCH c.childs WHERE c.id IN (SELECT id FROM cat_tree)",
       nativeQuery = true)
Optional<Category> findByNameWithParentHierarchy(final @Param("name") String name);

方案2：使用实体图（EntityGraph）定义加载规则 #

通过@NamedEntityGraph指定需要加载的关联属性，比JOIN FETCH更灵活：

第一步：在实体类上定义EntityGraph

@NamedEntityGraph(name = "Category.withChildsAndParent",
                  attributeNodes = {
                      @NamedAttributeNode("childs"),
                      @NamedAttributeNode("category")
                  })
@EqualsAndHashCode(callSuper = true)
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Category extends AbstractJpaAudit {
    // 原有实体代码不变
}

第二步：在Repository中使用EntityGraph

@EntityGraph(value = "Category.withChildsAndParent")
@Query("SELECT c FROM Category c WHERE c.name = :name ")
Optional<Category> findByName(final @Param("name") String name);

方案3：调整Mapper逻辑，减少不必要的关联加载 #

如果业务不需要返回完整的父级层级，可以修改getWithoutChilds方法，停止递归加载父级的父级，或者只返回父级的基础字段：

private CategoryApi getWithoutChilds(final Category category) {
    if (category == null) return null;

    return CategoryApi.builder()
                      .id(category.getId())
                      .description(category.getDescription())
                      .name(category.getName())
                      // 去掉递归加载父级的父级，仅返回直接父级
                      // .parent(getWithoutChilds(category.getCategory()))
                      .build();
}

方案4：修改关联的FetchType（不推荐） #

将@ManyToOne的加载策略改为EAGER，但这种方式会导致所有查询都自动加载父级，可能引发不必要的性能损耗，仅适用于父级总是需要加载的场景：

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "fk_category_id")
private Category category;

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