我如何解决N + 1选择问题？ [英] How can i resolve the N+1 Selects problem?

问题描述

我无法理解如何避免jpa或hibernate中的n + 1选择。

从我读到的，有'左连接抓取'，但是我'不知道它是否仍然适用于多个列表（oneToMany）..

有人可以向我解释，或给我一个链接，并附有清晰的完整说明请问如何解决这个问题？

对不起，如果这是一个noob问题，但我无法在这个问题上找到真正清晰的文章或文档。
$ b

感谢

解决方案

除了连接，您还可以使用子选择。这会导致2个查询被执行（或者如果有 m 列表，那么通常是 m + 1），但是它也可以很好地扩展大量列表，抓取。

使用联接抓取，如果您使用实体抓取2个表格（或列表），您将得到一个<笛卡尔式产品，即所有组合来自两个表格的行对。如果表格很大，结果可能很大，例如如果两个表都有1000行，笛卡尔积就包含100万行！

对于这种情况，更好的选择是使用子查询。在这种情况下，您将在主select（加载父实体）的顶部发出2个选择 - 每个表一个选择 - 因此总共可以用3个查询加载1 + 100 + 100行。

对于记录来说，延迟加载也会导致201个单独的选择，每个选择加载一行。

更新： 以下是一些示例：

• 教程： Tuning Lazy Fetching ，以及关于子选项的章节（顺便说一句，它也解释了n + 1选择问题和所有处理它的策略） / li>
  
• 来自Hibernate引用的HQL子查询，以防万一， ch从Hibernate引用中获取策略 - 与第一个类似的内容，但更彻底

I have trouble understanding how to avoid the n+1 select in jpa or hibernate.

From what i read, there's the 'left join fetch', but i'm not sure if it still works with more than one list (oneToMany)..

Could someone explain it to me, or give me a link with a clear complete explanation please ?

I'm sorry if this is a noob question, but i can't find a real clear article or doc on this issue.

Thanks

解决方案

Apart from the join, you can also use subselect(s). This results in 2 queries being executed (or in general m + 1, if you have m lists), but it scales well for a large number of lists too, unlike join fetching.

With join fetching, if you fetch 2 tables (or lists) with your entity, you get a cartesian product, i.e. all combinations of pairs of rows from the two tables. If the tables are large, the result can be huge, e.g. if both tables have 1000 rows, the cartesian product contains 1 million rows!

A better alternative for such cases is to use subselects. In this case, you would issue 2 selects - one for each table - on top of the main select (which loads the parent entity), so altogether you load 1 + 100 + 100 rows with 3 queries.

For the record, the same with lazy loading would result in 201 separate selects, each loading a single row.

Update: here are some examples:

• a tutorial: Tuning Lazy Fetching, with a section on subselects towards the end (btw it also explains the n+1 selects problem and all strategies to deal with it),
• examples of HQL subqueries from the Hibernate reference,
• just in case, the chapter on fetching strategies from the Hibernate reference - similar content as the first one, but much more thorough

这篇关于我如何解决N + 1选择问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！