SQL数据的层次结构（递归CTE vs HierarchyID vs 闭包表）

Question

SQL数据的层次结构（递归CTE vs HierarchyID vs 闭包表）

sql-servercommon-table-expressionrecursive-queryhierarchyidtransitive-closure-table

22

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

我已经搜索了整个网络，无法决定如何处理这种情况。我知道我有以下选择。

1. 我可以创建递归CTEs，通用表达式（又名RCTE）来处理分层数据。这似乎是处理普通访问最简单的方法，但我担心对于确定子对象的权限级别可能会很慢。

2. 我可以在表中创建一个hierarchyId数据类型字段，并使用SQL Server提供的函数，例如GetAncestor()，IsDescendantOf()等。这似乎会使查询相当容易，但似乎需要一个相当复杂的插入/更新触发器来通过插入和移动保持hierarchyId字段正确。

3. 我可以创建闭包表，该表将存储表中的所有关系。我将其想象为这样：父列和子列，每个父->子关系都表示为（例如，1->2 2->3将在数据库中表示为1-2、1-3、2-3）。缺点是，即使它们非常简单，这需要插入、更新和删除触发器，并且此方法会生成大量记录。

我已经尝试搜索整个网络，找不到任何关于这三种方法之间的建议。

PS我也对此问题的任何替代解决方案持开放态度。

- jp36

请在您的问题中标记您正在使用的SQL Server版本。您的查询是否倾向于从子级到父级进行？对于单个子项沿父链接向上遍历的RCTE不应太糟糕。反过来遍历所有子项就会变慢。 - HABO

我现在无法检查版本，但稍后会检查。我认为它可能是2008年或更新的版本。更有可能的情况是我更经常地获取父级的子项而不是获取子项的父级。 - jp36

我不能添加另一个标签，但它是SQL Server 2008 r2。 - jp36

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Kevin Suchlicki · Accepted Answer

我已经使用了所有三种方法，这主要是个人口味的问题。

我同意在表中具有父子关系的层次结构最简单。移动子树很容易，并且可以使用CTE编写递归访问。只有当您拥有非常大的树形结构并且经常访问分层数据时，性能才会成为问题。在大多数情况下，只要正确地为表创建索引，递归CTE非常快速。

闭包表更像是上述方法的补充。查找给定节点的所有后代非常快速，您不需要使用CTE，只需一个额外的连接即可完成，所以它非常适合。是的，记录的数量会增加，但是我认为它不会超过深度N的树（例如深度为5的三元树仅需存储父子关系而不是闭包表时需要121个连接，而使用闭包表需要1 + 3 +（9 * 2）+（27 * 3）+（81 * 4）=427个连接）。此外，闭包表记录非常狭窄（最少只有两个整数），几乎不占空间。插入新记录到层次结构时生成要插入到闭包表中的记录列表需要一点点开销。

我个人喜欢HierarchyId，因为它真正结合了上述两种方法的优点，即紧凑的存储和快速访问。一旦设置好，很容易查询并且占用很少空间。正如您所提到的，移动子树有点棘手，但是还是可以管理的。无论如何，在层次结构中您有多频繁地移动子树？你可以找到一些链接来建议一些方法，例如：

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

我发现使用hierarchyId的主要缺点是学习曲线比其他两种方法更陡峭。与其他两种方法相比，如何使用它不那么明显。我曾经与一些非常聪明的 SQL 开发人员合作过，他们经常会被卡住，所以你最终需要几个专家来回答其他人的问题。