GUID / UUID数据库键的优点和缺点 [英] Advantages and disadvantages of GUID / UUID database keys
问题描述
过去,我曾经从事过许多数据库系统工作,如果所有的数据库密钥都已经被 GUID / UUID 值。我已经考虑过几次这个路径,但总是有一些不确定性,特别是在性能和未读出的可通过电话的URL。
I've worked on a number of database systems in the past where moving entries between databases would have been made a lot easier if all the database keys had been GUID / UUID values. I've considered going down this path a few times, but there's always a bit of uncertainty, especially around performance and un-read-out-over-the-phone-able URLs.
有没有人在数据库中与GUID广泛合作?
Has anyone worked extensively with GUIDs in a database? What advantages would I get by going that way, and what are the likely pitfalls?
推荐答案
优点:
- 可以离线生成。
- 使复制变得轻而易举(与int不同,这使得它非常难)
- ORM通常喜欢
- 独特的应用程序。因此,我们可以在我们的应用程序(也是guid)中使用我们的CMS(guid)中的PK,并且知道我们永远不会产生冲突。
- Can generate them offline.
- Makes replication trivial (as opposed to int's, which makes it REALLY hard)
- ORM's usually like them
- Unique across applications. So We can use the PK's from our CMS (guid) in our app (also guid) and know we are NEVER going to get a clash.
缺点:
- 更大的空间使用,但空间便宜(呃)
- 在网址中看起来很丑陋,但真的,WTF正在将一个REAL DB键放入网址!
- 很难做手动调试,但不是那么难。
- Larger space use, but space is cheap(er)
- Can't order by ID to get the insert order.
- Can look ugly in a URL, but really, WTF are you doing putting a REAL DB key in a URL!?
- Harder to do manual debugging, but not that hard.
我个人使用它们用于大多数PK任何系统的体面的大小,但我得到了训练在一个系统,复制了所有的地方,所以我们HAD有他们。 YMMV。
Personally, I use them for most PK's in any system of a decent size, but I got "trained" on a system which was replicated all over the place, so we HAD to have them. YMMV.
我认为重复数据的东西是垃圾 - 你可以得到重复的数据,但你这样做。代理键通常在我一直在工作的地方皱起眉头。我们使用类似WordPress的系统虽然:
I think the duplicate data thing is rubbish - you can get duplicate data however you do it. Surrogate keys are usually frowned upon where ever I've been working. We DO use the WordPress-like system though:
- 行的唯一ID(GUID /任何)。
- 公开ID是从某些字段生成的(例如标题 - 将其称为标题)
UPDATE:
所以这一个获得了很多+1,我想我应该指出一个大的缺点GUID PK's:Clustered Indexes。
UPDATE: So this one gets +1'ed a lot, and I thought I should point out a big downside of GUID PK's: Clustered Indexes.
如果你有很多记录,并且在GUID上有一个聚集索引,你的插入性能将SUCK,
If you have a lot of records, and a clustered index on a GUID, your insert performance will SUCK, as you get inserts in random places in the list of items (thats the point), not at the end (which is quick)
所以如果你需要插入性能,也许使用一个auto-inc INT,如果您想与其他人共享(即向网址中的用户显示),则生成GUID
So if you need insert performance, maybe use a auto-inc INT, and generate a GUID if you want to share it with someone else (ie, show it to a user in a URL)
这篇关于GUID / UUID数据库键的优点和缺点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!