问题：存储和查询大量的64位无符号整数
在过去的6年中，我们一直在进行基于散列的序列搜索，使用MinHash衍生的方法FracMinHash，这是个相当大的兔子洞。(你可以在这里阅读更多关于FracMinHash的信息，但它本质上是ModHash的一个底层草图版本）。这一切都在sourmash软件中实现，这是一个基于Python和Rust的命令行生物信息学工具包。
其基本思想是，我们采取长的DNA序列，提取固定长度的子序列（例如k=31），对它们进行散列，然后通过只保留低于某个阈值的子序列来勾画它们。然后，我们根据重叠的哈希值的数量来搜索草图之间的匹配。这是重叠的k=31子序列数量的代理，而这又可以转换为各种序列相似度的指标。
我们要解决的问题的规模是相当大的。举个例子，我们有一个数据库（Genbank细菌），有115万个哈希值桶，这些桶里总共有46亿个哈希值（代表大约4.6万亿个原始K-分子）。因此，我们需要做一些适度聪明的事情来存储它们并快速搜索它们。
我们已经有多种格式来存储和查询草图集，包括包含JSON序列化草图的直接压缩文件，一个基于磁盘的自定义序列布鲁姆树，以及一个住在内存中的倒置索引。倒置索引一旦加载就会很快，但序列化却......不是那么好，而且内存消耗很高。这是我想要解决的问题!
我对SQLite情有独钟，这个小小的嵌入式数据库引擎快得令人发指，我决定弄清楚如何用SQLite来存储和查询我们的草图。
使用SQLite来存储64位无符号整数：第一次尝试
我面临的挑战是，我们的草图是由64位无符号整数组成的，而SQLite不能原生存储64位无符号整数。但这正是我所需要的!
进入类型转换器!我找到了两个非常好的资源，可以将64位的整数自动转换为SQLite可以处理的数据类型：这个stackoverflow帖子，"Python的int太大，无法转换为SQLite的INTEGER"，以及这个来自welllsr.com的伟大教程，为Python调整和转换SQLite数据类型。
简而言之，我从 stackoverflow 的答案中抽出代码来做了以下事情。
- 写一个函数，对于任何大于2**63-1的哈希值，将数字转换为十六进制字符串。
- 写一个相反的函数，将十六进制字符串转换为数字。
- 将这些函数注册为SQLite数据类型的适配器，以便为该类型的每一列自动运行。
这样做是因为SQLite有一个非常灵活的内部类型系统，它基本上可以将任何东西存储为字符串，而不管官方的列类型如何。
python代码看起来像这样。
MAX_SQLITE_INT = 2 ** 63 - 1
sqlite3.register_adapter(
int, lambda x: hex(x) if x > MAX_SQLITE_INT else x)
sqlite3.register_converter(
'integer', lambda b: int(b, 16 if b[:2] == b'0x' else 10))
当你连接到数据库时，你可以告诉SQLite注意这些适配器，像这样。
conn = sqlite3.connect(dbfile,
detect_types=sqlite3.PARSE_DECLTYPES)
然后你在SQLite中定义你的表。
CREATE TABLE IF NOT EXISTS hashes
(hashval INTEGER NOT NULL,
sketch_id INTEGER NOT NULL,
FOREIGN KEY (sketch_id) REFERENCES sketches (id))
CREATE TABLE IF NOT EXISTS sketches
(id INTEGER PRIMARY KEY,
name TEXT,
...)
并且你可以做所有你想做的查询，大的整数将被转换为十六进制字符串，生活很美好。对吗？
这段代码实际上工作得很好!除了一个问题。
它非常慢。让关系型数据库（特别是SQLite）变得快速的一个关键是使用索引，而这些INTEGER列不能再作为INTEGER列被索引，因为它们包含了十六进制字符串这意味着，一旦数据库变大，那么，基本上搜索和检索的速度就太慢了，无法发挥作用。
这段代码功能完善，在一些提交中继续存在，但它的速度不够快，不能用于生产代码。
不幸的是（或幸运的是？），我现在就在其中。我已经在这个问题上投入了足够多的时间，也有足够多的有效代码和测试，所以我决定继续下去。见：沉没成本谬论。
在SQLite中有效存储64位无符号整数
我可以把无符号的64位整数转换成有符号的64位数，把它们塞进数据库，然后在查询和哈希区间做等价测试。只要我有条不紊地进行转换，一切都会顺利进行！我最后写了两个适配器函数。
我最后写了两个适配器函数，我在Python代码中调用相关的值（不使用SQLite类型转换器注册表）
MAX_SQLITE_INT = 2 ** 63 - 1
convert_hash_to = lambda x: BitArray(uint=x, length=64).int if x > MAX_SQLITE_INT else x
convert_hash_from = lambda x: BitArray(int=x, length=64).uint if x < 0 else x
注意这里我使用的是可爱的bitstring包，这样我就不用费心思去处理比特的问题了（尽管现在我已经用测试锁定了一切，这也是一种可能的优化）。
我使用的SQL模式看起来像这样。
CREATE TABLE IF NOT EXISTS sketches
(id INTEGER PRIMARY KEY,
name TEXT,
...)
CREATE TABLE IF NOT EXISTS sourmash_hashes (
hashval INTEGER NOT NULL,
sketch_id INTEGER NOT NULL,
FOREIGN KEY (sketch_id) REFERENCES sourmash_sketches (id)
)
我还建立了三个索引，与我想做的各种查询相对应。
CREATE INDEX IF NOT EXISTS sourmash_hashval_idx ON sourmash_hashes (
hashval,
sketch_id
)
CREATE INDEX IF NOT EXISTS sourmash_hashval_idx2 ON sourmash_hashes (
hashval
)
CREATE INDEX IF NOT EXISTS sourmash_sketch_idx ON sourmash_hashes (
sketch_id
)
我在这个PR中途做出的一个设计决定是允许在sourmash_hashes ，因为不同的草图可以与其他草图共享哈希值，我们必须这样做，或者有另一个中间表，将唯一的哈希值与潜在的多个sketch_ids联系起来。只是看起来更简单的是让哈希值不唯一，而是为可能的查询建立一个索引。(我以后可能会重新考虑这个问题，现在我可以无所顾忌地重构了；）。
在这一点上，现在插入很容易。
sketch_id = ...
# insert all the hashes
hashes_to_sketch = []
for h in ss.minhash.hashes:
hh = convert_hash_to(h)
hashes_to_sketch.append((hh, sketch_id))
c.executemany("INSERT INTO sourmash_hashes (hashval, sketch_id) VALUES (?, ?)",
hashes_to_sketch)
而检索也同样简单。
sketch_id = ...
c.execute(f"SELECT hashval FROM sourmash_hashes WHERE sourmash_hashes.sketch_id=?", sketch_id)
for hashval, in c:
hh = convert_hash_from(hashval)
minhash.add_hash(hh)
因此，这对于在SQLite中存储草图是相当有效的！我可以完美地重建草图。我可以在通过SQLite进行往返后完美地重建草图，这是伟大的第一步。
下一个问题是：我能否以倒置索引的方式快速搜索哈希值？也就是说，我可以根据哈希值的查询来寻找草图，而不是（如上）使用sketch_id 来检索已经确定的草图的哈希值？
在SQLite中对64位无符号整数进行匹配
这最终是非常简单的!
为了查询哈希值的集合，我设置了一个包含查询哈希值的临时表，然后在精确值匹配上做一个连接。方便的是，这并不关心数据库中的值是否是有符号的--它只关心位模式是否相等!
代码，用于一个游标c。
def _get_matching_sketches(self, c, hashes, max_hash):
"""
For hashvals in 'hashes', retrieve all matching sketches,
together with the number of overlapping hashes for each sketch.
"""
c.execute("DROP TABLE IF EXISTS sourmash_hash_query")
c.execute("CREATE TEMPORARY TABLE sourmash_hash_query (hashval INTEGER PRIMARY KEY)")
hashvals = [ (convert_hash_to(h),) for h in hashes ]
c.executemany("INSERT OR IGNORE INTO sourmash_hash_query (hashval) VALUES (?)",
hashvals)
c.execute(f"""
SELECT DISTINCT sourmash_hashes.sketch_id,COUNT(sourmash_hashes.hashval) as CNT
FROM sourmash_hashes, sourmash_hash_query
WHERE sourmash_hashes.hashval=sourmash_hash_query.hashval
GROUP BY sourmash_hashes.sketch_id ORDER BY CNT DESC
""", template_values)
return c
作为一个附带的好处，这个查询按照草图之间的重叠大小对结果进行排序，这导致了一些相当好的、高效的阈值处理代码。
基准测试！？
我只想说，性能绝对是可以接受的--下面的基准测试将sqldb与我们的其他数据库格式进行了比较。我们正在搜索的数据库是一个由48000个草图组成的集合，共有1.61亿个哈希值--GTDB RS202，如果你好奇的话：）。
对于53.9k的查询哈希值，在数据库中发现了19.0k，SQLite的实现是很好的，而且速度很快，尽管有很大的磁盘占用。
| db格式 | 数据库大小 | 时间 | 内存 |
|---|---|---|---|
| sqldb | 15 GB | 28.2s | 2.6 GB |
| sbt | 3.5 GB | 2m 43s | 2.9 GB |
| 压缩 | 1.7 GB | 5m 16s | 1.9 GB |
对于较大的查询，有374.6k的查询哈希值，而我们在数据库中找到189.1k的哈希值，性能稍微平衡了一点。
| 数据库格式 | 数据库大小 | 时间 | 内存 |
|---|---|---|---|
| sqldb | 15 GB | 3m 58s | 9.9 GB |
| sbt | 3.5 GB | 7m 33s | 2.6 GB |
| 压缩 | 1.7 GB | 5m 53s | 2.0 GB |
请注意，压缩文件搜索根本不使用任何索引，所以搜索是线性的，预计无论查询什么，时间都会差不多。SBTs并不真正用于这种情况，但它们是我们拥有的其他 "快速搜索 "数据库，所以我还是对它们进行了基准测试。
(我们在这里做的事情有很多细微的差别，我想我主要是理解这些性能数字；我的想法见基准测试问题)。
真正好的是，对于我们的动机用例，即在反向索引中查找哈希值以与其他标签相关联，SQLite的性能比我们目前的JSON-on-disk/in-memory搜索格式好得多。
对于53.9k的查询哈希值，我们得到。
| LCA数据库格式 | db大小 | 时间 | 内存 |
|---|---|---|---|
| SQL | 1.6 GB | 20s | 380 MB |
| JSON | 175 MB | 1m 21s | 6.2 GB |
坦率地说，这是非常好的--对于8倍的磁盘大小，我们得到了4倍的查询速度和16倍的内存使用量(内存中的性能包括从磁盘加载，这是它如此糟糕的主要原因)。
进一步提高性能？
我在这次编码奥德赛中仍然相当疲惫（>250次提交，最后增加或改变了近3000行代码），所以我把一些工作留给未来。最具体的是，我们想以让多个读者同时从数据库中读取数据为基准，例如Web服务器后端。我希望它能在这方面工作得很好，但我们需要检查。
我确实使用了以下PRAGMAs进行配置，我想知道我是否应该花时间尝试不同的参数；这主要是一个围绕着写一次，读多次的数据库。欢迎提出建议:)。
PRAGMA cache_size=10000000
PRAGMA synchronous = OFF
PRAGMA journal_mode = MEMORY
PRAGMA temp_store = MEMORY
结论性的想法
上面的第二个解决方案是我当前拉动请求中的代码，我希望它最终会被合并到sourmash中并作为sourmash v4.4.0的一部分发布。它完全集成到了sourmash中（使用范围比我上面解释的要广得多；），而且我对它很满意。
总之，这就是我的SQLite探索之旅。欢迎提出意见
