estudy 한국어/English

JPA N+1 Problem Resolution Methods and Practical Application Tips

1,209 단어·3 분·원문(.md)

N+1 Problem #

A phenomenon where, when querying an entity with an established association, an additional query for the associated entity is generated n times, corresponding to the number of data records, to fetch the data.

Reproducing the Phenomenon #

In the database structure, a user can belong to only one team, and a team can have multiple users. For test data, we added 20 users in total, with 4 users per team across 4 teams.

When Fetch Mode is EAGER #

@Entity
public class User {
    @Id
    @GeneratedValue
    private long id;
    private String firstName;
    private String lastName;

    @ManyToOne(fetch = FetchType.EAGER)		// 즉시 로딩
    @JoinColumn(name = "team_id", nullable = false)
    private Team team;
}

@Entity
public class Team {
    @Id
    @GeneratedValue
    private long id;
    private String name;

    @OneToMany(fetch = FetchType.EAGER)
    private List<User> users = new ArrayList<>();
}

When calling findAll() on the TeamRepository, an interface object that extends JpaRepository,

Hibernate: select team0_.id as id1_0_, team0_.name as name2_0_ from team team0_
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
============== N+1 시점 확인용 ===================

As shown above, the N+1 problem occurs.

When Fetch Mode is Lazy (Lazy Loading) #

If we change only the Fetch mode to Lazy in the Team and User objects and then call findAll again,

Hibernate: select team0_.id as id1_0_, team0_.name as name2_0_ from team team0_

This time, it appears that N+1 did not occur.

However, if we try to use users as shown below, the N+1 problem occurs.

List<Team> all = teamRepository.findAll();
System.out.println("============== N+1 시점 확인용 ===================");
all.stream().forEach(team -> {
    team.getUsers().size();
});

Hibernate: select team0_.id as id1_0_, team0_.name as name2_0_ from team team0_
============== N+1 시점 확인용 ===================
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?
Hibernate: select users0_.team_id as team_id1_1_0_, users0_.users_id as users_id2_1_0_, user1_.id as id1_2_1_, user1_.first_name as first_na2_2_1_, user1_.last_name as last_nam3_2_1_, user1_.team_id as team_id4_2_1_ from team_users users0_ inner join user user1_ on users0_.users_id=user1_.id where users0_.team_id=?

In other words, while lazy loading seemed to avoid the N+1 problem, the N+1 problem actually occurs when attempting to access the objects. The only difference from eager loading is the timing of when the N+1 problem manifests.

Reason for Occurrence #

The reason the N+1 problem occurs is that when JPA analyzes JPQL to generate SQL, it does not refer to the global fetch strategy but only uses the JPQL itself.

That is, it operates in the following sequence:

When the Fetch Strategy is Eager Loading #

  1. The moment findAll is called, a JPQL statement select t from Team t is generated, and after analyzing this statement, an SQL query select * from team is generated and executed.
  2. It receives the results from the DB and creates instances of the Team entity.
  3. Associated users with the team must also be loaded.
  4. It checks if there are associated users in the persistence context.
  5. If not in the persistence context, an SQL statement select * from user where team_id = ? is generated, matching the number of team instances created in step 2. (N+1 occurs)

When the Fetch Strategy is Lazy Loading #

  1. The moment findAll is called, a JPQL statement select t from Team t is generated, and after analyzing this statement, an SQL query select * from team is generated and executed.
  2. It receives the results from the DB and creates instances of the Team entity.
  3. At the point in the code where an attempt is made to use the team's user object, it checks if there are associated users in the persistence context.
  4. If not in the persistence context, an SQL statement select * from user where team_id = ? is generated, matching the number of team instances created in step 2. (N+1 occurs)

Solutions #

Methods to solve the N+1 problem include Fetch join, the EntityGraph annotation, and Batch Size.

Fetch Join #

This method uses JPQL to fetch associated data along with the primary data from the DB right from the start. You can think of it like an SQL JOIN statement.

You need to create a separate method and use the @Query annotation to construct a join fetch associated_entity clause.

 @Query("select t from Team t join fetch t.users")
    List<Team> findAllFetchJoin();

   List<Team> all = teamRepository.findAllFetchJoin();
        System.out.println("============== N+1 시점 확인용 ===================");
        all.stream().forEach(team -> {
            team.getUsers().size();
        });

Hibernate: select team0_.id as id1_0_0_, user2_.id as id1_2_1_, team0_.name as name2_0_0_, user2_.first_name as first_na2_2_1_, user2_.last_name as last_nam3_2_1_, user2_.team_id as team_id4_2_1_, users1_.team_id as team_id1_1_0__, users1_.users_id as users_id2_1_0__ from team team0_ inner join team_users users1_ on team0_.id=users1_.team_id inner join user user2_ on users1_.users_id=user2_.id
============== N+1 시점 확인용 ===================

Looking at the SQL log, if no separate specification is made, the join fetch clause in JPQL is converted into an SQL inner join clause and executed.

EntityGraph Annotation #

@EntityGraph performs a fetch join using an annotation; just be aware that it exists.

The moment you use it, even slightly complex relationships can lead to a hellgate...

Batch Size #

This option isn't precisely a way to prevent the N+1 problem from occurring. Instead, it's a method to make the N+1 problem occur in a select * from user where team_id in (?,?,?) fashion, rather than select * from user where team_id = ?, even when the N+1 problem arises.

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 1000

Simply setting one configuration will result in queries with an in clause, as shown below.

How to Prevent the DB from Crashing Due to N+1 Problems in Production? #

  1. First, if association settings are required, use lazy loading mode instead of eager loading, which is difficult for performance optimization, and use Fetch Join for parts that require performance optimization.
  2. Additionally, set the Batch Size value to 1000 or less by default. (Maximum number of values in an IN clause in most databases: 1000)
  3. Other than that, although it's case-by-case, if association settings are not strictly necessary, breaking the association and using it that way can also be a method to prevent the database from crashing due to N+1 problems.
Back-End/jpa/n+1.md