MySQL主键：UUID / GUID vs BIGINT（时间戳+随机数）

Question

tl;dr：如果我不想处理UUID，是否将行ID分配为{unixtimestamp} {randomdigits}（例如1308022796123456）作为BIGINT是一个好主意？

想知道是否有任何与在多个服务器上为数据库记录分配ID / PRIMARY KEY相关的性能或其他技术考虑/限制的见解。

我的PHP + MySQL应用程序在多个服务器上运行，数据需要能够合并。因此，我已经超越了标准的顺序/自动增量整数方法来标识行。

我的研究带我到使用UUID / GUID的概念。但是，需要修改我的代码以处理在MySQL中将UUID字符串转换为二进制值似乎有点麻烦/工作量大。出于存储和性能原因，我不想将UUID存储为VARCHAR。

存储在二进制列中的UUID的另一个可能烦恼是，在查看PhpMyAdmin中的数据时，行ID并不明显 - 尽管我可能是错的 - 但是直接数字在任何类型的数据库系统中似乎更简单，并且不需要转换。

作为一个折中方案，我想到了使用BIGINT使我的ID列，并使用当前Unix时间戳后跟6个随机数字分配ID。因此，假设我的随机数字为123456，今天生成的ID将为：1308022796123456

对于在同一秒内创建的行冲突的一千万分之一的机会对我来说还可以接受。我不会快速进行任何大量行的创建。

我读到的有关随机生成的UUID的一个问题是它们对索引不利，因为值不是按顺序排列的（它们遍布在各处）。 MySQL中的UUID（）函数通过从当前时间戳生成UUID的第一部分来解决这个问题。因此，我复制了使用BIGINT开头的Unix时间戳的想法。我的索引会很慢吗？

我的BIGINT想法的优点：

• 给我UUID的多服务器/合并优势
• 对我的应用程序代码要求很少（已经编程处理ID整数）
• 相比UUID使用的存储空间减半（8个字节 vs 16个字节）

缺点：

• ??? - 如果您能想到，请告诉我。

以下是一些相关问题：

1. 在结尾使用6个随机数字是否太多或太少？ 这会影响索引性能吗？

2. 这两种方法中哪一个更“随机”？：让PHP生成6位数字并将它们连接在一起-VS-让PHP生成1-999999范围内的数字，然后填充0以确保有6位数。

感谢任何提示。 对于长篇文字，表示抱歉。

- LaVache

---

我的问题在这里提出了类似的事情，可能会为您提供更多有关所涉及考虑因素的见解。 - Timo

你应该查看生日悖论来了解在给定生成值数量和阈值下，需要多少随机位才能保持碰撞的几率低。举个例子，如果你有16位（真正的！）随机数，并创建12个值，那么你已经有约0.001％的机会至少发生一次碰撞。换句话说，如果你想生成许多值，如果你希望期望唯一性，你需要相当多的位数。 - Timo

使用GUID作为唯一索引，但是还要计算GUID的64位（BIGINT）哈希值，将其存储在一个单独的非唯一列中，并对其进行索引。检索时，查询两个列的匹配项 - 64位索引应该使此过程更有效率。 - ToolmakerSteve

Answer 1

我在职业生涯中曾遇到这个问题。我们使用时间戳+随机数，并在应用程序扩展（更多客户端、更多服务器、更多请求）时遇到了严重问题。尽管我们（愚蠢地）只使用了4位数字，然后改为6位，但你会惊讶地发现错误仍然经常发生。

长时间以后，你将“保证”会出现重复的关键字错误。我们的应用程序是使命关键型的，因此即使有微小的可能性由于内在的随机行为而导致失败也是不可接受的。我们开始使用UUID来避免这个问题，并仔细管理它们的创建。

使用UUID，你的索引大小将增加，而较大的索引将导致性能下降（可能无法察觉，但还是更差）。然而MySQL支持本地UUID类型（永远不要使用varchar作为主键！！），并且可以相当高效地处理索引、搜索等问题，甚至与bigint相比也不差。你的索引所受到的最大性能损失几乎总是索引的行数，而不是被索引的项目的大小（除非你想在longtext或其他荒谬的项目上进行索引）。

回答你的问题：如果你不打算大规模扩展你的应用程序/服务，那么带有随机数的bigint将是可以接受的。如果你的代码几乎不需要修改就能处理这个变化，且如果出现重复键错误，你的应用程序不会崩溃，那么就使用它吧。否则，就勇敢地去做更实质性的选择。

你总是可以稍后实施更大的变化，比如切换到完全不同的后端（我们现在正在面对这个问题...:P）。

- Colin

---

