估计+15亿行mysql表的查询速度

Question

我正在处理音频指纹问题，在这个问题上，我需要用行数(至少15亿行)来查询一个非常大的表，但在大小上(23G)相对比较好，并使用多个查询(在20到50个查询之间)检索大约50K到100 K的行。

该表有3列、一个散列和两个int值。没有任何限制。哈希列有许多冲突/重复项。以下是显示create表的输出

CREATE TABLE `fingerprints` (
  `hash` binary(10) NOT NULL,
  `int1` mediumint(8) unsigned NOT NULL,
  `int2` mediumint(8) unsigned NOT NULL,
  KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

查询很简单，下面是一个示例：

select int1 ,int2 from fingerprints 
  WHERE hash in 
    (UNHEX("1ff99335cce004f2765d"),UNHEX("14c4b93ed575982ed2e4")
     ,UNHEX("41044b0cf21dc8ac8f9b"),UNHEX("a791403ca116b4da53dd")
     ,UNHEX("d9f91514b900c25fa095"),UNHEX("3349f906deae6cd32883")
     ,UNHEX("221c0e3e2bc243fb0fe5") .... more here);

我尝试过不同的硬件规范(只使用一台机器/实例的AWS )。不同的my.cnf配置，但没有显著的性能提升。

此操作的目标速度阈值(总查询时间)为5秒。但是，我得到的最好结果是，一个查询的平均时间是3秒(如果我有20个查询，总的操作时间是1分钟)。

最后注意:在分析查询时，显示概要文件命令显示最慢的部分是(发送数据)状态。当结果集较大时，查询变得更慢(即检索10k行需要大约6秒，而检索1000行则需要2秒)。

问题：

• 对于具有足够RAM以容纳索引的SSD机器，这种查询场景的速度估计是什么？我没有在这个规模上工作的经验。
• 您对特定的db设置有建议吗？我应该试试mysql内存引擎吗？用分布式机器进行分区是必要的吗？我应该换到innodb吗？

我的设置：

• 只读取使用myisampack压缩并在where (散列)列上索引的myisam表。
• 索引表(MYI文件)被完全加载到RAM中。
• 带有有限iops的SSD硬盘(amazon )。根据AWS的图表，我有时会碰到700 Iop。

编辑

显示索引输出：

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| fingerprints |          1 | hash     |            1 | hash        | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

解释查询输出(对于示例查询)

+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fingerprints | NULL       | range | hash          | hash | 10      | NULL | 4912 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Answer 1

通过执行以下查询，我能够解决这个缓慢问题：

alter table fingerprints order by hash;

1. 我在散列上有很多重复(只有34m唯一的散列)。如果我正确地理解了这种情况，排序会使我的用例的读取更有顺序(选择*从表中选择散列=*)。
2. 如果检查显示索引的输出，则基数值为NULL。执行order命令后，基数now=唯一散列数= 34m。这很有道理。我想这就是根本问题。请参阅：https://stackoverflow.com/questions/6521673/is-null-cardinality-in-an-index-a-problem-mysql-5-x

这项工作大约需要60秒，现在只需要350毫秒。

Answer 2

(UNHEXing并不是问题的重要部分。)

真正的问题是散列的随机性。这导致在磁盘上的许多地方跳跃。让我们剖析一下查询。

• IN列表是分散在整个INDEX(hash)中的值列表。
• 每个值都是通过钻取一个BTree (在.MYI文件中找到)来查找的，该文件缓存在MyISAM的key_buffer中。
• key_buffer_size的价值是什么？
• SHOW TABLE STATUS LIKE 'fingerprints'的结果是什么？
• 如果index_size大于key_buffer_size，那么许多查找都会访问磁盘。
• 在每个BTree的叶节点上，查找将是一个5字节(我认为)“记录号”。
• 现在要查找行--这将是一个随机磁盘访问(一个查找，没有BTree)，在偏移量= 17 * record_number处进入fingerprints.MYD。(记录的FIXED长度似乎是17个字节。)
• 再次，我们将看到一个可能的磁盘命中--现在假设磁盘上剩余的空闲空间小于Data_length (请参见表状态)。

该怎么办呢？

案例1: Data_length + Index_length < RAM大小: key_buffer_size略大于Index_length。渐渐地，两个缓存将填充索引和数据，I/O将消失。

案例2:这个总数略大于RAM:选择一个缓存就足够大了。

案例3:和要比RAM大得多:在得到更多RAM之前，您会被大量的I/O所困。

我怀疑Data_length和Index_length差不多。我会将可用内存分成一半-一半用于key_buffer_size，其余用于数据缓存。

以下是两个更多的想法：

• 不要在第二步中获取KEY(hash, int1, int2)，这意味着只需要BTree查找；数据将位于叶节点中。使用这种方法，您可以将key_buffer_size设置为“大部分”可用内存。SELECT不会触及数据，只会触及索引。
• 切换到InnoDB。它的块是16 1KB，而不是1KB。这可能会让事情变得更快。但是磁盘的占用面积将是原来的2-3倍。同样，使用3列索引，但将key_buffer_size缩小到20M，并将innodb_buffer_pool_size提高到内存的70%。

其他说明：

• “发送数据”并没有告诉你任何事情。一般来说，分析是无用的。
• SSD的运行速度比HDD快得多。
• 你似乎被I/O束缚住了。
• 无论您是否受I/O约束，总查询时间与正在查找的散列数大致成正比。(这可以从我的解剖中推断出来。)
• 内存不可能比MyISAM更快或更慢。如果您的数据需要持久化，那么就会有麻烦，因为内存是不稳定的。
• 我预测压缩是无用的，因为您只有6个字节要压缩。(哈希本身可能是不可压缩的。)
• 如果您的提供者限制了IOP，那就是一个问题。如果您的索引被完全缓存(而不是大到不必要地消耗RAM)，那么IOP就是数据块的获取。3字节的密钥大约大70%；足够大的key_buffer是否适合内存？如果是这样的话，这种方法可能是最佳的。

Answer 3

当查询包括，

(“1ff99335cce004f2765d”)，UNHEX(“14.

将永远局限于缓慢。每一行都必须“取消十六进制”才能确定此查询的相关性。在您的示例中，每行检索几个UNHEX操作。