UniqueIdentifier 聚集索引上的 NewSequentialId [英] NewSequentialId on UniqueIdentifier Clustered Index

问题描述

我正在为我公司正在启动的新数据库制定数据库标准.我们试图定义的一件事是与 UniqueIdentifiers 相关的主键和聚集索引规则.

I am working on database standards for a new database my company is starting. One of the things we are trying to define is Primary Key and Clustered Index rules in relation to UniqueIdentifiers.

(注意:我不想讨论使用 UniqueIdentifier 作为主键或聚集索引的利弊.网络上有大量关于此的信息.这不是 那个讨论.)

(NOTE: I do not want a discussion on the pros and cons of using a UniqueIdentifier as a primary key or clustered index. There is a ton of info on the web about that. This is not that discussion.)

所以这是让我担心的场景:

So here is the scenario that has me worried:

假设我有一个表，它有一个 UniqueIdentifier 作为聚集索引和主键.让我们称之为可乐.我将 ColA 的默认值设置为 NewSequentialId().

Say I have a table with a UniqueIdentifier as the clustered index and primary key. Lets call it ColA. I set the default value for ColA to be NewSequentialId().

使用 NewSequentialId() 我插入三个连续的行:

Using that NewSequentialId() I insert three sequential rows:

{72586AA4-D2C3-440D-A9FE-CC7988DDF065}
{72586AA4-D2C3-440D-A9FE-CC7988DDF066}
{72586AA4-D2C3-440D-A9FE-CC7988DDF067}

{72586AA4-D2C3-440D-A9FE-CC7988DDF065}
{72586AA4-D2C3-440D-A9FE-CC7988DDF066}
{72586AA4-D2C3-440D-A9FE-CC7988DDF067}

然后我重新启动我的服务器.NewSequentialId 的文档 说重新启动 Windows 后，GUID 可以启动再次来自较低的范围，但仍然是全球唯一的."

Then I reboot my server. The docs for NewSequentialId say that "After restarting Windows, the GUID can start again from a lower range, but is still globally unique."

所以下一个起点可以低于上一个范围.

So the next starting point can be lower than the previous range.

所以在重新启动后，我又插入了 3 个值:

So after the restart, I insert 3 more values:

{35729A0C-F016-4645-ABA9-B098D2003E64}
{35729A0C-F016-4645-ABA9-B098D2003E65}
{35729A0C-F016-4645-ABA9-B098D2003E66}

{35729A0C-F016-4645-ABA9-B098D2003E64}
{35729A0C-F016-4645-ABA9-B098D2003E65}
{35729A0C-F016-4645-ABA9-B098D2003E66}

(我不确定该 guid 在数据库中的确切表示方式，但让我们假设，因为这个 guid 以 3 开头，而前一个以 7 开头，所以 3 个比 7 个小".)

(I am not sure exactly how the guid is represented in the database, but lets assume since this one starts with 3 and the previous ones started with 7 that the 3 ones are "smaller" than the 7 ones.)

当您在聚集索引中间执行插入操作时，必须重新映射索引.(至少我的 DBA 是这么告诉我的.)而且每次我重新启动时，我都会冒着让我的新 UniqueIdentifier 范围正好位于其他先前范围中间的风险.

When you do an insert that is in the middle of a clustered index, a remapping of the index has to happen. (At least so my DBA has told me.) And every time I reboot I run the risk of having my new UniqueIdentifier range be right in the middle of other previous ranges.

所以我的问题是:由于下一组 UniqueIdentifiers 将小于上一组，所以每次插入都会导致我的聚集索引混洗吗?

So my question is: Since the next set of UniqueIdentifiers will be smaller than the last set, will every insert cause my clustered index to shuffle?

如果没有，为什么?SQL Server 知道我在使用 NewSequentialId 吗?有什么办法弥补吗?

And if not, why? Does SQL Server know that I am using NewSequentialId? Does it some how compensate for that?

如果没有，那么它怎么知道我接下来要插入什么?也许接下来的一百万次插入将从 3 开始.或者他们可能会从 7 开始.它怎么知道?

If not, then how does it know what I will insert next? Maybe the next million inserts will start with 3. Or maybe they will start with 7. How does it know?

或者它不知道并保持一切井然有序.如果是这种情况，那么一次重新启动可能会严重影响性能.(这让我觉得我需要自己的不受重启影响的自定义 NewSequentialId.)对吗?还是有什么我不知道的魔法?

Or does it not know and just keeps everything in order. If that is the case then one reboot could massively affect performance. (Which makes me think I need my own custom NewSequentialId that is not affected by reboots.) Is that correct? Or is there some magic I am not aware of?

在我的标准中强烈不鼓励将 GUID 作为聚集索引.正如我上面所说，有很多原因表明这是一个坏主意.我想知道这是否是另一个原因.

GUID as a clustered index is strongly discouraged in my standard. As I said above, there are many reasons that this is a bad idea. I am trying to find out if this is another reason why.

推荐答案

通常，您将使用适当的 FILL FACTOR 创建索引，以便在所有页面中为这种情况留出空白空间.也就是说，一旦空白空间被填满，聚集索引就会重新排序.

Normally you will create your indexes with an appropriate FILL FACTOR to leave empty space in all your pages for just such a scenario. That being said, the clustered index does get reordered once the empty space is filled.

我知道您不想讨论使用 GUID 作为聚集键，但这是不推荐做法的原因之一.

I know you don't want to discuss using GUID as a clustered key, but this is one of the reasons that it's not a recommended practice.

将会发生的情况是，您将有越来越多的页面拆分，这将在您不断插入行时导致非常高的碎片水平，并且您将需要以更高的频率重建索引以保持性能线.

What will happen is that you will have an increasing volume of page splits, which will lead to a very high level of fragmentation as you keep inserting rows, and you will need to rebuild your index at a higher frequency to keep performance in line.

有关该主题的完整处理，没有比

For a full treatment on the topic, there's no better source than

金
Tripp 的
博客

附带说明，当您考虑创建自己的 NewSequentialID 创建函数时，您可能遇到了设计问题，应该重新考虑您的计划.

As a side note, when you are considering creating your own NewSequentialID creation function, you probably have a design issue and should reconsider your plan.

这篇关于UniqueIdentifier 聚集索引上的 NewSequentialId的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！