唯一标识符(guid)作为数据库设计中的主键

2021-12-26 00:00:00 sql-server database-design

我们的数据位于 SQL Server 2008 数据库中，表之间会有很多查询和连接.我们团队内部有这个论点，有些人认为使用整数标识对性能更好，有些人争论使用 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).

使用 GUID 作为主键的性能真的会受到如此严重的影响吗?

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

推荐答案

128 位 GUID (uniqueidentifier) 键当然比 32 位 int 键.但是，有一些关键优势:

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

  • 合并内容时没有IDENTITY INSERT"问题
  • 如果您使用 COMB 值而不是 NEWSEQUENTIALID()，您将获得一个免费"的 INSERT 时间戳.如果您需要一些花哨的 CAST() 调用，您甚至可以根据日期/时间范围从主键中 SELECT.
  • 它们在全球范围内都是独一无二的，结果证明它时不时地非常方便.
  • 由于不需要跟踪高水位线，您的 BL 层可以分配值而不是 SQL Server，从而省去了 SELECT scope_identity() 在插入后获取主键的步骤.
  • 如果您可能有超过 20 亿条记录，那么您将需要使用 bigint(64 位)而不是 int.一旦你这样做了，uniqueidentifier 只是 bigint 的两倍.
  • 使用 GUID 可以更安全地公开 URL 等中的密钥，而不会让您遭受猜测 ID"攻击.
  • 在 SQL Server 如何从磁盘加载页面和处理器现在大多是 64 位之间，仅仅因为数字是 128 位而不是 32 并不意味着比较需要 4 倍的时间.我看到的最后一个测试表明 GUID 几乎一样快.
  • 索引大小取决于包含的多少列.尽管 GUID 本身更大，但与索引中的其他列相比，额外的 8 或 12 个字节可能微不足道.
  • 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.

最后，通过使用整数来挤出一些小的性能优势可能不值得失去 GUID 的优势.凭经验对其进行测试并自行决定.

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.

相关文章