Java JPA和Hibernate中N+1问题的解决方案是什么？

What is the solution for the N+1 issue in JPA and Hibernate?

提问by Vipul Agarwal

I understand that the N+1 problem is where one query is executed to fetch N records and N queries to fetch some relational records.

我知道 N+1 问题是执行一个查询以获取 N 条记录和执行 N 个查询以获取一些关系记录。

But how can it be avoided in Hibernate?

但是如何在 Hibernate 中避免它呢？

采纳答案by karim mohsen

Suppose we have a class Manufacturer with a many-to-one relationship with Contact.

假设我们有一个与 Contact 有多对一关系的类制造商。

We solve this problem by making sure that the initial query fetches all the data needed to load the objects we need in their appropriately initialized state. One way of doing this is using an HQL fetch join. We use the HQL

我们通过确保初始查询获取在适当初始化状态下加载我们需要的对象所需的所有数据来解决这个问题。一种方法是使用 HQL fetch join。我们使用 HQL

"from Manufacturer manufacturer join fetch manufacturer.contact contact"

with the fetch statement. This results in an inner join:

使用 fetch 语句。这导致内部联接：

select MANUFACTURER.id from manufacturer and contact ... from 
MANUFACTURER inner join CONTACT on MANUFACTURER.CONTACT_ID=CONTACT.id

Using a Criteria query we can get the same result from

使用 Criteria 查询，我们可以获得相同的结果

Criteria criteria = session.createCriteria(Manufacturer.class);
criteria.setFetchMode("contact", FetchMode.EAGER);

which creates the SQL :

它创建了 SQL：

select MANUFACTURER.id from MANUFACTURER left outer join CONTACT on 
MANUFACTURER.CONTACT_ID=CONTACT.id where 1=1

in both cases, our query returns a list of Manufacturer objects with the contact initialized. Only one query needs to be run to return all the contact and manufacturer information required

在这两种情况下，我们的查询都会返回已初始化联系人的制造商对象列表。只需运行一个查询即可返回所需的所有联系人和制造商信息

for further information here is a link to the problemand the solution

有关更多信息，请访问问题和解决方案的链接

回答by Radim K?hler

Native solution for 1 + Nin Hibernate, is called:

Hibernate 中1 + N 的本机解决方案称为：

20.1.5. Using batch fetching

20.1.5. 使用批量获取

Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy.There are two ways we can configure batch fetching: on the 1) class level and the 2) collection level...

使用批量获取，如果访问了一个代理，Hibernate 可以加载多个未初始化的代理。批量抓取是对惰性选择抓取策略的优化。我们可以通过两种方式配置批量获取：1) 类级别和 2) 集合级别...

Check these Q & A:

检查这些问答：

• @BatchSize but many round trip in @ManyToOne case
• Avoiding n+1 eager fetching of child collection element association

• @BatchSize 但在 @ManyToOne 情况下有很多往返
• 避免 n+1 急切获取子集合元素关联

With annotations we can do it like this:

使用注释，我们可以这样做：

A classlevel:

Aclass级：

