ID列出不是解决N + 1问题的最佳解决方案

为了避免N + 1问题 ，Hibernate用户经常使用带有ID列表的IN谓词 。 在这篇文章中，我们将看到这些用户可能只是用一件不好的东西代替一件可怕的东西，虽然更好，但还不好。 原因如下：

N + 1问题

N + 1问题是一个众所周知的问题，在各种 博客 文章中都有记录。 先前链接的文章显示了以下查询集，以解释此问题的性质：

SELECT id, name FROM albums
SELECT id, name FROM songs WHERE album_id = 1
SELECT id, name FROM songs WHERE album_id = 2
SELECT id, name FROM songs WHERE album_id = 3
SELECT id, name FROM songs WHERE album_id = 4
SELECT id, name FROM songs WHERE album_id = 5

当实体配置为延迟获取时，这组查询通常由ORM（例如Hibernate）产生。

本文还通过用IN谓词用单个查询替换第二组N = 5查询来解决该问题：

SELECT id, title, filename FROM songs
WHERE album_id IN (1, 2, 3, 4, 5)

这样会将查询数量从N + 1减少到1 + 1，这肯定会更快。

但是让我们从SQL的角度来看一下

这样的带有ID列表的IN谓词是一个好的解决方案吗？ 对于很小的列表，这当然是可行的。 但是，当列表增加时，请考虑以下事项：

IN列表大小

并非所有数据库都支持IN列表的任意长度。 特别是存在以下限制：

• Oracle IN谓词：1000个元素
• Ingres：总共1024个绑定值
• SQLite：总共999个绑定值
• Sybase ASE：2000总绑定值
• SQL Server 2008 R2：总绑定值2100

这很烦人，因为开发人员可能必须艰难地学习上述内容。 如果您使用的是jOOQ ，则可以“安全地”忽略上述约束，因为jOOQ将重写查询，使得：

• 大型Oracle IN谓词分为多个OR连接的IN谓词或AND连接的NOT IN谓词
• 在SQL呈现时会检测到大量绑定值，并替换为内联值

可变装订速度

在某些JDBC驱动程序中，变量绑定涉及很多工作。 本质上，某些数据库协议将需要将许多值一对一地传输到数据库。 内联绑定值时不会发生这种情况，因为传输的唯一内容是单个SQL字符串。 绑定值太多（我说的是10k或更多）绝对不是一个好主意。

游标缓存未命中

诸如Oracle之类的复杂数据库维护着游标缓存 ，可以利用它来实现游标共享 。 这意味着，相同的SQL语句的后续执行将受益于已经执行的昂贵的执行计划计算以及所收集的游标统计信息。 这样考虑：

-- This is the first time Oracle encounters this 
-- query. The DB has to parse the query and 
-- calculate an execution plan, which can be quite 
-- expensive if you have lots of JOINs
SELECT id, name FROM songs 
WHERE album_id IN (?, ?)

-- This is the second time Oracle encounters this 
-- same query. The DB can now re-use the previous
-- execution plan as it is likely to be optimal 
-- again
SELECT id, name FROM songs 
WHERE album_id IN (?, ?)

-- This is not the same query as the previous ones
-- A new execution plan has to be calculated
SELECT id, name FROM songs 
WHERE album_id IN (?, ?, ?)

如您所见，上面的示例显示IN谓词中的ID列表是一个移动目标，由于每个查询都易于在您的查询中产生新的游标和新的执行计划，因此它可能会完全删除绑定值的用处。数据库。 您可能还内联了绑定值，这甚至可以帮助您防止绑定值偷看问题 。

那有什么比ID列表更好呢？

有很多更好的方法。 请注意，并非所有选项都可能适合您的具体问题，并且并非所有选项都总是优于ID列表。 请使用常识，也许还要进行负载和/或性能测试，这是您所处情况中最好的查询。

使用JOIN显式“渴望”获取

有时，对数据库中的数据进行非规范化将更容易。 与其一一取出歌曲，不如将它们与专辑一起取出：

SELECT
  a.id a_id, 
  a.name a_name,
  s.id s_id,
  s.name s_name
FROM albums a
JOIN songs s ON s.album_id = a.id

这将通过导线传输更多数据（重复专辑信息），以换取仅执行单个查询（将N + 1减少为1）。 这仅适用于轻微的非规范化。 如果您加入了许多1：N关系，那么您可能对这种解决方案不满意。

半联接原始查询

如果您可以访问原始查询的SQL代码，则在提取歌曲时将其半连接！ 这很简单：

SELECT id, name FROM songs 
WHERE album_id IN (
  SELECT id FROM albums
)

-- Or using EXISTS
SELECT s.id, s.name FROM songs s
WHERE EXISTS (
  SELECT 1 FROM albums a
  WHERE a.id = s.album_id
)

这将需要一些SQL转换。 再次，使用类型安全查询构建器/ SQL构建器来组合查询，例如jOOQ ， JaQu或Criteria API ，您可能能够更轻松地实现这种SQL转换/ SQL组合。

请注意，至少在具有强大查询优化器的复杂数据库中，这可能是您可以选择的最快解决方案。

对ID列表使用数组

如果您确实无法在没有ID列表的情况下查询您的歌曲，请至少使用一个数组作为绑定变量，例如Oracle方言：

SELECT id, name FROM songs 
WHERE album_id IN (
  SELECT * FROM TABLE(?)
)

上面的语法是特定于Oracle的 。 查看此堆栈溢出问题，以了解其他替代方法。 请注意，Oracle的VARRAY和TABLE类型是强类型的，即首先必须具有这样的类型：

CREATE TYPE numbers AS TABLE OF NUMBER(38);

另外，您可以使用以下“内置”表类型之一 ：

• ORA_MINING_NUMBER_NT
• ORA_MINING_VARCHAR2_NT

创建离散大小的IN列表

如果您的数据库不支持数组，并且您需要依赖ID列表，则可能有最后一种选择，那就是避免过多的游标缓存未命中和硬解析。 创建离散大小的IN列表，将绑定值填充到下一个离散长度。 假设长度为2、3、5、8、13。这可以通过示例很好地解释：

-- Of course, this only makes sense with bind values
-- Inlining is done for the purpose of the example
-- only

-- Two IDs   fill up to 2
album_id IN (1, 2)

-- Three IDs fill up to 3
album_id IN (1, 2, 3)

-- Four IDs  fill up to 5
album_id IN (1, 2, 3, 4, 4)

-- Five IDs  fill up to 5
album_id IN (1, 2, 3, 4, 5)

-- Six IDs   fill up to 8
album_id IN (1, 2, 3, 4, 5, 6, 6, 6)

您的IN列表大小应增加什么步骤没有经验法则，因此您可能需要实际测量。

注意！ 注意：可以使用NULL填充IN谓词的IN列表，但不能填充NOT IN谓词的IN列表。 要了解有关此内容的更多信息，请阅读有关NULL和NOT IN谓词的博客文章。

TL; DR：重新控制您的SQL

一旦您的数据处理中包含大量数据，通用ORM模型可能就不再足够了，因为很难调整此类ORM。 您可能需要求助于SQL，并以最适合问题域的最佳方式明确表达SQL语句。

参考： ID并不是我们的JCG合作伙伴 Lukas Eder在JAVA，SQL和JOOQ博客上列出的N + 1问题的最佳解决方案 。

翻译自: https://www.javacodegeeks.com/2013/11/id-lists-arent-the-best-solution-for-the-n1-problem.html