mysql - 在关系数据库中实现高效的外键

Question

据我所知，所有流行的 SQL 数据库都通过索引外键来有效地实现外键。

假设一个 N:1 关系 Student -> School，学校 ID 存储在带有(有时是可选的)索引的学生表中。对于给定的学生，您只需在行中查找学校 ID 即可找到他们的学校，对于给定的学校，您可以通过在 Students 中的外键索引中查找学校 ID 来找到其学生。关系数据库 101.

但这是唯一明智的实现方式吗？假设您是数据库实现者，您没有在外键列上使用 btree 索引，而是在关系的另一端(许多端)的行上添加了一个(用户不可见的)集合。因此，您没有为学生中的学校 ID 列编制索引，而是有一个不可见的列，它是学校行本身的一组学生 ID。然后获取给定学校的学生就像迭代集合一样简单。这种实现不常见有什么原因吗？是否有一些查询无法通过这种方式得到有效支持？这两种方法看起来或多或少是等同的，模数特定的实现细节。在我看来，您可以将任一解决方案与另一解决方案进行比较。

在我看来，它在概念上与 btree 的拆分相同，后者包含 (school_id, student_row_id) 的排序运行，并将每个运行存储在学校行本身上。在学校主键中查找学校 ID 可以得到学生 ID，这与在外键索引中查找学校 ID 相同。

为清晰起见进行了编辑

Answer 1

您似乎建议将“逗号分隔的值列表”作为字符串存储在表的字符列中。你说它“就像迭代集合一样简单”。

但在关系数据库中，事实证明，当将集合存储为列中的值列表时，“迭代集合”一点也不简单。也没有效率。它也不符合关系模型。

考虑将成员添加到集合或从集合中删除，甚至只是确定成员是否在集合中时所需的操作。考虑执行完整性所需的操作，以验证该“逗号分隔列表”中的每个成员是否有效。关系数据库引擎不会帮助我们解决这个问题，我们必须自己编写所有这些代码。

乍一看，这个想法似乎是个好方法。并且完全有可能做到，并让一些代码工作。但是，一旦我们超越琐碎的演示，进入实际问题和真实世界数据量的领域，就会发现这是一个非常非常糟糕的主意。

存储逗号分隔列表是再熟悉不过的 SQL 反模式。

我强烈推荐 Bill Karwin 的优秀著作的第 2 章:SQL Antipatterns: Avoiding the Pitfalls of Database Programming ISBN-13: 978-1934356555

---

(这里的讨论涉及“关系数据库”以及它是如何设计运行的，遵循关系模型，由 Ted Codd 和 Chris Date 开发的理论。)

“所有非键列都依赖于键，整个键，只有键。所以 Codd 帮帮我。”

---

问:这种实现不常见有什么原因吗？

是的，它并不常见，因为它与关系理论背道而驰。它使一个简单的问题(对于关系模型)变成了关系数据库无法帮助我们解决的困惑问题。如果我们存储的只是一串字符，并且数据库除了存储字符串和检索字符串之外不需要对它做任何事情，我们就很好了。但我们不能要求数据库将其解读为表示实体之间的关系。

问:是否有一些查询无法通过这种方式有效支持？

任何需要将该“值列表”转换为一组要返回的行的查询都是低效的。任何需要识别包含特定值的“值列表”的查询都是低效的。并且从“值列表”中插入或删除值的操作效率低下。