各种多对多嵌套算法的性能

自从jOOQ 3.15发布其革命性的标准SQLMULTISET 仿真功能以来，已经有一段时间了。我曾在twitter上承诺过一件事，那就是用jOOQ运行一些基准测试，比较各种嵌套多对多关系的方法的性能。

这篇文章将显示，在一些真实世界的场景中，在适度的数据集规模上，jOOQ的MULTISET 仿真的性能和以下的性能差不多

• 手动运行单个连接查询并手动重复计算结果
• 手动运行每个巢层的多个查询并在客户端匹配结果。

相比之下，上述所有方法都比可怕的N+1 "方法"（或者说是意外）要好得多，同时可读性和可维护性都更强。

结论是。

• 对于jOOQ用户来说，只要使用小的数据集，就可以自由使用MULTISET （即嵌套的循环连接也可以）。
• 对于jOOQ用户来说，在使用大的数据集时要谨慎使用MULTISET （例如，哈希连接或合并连接会更好，例如在报告中）。
• 对于ORM供应商来说，如果他们能完全控制他们的SQL来实现预定义的对象图，他们会更喜欢每个巢层的多次查询方法。

基准测试的想法

像往常一样，我们正在查询著名的Sakila数据库。在这个基准中，有两种类型的查询我已经测试过了。

一个对子集合进行双重嵌套的查询（DN = DoubleNesting）。

其结果将是这样的形式。

record DNCategory (String name) {}
record DNFilm (long id, String title, List<DNCategory> categories) {}
record DNName (String firstName, String lastName) {}
record DNActor (long id, DNName name, List<DNFilm> films) {}

所以，结果将是演员和他们的电影以及他们每部电影的类别。如果执行的是单连接，这应该会在数据中造成大量的重复（尽管遗憾的是，在我们的测试数据集中，每部电影只有一个类别）。

一个将两个子集合嵌套在一个父集合中的查询（MCC = Multiple Child Collections）。

结果将是这样的。

record MCCName (String firstName, String lastName) {}
record MCCActor (long id, MCCName name) {}
record MCCCategory (String name) {}
record MCCFilm (
    long id, 
    String title, 
    List<MCCActor> actors, 
    List<MCCCategory> categories
) {}

所以，结果将是电影和它们的演员以及它们的类别。这很难用单一的连接来重复，因为ACTOR × CATEGORY 之间的笛卡尔积。但是其他带有多个查询的方法仍然有效，以及MULTISET ，当然，这将是最方便的选择

数据集大小

除了上述用例的区分，该基准还将尝试拉入任一。

• 整个数据集（我们有1000个FILM 条目以及200个ACTOR 条目，所以不是一个巨大的数据集），其中散列连接往往更好一些
• 只有ACTOR_ID = 1 或FILM_ID = 1 的子集，其中嵌套循环连接往往更好。

这里的期望是，JOIN 倾向于在较大的结果集上表现得更好，因为RDBMS会倾向于使用散列连接算法。MULTISET 模拟不太可能转化为散列连接或合并连接，因为它使用的是JSON_ARRAYAGG ，可能很难转化为不同的东西，而这仍然是等价的。

基准测试

将对上述矩阵的每个组合进行以下基准测试。

1. 一个单一的MULTISET 查询及其3种可用的模拟，使用XML （如有），JSON 。JSONB
2. 一个单一的JOIN 查询，在父系和子系之间创建一个笛卡尔积
3. 一种方法是运行2个查询，将所有必要的数据取到客户端内存中，然后在客户端中执行嵌套。
4. 一个天真的N+1 "客户端嵌套循环连接"，这很糟糕，但在现实世界的客户端代码中并非不可能发生，要么用jOOQ（不太可能，但仍有可能），要么用懒惰加载ORM（更可能，因为 "意外"）。

完整的基准逻辑将在本文末尾公布。

1.单一MULTISET查询(DN)

该查询看起来像这样。

return state.ctx.select(
    ACTOR.ACTOR_ID,

    // Nested record for the actor name
    row(
        ACTOR.FIRST_NAME,
        ACTOR.LAST_NAME
    ).mapping(DNName::new),

    // First level nested collection for films per actor
    multiset(
        select(
            FILM_ACTOR.FILM_ID,
            FILM_ACTOR.film().TITLE,

            // Second level nested collection for categories per film
            multiset(
                select(FILM_CATEGORY.category().NAME)
                .from(FILM_CATEGORY)
                .where(FILM_CATEGORY.FILM_ID.eq(FILM_ACTOR.FILM_ID))
            ).convertFrom(r -> r.map(mapping(DNCategory::new)))
        )
        .from(FILM_ACTOR)
        .where(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID))
    ).convertFrom(r -> r.map(mapping(DNFilm::new))))
.from(ACTOR)

// Either fetch all data or filter ACTOR_ID = 1
.where(state.filter ? ACTOR.ACTOR_ID.eq(1L) : noCondition())
.fetch(mapping(DNActor::new));

要了解更多关于具体的MULTISET 语法和临时转换功能的信息，请参考先前解释细节的博文。隐式JOIN 功能也是如此，我将在这篇文章中使用该功能，以使SQL更简明。

2.单一 JOIN 查询 (DN)

我们也可以用一个单连接来做所有的事情。在这个例子中，我使用了一种函数式风格，以一种类型安全的方式将平面结果转化为双重嵌套的集合。这有点古怪，也许有更好的方法可以用非JDK的API来做这个。由于我不希望这与性能有关，我认为这已经很好了。

// The query is straightforward. Just join everything from
// ACTOR -> FILM -> CATEGORY via the relationship tables
return state.ctx.select(
    FILM_ACTOR.ACTOR_ID,
    FILM_ACTOR.actor().FIRST_NAME,
    FILM_ACTOR.actor().LAST_NAME,
    FILM_ACTOR.FILM_ID,
    FILM_ACTOR.film().TITLE,
    FILM_CATEGORY.category().NAME)