@Entity
@BatchSize(size=25)
@Table(...
public class MyEntity implements java.io.Serializable {...

A collectionlevel:

Acollection级：

@OneToMany(fetch = FetchType.LAZY...)
@BatchSize(size=25)
public Set<MyEntity> getMyColl() 

Lazy loading and batch fetching together represent optimization, which:

延迟加载和批量获取一起代表优化，即：

• does notrequire any explicit fetchingin our queries
• will be applied on any amount of referenceswhich are (lazily) touched after the root entity is loaded (while explicit fetching effects only these named in query)
• will solve issue 1 + N with collections(because only one collectioncould be fetched with root query)without need to farther processing To get DISTINCT root values (check: Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct)

• 它不要求任何明确的抓取我们查询
• 将应用于加载根实体后（懒惰地）触摸的任何数量的引用（而显式提取效果仅在查询中命名）
• 将通过集合解决问题 1 + N （因为只有一个集合可以通过根查询获取）而无需进一步处理获取 DISTINCT 根值（检查：Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct）

回答by Vlad Mihalcea

Since this is a very common question, I wrote this article, on which this answer is based on.

由于这是一个非常常见的问题，我写了 这篇文章，这个答案是基于它。

The problem

问题

The N+1 query issue happens when you forget to fetch an association and then you need to access it.

当您忘记获取关联然后需要访问它时，就会发生 N+1 查询问题。

For instance, let's assume we have the following JPA query:

例如，假设我们有以下 JPA 查询：

List<PostComment> comments = entityManager.createQuery(
    "select pc " +
    "from PostComment pc " +
    "where pc.review = :review", PostComment.class)
.setParameter("review", review)
.getResultList();

Now, if we iterate the PostCommententities and traverse the postassociation:

现在，如果我们迭代PostComment实体并遍历post关联：

for(PostComment comment : comments) {
    LOGGER.info("The post title is '{}'", comment.getPost().getTitle());
}

Hibernate will generate the following SQL statements:

Hibernate 将生成以下 SQL 语句：

SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_
FROM   post_comment pc
WHERE  pc.review = 'Excellent!'

INFO - Loaded 3 comments

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 1

INFO - The post title is 'Post nr. 1'

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 2

INFO - The post title is 'Post nr. 2'

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 3

INFO - The post title is 'Post nr. 3'

That's how the N+1 query issue is generated.

这就是 N+1 查询问题的生成方式。

Because the postassociation is not initialized when fetching the PostCommententities, Hibernate must fetch the Postentity with a secondary query, and for N PostCommententities, N more queries are going to be executed (hence the N+1 query problem).

因为post在获取PostComment实体时没有初始化关联，Hibernate 必须Post通过辅助查询获取实体，对于 N 个PostComment实体，将执行 N 个更多查询（因此出现 N+1 查询问题）。

The fix

修复

The first thing you need to do to tackle this issue is to add proper SQL logging and monitoring. Without logging, you won't notice the N+1 query issue while developing a certain feature.

解决此问题需要做的第一件事是添加适当的 SQL 日志记录和监控。如果没有日志记录，您在开发某个功能时不会注意到 N+1 查询问题。

Second, to fix it, you can just JOIN FETCHthe relationship causing this issue:

其次，要修复它，您只需JOIN FETCH导致此问题的关系：

List<PostComment> comments = entityManager.createQuery(
    "select pc " +
    "from PostComment pc " +
    "join fetch pc.post p " +
    "where pc.review = :review", PostComment.class)
.setParameter("review", review)
.getResultList();

If you need to fetch multiple child associations, it's better to fetch one collection in the initial query and the second one with a secondary SQL query.

如果您需要获取多个子关联，最好在初始查询中获取一个集合，并使用辅助 SQL 查询获取第二个集合。

How to automatically detect the N+1 query issue

如何自动检测N+1查询问题

This issue is better to be caught by integration tests.

这个问题最好通过集成测试来解决。

You can use an automatic JUnit assert to validate the expected count of generated SQL statements. The db-utilprojectalready provides this functionality, and it's open-source and the dependency is available on Maven Central.

您可以使用自动 JUnit 断言来验证生成的 SQL 语句的预期计数。该db-util项目已经提供了此功能，并且它是开源的，并且依赖项在 Maven Central 上可用。

For more details about how you can use the db-utilproject, check out this article.

有关如何使用该db-util项目的更多详细信息，请查看这篇文章。

回答by philippn

You can even get it working without having to add the @BatchSizeannotation everywhere, just set the property hibernate.default_batch_fetch_sizeto the desired value to enable batch fetching globally. See the Hibernate docsfor details.

你甚至可以让它工作而无需在@BatchSize任何地方添加注释，只需将属性hibernate.default_batch_fetch_size设置为所需的值即可全局启用批量提取。有关详细信息，请参阅 Hibernate 文档。

While you are at it, you will probably also want to change the BatchFetchStyle, because the default (LEGACY) is most likely not what you want. So a complete configuration for globally enabling batch fetching would look like this:

当您使用它时，您可能还想更改BatchFetchStyle，因为默认值 ( LEGACY) 很可能不是您想要的。因此，全局启用批量获取的完整配置如下所示：

hibernate.batch_fetch_style=PADDED
hibernate.default_batch_fetch_size=25

Also, I'm suprised that one of the proposed solutions involves join-fetching. Join-fetching is rarely desirable because it causes more data to be transferred with every result row, even if the dependent entity has already been loaded into the L1 or L2 cache. Thus I would recommend to disable it completey by setting

此外，我很惊讶提议的解决方案之一涉及连接提取。连接获取很少是可取的，因为它会导致每个结果行传输更多数据，即使依赖实体已经加载到 L1 或 L2 缓存中。因此，我建议通过设置来完全禁用它

hibernate.max_fetch_depth=0

回答by Ybri

This is a frequently asked question so I created the article Eliminate Spring Hibernate N+1 Queriesto detail the solutions

这是一个常见问题，所以我创建了文章消除 Spring Hibernate N+1 查询来详细说明解决方案

To help you detect all the N+1 queries in your application and avoid adding more queries, I created the library spring-hibernate-query-utilsthat auto-detects the Hibernate N+1 queries.

为了帮助您检测应用程序中的所有 N+1 查询并避免添加更多查询，我创建了自动检测 Hibernate N+1 查询的库spring-hibernate-query-utils。

Here is some code to explain how to add it to your application:

下面是一些代码来解释如何将它添加到您的应用程序中：

• Add the library to your dependencies

• 将库添加到您的依赖项

<dependency>
    <groupId>com.yannbriancon</groupId>
    <artifactId>spring-hibernate-query-utils</artifactId>
    <version>1.0.0</version>
</dependency>

• Configure it in your application properties to return exceptions, default is error logs

• 在您的应用程序属性中配置它以返回异常，默认为错误日志

hibernate.query.interceptor.error-level=EXCEPTION

回答by Ajay Kumar

Here are some snippet codes that would help you to fix the N+1 Problem.

以下是一些可以帮助您解决 N+1 问题的代码片段。

One to Many Relationship with Manager and Client Entity.

与经理和客户实体的一对多关系。

Client JPA Repository -

客户端 JPA 存储库 -

public interface ClientDetailsRepository extends JpaRepository<ClientEntity, Long> {
    @Query("FROM clientMaster c join fetch c.manager m where m.managerId= :managerId")
    List<ClientEntity> findClientByManagerId(String managerId);
}

Manager Entity -

经理实体 -

@Entity(name = "portfolioManager")
@Table(name = "portfolio_manager")
public class ManagerEntity implements Serializable {

      // some fields

@OneToMany(fetch = FetchType.LAZY, mappedBy = "manager")
protected List<ClientEntity> clients = new ArrayList<>();

     // Getter & Setter 

}

Client Entity -

客户实体 -

@Entity(name = "clientMaster")
@Table(name = "clientMaster")
public class ClientEntity implements Serializable {

    // some fields

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "manager_id", insertable = false, updatable = false)
    protected ManagerEntity manager;

    // Getter & Setter 

 }

And finally, Generate output -

最后，生成输出 -

Hibernate: select cliententi0_.client_id as client_id1_0_0_, cliententi0_.manager_id as manager_id2_0_0_, managerent1_.manager_id as manager_id1_2_1_, cliententi0_.created_by as created_by7_0_0_, cliententi0_.created_date as created_date3_0_0_, cliententi0_.client_name as client_name4_0_0_, cliententi0_.sector_name as sector_name5_0_0_, cliententi0_.updated_by as updated_by8_0_0_, cliententi0_.updated_date as updated_date6_0_0_, managerent1_.manager_name as manager_name2_2_1_ from client_master cliententi0_, portfolio_manager managerent1_ where cliententi0_.manager_id=managerent1_.manager_id and managerent1_.manager_id=?```