MySQL二进制与非二进制哈希ID的区别

Question

如果我想使用哈希作为ID而不是数字，将它们存储为 BINARY 与非二进制相比会有性能优势吗？

CREATE TABLE `test`.`foobar` (
  `id` CHAR(32) BINARY CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (`id`)
)
CHARACTER SET ascii;

- Gumbo

---

你能更具体地说明你所追求的“优势”是什么吗？你是想在空间、性能、易用性还是独特值范围方面进行优化？ - Bill Karwin

我主要关注性能优势，因为空间几乎是免费的，而且使用128位值作为主键可以使价值范围加倍，相比使用BIGINT。 - Gumbo

Answer 1

是的。通常哈希摘要以十六进制数字的ASCII表示形式存储，例如单词“hash”的MD5值为：

0800fc577294c34e0b28ad2839435945

这是一个由32个ASCII字符组成的字符串。

但是MD5实际上生成的是128位二进制哈希值。这理论上只需要16字节存储为二进制值而不是十六进制数字。因此，通过使用二进制字符串，您可以获得一些空间效率。

CREATE TABLE test.foobar (
  id BINARY(16) NOT NULL PRIMARY KEY
);

INSERT INTO test.foobar (id) VALUES (UNHEX(MD5('hash')));

关于您更关心性能而非空间效率的评论：

我不知道为什么BINARY数据类型会比CHAR更快。

如果你能有效地使用缓存缓冲区，那么大小减半可能会对性能有所帮助。也就是说，如果字符串的大小是用十六进制存储相同值所需的CHAR大小的一半，那么给定量的缓存内存可以存储两倍的BINARY数据行。同样，该列索引的缓存内存也可以存储两倍多。

结果是更有效的高速缓存，因为随机查询更有可能命中缓存的数据或索引，而不需要访问磁盘。在大多数数据库应用程序中，缓存效率很重要，因为通常的瓶颈是磁盘I/O。如果您可以使用缓存内存来降低磁盘I/O的频率，那么这比选择一个数据类型要好得多。

至于在BINARY中存储的哈希字符串与BIGINT之间的差异，我会选择BIGINT。缓存效率将更高，并且在64位处理器上，整数算术和比较应该非常快。

我没有测量来支持上述声明。选择一种数据类型而不是另一种数据类型的净收益取决于数据库和应用程序中的数据模式和查询类型。要获得最精确的答案，您必须尝试两种解决方案并测量差异。

关于您的假设——二进制字符串比默认的不区分大小写的字符串比较更快，我进行了以下测试：

mysql> SELECT BENCHMARK(100000000, 'foo' = 'FOO');
1 row in set (5.13 sec)

mysql> SELECT BENCHMARK(100000000, 'foo' = BINARY 'FOO');
1 row in set (4.23 sec)

因此，二进制字符串比较比不区分大小写的字符串比较快17.5％。但请注意，评估此表达式100百万次后，总差异仍不到1秒钟。尽管我们可以测量速度的相对差异，但速度的绝对差异实际上非常微不足道。

所以，我会重申：

• 测量，不要猜测或假设。您的猜测很多时候都是错误的。在每次更改之前和之后进行测量，以便您知道它有多大帮助。
• 将您的时间和注意力投入到获得最大回报的地方。
• 不要太过于纠结小事。当然，足够的迭代中会累计微小差异，但在这些迭代中，具有更大绝对收益的性能提升仍然是首选。

- Bill Karwin

---

我已经考虑过了。虽然2^128足够大（BIGINT只有2^64，这已经足够了），但这将节省一半的空间。 - Gumbo

我已经按照您的要求更新了我的问题。如果您再看一眼就好了。 - Gumbo

我认为二进制比普通的比较更快，因为它不会做大小写字母的额外比较，所以 "'foo'='FOO'" 为真，但 "BINARY 'foo'='FOO'" 为假。 - Gumbo

谢谢您的回复。我忘记了这个问题。感谢您提供基准测试的提示，我之前没有考虑到这一点。我想我会建立两个测试用例并自己进行测量。尽管我认为我肯定会选择16字节长的BINARY变量，甚至缩短到8字节。 - Gumbo

MD5哈希值始终为16个字节。 如果您将存储缩短为8个字节，您将存储截断的哈希值。 如果可以接受，请继续。 - Bill Karwin

将 'foo'='FOO' 与 'foo'=BINARY 'FOO' 进行基准测试是不公平的。前者必须比较三个字符，而后者在第一个不匹配后可能会停止。当然，这完全是毫秒级别的问题，但仍然值得注意 :) - Michael Krelin - hacker

Answer 2

来自手册：

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except
that they contain binary strings rather than non-binary strings. That is,
they contain byte strings rather than character strings. This means that
they have no character set, and sorting and comparison are based on the
numeric values of the bytes in the values.

由于CHAR(32) BINARY会在幕后创建一个BINARY(32)列，因此它的好处是在按该列排序时需要更少的时间，并且如果对该列进行索引，则可能需要更少的时间来查找相应的行。

- Allain Lalonde

---

谢谢你的回答。你不会恰好知道一些相关数字吗？ - Gumbo