优化JPA动态Count查询：如何避免Oracle生成EXISTS语句？

我们使用CriteriaBuilder实现分页查询时，通常会执行两个查询：一是统计结果总数，二是获取指定页的数据子集。但发现统计总条数的查询未被JPA优化，Oracle生成了包含EXISTS的SQL，性能表现不佳。相关Java代码及生成的SQL如下，请问如何避免生成EXISTS？这段Java代码是否存在问题？

相关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();

生成的Oracle 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 = ?)))
);

代码存在的问题 #

核心问题是复用了业务查询的Root<Foo> from对象构建统计查询。这个from已经绑定了业务查询的上下文（可能隐含关联、分组或重复数据处理逻辑），JPA为了满足countDistinct的去重需求，只能生成带EXISTS的子查询，而非直接对过滤后的结果计数。

避免EXISTS的优化方法 #

1. 为统计查询单独创建Root对象
   不要复用业务查询的from，重新创建统计专用的Root<Foo>，切断业务查询上下文的关联，让JPA生成简洁的计数SQL：

   // 单独初始化统计查询的Root
   Root<Foo> countRoot = criteriaBuilder.createQuery(Long.class).from(Foo.class);
   CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class)
           .select(criteriaBuilder.countDistinct(countRoot))
           .where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
   Long numberResults = entityManager.createQuery(countQuery).getSingleResult();
   

2. 移除不必要的countDistinct
   如果Foo的主键（比如REFERENCE+VERSION_NUM+ISSUER的组合）本身是唯一约束，那么查询结果不会有重复数据，直接用count(countRoot)替换countDistinct(countRoot)，进一步简化SQL逻辑：

   .select(criteriaBuilder.count(countRoot))
   

3. 检查predicates中的关联条件
   如果predicates包含关联其他实体的过滤条件，需为统计查询的Root单独处理关联逻辑，不要复用业务查询的关联关系，避免因关联导致的子查询生成。

内容的提问来源于stack exchange，提问作者anat0lius