如何让QueryDSL/Hibernate生成高效SQL避免低效IN语句与冗余子查询

1 核心写法调整：显式关联所有表，消除隐式子查询 #

原来的in/any()写法会触发Hibernate生成隐式子查询，改为显式Join Note与Keyword的多对多关联，直接关联KeywordCache即可：

private JPAQuery<Note> addFilter(JPAQuery<Note> query, List<String> filter) {
    // 显式关联Note的多对多Keyword表，替代隐式子查询
    QKeyword noteKeyword = QKeyword.keyword;
    query.innerJoin(QNote.note.keywords, noteKeyword);
    
    for (String f : filter) {
        UUID id = UUID.fromString(f);
        String variable = "kc_" + id.toString().replaceAll("-", "");
        QKeywordCache cache = new QKeywordCache(variable);
        // 直接关联cache的child与Note绑定的Keyword，无多余子查询
        query.from(cache)
             .where(cache.child.eq(noteKeyword))
             .where(cache.parent.id.eq(id));
    }
    return query;
}

该写法生成的SQL结构和你手写的原生SQL完全一致，直接关联Note、Note_Keyword、KeywordCache三张表，性能与原生SQL基本持平。

2 实体映射优化：调整关联加载策略 #

将KeywordCache的child、parent关联改为LAZY加载：

@NotNull
@ManyToOne(fetch = FetchType.LAZY)
@Id
private Keyword child;

@NotNull
@ManyToOne(fetch = FetchType.LAZY)
@Id
private Keyword parent;

因为你只需要用到这两个字段的ID做关联，LAZY加载下Hibernate不会额外关联Keyword表，彻底消除第二种写法中多余的Keyword表查询。

3 分页查询优化：自定义Count逻辑 #

fetchResults()自动生成的count查询可能携带多余字段，自定义count查询只统计Note ID，减少计算开销：

public Page<Note> find(List<String> filter, Pageable page) {
    JPAQuery<Note> query = new JPAQuery<>(entityManager);
    query.from(QNote.note);
    query.select(QNote.note);
    query.distinct();
    query = addFilter(query, filter);
    
    // 复用过滤逻辑自定义count查询，仅统计ID
    JPAQuery<Long> countQuery = query.clone(entityManager)
        .select(QNote.note.id.countDistinct());
    
    query.offset(page.getOffset())
         .limit(page.getPageSize());
    List<Note> results = query.fetch();
    long total = countQuery.fetchOne();
    return new PageImpl<>(results, page, total);
}

4 可选Hibernate配置优化 #

在项目配置文件中添加以下配置进一步优化SQL生成：

• spring.jpa.properties.hibernate.jpa.compliance.join=false：允许Hibernate优化关联路径，避免不必要的表关联
• spring.jpa.properties.hibernate.query.omit_join_for_superfluous_tables=true：自动省略查询中不需要的关联表

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