python - 在 pytables 中优化复杂的 table.where() 查询？

Question

我有一个非常大的数据库 - 我正在处理一个 350m 行的子集，但最终它大约有 3b 行。我在这里的全部目标是优化此数据库上的特定类型的查询，以牺牲内存以外的几乎所有内容为代价。我现在正在使用的 db 文件在 PyTables 版本 2.3.1 上使用级别 1 的 blosc 压缩(如果有帮助，我可以更新)。每行有 13 个条目 - 一个典型的条目如下所示:

['179', '0', '1', '51865852', '51908076', '42224', '22', '2', '20', '22', '2', '0.0516910530103', '0.0511359922511']

它们都是数字，但不一定是同一类型。我目前将它们存储在 PyTables 表中，定义如下:

ind = tables.UInt16Col(pos=0)
hap = tables.UInt8Col(pos=1)
chrom = tables.UInt8Col(pos=2)
hap_start = tables.Int32Col(pos=3)
hap_end = tables.Int32Col(pos=4)
hap_len = tables.Int16Col(pos=5)
mh_sites = tables.Int16Col(pos=6)
mh_alt = tables.Int16Col(pos=7)
mh_n_ref = tables.Int16Col(pos=8)
all_sites = tables.Int16Col(pos=9)
all_alt = tables.Int16Col(pos=10)
freq = tables.Float32Col(pos=11)
std_dev = tables.Float32Col(pos=12)

我真的不在乎设置这个数据库需要多长时间——我最终会创建它一次然后访问它。我的查询是以下形式:

a = [ x[:] for x in hap_table.where('''(mh_sites == 15) & (hap_len > 25000) & (hap_len < 30000) & (freq > .38) & (freq < .4) & (std_dev > .3) & (std_dev < .4)''')]

基本上，我正在搜索与给定公差附近的特定行匹配的条目。在我较小的数据库(3.5 亿行)上，如果我已将要搜索的所有四列编入索引，则该查询需要 38 秒:

byteorder := 'little'
chunkshape := (32768,)
autoIndex := True
colindexes := {
  "hap_len": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
  "freq": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
  "std_dev": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
  "mh_sites": Index(6, medium, shuffle, zlib(1)).is_CSI=False}

如果我没有事先编制索引，则为 10 秒。我不确定我是否理解为什么索引数据库上的查询速度较慢。也许索引会产生不必要的开销？

就像我说的，我的目标是尽可能地优化这种类型的查询——以牺牲内存使用以外的所有东西为代价(我想使用大约 2G，并且真的不想使用超过大约 5G ).我试过索引但它似乎不起作用。我所有的查询都基于 mh_sites 的单个值，并且只有大约 100 个可能的值，所以我考虑将其拆分为多个表，因此我在任何时候都只搜索数据的一个子集(虽然我除了 mydata.root.table_1、mydata.root.table_2 等之外，我不太确定该怎么做)。我也考虑过尝试将它存储为数组而不是 - 也许是 float 组，然后在我需要使用它们时将其他所有内容转换为整数？如果它有所作为，我的查询通常会返回 20k 到 500k 的结果。

关于优化这个查询有什么建议吗？

Answer 1

我想出了如何让它更快的方法 - 我的解决方案可能会对其他人有所帮助，所以我将其发布在这里。

我对 PyTables 中的索引如何工作感到困惑。我认为 CSI 实际上会对数据进行排序，但事实并非如此 - 一旦您附加了一组行，它们总是按该顺序。对我来说，在插入数据之前对数据进行排序是非常值得的。我的查询时间减少了 1-2 个数量级。

这也解释了为什么索引表实际上增加了查询时间——因为行基本上是随机分布的，我需要为每个查询读取整个数据库。因此，pytables 是否可以使用索引来确定需要读取哪些 block 并不重要，因为无论如何它都需要读取所有 block 。因此，当数据未排序时，索引只是增加了开销。对于排序表，索引绝对有帮助。