JPA实体图和分页 [英] JPA entity graphs and pagination

问题描述

在我当前的项目中，系统中有多个搜索页面，我们在其中从数据库中获取大量数据，以显示在UI中的大表元素中.我们正在使用JPA进行数据访问(我们的提供者是Hibernate).大多数页面的数据是从多个数据库表中收集的，在许多情况下大约是10个，其​​中包括一些来自OneToMany关系的汇总数据(例如类型X的关联实体数").为了提高性能，我们在用户滚动表时使用对TypedQuery.setFirstResult()和TypedQuery.setMaxResults()的结果集分页来从数据库延迟加载其他行.由于搜索非常动态，因此我们使用JPA CriteriaQuery API来构建查询.但是，我们目前正遭受N + 1 SELECT问题的困扰.实际上，在某些情况下这是非常糟糕的，因为我们可能要遍历嵌套的ToToMany关系的3个级别，其中每个级别上的数据都是延迟加载的.我们真的不能将那些集合声明为渴望加载到实体映射中，因为我们只对某些页面感兴趣. IE.我们可能会从几个不同页面中的同一张表中获取数据，但是我们将显示该表中以及不同页面中不同关联表中的不同数据.

In my current project we have multiple search pages in the system where we fetch a lot of data from the database to be shown in a large table element in the UI. We're using JPA for data access (our provider is Hibernate). The data for most of the pages is gathered from multiple database tables - around 10 in many cases - including some aggregate data from OneToMany relationships (e.g. "number of associated entities of type X"). In order to improve performance, we're using result set pagination with TypedQuery.setFirstResult() and TypedQuery.setMaxResults() to lazy-load additional rows from the database as the user scrolls the table. As the searches are very dynamic, we're using the JPA CriteriaQuery API to build the queries. However, we're currently somewhat suffering from the N+1 SELECT problem. It's pretty bad in some cases actually, as we might be iterating through 3 levels of nested OneToMany relationships, where on each level the data is lazy-loaded. We can't really declare those collections as eager loaded in the entity mappings, as we're only interested in them in some of our pages. I.e. we might fetch data from the same table in several different pages, but we're showing different data from the table and from different associated tables in different pages.

为了减轻这种情况，我们开始尝试JPA实体图，它们似乎对解决N + 1 SELECT问题有很大帮助.但是，当您使用实体图时，Hibernate显然会在内存中应用分页.我可以理解为什么会这样做，但是这种行为在很多情况下会抵消实体图的很多(如果不是全部)好处.当我们不使用实体图时，无论表有多少行，我们都可以加载数据而无需施加任何WHERE限制(即将整个表视为结果集)，因为行数非常有限实际上是由于分页而取得的.现在，分页是在内存中完成的，Hibernate基本上会获取整个数据库表(加上实体图中定义的所有关系)，然后在内存中应用分页，将其余的行扔掉.不好.

In order to alleviate this, we started experimenting with JPA entity graphs, and they seem to help a lot with the N+1 SELECT problem. However, when you use entity graphs, Hibernate apparently applies the pagination in-memory. I can somewhat understand why it does that, but this behavior negates a lot (if not all) of the benefits of the entity graphs in many cases. When we didn't use entity graphs, we could load data without applying any WHERE restrictions (i.e. considering the whole table as the result set), no matter how many millions of rows the table had, as only a very limited amount of rows were actually fetched due to the pagination. Now that the pagination is done in-memory, Hibernate basically fetches the whole database table (plus all relationships defined in the entity graph), and then applies the pagination in-memory, throwing the rest of the rows away. Not good.

所以问题是，是否有一种有效的方法可以将分页图和实体图应用于JPA(Hibernate)?如果JPA没有为此提供解决方案，则Hibernate特定的扩展也可以接受.如果两者都不可行，还有哪些其他选择?使用数据库视图?由于我们支持多家数据库供应商，因此视图会有些麻烦.为不同的供应商创建所有必要的视图将大大增加开发工作.

So the question is, is there an efficient way to apply both pagination and entity graphs with JPA (Hibernate)? If JPA does not offer a solution to this, Hibernate-specific extensions are also acceptable. If that's not possible either, what are the other alternatives? Using database Views? Views would be a bit cumbersome, as we support several database vendors. Creating all of the necessary views for different vendors would increase development effort quite a bit.

我的另一个想法是像我们目前一样应用实体图和分页，并且如果它们返回太多的行，就不会触发任何查询.我已经需要执行COUNT个查询才能使行的延迟加载在UI中正常工作.

Another idea I've had would be to apply both the entity graphs and pagination as we currently do, and simply not trigger any queries if they would return too many rows. I already need to do COUNT queries to get the lazy-loading of rows to work properly in the UI.

推荐答案

我不确定我是否完全理解您的问题，但是我们遇到了类似的问题:我们已分页列出了实体列表，其中可能包含来自多个联接实体的数据.可以对这些列表进行排序和过滤(由于dbms缺少功能，因此某些排序/过滤器必须在内存中应用，但这只是一个注释)，然后应应用分页.

I'm not sure I fully understand your problem but we faced something similar: We have paged lists of entities that may contain data from multiple joined entities. Those lists might be sorted and filtered (some of those sorts/filters have to be applied in memory due missing capabilities in the dbms but that's just a side note) and the paging should be applied afterwards.

将所有数据保留在内存中无法正常工作，因此我们采用了以下方法(可能会有更好/更标准的方法):

Keeping all that data in memory doesn't work well so we took the following approach (there might be better/more standard ones):

1. 使用查询加载主要实体的主键(在我们的情况下为简单long).仅加入排序和过滤所需的内容，以使查询尽可能简单.
   
   在我们的情况下，查询实际上将加载更多数据以在必要时在内存中应用排序和过滤器，但该数据将尽快释放，并且仅保留主键.
2. 在显示特定页面时，我们提取页面的相应主键，并使用第二个查询来加载要在该页面上显示的所有内容.第二个查询可能包含更多的联接，因此比第1步中的联接更复杂和更慢，但是由于我们仅加载该页面的数据，因此系统上的实际负担很低.

1. Use a query to load the primary keys (simple longs in our case) of the main entities. Join only what is needed for sorting and filtering to make the query as simple as possible.
   
   In our case the query would actually load more data to apply sorts and filters in memory where necessary but that data is released asap and only the primary keys are kept.
2. When displaying a specific page we extract the corresponding primary keys for a page and use a second query to load everything that is to be displayed on that page. This second query might contain more joins and thus be more complex and slower than the one in step 1 but since we only load data for that page the actual burden on the system is quite low.

这篇关于JPA实体图和分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！