java - 为什么我的查询性能急剧下降？

Question

当我有 1 或 2 个条目的映射时，使用以下 CriteriaBuilder 在 1 秒内返回结果，但是当它达到 3 时，查询性能会下降。然后添加第四个条目会进一步降低性能，以至于我实际上没有看到它返回结果集。

我尝试过使用子查询，但似乎遇到了类似的问题，其中 2 个 EXISTS 子查询将在合理的时间内返回数​​据，但 3 个或更多子查询将导致性能大幅下降。

我注意到，如果没有 ORDER BY，即使有 3 个条目(1 秒或更短)，查询也能很好地工作，但是一旦我将其添加回相同的 3 个条目，搜索就会花费超过 30 秒。

这是构建器:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Study> q = builder.createQuery(Study.class);
Root<Study> rootStudy = q.from(Study.class);

List<Predicate> pList = new ArrayList<Predicate>( searchMap.size() );
q.select(rootStudy);

Join<Study, Demographics> dem = rootStudy.join( "demographics" );
Join<Study, MetaData> met = rootStudy.join( "metadata" );

// add all conditions
for (Map.Entry<XmlTagHashKey, String> entry : searchMap.entrySet() ) {

    MapJoin< Demographics, XmlTagHashKey, Field2 > mapJoin = demJoin.joinMap( "fields" );

    Path<String> attributePath = mapJoin.get( "value" );
    Predicate p = builder.and( builder.equal( mapJoin.key(), entry.getKey() ),
                            builder.like( attributePath, "%" + entry.getValue() + "%" ));

    pList.add( p );
}

q.where( pList.toArray(new Predicate[]{}) );
q.orderBy( builder.desc(  met.<String>get( XMLTagEnum.bbrad_status_updated.name() ) ) );

TypedQuery<Study> typedQuery = em.createQuery( q );
typedQuery.setMaxResults( 100 );
return getResultsList(typedQuery);

以下是当我有 3 个映射条目时从上述构建器创建的 SQL:

SELECT t1.id, 
       t1.study_id, 
       t1.demographics_id, 
       t1.metadata_id 
FROM   demographics_has_fields t8, 
       field t7, 
       demographics_has_fields t6, 
       field t5, 
       demographics_has_fields t4, 
       field t3, 
       demographics t2, 
       study t1, 
       metadata t0 
WHERE  ( ( ( ( ( t6.xmltag = ? ) 
               AND t5.value LIKE ? ) 
             AND ( ( t4.xmltag = ? ) 
                   AND t3.value LIKE ? ) ) 
           AND ( ( t8.xmltag = ? ) 
                 AND t7.value LIKE ? ) ) 
         AND ( ( ( ( ( t2.id = t1.demographics_id ) 
                     AND ( ( t6.demographics_id = t2.id ) 
                           AND ( t5.id = t6.field_id ) ) ) 
                   AND ( ( t4.demographics_id = t2.id ) 
                         AND ( t3.id = t4.field_id ) ) ) 
                 AND ( ( t8.demographics_id = t2.id ) 
                       AND ( t7.id = t8.field_id ) ) ) 
               AND ( t0.id = t1.metadata_id ) ) ) 
ORDER  BY t0.bbradstatusupdated DESC 

以下是表结构的示例:

我想要实现的是选择一个研究，其中字段的值包含“james”，xml 标记将为“1”(1 是姓名，2 是性别)。我使用 map 的原因是因为您可能还想搜索其他字段，例如性别。我认为实现这一点的唯一方法是使用现有的子查询或连接，就像我上面所做的那样。

不幸的是，这个数据结构非常糟糕，但它是从以前的数据库设计继承的，所以我有点坚持它。

Answer 1

如果我正确理解您在做什么，那么您将添加到每个 map 条目的查询的联接。这意味着，您需要将查询必须处理的数据量乘以每个 map 元素的大量数据。这必然会导致糟糕的可扩展性。

要验证这是否确实是问题所在，请执行以下操作:

针对数据库运行 2 个和 3 个映射条目的 (sql) 语句并测量它们的性能。确保获取所有条目而不仅仅是第一个条目。

如果我的猜测是正确的，您将看到或多或少相同的性能下降，因此这不是 JPA/eclipse-link 问题。

解决方案可能是切换到不同的数据模型，可能是星型模式。但这会影响您应用程序中的很多事情，而我们对此知之甚少。