PostgreSQL使用UUID和文本作为主键的区别

Question

PostgreSQL使用UUID和文本作为主键的区别

postgresqlprimary-keyuuid

37

我们目前的PostgreSQL数据库使用GUID作为主键，并将它们存储为文本字段。

对此，我的最初反应是，尝试执行任何类型的最小笛卡尔连接都将成为索引的噩梦，因为需要找到所有匹配记录。但是，也许我的数据库索引知识有限。

我认为我们应该使用UUID，因为这些是以GUID的二进制表示形式存储的，而文本则不是，并且在文本列上获取的索引量是很少的。

更改这些将是一个重大的项目，我在思考是否值得这样做？

- Scottie

1

@Kevin：我可以这样做，但我希望在SO上有人能够回答，这样我就不必自己动手了... - Scottie

表的规模是多少？我们是在谈论拥有百万行的表吗？几千万行？还是十亿级别的？ - khampson

这是所有表格，有些可能会增长到数千万条记录。甚至可能达到数亿条。 - Scottie

“he amount of indexing that you get on a Text column is minimal” 的意思是什么？ - Falmarri

以我的理解，文本索引并不能在文本字段上进行太多的索引，大多数情况下需要进行全表扫描才能查找所选行并将它们返回。但是，我也可能完全错了... @Falmarri - Scottie

显示剩余4条评论

2个回答

23

正如 @Kevin 提到的那样，确切数据的比较和对比是唯一确定的方法，但从您所描述的情况来看，我不认为这与任何其他情况有什么不同，其中字符串要么是表中的主键，要么是独特索引的一部分。

可以提前说明的是，您的索引可能会更大，因为它们必须存储更大的字符串值，并且从理论上讲，索引的比较需要更长的时间，但如果这样做会很痛苦，我不建议进行过早优化。

根据我的经验，在拥有数十亿行的表格上使用 md5sums 的唯一索引性能非常好。我发现查询的其他方面往往会导致性能问题。例如，当您最终需要查询表格中的大量数据时（例如数十万行），连续扫描就成为更好的选择，因此这就是查询计划器所选择的内容，而这可能需要花费更长的时间。

针对该情况还有其他缓解策略，例如对查询进行分块，然后将结果进行联合 (例如手动模拟在 Hadoop 球中的 Hive 或 Impala 中执行的操作)。

关于您对文本索引的担忧，虽然我确定有些数据集会产生关键分布，从而使性能变差，但 GUID 与 md5sums、sha1 等索引通常表现良好，不需要连续扫描 (除非像上面提到的那样查询整个表格中的大量数据)。

影响索引性能的一个重要因素是有多少唯一值。因此，对于具有大量行的表格的布尔索引不太可能有所帮助，因为它基本上将针对索引中的任何值 (true、false 和潜在的 NULL) 都有大量行冲突。另一方面，GUID 索引可能会有大量没有冲突的值 (理论上定义如此，因为它们是 GUID)。

针对OP的评论进行编辑：

所以你是说UUID GUID和Text GUID在索引方面是一样的吗？我们的整个表结构都使用Text字段来存储GUID，但我不确定Postgre是否认为它是GUID。只是一个唯一的字符串。

不完全相同。但是我认为对于这种情况，它们应该具有非常类似的性能，并且我不认为事先优化是值得执行的，特别是考虑到你说要这样做将是一项非常复杂的任务。

如果在特定环境中遇到性能问题，您可以随时稍后更改。但是，正如我之前提到的，如果您遇到这种情况，还有其他事情可能会产生比更改PK数据类型更好的性能。

UUID 是128位数据类型（即16字节），而text的开销为1或4字节，再加上字符串的实际长度。对于GUID，这意味着至少33个字节，但根据使用的编码方式可以有很大的差异。

因此，考虑到这一点，基于文本的UUID的索引肯定会更大，因为值更大，并且比较两个字符串与两个数字值在理论上效率更低，但在这种情况下不太可能产生很大区别，至少在通常情况下不会。

如果要以显着的成本进行事先优化并且可能永远不会需要，则我不会这样做。如果到了那个时候，可以跨越这座桥（尽管我会首先考虑其他查询优化，如上面所述）。

关于Postgres是否知道这个字符串是GUID，它默认情况下肯定不知道。在它看来，它只是一个唯一的字符串。但这对大多数情况来说应该是可以的，例如匹配行等。如果您发现自己需要某些特定要求GUID的行为（例如，一些非等值比较，其中GUID比纯字典排序的结果不同），那么您可以将字符串强制转换为UUID，Postgres会在查询期间将其视为此类值。

例如，对于文本列foo，您可以执行foo::uuid将其转换为uuid。

还有一个可用于生成uuid的模块uuid-ossp。

- khampson

那么你的意思是UUID GUID和Text GUID在索引方面是一样的吗？我们整个表结构都使用带有GUID类似字符串的文本字段，但我不确定Postgre是否将其识别为GUID。只是一个独特的字符串。 - Scottie

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

处理UUID数字时，请将其存储为数据类型uuid。始终如此。根本没有理由考虑使用text作为替代方案。输入和输出默认都是通过文本表示完成的。类型转换非常廉价。 text数据类型在RAM和磁盘上需要更多的空间，处理速度较慢，并且容易出错。@khampson's answer提供了大部分的理由。（由于您现有的实现，他得出了不同的结论。）

这些问题以前都已经被问过、回答过和讨论过。以下是dba.SE上相关问题的详细解释：

`bigint`？

也许你根本不需要UUID（GUID）。考虑使用bigint代替。它只占用8个字节，在各个方面都更快。它的范围经常被低估：

-9223372036854775808 to +9223372036854775807

那是9.2亿亿亿个正数。换句话说，九百二十三万亿三千七百二十二亿三千零三十六亿多一些。

如果你每秒烧毁100万个身份证（一个极高的数字），你可以持续这样做292471年。然后再持续292471年来处理负数。"数千万或数亿"根本差得太远了。

UUID实际上只是为分布式系统和其他特殊情况设计的。

PostgreSQL使用UUID和文本作为主键的区别

bigint？

`bigint`？