.from(FILM_ACTOR)
.join(FILM_CATEGORY).on(FILM_ACTOR.FILM_ID.eq(FILM_CATEGORY.FILM_ID))
.where(state.filter ? FILM_ACTOR.ACTOR_ID.eq(1L) : noCondition())

// Now comes the tricky part. We first use JDK Collectors to group
// results by ACTOR
.collect(groupingBy(
    r -> new DNActor(
        r.value1(), 
        new DNName(r.value2(), r.value3()), 

        // dummy FILM list, we can't easily collect them here, yet
        null 
    ),

    // For each actor, produce a list of FILM, again with a dummy
    // CATEGORY list as we can't collect them here yet
    groupingBy(r -> new DNFilm(r.value4(), r.value5(), null))
))

// Set<Entry<DNActor, Map<DNFilm, List<Record6<...>>>>> 
.entrySet()
.stream()

// Re-map the DNActor record into itself, but this time, add the
// nested DNFilm list.
.map(a -> new DNActor(
    a.getKey().id(),
    a.getKey().name(),
    a.getValue()
     .entrySet()
     .stream()
     .map(f -> new DNFilm(
         f.getKey().id(),
         f.getKey().title(),
         f.getValue().stream().map(
             c -> new DNCategory(c.value6())
         ).toList()
     ))
     .toList()
))
.toList();

也许，这个例子可以改进，以避免在第一个collect() 调用中出现虚拟集合占位符，尽管这可能需要额外的记录类型或结构元组类型，如jOOλ中的那些。在这个例子中，我保持了 "简单"，不过我会在评论中接受你的建议。

3.在内存中合并的两个查询（DN）

一个完美的解决方案是运行多个查询（但不是N+1 查询！），即每层嵌套一个查询。这并不总是可能的，也不是最优的，但在这种情况下，有一个合理的解决方案。

我在这篇博文中拼出了冗长的Record5<...> 类型，以显示确切的类型。当然，你可以使用var ，从类型推理中获利。所有这些查询都使用Record.value5() 和类似的访问器来从基于索引的访问中获利，只是为了公平起见，防止字段查找，这在基准中是没有必要的。

// Straightforward query to get ACTORs and their FILMs
Result<Record5<Long, String, String, Long, String>> actorAndFilms =
state.ctx
    .select(
        FILM_ACTOR.ACTOR_ID,
        FILM_ACTOR.actor().FIRST_NAME,
        FILM_ACTOR.actor().LAST_NAME,
        FILM_ACTOR.FILM_ID,
        FILM_ACTOR.film().TITLE)
    .from(FILM_ACTOR)
    .where(state.filter ? FILM_ACTOR.ACTOR_ID.eq(1L) : noCondition())
    .fetch();

// Create a FILM.FILM_ID => CATEGORY.NAME lookup
// This is just fetching all the films and their categories.
// Optionally, filter for the previous FILM_ID list
Map<Long, List<DNCategory>> categoriesPerFilm = state.ctx
    .select(
        FILM_CATEGORY.FILM_ID,
        FILM_CATEGORY.category().NAME)
    .from(FILM_CATEGORY)
    .where(state.filter
        ? FILM_CATEGORY.FILM_ID.in(actorAndFilms.map(r -> r.value4()))
        : noCondition())
    .collect(intoGroups(
        r -> r.value1(),
        r -> new DNCategory(r.value2())
    ));

// Group again by ACTOR and FILM, using the previous dummy
// collection trick
return actorAndFilms
    .collect(groupingBy(
        r -> new DNActor(
            r.value1(), 
            new DNName(r.value2(), r.value3()), 
            null
        ),
        groupingBy(r -> new DNFilm(r.value4(), r.value5(), null))
    ))
    .entrySet()
    .stream()

    // Then replace the dummy collections
    .map(a -> new DNActor(
        a.getKey().id(),
        a.getKey().name(),
        a.getValue()
         .entrySet()
         .stream()
         .map(f -> new DNFilm(
             f.getKey().id(),
             f.getKey().title(),

             // And use the CATEGORY per FILM lookup
             categoriesPerFilm.get(f.getKey().id())
         ))
         .toList()
    ))
    .toList();

呜呜。笨重的。当然，从可读性的角度来看，MULTISET 的方法是首选？所有这些对中间结构数据类型的映射可能是很重的，特别是如果你犯了一个错，编译器就会跳出来。

4.N+1查询（DN）

这个天真的解决方案希望不是你在生产中主要做的事情，但我们都在某些时候做过（是的，有罪！），所以在这里。至少，这个逻辑比前面的更易读，它和原来的MULTISET 例子一样简单明了，事实上，因为它做的事情和MULTISET 例子几乎一样，但它不是用SQL做所有事情，而是在客户端关联子查询。

