分层SQL数据(递归CTE vs HierarchyID vs闭包表)

Question

我在Server数据库中使用了一组分层数据。数据以guid作为主键存储，parentGuid作为指向直接父对象的外键存储。我最常通过WebApi项目中的实体框架访问数据。为了使情况更加复杂，我还需要基于这个层次结构管理权限，以便应用于父级的权限应用于其所有后代。我的问题是：

我找遍了所有的地方，无法决定哪一个是处理这种情况的最好办法。我知道我有以下选择。

1. 我可以创建Recursive CTEs，公共表表达式(又称RCTE)来处理分层数据。对于正常访问，这似乎是最简单的方法，但我担心，当用于确定子对象的权限级别时，它可能会比较慢。
2. 我可以在表中创建一个hierarchyId数据类型字段，并使用Server提供的函数，如GetAncestor()、IsDescendantOf()等。这似乎使查询相当容易，但似乎需要相当复杂的insert/update触发器才能通过插入和移动使hierarchyId字段保持正确。
3. 我可以创建一个closure table，它将存储表中的所有关系。我设想它是这样的:父列和子列，每个父->子列都将被表示。(即1-> 2 ->3在数据库中表示为1-2、1-3、2-3)。缺点是，这需要insert、update和delete触发器，尽管它们非常简单，而且这个方法会生成很多记录。

我试着到处搜索，在这三种方法之间找不到任何建议。

PS我也愿意接受任何其他解决这个问题的方法。

Answer 1

这三种方法我都用过。这主要是味道的问题。

我同意表中有亲子关系的层次结构是最简单的。移动子树很简单，而且很容易用CTE对递归访问进行编码。只有当您有非常大的树结构并且经常访问分层数据时，性能才会成为一个问题。在大多数情况下，当表上有正确的索引时，递归CTE非常快。

闭包表更像是上述内容的补充。快速找到给定节点的所有后代，您不需要CTE，只需要一个额外的连接，所以很好。是的，记录的数量会爆炸，但我认为它不超过N深度树节点数的N-1倍(例如，深度为5的三级树需要1+3+9+ 27 + 81 = 121连接，而只存储父-子关系时，关闭表需要1+3+ (9 * 2) + (27 * 3) + (81 * 4) = 427 )。此外，闭包表记录太窄(至少只有两个ints )，几乎没有空间。在将新记录插入到层次结构中时，生成要插入到闭包表中的记录列表需要一些开销。

我个人喜欢HierarchyId，因为它真正地结合了上面两种方法的优点，即紧凑的存储和闪电般的快速访问。一旦设置好它，就很容易查询，占用的空间也很小。正如您所提到的，移动子树有点棘手，但它是可管理的。不管怎么说，你到底多长时间一次移动一个层次结构中的子树？您可以找到一些链接，这些链接将建议一些方法，例如：

http://sqlblogcasts.com/blogs/simons/archive/2008/03/31/SQL-Server-2008---HierarchyId---How-do-you-move-nodes-subtrees-around.aspx

我发现hierarchyId的主要缺点是学习曲线。如何使用它并不像其他两种方法那么明显。我与一些非常聪明的SQL开发人员一起工作，他们经常会被困在上面，所以您最终会遇到一两位常驻专家，他们必须回答其他人的问题。