使用 INT 或 GUID 作为主键 [英] Using INT or GUID as primary key

问题描述

我试图在 SQL Server VB.net 中创建一个 ID 列，该列将为数据库中创建的每个新行提供一个数字序列.所以我使用以下技术来创建 ID 列.

I was trying to create an ID column in SQL server, VB.net that would provide a sequence of numbers for every new row created in a database. So I used the following technique to create the ID column.

select * from T_Users
ALTER TABLE T_Users     
ADD User_ID INT NOT NULL IDENTITY(1,1) Primary Key

然后我在数据库中注册了几个用户名，它工作得很好.例如，前六行是 1、2、3、4、5、6.然后我第二天又注册了4个用户，但是这次ID号从6个跳到了一个非常大的数字，例如:1、2、3、4、5、6、1002、1003、1004、1005.然后两天后，我又注册了两个用户，新行显示为 3002,3004.所以我的问题是为什么每隔一天我注册用户就会跳过这么大的数字.我用来创建序列的技术是错误的吗?如果它是错的，任何人都可以告诉我如何做对吗?现在，当我对上面使用的技术感到沮丧时，我尝试使用顺序生成的 GUID 值.GUID 值的序列生成得很好.但是，唯一的缺点是，它会生成很长的数字(INT 大小的 4 倍).我的问题是使用 GUID 是否比 INT 有任何显着优势?

Then I registered few usernames into the database and it worked just fine. For example the first six rows would be 1,2,3,4,5,6. Then I registered 4 more users the NEXT day, but this time the ID numbers jumped from 6 to A very large number such as: 1,2,3,4,5,6,1002,1003,1004,1005. Then two days later, I registered two more users and the new rows read 3002,3004. So my question is why is it skipping such a large number every other day I register users. Is the technique I used to create the sequence wrong? If it is wrong can anyone please tell me how to do it right? Now as I was getting frustrated with the technique used above, alternatively I tried to use sequentially generated GUID values. The sequence of GUID values were generated fine. However, the only downside is, it generates a very long numbers (4 times the INT size). My question here is does using GUID have any significant advantage over INT?

问候，

推荐答案

GUID 的好处:

如果您希望离线客户端能够创建新记录，GUID 非常有用，因为当新记录同步回主数据库时，您永远不会遇到主键冲突.

GUIDs are good if you ever want offline clients to be able to create new records, as you will never get a primary key clash when the new records are synchronised back to the main database.

GUID 的缺点:

GUIDS 作为主键会对数据库的性能产生影响，因为对于集群主键，数据库希望按键值的顺序保留行.但这意味着在现有记录之间进行大量插入，因为 GUID 将是随机的.

GUIDS as primary keys can have an effect on the performance of the DB, because for a clustered primary key, the DB will want to keep the rows in order of the key values. But this means a lot of inserts between existing records, because the GUIDs will be random.

使用 IDENTITY 列不会受此影响，因为保证下一条记录具有最高值，因此每次都将行添加到末尾.无需重新洗牌.

Using IDENTITY column doesn't suffer from this because the next record is guaranteed to have the highest value and so the row is just tacked on the end every time. No re-shuffle needs to happen.

有一个折衷方案是生成一个伪 GUID，这意味着您预计每 70 年左右就会发生一次密钥冲突，但对索引编制有很大帮助.

There is a compromise which is to generate a pseudo-GUID which means you would expect a key clash every 70 years or so, but helps the indexing immensely.

其他缺点是 a) 它们确实占用了更多的存储空间，并且 b) 编写 SQL 确实很痛苦，即更容易键入 UPDATE TABLE SET FIELD = 'value' where KEY = 50003 比 UPDATE TABLE SET FIELD = 'value' where KEY = '{F820094C-A2A2-49cb-BDA7-549543BB4B2C}'

The other downsides are that a) they do take up more storage space, and b) are a real pain to write SQL against, i.e. much easier to type UPDATE TABLE SET FIELD = 'value' where KEY = 50003 than UPDATE TABLE SET FIELD = 'value' where KEY = '{F820094C-A2A2-49cb-BDA7-549543BB4B2C}'

您对 IDENTITY 列的声明在我看来很好.键值中的差距可能是由于尝试添加行失败所致.IDENTITY 值将增加，但该行永远不会被提交.不要让它打扰你，它几乎发生在每张桌子上.

Your declaration of the IDENTITY column looks fine to me. The gaps in your key values are probably due to failed attempts to add a row. The IDENTITY value will be incremented but the row never gets committed. Don't let it bother you, it happens in practically every table.

这个问题涵盖了我所说的伪 GUID 的含义.在聚集索引上使用顺序 GUID 键的 INSERT 不显着更快

This question covers what I was meaning by pseudo-GUID. INSERTs with sequential GUID key on clustered index not significantly faster

在 SQL Server 2005+ 中，您可以使用 NEWSEQUENTIALID() 获得一个随机值，该值应该大于以前的值.请参阅此处了解更多信息 http://technet.microsoft.com/en-us/library/ms189786%28v=sql.90%29.aspx

In SQL Server 2005+ you can use NEWSEQUENTIALID() to get a random value that is supposed to be greater than the previous ones. See here for more info http://technet.microsoft.com/en-us/library/ms189786%28v=sql.90%29.aspx

这篇关于使用 INT 或 GUID 作为主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！