优化 JPA 动态计数查询

Question

具有返回分页结果的典型方法，使用CriteriaBuilder并执行2个查询：

• 一个计算结果总数的查询
• 另一个为指定页面获取子集的查询

我们注意到第一个查询，JPA根本没有进行优化，因为它正在使用exists（来自Oracle）。

Java代码：

Root<Foo> from = criteriaQuery.from(Foo.class);
//... predicates
CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class)
        .select(criteriaBuilder.countDistinct(from))
        .where(predicates.toArray(new Predicate[predicates.size()]));
Long numberResults = entityManager.createQuery(countQuery).getSingleResult();

SQL 生成的查询：

SELECT COUNT(t0.REFERENCE) 
FROM foo t0 
WHERE EXISTS (
  SELECT t1.REFERENCE 
  FROM foo t1 
  WHERE ((((t0.REFERENCE = t1.REFERENCE) AND (t0.VERSION_NUM = t1.VERSION_NUM)) AND (t0.ISSUER = t1.ISSUER)) AND (t1.REFERENCE LIKE ? AND (t1.VERSION_STATUS = ?)))
);

我该如何避免使用exists？Java代码有什么问题吗？

- anat0lius

---

你真的需要使用countDistinct()而不是简单的count()吗？看起来REFERENCE是PK...尝试与使用count()生成的SQL进行比较。 - p3consulting

如果你想要更好的查询，我认为 select count(*), count(case when <your conditions> then 1 end) from <your table>; 可能是你想要的。不过我不知道你如何在你的java/jpa/任何代码中生成它。 - Boneist

@p3consulting 的确，countDistinct() 是多余的。只用 count()，仍然会给我一个与 EXISTS 相同的查询。 - anat0lius

所以问题似乎来自于被复制的原始谓词，通常情况下，JPQL查询更容易掌握（从JPQL表达式中获取所需的SQL...），更不用说本地查询了... - p3consulting

你一定要提供 //... predicates 部分。此外，exists 运算符对我来说似乎是标准 SQL 而不是 Oracle 特有的。你认为查询没有被优化吗？如果有的话，查询中似乎有一堆不必要的 ()，因为你使用了 AND 而没有用到 OR。虽然我不是很精通 SQL，但我认为这个查询 SELECT COUNT(t0.REFERENCE) FROM foo t0 WHERE t0.REFERENCE LIKE ? AND t0.VERSION_STATUS = ? 应该与你的查询达到相同的效果。 - XtremeBaumer

Answer 1

出于不同的原因，这个问题和这篇相关文章列举了其中一些，EclipseLink在countDistinct操作实现中使用EXISTS。

虽然我可以同意你的观点，但请注意，Oracle中EXISTS提供的性能实际上非常依赖于用例，并且它不一定很差。请考虑查看Tom Kyte博客中的这篇神话般的博客文章。

所以我的建议是，请继续使用生成的代码和相应的SQL。

如果您需要或想要使用不同的方法，也许更有效地计算记录的方法是获取与提供的谓词匹配的实体的ID（实际上，性能大部分取决于这些谓词），并在Java中在内存中计算结果。我的意思是：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
// I assume reference is String here
CriteriaQuery<String> query = cb.createQuery(String.class);
Root<Foo> root = query.from(Foo.class);

query
  .select(root.get("reference"))
    .distinct(true)
  .where(predicates.toArray(new Predicate[predicates.size()]))
;
List<String> references = entityManager.createQuery(query).getResultList();
int count = references.size();

虽然我认为这并不总是明智的，但如果数据量不大，您甚至可以从数据库中一次性获取结果，并使用Java在内存中进行分页，使用{{link1：subList}}非常简单。

最后，据我所知，其他JPA提供程序（如Hibernate）以不同的方式实现计数：如果切换JPA提供程序是一个选项，您可以尝试使用它。

- jccampanero

---

好的，谢谢。那么情况可能没有你所提到的那么糟糕。我发现主要问题出在使用了 LIKE 的某个特定谓词上。在我发布的例子中，是 t1.REFERENCE LIKE ? 的一部分。Reference 是 PK 的一部分。我意识到如果使用 = 替代 LIKE ，查询性能会更好。关于你提到的将结果进行子列表处理，我认为这样做很有风险，因为条目的数量可能非常大，并且会导致超时。 - anat0lius

非常感谢您的反馈@anat0lius。是的，这很有道理：使用like将阻止使用主键索引。使用=当然会利用它，查询性能会更好。是的，我同意您的观点：抱歉，我提到子列表的内存过滤只是一种可能的方法，但正如答案中所述，我认为它应该仅在处理小数据集时使用，以避免像您指出的那样的内存或超时相关问题。尽可能在数据库本身中过滤结果总是明智的。 - jccampanero

@anat0lius 为了完整起见，我在之前的评论中指出使用LIKE将阻止Oracle使用索引，但实际上我应该说“可能”，因为这并不总是正确的。我搜索了参考资料，例如我找到了这篇文章，它提供了不同的用例。希望能在某种程度上帮助到你。 - jccampanero

没错，在我们的情况下使用索引。我们在搜索术语末尾添加了“%”通配符，因此没有问题。但是您分享的文章提到了另一个通配符字符“_”，我不知道这一点……确切地说，客户正在使用包含下划线的术语，这会降低性能。 - anat0lius

@anat0lius，你可以尝试转义客户提供的术语中包含的下划线，JPA准则API提供了这种功能的机制。请参考此相关SO问题，我认为它会有所帮助。 - jccampanero

Answer 2

使用EXISTS或不使用EXISTS，查询计划是相同的。唯一的优化是在同一查询中返回COUNT()和结果，在SQL中很容易实现，可以使用"OVER()"。但是将Foo.class映射到视图并添加一个临时列来包含计数将使应用程序的许多其他部分变得复杂，并且将分页查询的结果映射到新的CountedFoo.class也会使解决方案变得复杂。

- p3consulting