jOOQ查询如何生成任意子查询/连接 对比内连接与子查询性能

• 目标：将现有应用迁移至jOOQ，消除n+1查询问题，保证自定义查询的类型安全，使用的数据库为PostgreSQL 13
• 表结构：
  • document表：存储文档基础信息，字段包括id（主键）、file_name（文件名）、file_size（文件大小）
  • document_attribute表：存储文档自定义属性，以document_id为外键关联文档表，字段包括archive_attribute_id（属性类型ID）、value（属性值），每个文档可关联多条唯一属性记录
• 示例数据：
  文档表数据：

acme=> select id, file_name, file_size from document;
                  id                  |        file_name        | file_size 
--------------------------------------+-------------------------+-----------
 1ae56478-d27c-4b68-b6c0-a8bdf36dd341 | My Really cool book.pdf |     13264
(1 row)

文档属性表数据：

acme=> select * from document_attribute ;
             document_id              |         archive_attribute_id         |   value    
--------------------------------------+--------------------------------------+------------
 1ae56478-d27c-4b68-b6c0-a8bdf36dd341 | b334e287-887f-4173-956d-c068edc881f8 | JustReleased
 1ae56478-d27c-4b68-b6c0-a8bdf36dd341 | 2f86a675-4cb2-4609-8e77-c2063ab155f1 | Tax
 1ae56478-d27c-4b68-b6c0-a8bdf36dd341 | 30bb9696-fc18-4c87-b6bd-5e01497ca431 | ShippingRequired
 1ae56478-d27c-4b68-b6c0-a8bdf36dd341 | 2eb04674-1dcb-4fbc-93c3-73491deb7de2 | Bestseller
 1ae56478-d27c-4b68-b6c0-a8bdf36dd341 | a8e2f902-bf04-42e8-8ac9-94cdbf4b6778 | Paperback
(5 rows)

• 原有查询逻辑：通过JDBC拼接SQL，支持按文档基础字段、任意数量的属性组合筛选文档，匹配到文档ID后再拉取全量属性，存在n+1问题。原有匹配多属性的SQL示例如下：

SELECT d.id FROM document d WHERE d.id = '1ae56478-d27c-4b68-b6c0-a8bdf36dd341'
AND d.id IN(SELECT da.document_id AS id0 FROM document_attribute da WHERE da.archive_attribute_id = '2eb04674-1dcb-4fbc-93c3-73491deb7de2' AND da.value = 'Bestseller')
AND d.id IN(SELECT da.document_id AS id1 FROM document_attribute da WHERE da.archive_attribute_id = 'a8e2f902-bf04-42e8-8ac9-94cdbf4b6778' AND da.value = 'Paperback');

• 需求：所有筛选条件（文档基础字段、属性条件）均为可选，支持任意组合查询，使用jOOQ的multiset特性一次性查出文档及关联属性，消除n+1。

• 如何在jOOQ中动态添加任意数量的属性匹配子查询条件？
• 该业务场景下，使用INNER JOIN实现属性匹配的性能是否优于子查询？

动态子查询实现 #

不需要拆分多个step变量，直接复用已有的Condition对象，循环遍历传入的属性参数，为每个属性拼接对应的IN子查询条件即可。jOOQ的查询构造步骤在调用fetch前可以持续拼接条件，逻辑和拼接基础字段条件的方式完全一致。

修正后的searchDocuments方法核心逻辑如下：

private List<CustomDocument> searchDocuments(UUID documentId, String fileName, Integer fileSize, Map<UUID, String> attributes) {
    TransactionManager transactionManager = getBean(TransactionManager.class);
    DSLContext ctx = transactionManager.getDslContext();

    // 初始化基础条件
    Condition condition = DSL.noCondition();

    // 拼接可选的文档基础字段条件
    if (documentId != null) {
        condition = condition.and(DOCUMENT.ID.eq(documentId));
    }
    if (fileName != null) {
        condition = condition.and(DOCUMENT.FILE_NAME.eq(fileName));
    }
    if (fileSize != null) {
        condition = condition.and(DOCUMENT.FILE_SIZE.eq(fileSize));
    }

    // 动态拼接任意数量的属性匹配子查询
    for (Map.Entry<UUID, String> attrEntry : attributes.entrySet()) {
        UUID attrId = attrEntry.getKey();
        String attrValue = attrEntry.getValue();
        condition = condition.and(
            DOCUMENT.ID.in(
                select(DOCUMENT_ATTRIBUTE.DOCUMENT_ID)
                .from(DOCUMENT_ATTRIBUTE)
                .where(
                    DOCUMENT_ATTRIBUTE.ARCHIVE_ATTRIBUTE_ID.eq(attrId),
                    DOCUMENT_ATTRIBUTE.VALUE.eq(attrValue)
                )
            )
        );
    }

    // 构造带multiset的最终查询，一次性查出文档及关联属性
    return ctx.select(
        DOCUMENT.ID,
        DOCUMENT.FILE_NAME,
        DOCUMENT.FILE_SIZE,
        multiset(
            selectDistinct(
                DOCUMENT_ATTRIBUTE.DOCUMENT_ID,
                DOCUMENT_ATTRIBUTE.ARCHIVE_ATTRIBUTE_ID,
                DOCUMENT_ATTRIBUTE.VALUE
            )
            .from(DOCUMENT_ATTRIBUTE)
            .where(DOCUMENT_ATTRIBUTE.DOCUMENT_ID.eq(DOCUMENT.ID))
        ).convertFrom(record -> record.map(
            r -> new CustomDocumentAttribute(r.value1(), r.value2(), r.value3())
        ))
    )
    .from(DOCUMENT)
    .where(condition)
    .fetch(r -> new CustomDocument(r.value1(), r.value2(), r.value3(), r.value4()));
}

该实现完全符合需求：传入匹配的属性参数时返回1条结果，传入不匹配的属性参数时返回0条结果，且所有条件均可选，通过multiset一次性加载关联属性，彻底消除n+1问题。

性能对比结论 #

在PostgreSQL 13环境下，两种写法性能没有本质差异：

• PostgreSQL的查询优化器会自动对语义等价的IN子查询和INNER JOIN做执行计划改写，最终生成的执行计划几乎完全一致，不会出现子查询性能更差的情况。
• 不建议改写为INNER JOIN实现：每增加一个属性条件就需要多关联一次document_attribute表，不仅动态SQL拼接更复杂，还容易因为多表关联产生重复的文档行，需要额外加DISTINCT去重，反而增加不必要的开销。
• 如果后续属性筛选条件数量极多，也可以替换为GROUP BY DOCUMENT.ID HAVING count(CASE WHEN (属性条件) THEN 1 END) = 匹配属性数量的写法，性能和IN子查询基本持平，但IN子查询的写法最贴合原有业务逻辑，维护成本最低。

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