memo-pad’s blog

自分のためのメモをまとめています。ここに書いてある内容については責任は負いません。全て自己責任でお願いします。

Spring Data JPAのN+1問題解決とMultipleBagFetchExceptionの回避方法

概要

以下のメモ

  • JPAで@OneToManyの要素を取得するときにN+1で取得してしまうので、その際の挙動と解決策
  • N+1問題解決のために@OneToManyを複数種類JOIN FETCHする際のMultipleBagFetchExceptionの回避方法

TL;DR

  • 1回のSQLで@OneToManyの要素を1種類取得したい場合は、JOIN FETCHで取得
  • 1回のSQLで@OneToManyの要素を複数種類取得したい場合は、MultipleBagFetchExceptionが発生してしまうため、List型ではなくSet型にしてJOIN FETCHで取得

今回の環境

  • テーブルの関係は図を参照
  • Parentが@OneToManyでChildOneとChildTwoを複数持つ f:id:memo-pad:20181006123539j:plain

  • コードはこちら

github.com

Data

INSERT INTO parent(id) VALUES (1)
INSERT INTO parent(id) VALUES (2)

INSERT INTO child_one(id,parent_id) VALUES (1,1)
INSERT INTO child_one(id,parent_id) VALUES (2,1)
INSERT INTO child_one(id,parent_id) VALUES (3,2)

INSERT INTO child_two(id,parent_id) VALUES (1,1)
INSERT INTO child_two(id,parent_id) VALUES (2,2)
INSERT INTO child_two(id,parent_id) VALUES (3,2)

Entity

  • Parent
@Table(name = "parent")
@Entity
@Data
public class Parent {
    @Id
    private Integer id;

    @OneToMany(mappedBy = "parent")
    private List<ChildOne> childOnes;

    @OneToMany(mappedBy = "parent")
    private List<ChildTwo> childTwos;
}
  • ChildOne
@Table(name = "child_one")
@Entity
@Data
@ToString(exclude = "parent")
public class ChildOne {
    @Id
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parentId", foreignKey = @ForeignKey(ConstraintMode.PROVIDER_DEFAULT))
    private Parent parent;
}
  • ChildTwo
@Table(name = "child_two")
@Entity
@Data
@ToString(exclude = "parent")
public class ChildTwo {

    @Id
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parentId", foreignKey = @ForeignKey(ConstraintMode.PROVIDER_DEFAULT))
    private Parent parent;
}

単純にfindAllでParent要素を全取得した場合

😥 Parentの全要素に対して、ChildOne, ChildTwo取得のSQLを毎回投げてしまう。

挙動

  • Parentを取得
Hibernate:
    select
        parent0_.id as id1_5_
    from
        parent parent0_
  • Parent(id=1)のChildOneとChildTwoを取得
Hibernate:
    select
        childones0_.parent_id as parent_i2_0_0_,
        childones0_.id as id1_0_0_,
        childones0_.id as id1_0_1_,
        childones0_.parent_id as parent_i2_0_1_
    from
        child_one childones0_
    where
        childones0_.parent_id=?
Hibernate:
    select
        childtwos0_.parent_id as parent_i2_1_0_,
        childtwos0_.id as id1_1_0_,
        childtwos0_.id as id1_1_1_,
        childtwos0_.parent_id as parent_i2_1_1_
    from
        child_two childtwos0_
    where
        childtwos0_.parent_id=?
Parent(id=1, childOnes=[ChildOne(id=1), ChildOne(id=2)], childTwos=[ChildTwo(id=1)])
  • Parent(id=2)のChildOneとChildTwoを取得
Hibernate:
    select
        childones0_.parent_id as parent_i2_0_0_,
        childones0_.id as id1_0_0_,
        childones0_.id as id1_0_1_,
        childones0_.parent_id as parent_i2_0_1_
    from
        child_one childones0_
    where
        childones0_.parent_id=?
Hibernate:
    select
        childtwos0_.parent_id as parent_i2_1_0_,
        childtwos0_.id as id1_1_0_,
        childtwos0_.id as id1_1_1_,
        childtwos0_.parent_id as parent_i2_1_1_
    from
        child_two childtwos0_
    where
        childtwos0_.parent_id=?
Parent(id=2, childOnes=[ChildOne(id=3)], childTwos=[ChildTwo(id=2), ChildTwo(id=3)])

ParentとChildOneをJoinして取得

😐 ParentとChildOneを先にJoinした結果を取得しておくので、ChildOne取得分は減らせる

  • Repository
public interface ParentRepository extends JpaRepository<Parent, Integer> {

    @Query("SELECT DISTINCT x FROM Parent x " +
            "LEFT JOIN FETCH x.childOnes")
    List<Parent> findParentWithSingleJoin();
}

挙動

  • ParentとChildOneをJoinした結果を取得
Hibernate:
    select
        distinct parent0_.id as id1_5_0_,
        childones1_.id as id1_0_1_,
        childones1_.parent_id as parent_i2_0_1_,
        childones1_.parent_id as parent_i2_0_0__,
        childones1_.id as id1_0_0__
    from
        parent parent0_
    left outer join
        child_one childones1_
            on parent0_.id=childones1_.parent_id
  • Parent(id=1)のChildTwoを取得
Hibernate:
    select
        childtwos0_.parent_id as parent_i2_1_0_,
        childtwos0_.id as id1_1_0_,
        childtwos0_.id as id1_1_1_,
        childtwos0_.parent_id as parent_i2_1_1_
    from
        child_two childtwos0_
    where
        childtwos0_.parent_id=?
