Hibernate的子选择与批量读取 [英] Hibernate Subselect vs Batch Fetching

问题描述

Hibernate为解决N + 1查询问题提供了（至少）两种选择。一个是将FetchMode设置为Subselect，它在此IN子句内生成一个带有IN子句和子选择符的select。另一种是指定一个BatchSize，它会生成一个带有IN子句的select，其中包含父母的ID。

两者都可以工作，但我发现Subselect选项经常遇到由于父母的查询造成的性能问题非常复杂。另一方面，对于大的BatchSize（比如说1000），查询的数量和这些查询的复杂度都非常小。我的问题是：你什么时候可以使用Hibernate的Subselect FetchMode而不是BatchSize？如果你有大量的父条目（数千），子选择可能是有意义的，但是在其他情况下，你更喜欢Subselect到BatchSize吗？

编辑：在处理急切加载时，我注意到了两者之间的区别。如果您将xToMany关联设置为热切加载并通过子查询，则会生成一个类似于懒惰的子查询。但是，如果指定了BatchSize，则生成的查询将使用外部联接而不是单独的查询。有没有办法迫使Hibernate在加载时急切地使用单独的批量查询？解决方案

我不使用子查询，因为它很难控制。在一个拥有复杂业务逻辑和庞大团队的大型系统中，很难说哪些查询被使用。 Subselect可以在特定的情况下工作，在这些情况下，您完全知道执行了哪个查询。

批量读取有一些很大的优势。它并不总是最快的，但通常足够快。另一方面，它非常稳定，没有任何副作用，并且对业务逻辑完全透明。我从来没有使用高于100的批量值。将N + 1减少到合理数量的查询就足够了。

Hibernate provides (at least) two options for getting around the N+1 query problem. The one is setting the FetchMode to Subselect, which generates a select with a IN-clause and a subselect within this IN-clause. The other is to specify a BatchSize, which generates a select with a IN-clause containing the parents' IDs.

Both work but I find that the Subselect option often runs into performance problems due to the query for the parents being complex. On the other hand, with a large BatchSize (say 1000), the number of queries and complexity of those queries are very small.

My question is thus: when would you use Hibernate's Subselect FetchMode over BatchSize? Subselect probably makes sense if you have a very large number of parent entries (thousands), but are there any other scenarios where you'd prefer a Subselect to BatchSize?

EDIT: I noticed a difference between the two when dealing with eager loading. If you have an xToMany association set to be loaded eagerly and through a subselect, it generates a subselect like it would if it was lazy. If you specify a BatchSize however, the generated query makes use of a outer join instead of a seperate query. Is there any way to force Hibernate to use a seperate batched query when loading eagerly?

解决方案

I don't use subselect, because it is hard to control. In a very large system which has complex business logic and a large team working on it, it is too hard to say which queries are used. Subselect may work in specific cases where you exactly know which query is performed.

Batch fetching has some big advantages. It is not always the fastest, but usually fast enough. On the other hand it is very stable, doesn't have any side effects and is completely transparent to the business logic. I never use batch values higher then 100. It is sufficient to reduce the N+1 to some reasonable amount of queries.

这篇关于Hibernate的子选择与批量读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！