MySQL中INT和UUID之间的区别 [英] The differences between INT and UUID in MySQL

问题描述

如果将主键设置为INT类型(AUTO_INCREMENT)或在UUID中设置，这两者在数据库性能上有什么区别(SELECT ，INSERT等)，为什么?

If I set the primary key to be INT type (AUTO_INCREMENT) or set it in UUID, what is the difference between these two in the database performance (SELECT, INSERT etc) and why?

推荐答案

UUID 返回 通用唯一标识符 (如果也导入到另一个数据库，希望也是唯一的.)

UUID returns a universal unique identifier (hopefuly also unique if imported to another DB as well).

要引用MySQL文档(重点是我的话):

To quote from MySQL doc (emphasis mine):

UUID被设计为一个在全球范围内在全球范围内唯一的数字， 时间.两次调用UUID()会产生两个不同的结果 值，即使这些调用是在两台单独的计算机上执行的，也 彼此没有连接.

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

另一方面，简单的INT主ID密钥(例如 AUTO_INCREMENT )将为特定的DB和DB表返回一个唯一整数，但是不是世界上唯一的(因此，如果导入到另一个数据库，则可能存在主键冲突).

On the other hand a simply INT primary id key (e.g. AUTO_INCREMENT) will return a unique integer for the specific DB and DB table, but which is not universally unique (so if imported to another DB chances are there will be primary key conflicts).

在性能方面，使用auto-increment与UUID不应有任何明显的区别.多数帖子(包括本网站作者的某些帖子)均按原样陈述.当然，UUID可能会花费更多的时间(和空间)，但是对于大多数(如果不是全部)情况，这并不是性能瓶颈.将列设为Primary Key应该使两个选择都等同于性能.请参阅下面的参考:

In terms of performance, there shouldn't be any noticeable difference using auto-increment over UUID. Most posts (including some by the authors of this site), state as such. Of course UUID may take a little more time (and space), but this is not a performance bottleneck for most (if not all) cases. Having a column as Primary Key should make both choices equal wrt to performance. See references below:

1. 到UUID或不UUID?
2. 神话，GUID与Autoincrement
3. 性能:cakephp-mysql中的UUID vs auto-increment
4. UUID在MySQL中的性能?
5. 主键:ID s与GUID s(编码恐怖) )

1. To UUID or not to UUID?
2. Myths, GUID vs Autoincrement
3. Performance: UUID vs auto-increment in cakephp-mysql
4. UUID performance in MySQL?
5. Primary Keys: IDs versus GUIDs (coding horror)

(UUID与auto-increment的性能结果，改编自神话，GUID与Autoincrement )

(UUID vs auto-increment performance results, adapted from Myths, GUID vs Autoincrement)

UUID优点/缺点(改编自主键:ID s与GUID s )

UUID pros / cons (adapted from Primary Keys: IDs versus GUIDs)

GUID优点

GUID Pros

• 在每个表，每个数据库，每个服务器上都是唯一的
• 允许轻松合并来自不同数据库的记录
• 允许在多个服务器之间轻松分配数据库
• 您可以在任何位置生成ID，而不必往返于数据库
• 大多数复制方案仍然需要GUID列

• Unique across every table, every database, every server
• Allows easy merging of records from different databases
• Allows easy distribution of databases across multiple servers
• You can generate IDs anywhere, instead of having to roundtrip to the database
• Most replication scenarios require GUID columns anyway

GUID缺点

GUID Cons

• 比传统的4字节索引值大4倍；如果这会严重影响性能和存储 你不小心
• 调试麻烦(where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
• 生成的GUID应该是部分顺序的，以实现最佳性能(例如，SQL 2005中的newsequentialid())并允许使用 聚簇索引.

• It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
• Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
• The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes.

我会仔细阅读提到的参考资料，并根据我的用例决定是否使用UUID.也就是说，在许多情况下UUID确实是更好的选择.例如，可以完全不使用/访问数据库的情况下生成UUID，甚至可以使用已预先计算和/或存储在其他位置的UUID.另外，您可以轻松地概括/更新数据库架构和/或群集方案，而不必担心ID中断并引起冲突.

I would read carefully the mentioned references and decide whether to use UUID or not depending on my use case. That said, in many cases UUIDs would be indeed preferable. For example one can generate UUIDs without using/accessing the database at all, or even use UUIDs which have been pre-computed and/or stored somewhere else. Plus you can easily generalise/update your database schema and/or clustering scheme without having to worry about IDs breaking and causing conflicts.

这篇关于MySQL中INT和UUID之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！