// Fetch all ACTORs
return state.ctx
    .select(ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
    .from(ACTOR)
    .where(state.filter ? ACTOR.ACTOR_ID.eq(1L) : noCondition())
    .fetch(a -> new DNActor(
        a.value1(),
        new DNName(a.value2(), a.value3()),

        // And for each ACTOR, fetch all FILMs
        state.ctx
            .select(FILM_ACTOR.FILM_ID, FILM_ACTOR.film().TITLE)
            .from(FILM_ACTOR)
            .where(FILM_ACTOR.ACTOR_ID.eq(a.value1()))
            .fetch(f -> new DNFilm(
                f.value1(),
                f.value2(),

                // And for each FILM, fetch all CATEGORY-s
                state.ctx
                    .select(FILM_CATEGORY.category().NAME)
                    .from(FILM_CATEGORY)
                    .where(FILM_CATEGORY.FILM_ID.eq(f.value1()))
                    .fetch(r -> new DNCategory(r.value1()))
            ))
    ));

1.单一MULTISET查询(MCC)

现在，我们再重复一次练习，将数据收集到一个更像树状的数据结构中，其中父类型有多个子集合，这一点在JOIN 查询中更难做到。用MULTISET 小菜一碟，它可以直接在SQL中嵌套集合。

return state.ctx
    .select(
        FILM.FILM_ID,
        FILM.TITLE,

        // Get all ACTORs for each FILM
        multiset(
            select(
                FILM_ACTOR.ACTOR_ID,
                row(
                    FILM_ACTOR.actor().FIRST_NAME,
                    FILM_ACTOR.actor().LAST_NAME
                ).mapping(MCCName::new)
            )
            .from(FILM_ACTOR)
            .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
        ).convertFrom(r -> r.map(mapping(MCCActor::new))),

        // Get all CATEGORY-s for each FILM
        multiset(
            select(FILM_CATEGORY.category().NAME)
            .from(FILM_CATEGORY)
            .where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
        ).convertFrom(r -> r.map(mapping(MCCCategory::new))))
    .from(FILM)
    .where(state.filter ? FILM.FILM_ID.eq(1L) : noCondition())
    .fetch(mapping(MCCFilm::new));

同样，要了解更多关于具体的MULTISET 语法和临时转换功能，请参考早期的博文解释细节。隐式JOIN 功能也是如此，我将在这篇文章中使用它来保持SQL的简洁性。

2.单一JOIN查询(MCC)

这种类型的嵌套在单个JOIN 查询中很难做到，因为在ACTOR 和CATEGORY 之间会有一个笛卡尔乘积，这可能很难在事后扣除。在这种情况下，这将是可能的，因为我们知道每个ACTOR ，每个FILM ，每个CATEGORY ，都只列出一次。但是，如果情况不是这样呢？可能不可能正确地删除重复的内容，因为我们无法区分。

• 源自JOIN 的卡特尔乘积的重复。
• 源自底层数据集的重复数据

由于很难（可能不是不可能）保证正确性，在这里测试性能是徒劳的。

3.在内存中合并的两个查询（MCC）

这也是使用普通的JOIN 查询来实现这种嵌套的相当合理的方法。

这可能是大多数尚未支持MULTISET ，如集合嵌套的ORM的做法。当ORM完全控制生成的查询时，使用这种方法是完全合理的（例如，在获取预定义的对象图时）。但是当允许自定义查询时，这种方法对于复杂的查询来说就不好用了。例如，JPQL的JOIN FETCH 语法可能在幕后使用这种方法，但这使得JPQL无法支持非复杂的查询，其中JOIN FETCH 在派生表或相关的子查询中使用，并且本身连接派生表，等等。如果我错了，请纠正我，但我认为这似乎是非常难做到的，要把复杂的嵌套查询转化为多个单独的查询，一个接一个地执行，然后才重新组合结果。

在任何情况下，这种方法对于控制其SQL的ORM来说是很好的，但对于最终用户来说，手动执行是很费力的。

// Straightforward query to get ACTORs and their FILMs
Result<Record5<Long, String, Long, String, String>> filmsAndActors = 
state.ctx
    .select(
        FILM_ACTOR.FILM_ID,
        FILM_ACTOR.film().TITLE,
        FILM_ACTOR.ACTOR_ID,
        FILM_ACTOR.actor().FIRST_NAME,
        FILM_ACTOR.actor().LAST_NAME)
    .from(FILM_ACTOR)
    .where(state.filter ? FILM_ACTOR.FILM_ID.eq(1L) : noCondition())
    .fetch();

// Create a FILM.FILM_ID => CATEGORY.NAME lookup
// This is just fetching all the films and their categories.
Map<Long, List<MCCCategory>> categoriesPerFilm = state.ctx
    .select(
        FILM_CATEGORY.FILM_ID,
        FILM_CATEGORY.category().NAME)
    .from(FILM_CATEGORY)
    .where(FILM_CATEGORY.FILM_ID.in(
        filmsAndActors.map(r -> r.value1())
    ))
    .and(state.filter ? FILM_CATEGORY.FILM_ID.eq(1L) : noCondition())
    .collect(intoGroups(
        r -> r.value1(),
        r -> new MCCCategory(r.value2())
    ));

// Group again by ACTOR and FILM, using the previous dummy
// collection trick
return filmsAndActors
    .collect(groupingBy(
        r -> new MCCFilm(r.value1(), r.value2(), null, null),
        groupingBy(r -> new MCCActor(
            r.value3(), 
            new MCCName(r.value4(), r.value5())
        ))
    ))
    .entrySet()
    .stream()

    // This time, the nesting of CATEGORY-s is simpler because
    // we don't have to nest them again deeply
    .map(f -> new MCCFilm(
        f.getKey().id(),
        f.getKey().title(),
        new ArrayList<>(f.getValue().keySet()),
        categoriesPerFilm.get(f.getKey().id())
    ))
    .toList();

正如你所看到的，手动完成所有这些分组和嵌套，确保所有的中间结构类型是正确的，感觉还是很麻烦的，但至少MCC 的情况比之前的DN 的情况要简单一些，因为嵌套的深度比较小。

但是我们都知道，我们最终会把这些方法结合起来，嵌套出任意复杂的树状结构。

4.N+1查询（MCC）

还是那句话，不要在家里（或在生产中）这样做，但我们都经历过，下面是很多应用程序的做法，要么是显式的（为作者感到羞耻！），要么是隐式的（为ORM允许作者把羞耻的事情说出来而感到羞耻！)

