自从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 ，或许很难转化为不同的东西，而这仍然是等价的。
基准测验
将对上述矩阵的每个组合进行以下基准测验。
- 一个单一的
MULTISET查询及其3种可用的模仿，运用XML（如有），JSON。JSONB - 一个单一的
JOIN查询，在父系和子系之间创立一个笛卡尔积 - 一种办法是运转2个查询，将一切必要的数据取到客户端内存中，然后在客户端中履行嵌套。
- 一个单纯的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 = trueJOIN - 依据
XML，MULTISET的仿真总是最慢的，或许是因为它需求更多的格式化。MULTISET(在一个Oracle事例中，依据XML的模仿乃至比一般的N+1办法还慢）。 -
JSONB在PostgreSQL中比 要慢一些，或许是因为 是一个纯粹的依据文本的格式，没有任何后期处理/清理。在PostgreSQL中，的优势不在于只进行投影查询，而在于存储、比较和其他操作。关于大多数用处， 或许更好。关于纯投影来说， 是更好的（jOOQ 3.17将使其成为 模仿的默认值）。JSONJSONJSONBJSONBJSONMULTISET - 值得注意的是，jOOQ将记录序列化为JSON数组，而不是JSON目标，以防止传输重复的列名，并在解序列化数组时提供位置索引。
- 关于大型的数据集（其间
filter = false），MULTISET相关子查询的N+1因素或许成为一个问题（因为算法杂乱性的性质），因为它阻止了运用更有用的散列衔接。在这些状况下，依据单次查询JOIN的办法或每巢级1次查询的办法比较好
简而言之。
MULTISET只需嵌套循环衔接是最优的，就能够运用。-
假如散列衔接或兼并衔接更理想，那么单查询
JOIN办法或每巢1次查询的办法往往体现更好（尽管跟着杂乱性的添加，它们有自己的注意事项）
关于小数据集来说，在便当性和正确性方面的优点是绝对值得的。关于较大的数据集，持续运用JOIN 。自始自终，没有银弹。
本博文没有调查的工作
这篇博文没有调查的几件事，包含。
- 服务器中的序列化开支。一般的JDBC
ResultSet倾向于从服务器和客户端之间的二进制网络协议中获益。有了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 (在新窗口翻开)
-
更多
-
点击发送链接给朋友 (在新窗口翻开)
-
点击打印 (在新窗口中翻开)
喜爱这个
喜爱 正在加载…
