MySQL PRIMARY KEYs：UUID / GUID vs BIGINT（timestamp + random） [英] MySQL PRIMARY KEYs: UUID / GUID vs BIGINT (timestamp+random)

问题描述

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

只是想知道是否有人对于分配给多个服务器上的数据库记录的ID / PRIMARY KEY的任何性能或其他技术注意事项/限制有一些了解。

我的PHP + MySQL应用程序在多个服务器上运行，并且数据需要能够合并。所以我已经超出了识别行的标准的顺序/ auto_increment整数方法。

我对一个解决方案的研究带给我一个使用UUIDs / GUIDs的概念。然而，需要改变我的代码来处理将UUID字符串转换为MySQL中的二进制值似乎有点痛苦/工作。出于存储和性能原因，我不想将UUID存储为VARCHAR。

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

作为一个中间点我想出了想法使我的ID列为BIGINT，并使用当前unix时间戳，后跟6个随机数字分配ID。所以让我说我的随机数字是123456，我今天生成的ID将会出来： 1308022796123456

的冲突在同一秒钟内创建的行很好与我。我不快速做任何种类的质量行创建。

我已经阅读关于随机生成的UUID的一个问题是，它们不适合索引，因为值不是连续的（它们分布在整个地点）。 MySQL中的UUID（）函数通过从当前时间戳生成UUID的第一部分来解决这个问题。因此，我复制了在我的BIGINT开始有unix时间戳的想法。我的索引会慢吗？

我的BIGINT建议的优点：

• 提供UUID的多服务器/合并优点


• 需要对应用程序代码进行很少的更改（一切已编程为处理ID的整数）


• UUID的一半存储空间（8字节对16字节）

缺点：

• ？ - 请让我知道，如果你能想到任何。

有些跟进问题可以解决：

1. 我应该在结束时使用多于还是少于6个随机数字？它会对指数表现产生影响吗？




2. 这些方法之一是什么randomer？：获取PHP生成6位数字并将它们连接在一起-VS-让PHP生成一个数字1 - 999999范围，然后零填充，以确保6位数。

感谢任何提示。对于文字墙很抱歉。

解决方案

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

的时间，您可以确保获得重复的键错误。我们的应用是关键任务，因此即使是最小的机会，它可能失败到固有的随机行为是不可接受的。我们开始使用UUID避免此问题，并仔细管理它们的创建。

使用UUID，您的索引大小将增加，更大的索引将导致更差的性能不明显，但更贫穷，无所谓）。但是MySQL支持本地UUID类型（永远不要使用varchar作为主键!!），并且可以处理索引，搜索等有效，即使与bigint相比。对你的索引的最大的性能几乎总是索引的行数，而不是索引的项目大小（除非你想在一个长文本上索引或者这样可笑的）。

回答你的问题：Bigint（带有随机数字）将确定，如果你不打算扩大你的应用程序/服务显着。如果您的代码可以处理更改，而没有太多的改动，如果发生重复的键错误，您的应用程序不会爆炸，请使用它。

您随时可以实施更大的更改，例如切换到完全不同的后端（我们是现在面临...：P）

tl;dr: Is assigning rows IDs of {unixtimestamp}{randomdigits} (such as 1308022796123456) as a BIGINT a good idea if I don't want to deal with UUIDs?

Just wondering if anyone has some insight into any performance or other technical considerations / limitations in regards to IDs / PRIMARY KEYs assigned to database records across multiple servers.

My PHP+MySQL application runs on multiple servers, and the data needs to be able to be merged. So I've outgrown the standard sequential / auto_increment integer method of identifying rows.

My research into a solution brought me to the concept of using UUIDs / GUIDs. However the need to alter my code to deal with converting UUID strings to binary values in MySQL seems like a bit of a pain/work. I don't want to store the UUIDs as VARCHAR for storage and performance reasons.

Another possible annoyance of UUIDs stored in a binary column is the fact that rows IDs aren't obvious when looking at the data in PhpMyAdmin - I could be wrong about this though - but straight numbers seem a lot simpler overall anyway and are universal across any kind of database system with no conversion required.

As a middle ground I came up with the idea of making my ID columns a BIGINT, and assigning IDs using the current unix timestamp followed by 6 random digits. So lets say my random number came about to be 123456, my generated ID today would come out as: 1308022796123456

A one in 10 million chance of a conflict for rows created within the same second is fine with me. I'm not doing any sort of mass row creation quickly.

One issue I've read about with randomly generated UUIDs is that they're bad for indexes, as the values are not sequential (they're spread out all over the place). The UUID() function in MySQL addresses this by generating the first part of the UUID from the current timestamp. Therefore I've copied that idea of having the unix timestamp at the start of my BIGINT. Will my indexes be slow?

Pros of my BIGINT idea:

• Gives me the multi-server/merging advantages of UUIDs
• Requires very little change to my application code (everything is already programmed to handle integers for IDs)
• Half the storage of a UUID (8 bytes vs 16 bytes)

Cons:

• ??? - Please let me know if you can think of any.

Some follow up questions to go along with this:

1. Should I use more or less than 6 random digits at the end? Will it make a difference to index performance?

2. Is one of these methods any "randomer" ?: Getting PHP to generate 6 digits and concatenating them together -VS- getting PHP to generate a number in the 1 - 999999 range and then zerofilling to ensure 6 digits.

Thanks for any tips. Sorry about the wall of text.

解决方案

I have run into this very problem in my professional life. We used timestamp + random number and ran into serious issues when our applications scaled up (more clients, more servers, more requests). Granted, we (stupidly) used only 4 digits, and then change to 6, but you would be surprised how often that the errors still happen.

Over a long enough period of time, you are guaranteed to get duplicate key errors. Our application is mission critical, and therefore even the smallest chance it could fail to due inherently random behavior was unacceptable. We started using UUIDs to avoid this issue, and carefully managed their creation.

Using UUIDs, your index size will increase, and a larger index will result in poorer performance (perhaps unnoticeable, but poorer none-the-less). However MySQL supports a native UUID type (never use varchar as a primary key!!), and can handle indexing, searching,etc pretty damn efficiently even compared to bigint. The biggest performance hit to your index is almost always the number of rows indexed, rather than the size of the item being index (unless you want to index on a longtext or something ridiculous like that).

To answer you question: Bigint (with random numbers attached) will be ok if you do not plan on scaling your application/service significantly. If your code can handle the change without much alteration and your application will not explode if a duplicate key error occurs, go with it. Otherwise, bite-the-bullet and go for the more substantial option.

You can always implement a larger change later, like switching to an entirely different backend (which we are now facing... :P)

这篇关于MySQL PRIMARY KEYs：UUID / GUID vs BIGINT（timestamp + random）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！