Parent(id=1, childOnes=[ChildOne(id=1), ChildOne(id=2)], childTwos=[ChildTwo(id=1)])
  • Parent(id=2)のChildTwoを取得
Hibernate:
    select
        childtwos0_.parent_id as parent_i2_1_0_,
        childtwos0_.id as id1_1_0_,
        childtwos0_.id as id1_1_1_,
        childtwos0_.parent_id as parent_i2_1_1_
    from
        child_two childtwos0_
    where
        childtwos0_.parent_id=?
Parent(id=2, childOnes=[ChildOne(id=3)], childTwos=[ChildTwo(id=2), ChildTwo(id=3)])

ParentとChildOneとChildTwoをJoinして取得

😱 複数のListをJoinで取得しようとするとorg.hibernate.loader.MultipleBagFetchExceptionが発生してしまう

  • Repository
public interface ParentRepository extends JpaRepository<Parent, Integer> {
    // org.hibernate.loader.MultipleBagFetchExceptionが発生してしまう
    @Query("SELECT DISTINCT x FROM Parent x " +
            "LEFT JOIN FETCH x.childOnes " +
            "LEFT JOIN FETCH x.childTwos")
    List<Parent> findParentWithDoubleJoin();
}
  • org.hibernate.loader.MultipleBagFetchException
Caused by: java.lang.IllegalArgumentException: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.example.jpa_n_plus_one.entity.Parent.childOnes, com.example.jpa_n_plus_one.entity.Parent.childTwos]

ParentとChildOneとChildTwoをJoinして取得 (MultipleBagFetchException回避版)

😊 1回のSQLで全要素を取得可能

  • Parentの@OneToMany部分をList型にするとMultipleBagFetchExceptionが発生してしまうため、Set型に変更したもので行う

  • Repository

public interface NeoParentRepository extends JpaRepository<NeoParent, Integer> {

    @Query("SELECT DISTINCT x FROM NeoParent x " +
            "LEFT JOIN FETCH x.neoChildOnes " +
            "LEFT JOIN FETCH x.neoChildTwos")
    List<NeoParent> findParentWithDoubleJoin();
}

Data (MultipleBagFetchException回避版)

INSERT INTO neo_parent(id) VALUES (1)
INSERT INTO neo_parent(id) VALUES (2)

INSERT INTO neo_child_one(id,neo_parent_id) VALUES (1,1)
INSERT INTO neo_child_one(id,neo_parent_id) VALUES (2,1)
INSERT INTO neo_child_one(id,neo_parent_id) VALUES (3,2)

INSERT INTO neo_child_two(id,neo_parent_id) VALUES (1,1)
INSERT INTO neo_child_two(id,neo_parent_id) VALUES (2,2)
INSERT INTO neo_child_two(id,neo_parent_id) VALUES (3,2)

Entity (MultipleBagFetchException回避版)

  • NeoParent
@Table(name = "neo_parent")
@Entity
@Data
@EqualsAndHashCode(exclude = {"neoChildOnes", "neoChildTwos"})
public class NeoParent {
    @Id
    private Integer id;

    @OneToMany(mappedBy = "neoParent")
    private Set<NeoChildOne> neoChildOnes;

    @OneToMany(mappedBy = "neoParent")
    private Set<NeoChildTwo> neoChildTwos;
}
  • NeoChildOne
@Table(name = "neo_child_one")
@Entity
@Data
@ToString(exclude = "neoParent")
public class NeoChildOne {
    @Id
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "neoParentId", foreignKey = @ForeignKey(ConstraintMode.PROVIDER_DEFAULT))
    private NeoParent neoParent;
}
  • NeoChildTwo
@Table(name = "neo_child_two")
@Entity
@Data
@ToString(exclude = "neoParent")
public class NeoChildTwo {

    @Id
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "neoParentId", foreignKey = @ForeignKey(ConstraintMode.PROVIDER_DEFAULT))
    private NeoParent neoParent;
}

挙動

  • NeoParentとNeoChildOneとNeoChildTwo をJoinした結果を取得
Hibernate:
    select
        distinct neoparent0_.id as id1_4_0_,
        neochildon1_.id as id1_2_1_,
        neochildtw2_.id as id1_3_2_,
        neochildon1_.neo_parent_id as neo_pare2_2_1_,
        neochildon1_.neo_parent_id as neo_pare2_2_0__,
        neochildon1_.id as id1_2_0__,
        neochildtw2_.neo_parent_id as neo_pare2_3_2_,
        neochildtw2_.neo_parent_id as neo_pare2_3_1__,
        neochildtw2_.id as id1_3_1__
    from
        neo_parent neoparent0_
    left outer join
        neo_child_one neochildon1_
            on neoparent0_.id=neochildon1_.neo_parent_id
    left outer join
        neo_child_two neochildtw2_
            on neoparent0_.id=neochildtw2_.neo_parent_id
NeoParent(id=1, neoChildOnes=[NeoChildOne(id=1), NeoChildOne(id=2)], neoChildTwos=[NeoChildTwo(id=1)])
NeoParent(id=2, neoChildOnes=[NeoChildOne(id=3)], neoChildTwos=[NeoChildTwo(id=3), NeoChildTwo(id=2)])

まとめ

  • 単純に@OneToManyをfindAllで取得すると複数回SQL投げてしまう
  • 1回のSQLで@OneToManyの要素を1種類取得したい場合は、JOIN FETCHで取得
  • 1回のSQLで@OneToManyの要素を複数種類取得したい場合は、MultipleBagFetchExceptionが発生してしまうため、List型ではなくSet型にしてJOIN FETCHで取得