具有群集GUID PK的SQL Server数据库-切换群集索引还是切换到顺序(梳状)GUID? [英] SQL Server database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUIDs?

问题描述

我们有一个数据库，其中所有PK都是GUID，并且大多数PK也是表的聚集索引.我们知道这很不好(由于GUID的随机性).因此，这里似乎基本上有两个选择(除了将GUID完全丢弃为PK，我们不能做(至少在目前还不能这样做)).

We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out GUIDs as PKs altogether, which we cannot do (at least not at this time)).

• 我们可以将GUID生成算法更改为例如NHibernate使用的一个，如此帖子中所述，或
• 对于使用最频繁的表，我们可以更改为其他聚簇索引，例如IDENTITY列，并将随机" GUID保留为PK.

• We could change the GUID generation algorithm to e.g. the one that NHibernate uses, as detailed in this post, or
• we could, for the tables that are under the heaviest use, change to a different clustered index, e.g. an IDENTITY column, and keep the "random" GUIDs as PKs.

在这种情况下是否可以提出任何一般性建议?

Is it possible to give any general recommendations in such a scenario?

有问题的应用程序有500多个表，目前最大的一个表大约有150万行，一些表大约有50万行，其余的则显着降低(大多数都远低于10K).

The application in question has 500+ tables, the largest one presently at about 1,5 million rows, a few tables around 500 000 rows, and the rest significantly lower (most of them well below 10K).

此外，该应用程序已经安装在多个客户站点上，因此我们必须考虑对现有客户的任何可能的负面影响.

Furthermore, the application is installed at several customer sites already, so we have to take any possible negative effects for existing customer into consideration.

谢谢！

推荐答案

如果您能够轻松地将GUID一代更改为顺序GUID一代，那么这可能是您的快速选择.顺序guid将停止表的碎片化，同时保留为聚集索引.但是，具有顺序guid的主要缺点是，它们随后变得可猜测，这通常是不希望的，并且首先使用guid的原因.

If you are able to change your guid generation to a sequential guid generation easily then that is probably your quick win option. The sequential guid will stop the fragmentation on the table whilst remaining as your clustered index. The major downside with a sequential guid though is that they then become guessable which is often not desired and the reason guids are used in the first place.

如果沿着集群主键的身份"路由，然后在guid列上只有一个索引，那么您的guid索引上仍然会出现很多碎片.但是，该表将不再分散的事实将是一个巨大的收获.

If you go down the Identity route for your clustered primary key and then just an index on your guid column then you will still get a lot of fragmentation on your guid index. However the fact that the table will no longer get fragmented will be a massive gain.

最后，我知道您说您暂时不能这样做，但是，如果您根本不需要使用guid作为索引，那么您就可以消除所有这些问题.

Finally though, I know you said you can't do this for now, but, if you don't NEED to use guids as an index at all then you remove all of these problems.

这篇关于具有群集GUID PK的SQL Server数据库-切换群集索引还是切换到顺序(梳状)GUID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！