Masalah dengan pertanyaan N + 1 di JPA dan Hibernate

Untuk mengantisipasi kursus "Arsitek Beban Tinggi", kami mengundang Anda untuk menghadiri tutorial terbuka tentang "Pola Penyimpanan Skala Keluar" .

Sementara itu, kami membagikan terjemahan tradisional dari artikel yang bermanfaat.

pengantar

Pada artikel ini, saya akan menjelaskan apa masalah kueri N + 1 dengan JPA dan Hibernate dan cara terbaik untuk memperbaikinya. 

Masalah N + 1 tidak spesifik untuk JPA dan Hibernate, dan Anda mungkin mengalaminya saat menggunakan teknologi akses data lainnya.

Apa masalah N + 1

Masalah N + 1 terjadi saat kerangka akses data menjalankan N kueri SQL tambahan untuk mendapatkan data yang sama yang dapat diperoleh dari kueri SQL tunggal.

Semakin besar nilai N, semakin banyak query yang akan dieksekusi dan semakin besar pengaruhnya terhadap kinerja. Dan meskipun log kueri yang lambat dapat membantu Anda menemukan kueri yang lambat, itu tidak akan mendeteksi masalah N + 1, karena setiap kueri tambahan yang terpisah dijalankan dengan cukup cepat. 

, , .

: post () post_comments ( ), "--":

post :

INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 1', 1)
  
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 2', 2)
  
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 3', 3)
  
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence - Part 4', 4)

post_comment :

INSERT INTO post_comment (post_id, review, id)
VALUES (1, 'Excellent book to understand Java Persistence', 1)
  
INSERT INTO post_comment (post_id, review, id)
VALUES (2, 'Must-read for Java developers', 2)
  
INSERT INTO post_comment (post_id, review, id)
VALUES (3, 'Five Stars', 3)
  
INSERT INTO post_comment (post_id, review, id)
VALUES (4, 'A great reference book', 4)

N+1 SQL

, N + 1 , SQL.

post_comments SQL-:

List<Tuple> comments = entityManager.createNativeQuery("""
    SELECT
        pc.id AS id,
        pc.review AS review,
        pc.post_id AS postId
    FROM post_comment pc
    """, Tuple.class)
.getResultList();

(title) (post) (post_comment):

for (Tuple comment : comments) {
    String review = (String) comment.get("review");
    Long postId = ((Number) comment.get("postId")).longValue();
 
    String postTitle = (String) entityManager.createNativeQuery("""
        SELECT
            p.title
        FROM post p
        WHERE p.id = :postId
        """)
    .setParameter("postId", postId)
    .getSingleResult();
 
    LOGGER.info(
        "The Post '{}' got this review '{}'",
        postTitle,
        review
    );
}

N + 1, SQL- (1 + 4):

SELECT
    pc.id AS id,
    pc.review AS review,
    pc.post_id AS postId
FROM post_comment pc
 
SELECT p.title FROM post p WHERE p.id = 1
-- The Post 'High-Performance Java Persistence - Part 1' got this review
-- 'Excellent book to understand Java Persistence'
    
SELECT p.title FROM post p WHERE p.id = 2
-- The Post 'High-Performance Java Persistence - Part 2' got this review
-- 'Must-read for Java developers'
     
SELECT p.title FROM post p WHERE p.id = 3
-- The Post 'High-Performance Java Persistence - Part 3' got this review
-- 'Five Stars'
     
SELECT p.title FROM post p WHERE p.id = 4
-- The Post 'High-Performance Java Persistence - Part 4' got this review
-- 'A great reference book'

N + 1 . , , SQL-, , :

List<Tuple> comments = entityManager.createNativeQuery("""
    SELECT
        pc.id AS id,
        pc.review AS review,
        p.title AS postTitle
    FROM post_comment pc
    JOIN post p ON pc.post_id = p.id
    """, Tuple.class)
.getResultList();
 
for (Tuple comment : comments) {
    String review = (String) comment.get("review");
    String postTitle = (String) comment.get("postTitle");
 
    LOGGER.info(
        "The Post '{}' got this review '{}'",
        postTitle,
        review
    );
}

SQL- , .

N + 1 JPA Hibernate

JPA Hibernate N + 1, , .

, post post_comments:

JPA- :

@Entity(name = "Post")
@Table(name = "post")
public class Post {
 
    @Id
    private Long id;
 
    private String title;
 
    //Getters and setters omitted for brevity
}
 
@Entity(name = "PostComment")
@Table(name = "post_comment")
public class PostComment {
 
    @Id
    private Long id;
 
    @ManyToOne
    private Post post;
 
    private String review;
 
    //Getters and setters omitted for brevity
}

FetchType.EAGER

