我应该担心 HierarchyID 耗尽吗？

发布于 2024-09-01 07:45:20 字数 187 浏览 14 评论 0原文

当您在另外两个人之间请求新的 HierarchyID 时，结果会逐渐变长。例如，在 2/5.6 和 2/5.7 之间，只有 2/5.6.1 和其他 4 个组件路径。 HierarchyID 数据类型限制为 800 个字节，因此您不能永远重复此操作。话又说回来，整数类型也有限制，但在实践中这不是问题。我是否应该定期对表格进行碎片整理，以便高度不会无限制地增长？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

为你拒绝所有暧昧 2024-09-08 07:45:20

它被认为是使用 hierarchyid 来“附加”新 ID 的“最佳实践”，这样您就不会使用那些中间状态（例如 /2/5.6/ ）根本。如果您的 hierarchyid 是聚集主键，那么这对性能不利，它将导致页面拆分，类似于 uniqueidentifier 的方式。

如果您生成连续的子级，那么您几乎不需要担心用完；每个父母实际上可以拥有数百万个孩子。

此处是您期望如何生成 hierarchyid 值：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
    UPDATE Hierarchy
    SET @LastChild = LastChild = HId.GetDescendant(LastChild, NULL)
    WHERE HId = @ParentID

    INSERT Hierarchy (HId, ...)
    VALUES (@LastChild, ...)
COMMIT

如果您以这种方式生成 id，请放心，您永远不必担心用完。

出于好奇，我进行了一个快速测试，以确定您可以走多深。这是一个测试脚本：

DECLARE
    @parent hierarchyid,
    @child hierarchyid,
    @high hierarchyid,
    @cnt int

SET @parent = '/1/'
SET @child = @parent.GetDescendant(NULL, NULL)
SET @cnt = 0

WHILE (@@ERROR = 0)
BEGIN
    SET @cnt = @cnt + 1
    PRINT CAST(@cnt AS varchar(10)) + ': ' + @child.ToString()
    SET @high = @parent.GetDescendant(@child, @high)
    SET @child = @parent.GetDescendant(@child, @high)
END

您可以看到它在点嵌套级别 1426 处出错，因此这是您可以创建的“中间”节点数量的最坏情况限制，最坏情况意味着每个插入都位于两个嵌套最深的节点之间。

正如我在评论中提到的，达到这个限制相当困难，但这仍然不是一个值得尝试的好主意。随着您使用越来越多的“点”，实际字节长度也会变得越来越长，这会降低性能。如果 hierarchyid 是您的聚集索引，这将通过页面拆分降低性能。如果您尝试按父节点对节点进行排名，请改用排名列；从稍后的 SELECT 中进行排序比在 INSERT 期间进行排序更容易和，在 INSERT 期间您必须担心事务隔离和其他问题这样的头痛。

It's considered a "best practice" with the hierarchyid to "append" new IDs so that you don't use those in-between states (such as /2/5.6/) at all. If your hierarchyid is a clustered primary key then that's bad for performance, it will cause page splits similar to the way a uniqueidentifier will.

If you generate sequential children, it's highly unlikely that you'd ever need to worry about running out; you can have literally millions of children for each parent.

Here is an example of how you're expected to generate hierarchyid values:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
    UPDATE Hierarchy
    SET @LastChild = LastChild = HId.GetDescendant(LastChild, NULL)
    WHERE HId = @ParentID

    INSERT Hierarchy (HId, ...)
    VALUES (@LastChild, ...)
COMMIT

If you generate the ids this way, rest assured you'll never have to worry about running out.

For curiosity's sake, I ran a quick test to find out for sure how deep you can go. Here's a test script:

DECLARE
    @parent hierarchyid,
    @child hierarchyid,
    @high hierarchyid,
    @cnt int

SET @parent = '/1/'
SET @child = @parent.GetDescendant(NULL, NULL)
SET @cnt = 0

WHILE (@@ERROR = 0)
BEGIN
    SET @cnt = @cnt + 1
    PRINT CAST(@cnt AS varchar(10)) + ': ' + @child.ToString()
    SET @high = @parent.GetDescendant(@child, @high)
    SET @child = @parent.GetDescendant(@child, @high)
END

You can see it error out at a point-nesting level of 1426, so that's your worst-case limit for how many "in-between" nodes you can create, worst case meaning that every single insertion goes in between the two most deeply-nested nodes.

As I mentioned in the comments, it's pretty hard to hit this limit, but that still doesn't make it a good idea to try. The actual byte length gets longer as longer as you use up more and more "points", which degrades performance. If the hierarchyid is your clustered index, this will kill performance by page splits. If you're trying to rank nodes by parent then use a ranking column instead; it's easier and more efficient to sort from a later SELECT than it is to do during your INSERT where you have to worry about transaction isolation and other such headaches.

回复收藏 0 原文

~没有更多了~