MD5字段的最佳数据类型是什么？

SELECT list, of, fields 
FROM table 
WHERE name_key IN (md5a, md5b, md5c...);

#1 楼

数据类型uuid非常适合该任务。对于varchar或text表示，它仅占用16个字节，而RAM中仅占用37个字节。 （或者磁盘上有33个字节，但是奇数在很多情况下需要填充才能有效地变为40个字节。）而uuid类型具有更多优势。
示例：

SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;

请参阅：

将文本表示形式的十六进制转换为十进制数
当所有值均为36个字符时，使用char vs varchar可使索引查找明显更快

您可以考虑其他（便宜的）哈希函数，如果您不需要md5的加密组件，但是我会在您的用例中使用md5（通常是只读的）。
警告：对于您的情况（immutable once written）从功能上依赖（伪自然）PK很好。但是如果在text上进行更新，那同样会很痛苦。考虑纠正错字：PK和所有相关索引，“数十个其他表”中的FK列以及其他引用也必须更改。表和索引膨胀，锁定问题，更新缓慢，引用丢失，...
如果text可以在正常操作中进行更改，则替代PK将是更好的选择。我建议使用bigserial列（范围为-9223372036854775808 to +9223372036854775807-这是九个五千二百二十三四极子三百七十二亿三百六十六亿十亿左右）billions of rows的不同值。在任何情况下都可能是一个好主意：数十个FK列和索引的8个字节而不是16个字节！）。或用于更大基数或分布式系统的随机UUID。您总是可以额外存储所说的md5（作为uuid），以便快速从原始文本中查找主表中的行。相关：

Postgres中UUID列的默认值

对于您的查询：

使用大IN优化Postgres查询


要解决@Daniel的评论：如果您希望不使用连字符的表示形式，请删除要显示的连字符：

SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')

，但我不会打扰。默认表示就可以了。问题实际上不是这里的表示。
如果其他各方应该采用不同的方法，并在字符串中添加不带连字符的字符串，那也没问题。 Postgres接受几种合理的文本表示形式作为uuid的输入。手册：

PostgreSQL还接受以下替代输入形式：使用
大写数字，用大括号括起来的标准格式，
省略一些或所有连字符，添加任意四位数字后的连字符。示例如下：

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

此外，md5()函数返回text，您将使用decode()转换为bytea，其默认表示为：

SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')

07R^46HP274
SELECT encode(my_md5_as_bytea, 'hex');
7232q

您必须再次encode()才能获得原始文本表示形式：
q4312078q
最重要的是，由于以下原因，存储为bytea的值将在RAM中占据20个字节（在磁盘上占据17个字节，在填充时占据24个字节）。内部varlena开销，这特别不利于简单索引的大小和性能。
这里的一切工作都有利于uuid。

#2 楼

我会将MD5存储在text或varchar列中。各种字符数据类型之间没有性能差异。您可能想通过使用varchar(xxx)来限制md5值的长度，以确保md5值从不超过特定长度。

通常，大型IN列表的速度并不是很快，因此最好执行以下操作：

with md5vals (md5) as (
  values ('one'), ('two'), ('three')
)
select t.*
from the_table t
  join md5vals m on t.name_key  = m.md5;

另一种有时被称为更快的方法是使用数组：

select t.*
from the_table t
where name_key = ANY (array['one', 'two', 'three']);

在比较相等性时，常规的BTree索引应该可以。这两个查询都应该能够使用这样的索引（特别是如果只是选择了行的一小部分。

#3 楼

另一种选择是使用4个INTEGER或2个BIGINT列。