java - 带有连接的排序数据的 hibernate 分页

Question

我们目前正在研究加速我们应用程序的方法，其中很大一部分与实体列表(实际上是一个表格)有关。

参数和要求

该列表的参数和要求如下(我将尝试只在这里提及相关的):

列表中最多可以有 500,000 行/实体。

一次只显示其中的几个，我们将在这里使用分页。

用户可以选择要在列表中显示哪些列(因此我们无法提供单个“静态”查询)。

大多数列表列都是可排序和/或可过滤的。

实体有一对多的关系，它们也提供一些列表列。

其中一些列表列可以包含多个值(这些值显示为单个单元格内的列表)

对源自当前用户操作(例如编辑、上传等)的数据的任何更新都应尽快(即“立即”)反射(reflect)在列表中

为了使模型更清晰，请考虑以下简化实体(如在 JPA 中)模型:

class Car {
  String manufacturer;
  String model;
  Date dateOfProduction;
  List<TyreSize> allowedTyreSizes;
  Set<Date> inspectionDates;
}

请不要试图在该模型中赋予太多意义，因为它只是为了说明问题(我们的数据不同且复杂得多)。

汽车的“完整”列表可能如下所示:

+==============+=======+=======+===============+=============+
| Manufacturer | Model | Prod. | Allowed Tyres | Inspections |
+==============+=======+=======+===============+=============+
| BMW          | 320d  |01/2016| - 225/40 R18  | - 01/07/16  |
|              |       |       | - 225/45 R17  | - 13/12/16  |
+--------------+-------+-------+---------------+-------------+
| Toyota       | Camry |09/2016| - 185/70 R13  | - 31/12/16  |
+--------------+-------+-------+---------------+-------------+ 

由于用户可以在运行时选择要显示的列，因此我们正在动态构建必要的查询。到目前为止，这一切都运行良好。

基本问题

我们遇到的问题是涉及排序和过滤时的性能:我们当前的方法是将排序和过滤所需的所有数据加载到内存中，在那里进行排序和过滤，然后在其中保留已排序 id 和页面的列表。我们知道这有点慢，但到目前为止，性能足以让我们的管理层满意。但是，由于我们现在有更多数据可供操作并且性能要求有所提高，因此情况发生了变化。

因此，我们正在研究改进所有数据的排序和过滤的方法，而我们目前正在遵循在数据库上执行此操作的方法我仍然会问这个(侧面)问题:

如何最好地处理具有动态列和每个单元格可能有多个值的大量行的分页？

目前我们正在使用 Postgresql，如果可能的话，我们希望继续使用它，但如果不同的存储更适合，我们至少会检查一下。

当前方法和问题 (在底部)

如上所述，我们目前正在尝试让数据库对我们的数据进行排序、过滤和分页。可以使用 2 个查询:一个用于获取当前页面的行 ID，另一个用于实际加载这些行的数据。

由于挑战是第一个查询，我将专注于:

AFAIK 我们可以在 SQL 中做这样的事情(使用上面的汽车示例):

SELECT DISTINCT id FROM (
  SELECT id, ... FROM car c
    LEFT OUTER JOIN allowedtyresizes ats ON c.id = ats.car_id
    LEFT OUTER JOIN tyresizes ts ON ts.id = ats.tyresize_id
    ... //additional joins if required
    ORDER BY ... //apply any user-defined sorts
    WHERE ... //apply any user-defined filters (or maybe put them into the joins)
  )
  OFFSET ... //page offset
  LIMIT ...  //page size

理论上，这个查询(虽然它可能不完全正确)应该提供我们需要确定为当前页面加载哪些行所需的结果。

由于我们使用的是 Hibernate (5.2 atm)，因此我们希望使用 HQL 或 Criteria 来实现这一点。然而，似乎 Hibernate 不支持从上面的 select 语句中进行选择，因此这可能不是一种可行的方法。如果我们不得不退回到 native SQL 或完全不同的方法，那就这样吧，但我们更愿意让它与当前的基础设施一起工作。

所以问题是:

Hibernate 5.x 是否支持“从选择中选择”之类的功能？

当必须对多对关系进行排序和过滤时，如何使用 Hibernate 进行分页，即单个连接可能导致重复行？

Answer 1

我在过去的雇主那里也有类似的要求，我们同样注意到，在较小的数据集下，数据库完全有能力做到这一点；然而，将有一个临界点，甚至数据库也会受到影响。

我的解决方案是引入 Hibernate Search 及其与 ElasticSearch 的集成，以将搜索数据存储在 NoSQL Lucene 数据存储中，这对于基于 unicode 的文本查询和排序来说绝对是快速的，正如您所描述的。

这允许您继续使用您已经拥有的 Hibernate ORM 基础设施，并以最少的努力将这些附加组件融入您的架构中。集成是无缝的，当然值得投资，尤其是当您的数据集随着时间的推移而不断增长时。

我们正在处理数百万行，并且绝对没有性能问题。执行排序和分页的查询平均不到 100 毫秒。