FetchType.EAGER JPA- — , , . , FetchType.EAGER N + 1. 

, @ManyToOne @OneToOne FetchType.EAGER, , :

@ManyToOne
private Post post;

FetchType.EAGER ,  JOIN FETCH PostComment JPQL- Criteria API:

List<PostComment> comments = entityManager
.createQuery("""
    select pc
    from PostComment pc
    """, PostComment.class)
.getResultList();

N + 1:

SELECT
    pc.id AS id1_1_,
    pc.post_id AS post_id3_1_,
    pc.review AS review2_1_
FROM
    post_comment pc
 
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4

SELECT, , PostComment post.

, find EntityManager, JPQL- Criteria API (fetch plan), Hibernate , JOIN FETCH. , .

post, : FetchType.EAGER . FetchType.LAZY.

post, JOIN FETCH, N + 1:

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    join fetch pc.post p
    """, PostComment.class)
.getResultList();
 
for(PostComment comment : comments) {
    LOGGER.info(
        "The Post '{}' got this review '{}'",
        comment.getPost().getTitle(),
        comment.getReview()
    );
}

Hibernate SQL-:

SELECT
    pc.id as id1_1_0_,
    pc.post_id as post_id3_1_0_,
    pc.review as review2_1_0_,
    p.id as id1_0_1_,
    p.title as title2_0_1_
FROM
    post_comment pc
INNER JOIN
    post p ON pc.post_id = p.id
     
-- The Post 'High-Performance Java Persistence - Part 1' got this review
-- 'Excellent book to understand Java Persistence'
 
-- The Post 'High-Performance Java Persistence - Part 2' got this review
-- 'Must-read for Java developers'
 
-- The Post 'High-Performance Java Persistence - Part 3' got this review
-- 'Five Stars'
 
-- The Post 'High-Performance Java Persistence - Part 4' got this review
-- 'A great reference book'

, FetchType.EAGER, .

FetchType.LAZY

FetchType.LAZY , N + 1.

post :

@ManyToOne(fetch = FetchType.LAZY)
private Post post;

, PostComment:

List<PostComment> comments = entityManager
.createQuery("""
    select pc
    from PostComment pc
    """, PostComment.class)
.getResultList();

Hibernate SQL-:

SELECT
    pc.id AS id1_1_,
    pc.post_id AS post_id3_1_,
    pc.review AS review2_1_
FROM
    post_comment pc

lazy-load post:

for(PostComment comment : comments) {
    LOGGER.info(
        "The Post '{}' got this review '{}'",
        comment.getPost().getTitle(),
        comment.getReview()
    );
}

N + 1 :

SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1
-- The Post 'High-Performance Java Persistence - Part 1' got this review
-- 'Excellent book to understand Java Persistence'
 
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2
-- The Post 'High-Performance Java Persistence - Part 2' got this review
-- 'Must-read for Java developers'
 
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3
-- The Post 'High-Performance Java Persistence - Part 3' got this review
-- 'Five Stars'
 
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4
-- The Post 'High-Performance Java Persistence - Part 4' got this review
-- 'A great reference book'

post , SQL- .

, JOIN FETCH JPQL:

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    join fetch pc.post p
    """, PostComment.class)
.getResultList();
 
for(PostComment comment : comments) {
    LOGGER.info(
        "The Post '{}' got this review '{}'",
        comment.getPost().getTitle(),
        comment.getReview()
    );
}

FetchType.EAGER, JPQL- SQL-.

FetchType.LAZY @OneToOne, N + 1.

, N+1 c @OneToOne-, .

N + 1 .

, JPQL-, :

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    order by pc.post.id desc
    """, PostComment.class)
.setMaxResults(10)
.setHint(QueryHints.HINT_CACHEABLE, true)
.getResultList();

PostComment , N PostComment:

-- Checking cached query results in region: org.hibernate.cache.internal.StandardQueryCache
-- Checking query spaces are up-to-date: [post_comment]
-- [post_comment] last update timestamp: 6244574473195524, result set timestamp: 6244574473207808
-- Returning cached query results
  
SELECT pc.id AS id1_1_0_,
       pc.post_id AS post_id3_1_0_,
       pc.review AS review2_1_0_
FROM post_comment pc
WHERE pc.id = 3
  
SELECT pc.id AS id1_1_0_,
       pc.post_id AS post_id3_1_0_,
       pc.review AS review2_1_0_
FROM post_comment pc
WHERE pc.id = 2
  
SELECT pc.id AS id1_1_0_,
       pc.post_id AS post_id3_1_0_,
       pc.review AS review2_1_0_
FROM post_comment pc
WHERE pc.id = 1

PostComment. , PostComment , N SQL-.

"Highload Architect".