感谢您的回复，Colin。您的冲突问题只发生在以下情况吗：(1) 您尝试从多个服务器合并数据；或者 (2) 应用程序在当前服务器上创建时没有检查重复项？为了解决问题1，可以使用 {时间戳}{服务器ID}{随机数} 的格式来解决。问题2可以通过让代码在当前服务器上创建新的ID来解决，如果出现重复错误（我想即使使用UUID，应用程序也应该这样做）。 - LaVache

据我所知，MySQL没有UUID列类型，仅有生成值的函数。你将UUID存储在哪种类型的列中？大多数论坛帖子推荐使用BINARY(16)或BINARY(36)。出于好奇，你要转移到哪个数据库，并且为什么要这样做？ - LaVache

我们使用二进制(16)，实际上我们创建和管理UUID的方式与bigint(dttmstamp + rand)的想法基本相同，尽管我们在rand部分做了更多的工作，它基本上是一个16字节的数字而不是8字节。我们正在研究Hadoop/HDFS，因为我们的问题在MapReduce风格/上下文中解决起来相当容易，并且它应该有助于可扩展性。 - Colin

当您只使用4个随机数字时，您的平均行创建速率大约是多少？ - LaVache

出于好奇，这样的问题是否更适合程序员的堆栈交换平台？我以前在这里问过类似的问题，结果被踩和灰心丧气。 - Amogh Talpallikar

十年前...但是，你仍然拯救了我的未来。 - Muhamad Rafi Pamungkas

Answer 2

您可以手动更改自动编号的起始数字。

ALTER TABLE foo AUTO_INCREMENT = ####

一个无符号整数可以存储最多 4,294,967,295，我们将其减少到 4,290,000,000。

使用前三位数字作为服务器序列号，最后七位数字作为行 ID。

这样你就可以拥有最多 430 台服务器（包括 000），每个服务器最多有 1000 万个 ID。

因此，对于服务器 #172，您需要手动更改自动编号以从 1,720,000,000 开始，然后让它按顺序分配 ID。

如果您认为您可能会有更多的服务器，但每个服务器的 ID 较少，则应将其调整为每个服务器 4 位数字和 ID 6 位数字（即最多 100 万个 ID）。

您还可以使用二进制数字而不是十进制数字来分割数字（例如，每个服务器使用 10 个二进制数字，ID 使用 22 个二进制数字。因此，例如，服务器 76 从 2^22*76 = 318,767,104 开始，结束于 322,961,407）。

实际上，您甚至不需要明确的分割。将 4,294,967,295 除以您认为您将拥有的最大服务器数量，那就是您的间距。

如果您认为需要更多标识符，则可以使用 bigint，但那是一个非常庞大的数字。

- Ariel

---

谢谢您的回复。我之前确实考虑过这个选项，但是意识到当将数据合并到更低的 server_id 服务器时可能会出现问题。假设我有一堆服务器，包括编号为 160 和 170 的服务器。如果我将来自 170 号服务器的行导入到 160 号服务器上，表的 auto_increment 值将被设置为表中最高的 ID 号码。因此，160 号服务器无法继续分配在 160xxxxxx 范围内的 ID。我刚刚进行了一次小实验，虽然 MySQL 接受了将 auto_increment 值改回低值的命令，但没有生效。 - LaVache

你需要制定一个规则，一旦合并数据，旧服务器就会获得一个新的编号。然后，新服务器将获得较高的服务器编号（或者也可以给它一个新的编号）。你应该编写代码，使更改服务器编号非常容易。 - Ariel

Answer 3

使用GUID作为唯一索引，但同时计算GUID的64位（BIGINT）哈希值，将其存储在单独的非唯一列中，并对其进行索引。要检索，请查询两个列的匹配项-64位索引应使此过程高效。

这样做的好处是哈希值：
a. 不必是唯一的。
b. 很可能分布良好。

成本：额外的8字节列及其索引。

- ToolmakerSteve

Answer 4

如果您想使用时间戳方法，请按照以下步骤操作：

为每个服务器分配一个编号，然后附加执行插入的应用程序的进程ID（或线程ID）（在PHP中是getmypid()），然后附加该进程已经运行/活动了多长时间（在PHP中是getrusage()），最后添加一个计数器，在每个脚本调用的开始处从0开始（即在同一脚本中的每个插入都会将其增加1）。

此外，您不需要存储完整的Unix时间戳-大部分数字用于表示它是2011年而不是1970年。因此，如果您无法获取表示进程运行时间的数字，则至少减去代表今天的固定时间戳-这样您将需要更少的数字。

- Ariel

---

这会增加多年来发生碰撞的可能性。 - aredridel

@Aredridel 不太确定你的意思。它不会除或四舍五入，而是减去一个固定的数字。 - Ariel

啊，这很有道理。将纪元重置为更接近现在的时间。 - aredridel