如何使char+bigint的密钥比bigint+bigint的密钥具有更好的性能？

Question

我想将一些与用户相关的数据存储到一个表中。数据由MD5哈希标识。

我有两个选择：

选项1-有一个2列的表：hash_id (unsigned bigint)和md5 (char(32))。将实际数据存储在键为hash_id (unsigned bigint) + user_id (bigint)的另一个表中。

选项2-将数据存储在键为md5 (char(32)) + user_id (bigint)的表中。

选项1看起来更“规范化”，但我更喜欢选项2，所以我想知道使用它是否会导致严重的性能损失。

为了检查，我定义了两个表并尝试了以下操作：

1. 将100 K项插入空表
2. 使用表中已经存在的10KID/MD5，并为每个it运行一个select查询和一个update查询(以模拟我希望在已经存在的条目中设置数据的场景，因此我需要更新它)。
3. 使用表中不存在的10KID/MD5，并为每个it运行一个select查询和一个insert查询(以模拟我希望在不存在的条目中设置数据的场景，因此我需要创建它)。
4. 删除10K现有条目

在除最后一个测试之外的所有测试中，第二个表(具有实际MD5的表)的结果要比第一个表(带有hash_id的表)的结果要好得多。

这怎么可能呢？我认为使用char键比使用bigint-8字节更糟糕，它的长度为- 32字节。我在这里错过了什么？

我使用MySQL + InnoDB。

谢谢。

Answer 1

一个指数的大小和构成，无论是否合成，都不会对性能产生很大影响。

然而，访问的随机性确实如此。这种组合是性能杀手：

• “随机”索引(或索引的一部分)：UUID / MD5 / SHA256 /等。
• 索引(或表)比在内存中(在buffer_pool中)缓存的要大。

假设索引(或表)是缓冲池的4倍。然后，“next”查找(对于SELECT或添加一行，从而更新索引)有3/4的机会成为磁盘丢失。也就是说，75%的此类操作将导致磁盘命中。如果您使用的是HDD，即(经验法则)10 is。(SSD更快，但仍然不是免费的。

您可以通过缩小表的大小来减轻这个问题。这可以通过缩小数据类型来完成。不要使用INT (4字节)作为Yes/No标志，使用TINYINT (1字节)。不要将MD5存储为CHAR(32) (32字节--如果是utf8，则为96字节)；在获取和存储到BINARY(16)列时使用HEX()和UNHEX()。

大多数其他类型的指标都有一定程度的“参考局部性”。也就是说，两个(或1000个)连续引用将“接近”彼此，因此buffer_pool的缓存可以帮助避免I/O，即使表/索引比buffer_pool大得多。

更多讨论关于UUID失败的原因，加上对Type-1 UUID的部分补救，这是基于时间的，并且是MySQL使用的类型。

至于你的基准..。

• 在一个“真正的”表中，还会有几个列。
• 在开始获得大量磁盘错误之前，您不会增加行数。
• 此时，2列查找表可以保持缓存，但较大的“真实”表不能。
• 因此，如果存在“引用的局部性”(比如只查看‘最近的’行)，那么MD5就会到处跳，但是BIGINT会碰到更多可缓存的东西。