// Fetch all FILMs
return state.ctx
    .select(FILM.FILM_ID, FILM.TITLE)
    .from(FILM)
    .where(state.filter ? FILM.FILM_ID.eq(1L) : noCondition())
    .fetch(f -> new MCCFilm(
        f.value1(),
        f.value2(),

        // For each FILM, fetch all ACTORs
        state.ctx
            .select(
                FILM_ACTOR.ACTOR_ID,
                FILM_ACTOR.actor().FIRST_NAME,
                FILM_ACTOR.actor().LAST_NAME)
            .from(FILM_ACTOR)
            .where(FILM_ACTOR.FILM_ID.eq(f.value1()))
            .fetch(a -> new MCCActor(
                a.value1(),
                new MCCName(a.value2(), a.value3())
            )),

        // For each FILM, fetch also all CATEGORY-s
        state.ctx
            .select(FILM_CATEGORY.category().NAME)
            .from(FILM_CATEGORY)
            .where(FILM_CATEGORY.FILM_ID.eq(f.value1()))
            .fetch(c -> new MCCCategory(c.value1()))
    ));

算法的复杂性

在我们继续讨论基准测试结果之前，请一如既往地对你的解释非常小心。

这个基准的目标并不是要找到一个明显的赢家（或让一个明显的输家感到羞耻）。这个基准的目标是检查MULTISET 方法是否比其他更多手动和不方便的方法有任何明显的好处和/或缺点。

不要得出结论说，如果某样东西比其他东西快1.5倍或3倍，那它就是更好的。在这种情况下可能是这样，但在不同的情况下可能不是这样，比如说

• 当数据集较小时
• 当数据集较大时
• 当数据集的分布不同时（例如，每部电影有更多的类别，或者每个演员的电影数量不太固定（sakila数据集的生成相当均匀））。
• 当更换供应商时
• 当转换版本时
• 当你在系统上有更多的负载时
• 当你的查询更加多样化时（基准往往只运行单一的查询，这从数据库服务器的缓存中大大受益！）。

所以，还是那句话，和每一个基准结果一样，对你的解释要非常小心。

N+1的情况

即使是N+1的情况，也可能变成可怕的事情，并不总是 错误的选择。

正如我们从Big O Notation中知道的，只有当N ，而不是当它很小的时候，才会出现算法复杂度不好的问题。

• 一个单一的嵌套集合的算法复杂度是O(N * log M) ，即N 次在索引中查找M 值（假设有一个索引）。
• 然而，一个双重嵌套的集合的算法复杂度要差得多，它是O(N * log M * ? * log L) ，即N 次，在索引中为M ，然后? 次（取决于分布），在索引中为L 。

最好希望所有这些值都非常小。如果它们是，你就没事了。如果不是这样，你会在周末的生产中注意到。

MULTISET案例

虽然我一直主张将MULTISET 作为圣杯，因为它是如此强大、方便、类型安全和合理的性能，但正如我们接下来要看到的，它并不是像我们曾经希望的其他所有东西一样的圣杯。

虽然理论上有可能在MULTISET 的情况下实现一些哈希连接式的嵌套集合算法，但我怀疑目前使用XMLAGG,JSON_ARRAYAGG 或类似结构的模拟不会被这样优化，因此，我们会得到相关的子查询，这基本上是N+1，但100%是在服务器端。

随着越来越多的人使用SQL/JSON功能，这些功能在未来可能会被进一步优化。我不会对RDBMS厂商投入时间来改进SQL/XML抱有太大的希望（很遗憾）。

我们可以通过对jOOQ生成的双重嵌套集合情况下的查询运行EXPLAIN （在PostgreSQL上）来验证执行计划。

explain select
  actor.actor_id,
  row (actor.first_name, actor.last_name),
  (
    select coalesce(
      json_agg(json_build_array(v0, v1, v2)),
      json_build_array()
    )
    from (
      select
        film_actor.film_id as v0,
        alias_75379701.title as v1,
        (
          select coalesce(
            json_agg(json_build_array(v0)),
            json_build_array()
          )
          from (
            select alias_130639425.name as v0
            from (
              film_category
                join category as alias_130639425
                  on film_category.category_id = 
                    alias_130639425.category_id
              )
            where film_category.film_id = film_actor.film_id
          ) as t
        ) as v2
      from (
        film_actor
          join film as alias_75379701
            on film_actor.film_id = alias_75379701.film_id
        )
      where film_actor.actor_id = actor.actor_id
    ) as t
  )
from actor
where actor.actor_id = 1

其结果是。

QUERY PLAN                                                                                                                   
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on actor  (cost=0.00..335.91 rows=1 width=72)                                                                       
  Filter: (actor_id = 1)                                                                                                     
  SubPlan 2                                                                                                                  
    ->  Aggregate  (cost=331.40..331.41 rows=1 width=32)                                                                     
          ->  Hash Join  (cost=5.09..73.73 rows=27 width=23)                                                                 
                Hash Cond: (alias_75379701.film_id = film_actor.film_id)                                                     
                ->  Seq Scan on film alias_75379701  (cost=0.00..66.00 rows=1000 width=23)                                   
                ->  Hash  (cost=4.75..4.75 rows=27 width=8)                                                                  
                      ->  Index Only Scan using film_actor_pkey on film_actor  (cost=0.28..4.75 rows=27 width=8)             
                            Index Cond: (actor_id = actor.actor_id)                                                          
          SubPlan 1                                                                                                          
            ->  Aggregate  (cost=9.53..9.54 rows=1 width=32)                                                                 
                  ->  Hash Join  (cost=8.30..9.52 rows=1 width=7)                                                            
                        Hash Cond: (alias_130639425.category_id = film_category.category_id)                                 
                        ->  Seq Scan on category alias_130639425  (cost=0.00..1.16 rows=16 width=15)                         
                        ->  Hash  (cost=8.29..8.29 rows=1 width=8)                                                           
                              ->  Index Only Scan using film_category_pkey on film_category  (cost=0.28..8.29 rows=1 width=8)
                                    Index Cond: (film_id = film_actor.film_id)                                               

正如预期，两个嵌套的标量子查询。不要被子查询中的哈希连接所迷惑。这些都是预料之中的，因为我们在子查询中连接了例如FILM 和FILM_ACTOR ，或者CATEGORY 和FILM_CATEGORY 。但是这并不影响这两个子查询与最外层查询之间的关联，在这里我们不能使用任何哈希连接。

