MySQL中的UUID性能? [英] UUID performance in MySQL?

问题描述

我们正在考虑将UUID值用作MySQL数据库的主键.所插入的数据是由数十，数百甚至数千台远程计算机生成的，并且以每秒100-40,000次插入的速度插入，我们将永远不会进行任何更新.

We're considering using UUID values as primary keys for our MySQL database. The data being inserted is generated from dozens, hundreds, or even thousands of remote computers and being inserted at a rate of 100-40,000 inserts per second, and we'll never do any updates.

在我们开始选择数据之前，数据库本身通常会获得大约5000万条记录，因此不是庞大的数据库，但也不是很小的.我们也计划在InnoDB上运行，但是如果有更好的引擎来执行我们的工作，我们可以进行更改.

The database itself will typically get to around 50M records before we start to cull data, so not a massive database, but not tiny either. We're also planing to run on InnoDB, though we are open to changing that if there is a better engine for what we're doing.

我们已经准备好使用Java的Type 4 UUID，但是在测试中已经看到了一些奇怪的行为.一方面，我们将其存储为varchar(36)，但现在我意识到使用Binary(16)会更好-尽管我不确定有多少更好.

We were ready to go with Java's Type 4 UUID, but in testing have been seeing some strange behavior. For one, we're storing as varchar(36) and I now realize we'd be better off using binary(16) - though how much better off I'm not sure.

更大的问题是:当我们有50M条记录时，此随机数据会如何严重破坏索引?例如，如果我们使用类型1的UUID来标记最左边的比特，我们会更好吗?还是我们应该完全放弃UUID并考虑使用auto_increment主键?

The bigger question is: how badly does this random data screw up the index when we have 50M records? Would we be better off if we used, for example, a type-1 UUID where the leftmost bits were timestamped? Or maybe we should ditch UUIDs entirely and consider auto_increment primary keys?

我正在寻找有关不同类型的UUID在MySQL中作为索引/主键存储时的性能的一般想法/提示.谢谢！

I'm looking for general thoughts/tips on the performance of different types of UUIDs when they are stored as an index/primary key in MySQL. Thanks!

推荐答案

UUID是通用唯一ID.这是您应该在此处考虑的普遍部分.

A UUID is a Universally Unique ID. It's the universally part that you should be considering here.

您真的真的需要ID是唯一的吗?如果是这样，那么UUID可能是您唯一的选择.

Do you really need the IDs to be universally unique? If so, then UUIDs may be your only choice.

我强烈建议如果您 do 使用UUID，则应将它们存储为数字而不是字符串.如果您有超过5000万条记录，那么节省存储空间将提高您的性能(尽管我不能说多少).

I would strongly suggest that if you do use UUIDs, you store them as a number and not as a string. If you have 50M+ records, then the saving in storage space will improve your performance (although I couldn't say by how much).

如果您的ID不必是唯一的，那么我认为仅使用auto_increment可以做得更好，这可以确保ID在表中是唯一的(因为值每次都会递增)

If your IDs do not need to be universally unique, then I don't think that you can do much better then just using auto_increment, which guarantees that IDs will be unique within a table (since the value will increment each time)

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