如何处理MySQL中用于生物识别项目的数十亿行？

Question

我的MySQL表看起来像这样。

animal_id (mediumint) | dna_hash (binary 10)

目前，我们有大约20亿种不同的dna_hash，而且每天都在增加。

我们需要通过dna_hash搜索这只动物。所以，我有一个关于dna_hash的索引。

目前，我开始担心，因为在导入了大约2000万行之后，我在dna_hash上的索引超过了500 my。如果我继续添加，最终索引大小将超过50 to，这是非常昂贵的操作。

那么，我只是想知道是否还有其他可能的方法来缩小索引大小？或者我应该升级我的硬件。

谢谢。

Answer 1

为什么你被冲洗了

所以，你需要把一个10字节的哈希映射到一个3字节的id，对吗？他们有2B+吗？桌子上没有其他东西了？(添加到表中将增加下面的数字。)

CREATE TABLE HashToAnimal (
    hash BINARY(10) NOT NULL,
    animal_id MEDIUMINT UNSIGNED NOT NULL,
    PRIMARY KEY (hash),
    INDEX(animal_id)  -- might you need this for maintenance?
) ENGINE=InnoDB.

hash是非常随机的。因此，有两种极端情况(两者之间不多)：

• 这张桌子住在buffer_pool里。访问不会导致磁盘命中(非常快)。2B行将占用大约100 2B。这意味着超过100 of的RAM。(不，它不能容纳50‘t；有几种类型的开销。)
• buffer_pool太小了，拿不动桌子。现在，每个SELECT将需要一个磁盘命中，因为缓存是‘不可能’。你能在亚马逊买多少个IOPS？悲观地将其除以3，以求获取行的速度。

你引用了1000次，花费了10秒。听起来像是冷缓存和传统硬盘。每秒100次的磁盘点击率对于传统磁盘来说是一个很好的经验规则。(我以为亚马逊完全是SSD?)当更多的表被缓存时，10秒会变得更快--如果buffer_pool很大的话。最终不会有任何磁盘点击，我的“非常快”将成为现实。

另一个注意事项:这个表中的INSERTing有类似的缓存注意事项。每次插入到BTree中都是非常随机的，以hash开头(无论是PRIMARY KEY还是次要)。如果该表比buffer_pool大得多，它将是磁盘命中；否则，它最终将被缓存并快速运行。

注意:如果Amazon进行某些类型的维护，缓存可能会突然变得“冷”。

哈希、MD5s、GUID、UUID等都很难使用。你必须支付大量的内存或大量的IOP。

开箱即用，

你通常都不想养动物，对吧？那么，首选的测试是“存在”，而不是“获取”？因此，一个2**80位的位串会说看到了哪些散列。这太不切实际了。

下面是一个可能需要5GB的表模式，它允许您测试2B不同散列的存在：

设x是散列的前27位。让我们来做下一个8位。

CREATE TABLE CheckHash (
        x BINARY(3) NOT NULL,
        y BLOB NOT NULL,
        PRIMARY KEY(x)
    ) ENGINE=InnoDB;

INSERTing有点复杂。获取x的行，查看y是否已经在y列中；如果没有，则添加它。(如果没有x行，则添加一个行。)

检查是SELECTing WHERE x=?，通过y查看。

会有一些“碰撞”，但没关系。

会有一小部分错误点击(15%?)；然后使用完整的散列在HashToAnimal中查找它们。假设这个百分比足够小，那么这些磁盘点击率应该足够小，可以接受。

目标是能够完全缓存较小的CheckHash，但不必担心HashToAnimal没有很好地缓存。

这个表将逐渐增长；4B散列可能只需要8GB。

(如果你超越了5B散列，你应该用28而不是27重建这个表。这是空间开销与扫描blob的CPU时间之间的权衡。)

(当然还有其他技术。)