jOOQ MULTISET性能逊于JOIN？求问题排查与优化建议

在使用jOOQ的MULTISET特性时，发现其性能不如传统JOIN查询，即使JOIN返回了大量重复数据。基于Sakila数据库，我执行了以下两个查询：

JOIN 查询代码 #

val result: Result<Record3<CustomerId, Int, Int>> = dslContext
.select(
    CUSTOMER.CUSTOMER_ID,
    PAYMENT.PAYMENT_ID,
    RENTAL.RENTAL_ID,
)
.from(CUSTOMER)
.join(PAYMENT).on(PAYMENT.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
.join(RENTAL).on(RENTAL.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
.orderBy(CUSTOMER.EMAIL)
.fetch()

MULTISET 查询代码 #

val result: Result<Record3<CustomerId, Result<Record1<Int>>, Result<Record1<Int>>>> = dslContext
.select(
    CUSTOMER.CUSTOMER_ID,
    multiset(
        DSL.select(PAYMENT.PAYMENT_ID)
           .from(PAYMENT)
           .where(PAYMENT.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
    ),
    multiset(
        DSL.select(RENTAL.RENTAL_ID)
           .from(RENTAL)
           .where(RENTAL.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
    ),
)
.from(CUSTOMER)
.fetch()

性能测试结果 #

445483 Records via JOIN.    in 466.389250ms
599    Records via MULTISET in 747.627541ms

即便JOIN因重复传输Customer数据产生额外开销，MULTISET的性能优势仍不明显，请问忽略了哪些关键因素？

关键因素分析 #

1. 序列化/反序列化开销 #

jOOQ的MULTISET默认依赖SQL/JSON或SQL/XML实现嵌套结果：数据库需要将嵌套查询的结果序列化为JSON/XML格式，jOOQ再将其反序列化为Result对象。这两层序列化操作是JOIN查询没有的额外开销，当嵌套数据集较大时，该开销会显著累加，甚至超过JOIN重复传输数据的成本。

2. 底层查询执行逻辑差异 #

• 数据库原生支持情况：若你的数据库版本不支持原生JSON聚合（如MySQL < 8.0、PostgreSQL < 12），jOOQ会退化为客户端层面的"N+1查询"——先查询所有Customer，再逐个发起PAYMENT和RENTAL的子查询，这种模式性能必然远低于批量JOIN。
• 执行计划差异：JOIN查询的优化器可以选择哈希连接、嵌套循环等高效批量关联策略，而MULTISET的JSON聚合逻辑（如json_agg）需要先分组再序列化，若没有合适索引支持，聚合阶段的耗时会高于JOIN。

3. 索引与查询覆盖度 #

JOIN查询能更好地利用PAYMENT.CUSTOMER_ID、RENTAL.CUSTOMER_ID上的索引，优化器可生成更高效的执行计划。而MULTISET的嵌套查询若未配置覆盖索引（如包含PAYMENT_ID和CUSTOMER_ID的复合索引），数据库需要回表查询，进一步增加耗时。

4. 数据传输的实际开销对比 #

JOIN返回的44万条记录虽包含重复的Customer数据，但均为原始列值，传输成本是线性的字节数。而MULTISET返回的599条记录包含嵌套JSON数组，序列化后的JSON结构本身带有额外的语法开销（如括号、逗号），加上反序列化时的对象创建成本，总开销可能超过重复传输CustomerId的成本。

5. jOOQ配置与对象转换开销 #

若使用默认的JSON解析器（如Jackson）且未做优化，或返回的Result<Record1<Int>>包含不必要的元数据，会增加对象创建和转换的耗时。相比之下，JOIN返回的扁平记录转换成本更低。

优化建议 #

1. 验证数据库原生支持：确保数据库版本支持原生JSON聚合（如PostgreSQL 12+用jsonb_agg、MySQL 8.0+用json_arrayagg），jOOQ会自动生成最优SQL，避免客户端N+1查询。
2. 优化索引配置：为PAYMENT和RENTAL表创建包含关联字段和查询字段的覆盖索引，例如：

   CREATE INDEX idx_payment_customer_id ON payment(customer_id, payment_id);
   CREATE INDEX idx_rental_customer_id ON rental(customer_id, rental_id);
   

3. 对比底层SQL耗时：打印jOOQ生成的SQL，直接在数据库中执行，区分是数据库层面的查询耗时问题，还是jOOQ客户端的反序列化耗时问题。
4. 使用MULTISET_AGG替代子查询：对于聚合场景，multisetAgg比嵌套SELECT更高效，示例：

   select(
       CUSTOMER.CUSTOMER_ID,
       multisetAgg(PAYMENT.PAYMENT_ID),
       multisetAgg(RENTAL.RENTAL_ID)
   ).from(CUSTOMER)
   .leftJoin(PAYMENT).on(PAYMENT.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
   .leftJoin(RENTAL).on(RENTAL.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
   .groupBy(CUSTOMER.CUSTOMER_ID)
   .fetch()
   

5. 简化返回类型：若不需要Result<Record1<Int>>的元数据，可将返回类型改为List<Int>，减少对象转换开销，例如：

   multiset(...).convertFrom { it.map(Record1::value1) }
   

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