c# - 在 SQL Server 中使用 GUID 的性能影响

Question

我在询问之前尝试过搜索这个，但我发现的每个结果都提到 GUID 作为 PK，但这里不是这种情况。

我有一个数据库，它使用 INT 作为所有表的 PK。然而，数据是通过 API 调用访问的，并且要求不在任何 API 中返回或使用 INT 值。因此，我想在包含 GUID 的表上增加一列。

现在我的问题是，如果我为 GUID 列编制索引，这会对性能产生什么样的影响？它是积极的还是消极的？请记住，GUID 不是 PK 或 FK。

Answer 1

我认为你在正确的轨道上，但不要从我这里拿走......

在 Kimberly Tripp 的一篇文章的评论部分，她回应了与您的立场相反的评论，她不同意并争论您提出的相同解决方案(非聚集索引 guid 与聚集 int/bigint 主键)。

Herman:

If the GUID is the intrinsic identifier for the entity being modelled (i.e. used by selects) then it should be the clustered primary key without question. The reason is that adding a surrogate identity key (of int or bigint) and demoting the GUID primary key to a column with an index/unique constraint requires 2 indexes to be maintained and slows down, in my experience, by a factor of 2.

---

Kimberly Tripp

Hey there Herman – Actually, I disagree. For point-based queries using a nonclustered index does not add a significant amount of costly IOs. And, the maintenance of a nonclustered index that’s heavily fragmented is a lot cheaper than the required maintenance on a heavily fragmented clustered index. Additionally, the GUID might make your nonclustered indexes unnecessarily wide – making them take: more log space, more disk space, more cache as well as adding time on insert and access (especially in larger queries/joins).

So, while you might not feel like an arbitrary/surrogate key is useful (because you never directly query against it) it can be incredibly efficient to use indirectly through your nonclustered indexes. There’s definitely an element of “it depends” here but if you have even just a few nonclustered indexes then it’s likely to be more beneficial than negative and often significantly so.

Cheers,
kt ~ GUIDs as PRIMARY KEYs and/or the clustering key - Kimberly L. Tripp