Spring Data JDBC 3.2.0-M2：使用单个select语句加载任意聚合


从 Spring Data JDBC 3.2.0-M2 开始，Spring Data JDBC 支持单查询加载。单查询加载可通过单个选择语句加载任意聚合。

要启用单查询加载，需要在 RelationalMappingContext 上调用 setSingleQueryLoadingEnabled(true)。
注意：这只适用于简单的聚合（由聚合根和其他实体的单一集合组成）。它还仅限于使用 CrudRepository 中的 findAll、findById 和 findAllByIds 方法。未来的版本将对此进行改进。

最后一个限制是，使用的数据库必须支持分析函数（又称窗口函数）。除了内存数据库（H2 和 Hsql DB）外，所有官方支持的数据库都支持分析功能。

你可以将单次查询加载缩写为 SQL，但请不要这样做。

如果你想了解它是如何工作的，以及我们是如何想到它的，请继续阅读。

问题所在：N+1 问题
从概念上讲，Spring Data JDBC 可以一次性加载完整的聚合。但到目前为止，如果查看实际运行的 SQL，就会发现对于非小聚合而言，需要运行多条 SQL 语句。例如，考虑到 Minion 类型会引用 Hobby 实体集合和 Toy 实体集合。当 Spring Data JDBC 加载一堆这样的 Minion 时，它会

  • 运行 SELECT ... FROM MINION
  • 对于该查询中的每个结果，它会
  • 运行 SELECT ... FROM HOBBY
  • 运行 SELECT ... FROM TOY

这样做效率很低，被称为 N+1 问题，因为如果一个聚合只有一个集合，要加载 N 个聚合，就要执行 N+1 个查询（一个针对根实体，N 个针对子实体）。如果只有一个集合，可以使用join，但如果有多个集合，join就会失效。

这个问题绝非 Spring Data JDBC 独有。

其他 ORM 使用不同的策略来尽量减少这一问题：

  • 例如，它们可以将一个子实体join到集合根。
  • 或者，它们可以对相关实体使用批量加载。

所有这些方法都是限制了问题的影响，但它们只是治标不治本。

此外，大多数人都会告诉你，你不能在单个查询中真正做到这一点，因为你会得到所有子表的交叉积，这可能会非常糟糕。假设有 5 个子表，每个子表有 10 个条目。这些表的交叉积将是 1010101010 = 10000 行！

创新想法
很久以前，我想起一位前同事说过的一句话"关系数据库的问题在于，它们总是返回表，而有时你需要一棵树"。
他是用德语说的，我不记得他的原话了，但大意就是这样。

这引起了我的思考：的确，SQL 查询基本上总是返回一个表。但我该如何在其中表示一棵树呢？
换一种说法：在 Excel 中如何表示聚合的数据？如果忽略 Excel 基本上是一个具有超能力的关系数据库这一事实，只把它当作一个单一的电子表格呢？

让我们从一个相当简单的案例开始：

class Minion {
    @Id
    Long id;
    String name;
    List<Toy> toys;
    // the skills you need to excel at this hobby.
    List<Hobby> hobbies;
}

如果我想用 Excel 来表示，我可能会这样做：

id    name    toys       hobbies
1    Bob      Teddy        Hold Teddy
             Blue Light    Look Cute
                       Follow Kevin
2    Kevin    ...    ...

从查询中得到这样的结果真的很不错。只需对结果集进行一次传递，就能从中构建 Java 实例，这并不难。

这时，我想起 SQL 实际上是图灵完备的。因此，我可以用 SQL 来表达这个问题。问题只是如何表达！知道问题有解决方案总是有帮助的。当你能让脑中那个试图说服你 "没有解决方案，你只是在浪费时间 "的声音闭嘴时，找到解决方案就会变得容易得多。

窗口函数row_number()
集合中的元素是通过 Minion 中行的索引 "join"起来的。但数据库中并不存在该索引。幸运的是，使用 row_number() 窗口函数可以非常容易地创建这样一个索引。

如果你不了解窗口函数（又称分析函数），它们与聚合函数类似，但分组方式不会将所有匹配的行折叠成一条。相反，分析函数会应用到分组所定义的窗口中，并在每一行中显示结果。而且，组中所有行的结果并不一定总是相同的。使用这些函数，你还可以做很多事情。你应该阅读更多相关内容。但对于我们当前的问题，我们只需要

  • row_number()，为一组中的所有行分配一个唯一的、持续增加的数字。
  • count(*)，用于计算组中的行数。我知道，这很令人吃惊。

我们首先为每个子表创建一个subselect 。每个subselect 从底层表中select所有列、row_number() 和 count(*)，每个列都按 minion_id 分组。

( 
  select *,
    row_number() over (partition by minion_id) h_rn,
    count(*) over (partition by minion_id) h_cnt
  from hobby
) h

实际上，我们对聚合根也是这样做的。不过，我们不需要 row_number，因为我们知道每一行只有一个 minion。因此，我们可以将其固定为 1。

( 
  select *,
    1 m_rn
  from minion
) m

Join by Id
接下来，我们用标准的left join将所有这些子选择连接到一起：

select *
from ( ... ) m
left join 
  ( ... ) h
  on m.id = h.minion_id
left join 
  ( ... ) t
  on m.id = t.minion_id

而我们需要的是类似于对不同行号的全外连接。遗憾的是，在 SQL 中不能对一列进行左连接，而对另一列进行全外连接。但我们可以用 where 子句来解决这个问题。

Row Numbers伪全外连接
该 where 子句的天真版本是：

where m_rn = h_rn
and   m_rn = t_rn

这忽略了我们需要outer join语义这一事实。为了解决这个问题，我们添加了大量的 null 检查和与 cnt 列的比较，这使得 where 子句相当难读。而且这个过程也非常复杂，我无法在不犯大量错误的情况下把它写下来。因此，我就不细说了。如果你真的想知道，请启用 SQL 日志。

这样，我们就把行数降到了正确的数字。好极了！但我们仍在重复部分数据。
例如，对于没有匹配hobbies ，一个toy的数据会重复出现。

我们真的希望将其减少为空值。为此，我们将几乎所有的列都替换成如下表达式：

case when x_rn = rn then name end

这里 x_rn 是作为相关列来源的子选择的行号。rn 是总行号，即所有子选择连接的行号。

  • 这个条件基本上表达了如果subselects 有这一行的数据，就使用它；
  • 否则，就使用null空值。

我们对所有普通列都使用这种模式。只有在下段所述的further join中使用的列才不使用此模式。

这样我们返回的数据行数最少，而且没有重复数据！

但是，我们只能为单层嵌套实体做到这一点！这可以通过简单的递归来解决：我们得到的结果看起来就像一个简单的表。因此，它就可以这样使用。准确地说，它可以用来代替为选择添加行号的subselect ，因为它已经有了行号。

条件
到目前为止，我们基本上研究了 findAll 操作的查询。大约半年前，我已经有了一个适用于 findAll 的解决方案，但对于 findById 或 findByAddressName 这样的操作，却没有有效的解决方案。上述解决方案并不存在这个问题。任何 where 子句都会应用到聚合根的最内层选择，并通过连接限制所有数据。无论如何，为外键和 ID 创建的索引都能很好地支持这一点，因此我们确信这种查询方式可以高效执行。