mysql - 节点关系的MySQL表

Question

我试图弄清楚什么是存储类型（var）char的节点之间关系的最佳数据库和表结构。多年前，我上次使用MySQL作为一些简单的PHP网页的后端，但从未超越。我希望一些经验丰富的用户可以给我他们的意见。

假设我有很多名字：


汤玛士
简
费利克斯
渣
安妮


我现在想存储他们的关系。我的想法是要有两个看起来像这样的表：

names (id, name)        relationships (id_1, id_2)
0 Thomas                0 1
1 Jane                  0 3
2 Felix                 1 2
3 Marc                  3 4
4 Anne                  ...
...                     

数据范围如下：


表“名称”将包含大约500万行。
表“关系”将包含150-200百万行。
我只能在本地访问数据库（服务器和客户端是同一台机器）
我不需要像Web服务器那样的响应能力，仅在少数情况下需要高吞吐量（以减少等待时间）


我的问题是：


我记得正确使用PRIMARY_KEY很重要。我隐约记得有可能将密钥分配给两列（在我的情况下为id_1，id_2）；我想这可以帮助查询吗？
MySQL内是否有一种方法可以防止在插入过程中创建重复关系（例如0：4和4：0）？
MySQL对我而言默认为InnoDB。这是您为我的方案推荐的数据库吗？


任何指针欢迎。谢谢。

Answer 1

首先，您需要考虑您的关系是否具有与之关联的“方向”。例如，关系“是...的孩子”与相反的关系“是...的孩子”具有相反的方向；另一方面，关系“是...的同级关系”是无向的（或双向的，取决于一个人的观点）。

您描述的结构非常适合直接关系。

另一方面，双向关系通常最好通过刻意执行第二个要点中描述的重复来表示。尽管这会消耗更多的存储空间，但它极大地简化了诸如“查找X的所有同级”之类的查询，否则可能需要将两个独立的查询结合起来：

SELECT id_2 FROM my_table WHERE id_1=X
UNION
SELECT id_1 FROM my_table WHERE id_2=X

因为结果列上没有索引，所以如果要对结果做更多​​的事情（例如，按 id排序或与 names表联接），则这类查询可能会非常慢。这种情况下，一个人可以在联合之前执行联接，但这只会增加数据处理代码的冗余性和复杂性。

可以使用 triggers确保只要将关系写入（插入，更新或删除）表示双向关系的表，就会对反向关系自动执行相同的操作。

其次，您描述的表示形式称为“邻接表”，它非常简单且易于理解。但是，它不能很好地处理数据层次结构中的深度搜索，尤其是在MySQL上（与某些其他RDBMS不同，MySQL不支持递归函数）。因此，找到“ X的所有后代”或“ Y的所有祖先”实际上是非常困难的。对于这些任务，其他数据模型（例如“ nested sets”或“ transitive closure”）要好得多。

带着序言说到您的问题：



  我记得正确使用PRIMARY_KEY很重要。我隐约记得有可能将密钥分配给两列（在我的情况下为id_1，id_2）；我想这可以帮助查询吗？


relationship表有四个可能的主键：


(id_1)
(id_2)
(id_1, id_2)
(id_2, id_1)


根据定义，主键在表中必须唯一。确实，这是识别记录的主要方法。但是如果需要，还可以定义其他 UNIQUE键，这些键与主键具有相同的约束作用（差异相对较小，超出了此答案的范围）：因此，实际上可以强制执行上述约束的任意组合。

上述限制分别是：将每个名称在关系的一侧限制为不超过一次；限制每个名字在关系的另一侧不超过一次；最后两个将名称的每个组合限制在一次相同的关系内（最多只是索引的存储顺序）。如果该表表示无向关系，则显然第二和第四约束在语义上分别等效于第一和第三约束。

一些例子：


如果您的表表示“ id_1是 id_2的遗传父亲”，则 id_1可能有很多孩子。因此 (id_1)不能作为主键，因为它不能唯一地标识有多个孩子的父亲的记录。另一方面， id_2只能有一个遗传父亲（不包括胚胎学方面的发展），因此 (id_2)将唯一地标识一条记录，并且可以作为主键（也就是说，这种多对一关系可能会可以通过 father_id表中的 names列进行建模）。其他两个（复合）键将允许孩子有很多父亲，因此必须不正确。
如果您的表表示“ id_1是 id_2的父代”，则父代可以有多个子代，而子代可以有多个父代（这被称为多对多关系）。因此，前两个约束是不正确的，并且必须在后两个约束之间进行选择（如前所述，区别只是索引的存储顺序-因此MySQL必须先找到第一列才能查找第二列）。顺便说一句，在这种情况下，可以考虑在 relationship表中增加一列，以指示该关系表示的父级；如果一个孩子只能在每种类型中有一个父母，则可以将主键定义为 (child_id, parent_type)。
如果您的表代表“ id_1和 id_2已婚”，那么 (id_1)和 (id_2)都是“候选键”，因为没有人可以与一个以上的人结婚（至少在英国，一夫多妻制除外） ）。因此，可以将 (id_1)定义为主键，并在 UNIQUE上定义第二个 (id_2)键。如前所述，很可能希望将记录以两种方式都放在表中，而这些约束不会阻止这种情况。


  MySQL内是否有一种方法可以防止在插入过程中创建重复关系（例如0：4和4：0）？


是的，可以使用触发器来做到这一点：但是请注意上面关于双向关系的论述（通常需要这种“重复”）。强制执行此类约束的触发器示例可能是：

CREATE TRIGGER rel_ins BEFORE INSERT ON relationships FOR EACH ROW
IF EXISTS (
  SELECT * FROM relationships WHERE id_1=NEW.id_2 AND id_2=NEW.id_1
) THEN
  SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Reverse relationship already exists';
END IF;;

可能还需要“更新前”类似的触发器。

这种约束可能是理想的情况，该表表示“是其父母”，因为父母不能是其子女的孩子（但是，在这种情况下，值得注意的是，在这种关系表中，实际上，人们可能希望走得更远，避免发生各种情况（例如，阻止孩子成为其祖父母的父母）。同样，“邻接表”不是执行这种约束的最佳模型，另一方面，“嵌套集”完全依靠其结构完全阻止了所有圆度。

  MySQL对我而言默认为InnoDB。这是您为我的方案推荐的数据库吗？


InnoDB的最大优点是它完全兼容 ACID，因此提供了事务支持。如果您可能一次从多个位置写入数据库，这将特别有用。如果您只是要将一堆静态数据一次性加载到数据库中以进行后续查询，则它可能会比MyISAM慢一点。