因此，我们有一个N+1的情况，只是没有了每次运行服务器往返的延迟!算法的复杂性是一样的，但每项的恒定开销已经被移除，允许更大的N ，才会有伤害--但这种方法最终还是会失败，就像在不支持散列连接或合并连接，只支持嵌套循环连接的RDBMS中，在大数据集上有太多的JOIN ，效率很低（例如旧的MySQL版本）。

jOOQ的未来版本可能会在Oracle和PostgreSQL上更原生地支持MULTISET 。它已经在Informix中得到了原生支持，它有标准的SQLMULTISET 支持。在PostgreSQL中，可以使用ARRAY(<subquery>) 和ARRAY_AGG() ，这对优化器来说可能比JSON_AGG 更透明。如果是这样，我一定会再写一篇博文跟进。

单一JOIN查询案例

如果嵌套的集合不是太大（即没有太多重复的数据），我希望这种方法可以正常工作。一旦嵌套的集合变大，重复数据删除将承担相当大的成本，因为。

• 更多的冗余数据必须在服务器端产生（需要更多的内存和CPU）。
• 更多的冗余数据需要通过网络传输
• 更多的重复数据需要在客户端完成（需要更多的内存和CPU）。

总而言之，这种方法对于复杂的嵌套来说似乎很愚蠢，但对于单一的嵌套集合来说是可行的。这个基准并没有测试巨大的重复数据删除。

每巢层1个查询的情况

我预计，随着N 的扩展，非常不方便的每巢级1个查询的情况将是最有性能的。对于ORM来说，这也是比较简单的实现方式，因为ORM可以完全控制生成的SQL，并且不需要尊重任何用户的查询要求。如果把它混入用户查询语法中，效果就不好，而且很难每次都为用户手动完成。

然而，这是一种 "事后 "的集合嵌套方法，也就是说，只有在可以保持对原始查询的一些假设的情况下，它才能很好地工作。例如，JPQL中的JOIN FETCH ，只能带你到这里。这可能是一个很好的解决嵌套集合的方法，并使这个概念在简单的情况下可用，但我肯定JPA / JPQL会发展，也会采用基于MULTISET 的方法。毕竟，MULTISET 已经是ORDBMS的一个SQL标准了。

嵌套集合的长期解决方案只能是直接在SQL中嵌套，并使所有的逻辑可供优化器进行各种决策。

基准测试结果

终于有了一些结果!我在这4个RDBMS上运行了基准测试。

• MySQL
• 甲骨文
• PostgreSQL
• SQL服务器

我没有在Db2上运行，它还不能关联派生表，这是3.15-3.17的MULTISET 仿真中jOOQ中关联的MULTISET 子查询的基本功能（详见github.com/jOOQ/jOOQ/i…）。

像往常一样，由于商业RDBMS的基准结果不能公布，我不公布实际时间，只公布相对时间，其中最慢的执行是1，更快的执行是1的倍数。这样一来，RDBMS只能与自己比较，而不是相互比较。

MySQL。

Benchmark                                                        (filter)   Mode  Cnt     Score     Error   Units
MultisetVsJoinBenchmark.doubleNestingJoin                            true  thrpt    7   4413.48 ±  448.63   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                    true  thrpt    7   2524.96 ±  402.38   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                   true  thrpt    7   2738.62 ±  332.37   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingNPlusOneQueries                 true  thrpt    7    265.37 ±   42.98   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingTwoQueries                      true  thrpt    7   2256.38 ±  363.18   ops/time-unit
                                                                                                            
MultisetVsJoinBenchmark.doubleNestingJoin                           false  thrpt    7    266.27 ±   13.31   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                   false  thrpt    7     54.98 ±    2.25   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                  false  thrpt    7     54.05 ±    1.58   ops/time-unit

甲骨文。

Benchmark                                                        (filter)   Mode  Cnt     Score     Error   Units
MultisetVsJoinBenchmark.doubleNestingJoin                            true  thrpt    7    669.54 ±   28.35   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                    true  thrpt    7    419.13 ±   23.60   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                   true  thrpt    7    432.40 ±   17.76   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetXML                     true  thrpt    7    351.42 ±   18.70   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingNPlusOneQueries                 true  thrpt    7    251.73 ±   30.19   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingTwoQueries                      true  thrpt    7    548.80 ±  117.40   ops/time-unit
                                                                                                            
MultisetVsJoinBenchmark.doubleNestingJoin                           false  thrpt    7     15.59 ±    1.86   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                   false  thrpt    7      2.41 ±    0.07   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                  false  thrpt    7      2.40 ±    0.07   ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetXML                    false  thrpt    7      1.91 ±    0.06   ops/time-unit

PostgreSQL。

Benchmark                                                        (filter)   Mode  Cnt     Score     Error   Units
MultisetVsJoinBenchmark.doubleNestingJoin                            true   thrpt   7     4128.21 ± 398.82  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                    true   thrpt   7     3187.88 ± 409.30  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                   true   thrpt   7     3064.69 ± 154.75  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetXML                     true   thrpt   7     1973.44 ± 166.22  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingNPlusOneQueries                 true   thrpt   7      267.15 ±  34.01  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingTwoQueries                      true   thrpt   7     2081.03 ± 317.95  ops/time-unit
                                                                                                            
MultisetVsJoinBenchmark.doubleNestingJoin                           false   thrpt   7      275.95 ±   6.80  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                   false   thrpt   7       53.94 ±   1.06  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                  false   thrpt   7       45.00 ±   0.52  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetXML                    false   thrpt   7       25.11 ±   1.01  ops/time-unit

SQL Server。

