估計 +15 億行 mysql 表的查詢速度

我正在研究音頻指紋辨識問題，我需要查詢一個非常大的表的行數（至少 15 億行），但大小相對可以（23G），總共檢索大約 50K 到 100K 行，使用多個查詢（20 到 50 個查詢）。

該表有 3 列、一個雜湊和兩個 int 值。沒有任何約束。雜湊列有很多衝突/重複。這是 show create table 的輸出

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 分鐘）。

最後一點：在分析查詢時，SHOW profile 命令顯示最慢的部分是（SENDING DATA）狀態。當結果集較大時，查詢會變慢（即檢索 10k 行大約需要 6 秒，而檢索 1000 行需要 2 秒）

問題：

• 對於具有足夠 RAM 來保存索引的 SSD 機器，這種查詢場景的速度估計是多少。我沒有在這種規模上工作的經驗。
• 您對特定的數據庫設置有建議嗎？我應該嘗試mysql記憶體引擎嗎？這裡的分區是分佈式機器的必要條件嗎？我應該切換到innodb嗎？

我的設置：

• 只讀 myisam 表，用 myisampack 壓縮並在 where (hash) 列上建立索引。
• 索引表（MYI 文件）已完全載入到 RAM
• 具有有限 iops 的 SSD 硬碟（亞馬遜 AWS）。根據 AWS 圖表，我有時會達到 700 Iops。

編輯：

顯示索引輸出：

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 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       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

EXPLAIN QUERY 輸出（用於範例查詢）

+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 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 |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

我能夠通過執行以下查詢來解決這個緩慢的問題：

alter table fingerprints order by hash;

1. 我在雜湊列上有很多重複（只有 34m 唯一的雜湊）。如果我正確地理解了情況，那麼對於我的案例（從雜湊 = *** 的表中選擇 *），排序會使讀取更加連續。
2. 如果檢查 SHOW INDEX 的輸出，基數值為 NULL。通過命令執行 order 後，基數 now= 唯一雜湊數 = 34m。這是有道理的。我想這是根本問題。見：https ://stackoverflow.com/questions/6521673/is-null-cardinality-in-an-index-a-problem-mysql-5-x

大約需要 60 秒的工作，現在只需要 350 毫秒。

引用自：https://dba.stackexchange.com/questions/234081