代码之家 › 专栏 › 技术社区 › Gumbo

针对散列ID的MySQL二进制与非二进制

binary string mysql

Gumbo · 技术社区 · 17 年前

假设我想使用散列作为ID而不是数字。将它们存储为 BINARY

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

2 回复 | 直到 17 年前

Bill Karwin 17 年前

对哈希摘要通常存储为十六进制数字的ASCII表示形式,例如单词“hash”的MD5是:

0800fc577294c34e0b28ad2839435945

这是一个32个字符的ASCII字符串。

但是MD5实际上产生了一个128位的二进制哈希值。这应该

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

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

重新。您认为您更关心性能而不是空间效率:

我不知道二进制数据类型比CHAR更快的原因。

如果您有效地使用缓存缓冲区,那么大小的一半可能是性能的优势。也就是说,如果字符串的大小是以十六进制存储相同值所需的字符大小的一半,那么给定数量的缓存可以存储两倍于二进制数据行数的数据。同样,该列上索引的缓存内存可以存储两倍的数据。

至于二进制存储的哈希字符串与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%。但是请注意,在对该表达式求值1亿次之后,总差值仍然小于1秒。虽然我们可以测量速度的相对差异,但速度的绝对差异实际上是微不足道的。

因此,我要重申:

测量,不要猜测或假设。你受过教育的猜测在很多时候都是错误的。在每次改变之前和之后进行测量,这样你就知道它有多大帮助。
把你的时间和注意力投入到你能得到最大回报的地方。
不要为小事操心。当然,一个微小的差异加上足够的迭代次数,但是考虑到这些迭代次数,具有更大绝对效益的性能改进仍然是可取的。

ZygD 5 年前

从…起 the manual :

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) 要在后台创建列,好处是按该列排序所需的时间更少,如果对该列进行索引,查找相应行所需的时间也可能更少。