Benchmark                                                        (filter)   Mode  Cnt    Score     Error  Units
MultisetVsJoinBenchmark.doubleNestingJoin                            true  thrpt    7  4081.85 ± 1029.84  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                    true  thrpt    7  1243.17 ±   84.24  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                   true  thrpt    7  1254.13 ±   56.94  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetXML                     true  thrpt    7  1077.23 ±   61.50  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingNPlusOneQueries                 true  thrpt    7   264.45 ±   16.12  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingTwoQueries                      true  thrpt    7  1608.92 ±  145.75  ops/time-unit
                                                                                             
MultisetVsJoinBenchmark.doubleNestingJoin                           false  thrpt    7   359.08 ±   20.88  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSON                   false  thrpt    7     8.41 ±    0.06  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetJSONB                  false  thrpt    7     8.32 ±    0.15  ops/time-unit
MultisetVsJoinBenchmark.doubleNestingMultisetXML                    false  thrpt    7     7.24 ±    0.08  ops/time-unit

基准结论

正如在大多数RDBMS中可以看到的那样。

• 所有的RDBMS都产生了类似的结果。
• N+1的增加的延迟几乎总是贡献了一个明显的性能惩罚。一个例外是我们有一个filter = true 和多个子集合，在这种情况下，父N 是1（duh），只实现了一个单一的巢层。
• MULTISET 在 ，并且有多个子集合的情况下，基于单次查询的 方法或每巢层1次查询的方法的表现甚至更好，可能是因为数据格式更紧凑。filter = true JOIN
• 基于XML ，MULTISET 的仿真总是最慢的，可能是因为它需要更多的格式化。MULTISET (在一个Oracle案例中，基于XML 的模拟甚至比普通的N+1方法还慢）。
• JSONB 在PostgreSQL中比 要慢一些，可能是因为 是一个纯粹的基于文本的格式，没有任何后期处理/清理。在PostgreSQL中，的优势不在于只进行投影查询，而在于存储、比较和其他操作。对于大多数用途， 可能更好。对于纯投影来说， 是更好的（jOOQ 3.17将使其成为 模拟的默认值）。JSON JSON JSONB JSONB JSON MULTISET
• 值得注意的是，jOOQ将记录序列化为JSON数组，而不是JSON对象，以避免传输重复的列名，并在解序列化数组时提供位置索引。
• 对于大型的数据集（其中filter = false ），MULTISET 相关子查询的N+1因素可能成为一个问题（由于算法复杂性的性质），因为它阻碍了使用更有效的散列连接。在这些情况下，基于单次查询JOIN 的方法或每巢级1次查询的方法比较好

简而言之。

• MULTISET 只要嵌套循环连接是最优的，就可以使用。
• 如果散列连接或合并连接更理想，那么单查询JOIN 方法或每巢1次查询的方法往往表现更好（尽管随着复杂性的增加，它们有自己的注意事项）

对于小数据集来说，在便利性和正确性方面的好处是绝对值得的。对于较大的数据集，继续使用JOIN 。一如既往，没有银弹。

本博文没有调查的事情

这篇博文没有调查的几件事，包括。

• 服务器中的序列化开销。普通的JDBCResultSet 倾向于从服务器和客户端之间的二进制网络协议中获益。有了JSON 或XML ，这种协议紧凑性的好处就消失了，而产生了系统的开销。这在多大程度上起到了作用，还没有进行调查。
• 在客户端也是如此，嵌套的JSON 或XML 文档需要被反序列化。虽然下面的VisualVM截图显示有一些 开销，但与执行时间相比，它并不显著。而且，与jOOQ在ResultSet 和jOOQ数据结构之间进行映射时产生的开销相比，它也没有明显的增加。我的意思是，很明显，如果你做得对的话，直接使用JDBC会更快，但这样你就去掉了jOOQ创造的所有便利。

比较反序列化结果（4.7%）与执行查询（92%）所需的时间

基准代码

最后，如果你想复制这个基准，或者根据你自己的需要进行调整，这里有代码。

我使用了JMH来做这个基准。虽然这显然不是一个 "微型基准"，但我喜欢JMH的基准测试方法，包括。

• 易于配置
• 通过做热身迭代来消除热身惩罚
• 收集统计数据以处理离群效应

很明显，所有的版本都使用jOOQ进行查询构建、执行、映射，以达到公平和有意义的结果。在非MULTISET 方法中直接使用JDBC也是可能的，但这不是一个公平的概念比较。

该基准假设有一个SAKILA 数据库实例，以及生成的代码，类似于这个jOOQ演示。

package org.jooq.test.benchmarks.local;

import static java.util.stream.Collectors.groupingBy;
import static org.jooq.Records.intoGroups;
import static org.jooq.Records.mapping;
import static org.jooq.example.db.postgres.Tables.*;
import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.noCondition;
import static org.jooq.impl.DSL.row;
import static org.jooq.impl.DSL.select;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.function.Consumer;

import org.jooq.DSLContext;
import org.jooq.Record5;
import org.jooq.Result;
import org.jooq.conf.NestedCollectionEmulation;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.Fork;
import org.openjdk.jmh.annotations.Level;
import org.openjdk.jmh.annotations.Measurement;
import org.openjdk.jmh.annotations.Param;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.Setup;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.annotations.TearDown;
import org.openjdk.jmh.annotations.Warmup;

@Fork(value = 1)
@Warmup(iterations = 3, time = 3)
@Measurement(iterations = 7, time = 3)
public class MultisetVsJoinBenchmark {

    @State(Scope.Benchmark)
    public static class BenchmarkState {

        Connection connection;
        DSLContext ctx;

        @Param({ "true", "false" })
        boolean    filter;

        @Setup(Level.Trial)
        public void setup() throws Exception {
            try (InputStream is = BenchmarkState.class.getResourceAsStream("/config.mysql.properties")) {
                Properties p = new Properties();
                p.load(is);
                Class.forName(p.getProperty("db.mysql.driver"));
                connection = DriverManager.getConnection(
                    p.getProperty("db.mysql.url"),
                    p.getProperty("db.mysql.username"),
                    p.getProperty("db.mysql.password")
                );
            }

            ctx = DSL.using(connection, new Settings()
                .withExecuteLogging(false)
                .withRenderSchema(false));
        }

        @TearDown(Level.Trial)
        public void teardown() throws Exception {
            connection.close();
        }
    }

    record DNName(String firstName, String lastName) {}
    record DNCategory(String name) {}
    record DNFilm(long id, String title, List<DNCategory> categories) {}
    record DNActor(long id, DNName name, List<DNFilm> films) {}

    @Benchmark
    public List<DNActor> doubleNestingMultisetXML(BenchmarkState state) {
        state.ctx.settings().setEmulateMultiset(NestedCollectionEmulation.XML);
        return doubleNestingMultiset0(state);
    }

    @Benchmark
    public List<DNActor> doubleNestingMultisetJSON(BenchmarkState state) {
        state.ctx.settings().setEmulateMultiset(NestedCollectionEmulation.JSON);
        return doubleNestingMultiset0(state);
    }

    @Benchmark
    public List<DNActor> doubleNestingMultisetJSONB(BenchmarkState state) {
        state.ctx.settings().setEmulateMultiset(NestedCollectionEmulation.JSONB);
        return doubleNestingMultiset0(state);
    }

    private List<DNActor> doubleNestingMultiset0(BenchmarkState state) {
        return state.ctx
            .select(
                ACTOR.ACTOR_ID,
                row(
                    ACTOR.FIRST_NAME,
                    ACTOR.LAST_NAME
                ).mapping(DNName::new),
                multiset(
                    select(
                        FILM_ACTOR.FILM_ID,
                        FILM_ACTOR.film().TITLE,
                        multiset(
                            select(FILM_CATEGORY.category().NAME)
                            .from(FILM_CATEGORY)
                            .where(FILM_CATEGORY.FILM_ID.eq(FILM_ACTOR.FILM_ID))
                        ).convertFrom(r -> r.map(mapping(DNCategory::new)))
                    )
                    .from(FILM_ACTOR)
                    .where(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID))
                ).convertFrom(r -> r.map(mapping(DNFilm::new))))
            .from(ACTOR)
            .where(state.filter ? ACTOR.ACTOR_ID.eq(1L) : noCondition())
            .fetch(mapping(DNActor::new));
    }

    @Benchmark
    public List<DNActor> doubleNestingJoin(BenchmarkState state) {
        return state.ctx
            .select(
                FILM_ACTOR.ACTOR_ID,
                FILM_ACTOR.actor().FIRST_NAME,
                FILM_ACTOR.actor().LAST_NAME,
                FILM_ACTOR.FILM_ID,
                FILM_ACTOR.film().TITLE,
                FILM_CATEGORY.category().NAME)
            .from(FILM_ACTOR)
            .join(FILM_CATEGORY).on(FILM_ACTOR.FILM_ID.eq(FILM_CATEGORY.FILM_ID))
            .where(state.filter ? FILM_ACTOR.ACTOR_ID.eq(1L) : noCondition())
            .collect(groupingBy(
                r -> new DNActor(r.value1(), new DNName(r.value2(), r.value3()), null),
                groupingBy(r -> new DNFilm(r.value4(), r.value5(), null))
            ))
            .entrySet()
            .stream()
            .map(a -> new DNActor(
                a.getKey().id(),
                a.getKey().name(),
                a.getValue()
                 .entrySet()
                 .stream()
                 .map(f -> new DNFilm(
                     f.getKey().id(),
                     f.getKey().title(),
                     f.getValue().stream().map(c -> new DNCategory(c.value6())).toList()
                 ))
                 .toList()
            ))
            .toList();
    }

    @Benchmark
    public List<DNActor> doubleNestingTwoQueries(BenchmarkState state) {
        Result<Record5<Long, String, String, Long, String>> actorAndFilms = state.ctx
            .select(
                FILM_ACTOR.ACTOR_ID,
                FILM_ACTOR.actor().FIRST_NAME,
                FILM_ACTOR.actor().LAST_NAME,
                FILM_ACTOR.FILM_ID,
                FILM_ACTOR.film().TITLE)
            .from(FILM_ACTOR)
            .where(state.filter ? FILM_ACTOR.ACTOR_ID.eq(1L) : noCondition())
            .fetch();

        Map<Long, List<DNCategory>> categoriesPerFilm = state.ctx
            .select(
                FILM_CATEGORY.FILM_ID,
                FILM_CATEGORY.category().NAME)
            .from(FILM_CATEGORY)
            .where(state.filter
                ? FILM_CATEGORY.FILM_ID.in(actorAndFilms.map(r -> r.value4()))
                : noCondition())
            .collect(intoGroups(
                r -> r.value1(),
                r -> new DNCategory(r.value2())
            ));

        return actorAndFilms
            .collect(groupingBy(
                r -> new DNActor(r.value1(), new DNName(r.value2(), r.value3()), null),
                groupingBy(r -> new DNFilm(r.value4(), r.value5(), null))
            ))
            .entrySet()
            .stream()
            .map(a -> new DNActor(
                a.getKey().id(),
                a.getKey().name(),
                a.getValue()
                 .entrySet()
                 .stream()
                 .map(f -> new DNFilm(
                     f.getKey().id(),
                     f.getKey().title(),
                     categoriesPerFilm.get(f.getKey().id())
                 ))
                 .toList()
            ))
            .toList();
    }

    @Benchmark
    public List<DNActor> doubleNestingNPlusOneQueries(BenchmarkState state) {
        return state.ctx
            .select(ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
            .from(ACTOR)
            .where(state.filter ? ACTOR.ACTOR_ID.eq(1L) : noCondition())
            .fetch(a -> new DNActor(
                a.value1(),
                new DNName(a.value2(), a.value3()),
                state.ctx
                    .select(FILM_ACTOR.FILM_ID, FILM_ACTOR.film().TITLE)
                    .from(FILM_ACTOR)
                    .where(FILM_ACTOR.ACTOR_ID.eq(a.value1()))
                    .fetch(f -> new DNFilm(
                        f.value1(),
                        f.value2(),
                        state.ctx
                            .select(FILM_CATEGORY.category().NAME)
                            .from(FILM_CATEGORY)
                            .where(FILM_CATEGORY.FILM_ID.eq(f.value1()))
                            .fetch(r -> new DNCategory(r.value1()))
                    ))
            ));
    }

    record MCCName(String firstName, String lastName) {}
    record MCCCategory(String name) {}
    record MCCActor(long id, MCCName name) {}
    record MCCFilm(long id, String title, List<MCCActor> actors, List<MCCCategory> categories) {}

    @Benchmark
    public List<MCCFilm> multipleChildCollectionsMultisetXML(BenchmarkState state) {
        state.ctx.settings().setEmulateMultiset(NestedCollectionEmulation.XML);
        return multipleChildCollectionsMultiset0(state);
    }

    @Benchmark
    public List<MCCFilm> multipleChildCollectionsMultisetJSON(BenchmarkState state) {
        state.ctx.settings().setEmulateMultiset(NestedCollectionEmulation.JSON);
        return multipleChildCollectionsMultiset0(state);
    }

    @Benchmark
    public List<MCCFilm> multipleChildCollectionsMultisetJSONB(BenchmarkState state) {
        state.ctx.settings().setEmulateMultiset(NestedCollectionEmulation.JSONB);
        return multipleChildCollectionsMultiset0(state);
    }

    private List<MCCFilm> multipleChildCollectionsMultiset0(BenchmarkState state) {
        return state.ctx
            .select(
                FILM.FILM_ID,
                FILM.TITLE,
                multiset(
                    select(
                        FILM_ACTOR.ACTOR_ID,
                        row(
                            FILM_ACTOR.actor().FIRST_NAME,
                            FILM_ACTOR.actor().LAST_NAME
                        ).mapping(MCCName::new)
                    )
                    .from(FILM_ACTOR)
                    .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
                ).convertFrom(r -> r.map(mapping(MCCActor::new))),
                multiset(
                    select(
                        FILM_CATEGORY.category().NAME
                    )
                    .from(FILM_CATEGORY)
                    .where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
                ).convertFrom(r -> r.map(mapping(MCCCategory::new))))
            .from(FILM)
            .where(state.filter ? FILM.FILM_ID.eq(1L) : noCondition())
            .fetch(mapping(MCCFilm::new));
    }

    @Benchmark
    public List<MCCFilm> multipleChildCollectionsTwoQueries(BenchmarkState state) {
        Result<Record5<Long, String, Long, String, String>> filmsAndActors = state.ctx
            .select(
                FILM_ACTOR.FILM_ID,
                FILM_ACTOR.film().TITLE,
                FILM_ACTOR.ACTOR_ID,
                FILM_ACTOR.actor().FIRST_NAME,
                FILM_ACTOR.actor().LAST_NAME)
            .from(FILM_ACTOR)
            .where(state.filter ? FILM_ACTOR.FILM_ID.eq(1L) : noCondition())
            .fetch();

        Map<Long, List<MCCCategory>> categoriesPerFilm = state.ctx
            .select(
                FILM_CATEGORY.FILM_ID,
                FILM_CATEGORY.category().NAME)
            .from(FILM_CATEGORY)
            .where(FILM_CATEGORY.FILM_ID.in(
                filmsAndActors.map(r -> r.value1())
            ))
            .and(state.filter ? FILM_CATEGORY.FILM_ID.eq(1L) : noCondition())
            .collect(intoGroups(
                r -> r.value1(),
                r -> new MCCCategory(r.value2())
            ));

        return filmsAndActors
            .collect(groupingBy(
                r -> new MCCFilm(r.value1(), r.value2(), null, null),
                groupingBy(r -> new MCCActor(r.value3(), new MCCName(r.value4(), r.value5())))
            ))
            .entrySet()
            .stream()
            .map(f -> new MCCFilm(
                f.getKey().id(),
                f.getKey().title(),
                new ArrayList<>(f.getValue().keySet()),
                categoriesPerFilm.get(f.getKey().id())
            ))
            .toList();
    }

    @Benchmark
    public List<MCCFilm> multipleChildCollectionsNPlusOneQueries(BenchmarkState state) {
        return state.ctx
            .select(FILM.FILM_ID, FILM.TITLE)
            .from(FILM)
            .where(state.filter ? FILM.FILM_ID.eq(1L) : noCondition())
            .fetch(f -> new MCCFilm(
                f.value1(),
                f.value2(),
                state.ctx
                    .select(
                        FILM_ACTOR.ACTOR_ID,
                        FILM_ACTOR.actor().FIRST_NAME,
                        FILM_ACTOR.actor().LAST_NAME)
                    .from(FILM_ACTOR)
                    .where(FILM_ACTOR.FILM_ID.eq(f.value1()))
                    .fetch(a -> new MCCActor(
                        a.value1(),
                        new MCCName(a.value2(), a.value3())
                    )),
                state.ctx
                    .select(FILM_CATEGORY.category().NAME)
                    .from(FILM_CATEGORY)
                    .where(FILM_CATEGORY.FILM_ID.eq(f.value1()))
                    .fetch(c -> new MCCCategory(c.value1()))
            ));
    }
}

• 点击分享到Facebook (在新窗口打开)

• 点击在LinkedIn上分享 (在新窗口打开)

• 点击在Twitter上分享 (在新窗口中打开)

• 点击分享到Reddit (在新窗口打开)

• 更多

• 点击发送链接给朋友 (在新窗口打开)

• 点击打印 (在新窗口中打开)

喜欢这个

喜欢 正在加载...