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屋！