唯一标识符（guid）作为数据库设计的主要关键 [英] Unique identifier (guid) as primary key in database design

问题描述

我们的数据驻留在SQL Server 2008数据库中，表之间将有很多查询和连接。我们在团队内部有这样一个争论，有些正在争论使用整数身份更好的表现，有些则争论使用guid（唯一标识符）。

性能真的受到严重的使用GUID作为主键吗？

解决方案

128位GUID（ uniqueidentifier ）键当然比32位 int大4倍>键。但是，有一些关键的优点：

• 合并内容时没有IDENTITY INSERT问题


• 如果使用COMB值而不是NEWSEQUENTIALID（），则会得到空闲INSERT时间戳。您可以根据日期/时间范围从主键中选择 SELECT ，如果您想要几个花式 CAST（）电话。


• 他们是全球独一无二的，现在证明是非常方便的。


• 由于没有必要跟踪高水标记，您的BL层可以分配值而不是SQL Server，因此消除了插入后获取主键的 SELECT scope_identity（）的步骤。


• 如果您甚至可能有超过20亿条记录，您将需要使用 bigint （64位）而不是 int 。一旦你这样做， uniqueidentifier 只是一个 bigint 的两倍。


• 使用GUID可以更方便地在URL等中公开密钥，而不会使自己遭受guess-the-ID攻击。


• SQL Server如何从磁盘加载页面以及处理器现在大多是64位，只是因为一个数字是128位而不是32并不意味着比较需要4倍的时间。我看到的最后一次测试显示，GUID几乎一样快。


• 索引大小取决于包含多少个列。即使GUID本身较大，与索引中的其他列相比，额外的8或12个字节可能不显着。

最后，通过使用整数来挤出一些小的性能优势可能不值得失去GUID的优点。根据情况，我根据情况进行测试，但是根据情况，我个人认为，这个决定因素从来没有真的下降到我的情况。

Our data resides in a SQL Server 2008 database, there will be a lot queries and joinings between tables. We have this argument inside the team, some are arguing use of integer identity is better for performance, some are arguing use of guid (unique identifier).

Does the performance really suffer that badly using a GUID as a primary key?

解决方案

A 128-bit GUID (uniqueidentifier) key is of course 4x larger than a 32-bit int key. However, there are a few key advantages:

• No "IDENTITY INSERT" issue when merging content
• If you use a COMB value instead of NEWSEQUENTIALID(), you get a "free" INSERT timestamp. You can even SELECT from the primary key based on a date/time range if you want with a few fancy CAST() calls.
• They are globally unique, which turns out to be pretty handy now and then.
• Since there's no need to track high-water marks, your BL layer can assign the value rather than SQL Server, thus eliminating the step of SELECT scope_identity() to get the primary key after an insert.
• If it's even remotely possible that you could have more than 2 billion records, you'll need to use bigint (64 bits) instead of int. Once you do that, uniqueidentifier is only twice as big as a bigint.
• Using GUIDs makes it safer to expose keys in URLs, etc. without exposing yourself to "guess-the-ID" attacks.
• Between how SQL Server loads pages from disk and how processors are now mostly 64-bit, just because a number is 128 bits instead of 32 doesn't mean it takes 4x longer to compare. The last test I saw showed that GUIDs are nearly as fast.
• Index size depends on how many columns are included. Even though the GUIDs themselves are larger, the extra 8 or 12 bytes may be insignificant compared to the other columns in the index.

In the end, squeezing out some small performance advantage by using integers may not be worth losing the advantages of a GUID. Test it empirically and decide for yourself.

Personally, I still use both, depending on the situation, but the deciding factor has never really come down to performance in my case.

这篇关于唯一标识符（guid）作为数据库设计的主要关键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！