我应该担心用完HierarchyIDs吗? [英] Should I worry about running out of HierarchyIDs?

问题描述

当您要求其他两个对象之间使用新的HierarchyID时，结果将逐渐变长.例如，在2/5.6和2/5.7之间，只有2/5.6.1和其他4个组件路径. HierarchyID数据类型限制为800个字节，因此您不能永远重复此操作.再说一次，整数类型也受到限制，但实际上这不是问题.我是否应该定期对桌子进行碎片整理，以使高度不会变得无边无际?

When you ask for a new HierarchyID between two others, the result gets progressively longer. For example, between 2/5.6 and 2/5.7 there's only 2/5.6.1 and other 4 component paths. The HierarchyID data type is limited to 800 some bytes, so you can't repeat this forever. Then again, integer types are also limited, but it isn't a problem in practice. Should I periodically defragment my table so that height doesn't grow unbounded?

推荐答案

使用hierarchyid来附加"新ID被认为是最佳实践"，这样您就不必使用介于中间的状态(例如/2/5.6/).如果您的hierarchyid是集群主键，那么这会降低性能，它将导致页面拆分，类似于uniqueidentifier的方式.

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.

此处是一个说明您如何期望的示例生成hierarchyid值:

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

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

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

您会看到它在点嵌套级别1426上出错，因此这是您可以创建多少个中间"节点的最坏情况限制，最坏情况意味着单个插入位于两个最深的嵌套节点之间.

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.

正如我在评论中提到的，要达到这个极限非常困难，但这仍然不是尝试的好主意.随着您使用越来越多的点"，实际的字节长度会越来越长，这会降低性能.如果hierarchyid是您的聚集索引，则将通过页面拆分 kill 性能.如果您尝试按父节点对节点进行排名，请使用排名列；与在以后的INSERT中进行排序相比，和在以后的SELECT中进行排序要容易得多，并且效率更高，在这种情况下，您不得不担心事务隔离和其他类似的麻烦.

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.

这篇关于我应该担心用完HierarchyIDs吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！