大型连接表和缩放

Question

问题

我们有一个快速增长的数据库，有几个大型连接表(目前位于数十亿行中)，但随着这些表的增长，查询时间受到了影响。需要关注的是，随着这些连接表链接的表中添加了更多的数据，连接表将继续以更快的速度增长，并对查询速度产生不利影响。

背景

我正在处理一个存储基因组信息的数据库。一些标记(300万)对应于存在DNA变异的位点，这些标记与在这些位点上已经确定了他们的基因型的个体有关。每个标记都有许多可能的基因型，每个个体都必须有一个基因型。

目前的执行情况

当数据库(postgresql)仍然很小时，使用外键将基因型连接到标记，然后通过联接表将个体连接到基因型没有问题。这样，就可以很容易地查找一个人的所有基因型，或者查找所有具有特定基因型的个体。

下面列出了这些表格的精简版本：

                                        Table "public.genotypes"
      Column      |            Type             |                       Modifiers                        
------------------+-----------------------------+--------------------------------------------------------
 id               | integer                     | not null default nextval('genotypes_id_seq'::regclass)
 ref_variation_id | integer                     | 
 value            | character varying(255)      |  
Indexes:
    "genotypes_pkey" PRIMARY KEY, btree (id)
    "index_genotypes_on_ref_variation_id" btree (ref_variation_id)


Table "public.genotypes_individuals"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 genotype_id   | integer | 
 individual_id | integer | 
Indexes:
    "index_genotypes_individuals_on_genotype_id_and_individual_id" UNIQUE, btree (genotype_id, individual_id)
    "index_genotypes_individuals_on_genotype_id" btree (genotype_id)

                                       Table "public.individuals"
    Column     |            Type             |                        Modifiers                         
---------------+-----------------------------+----------------------------------------------------------
 id            | integer                     | not null default nextval('individuals_id_seq'::regclass)
 hap_id        | character varying(255)      | 
 population_id | integer                     | 
 sex           | character varying(255)      | 
Indexes:
    "individuals_pkey" PRIMARY KEY, btree (id)
    "index_individuals_on_hap_id" UNIQUE, btree (hap_id)

目前的瓶颈是查找个体的所有基因型，并按其位置进行排序。这是经常使用的，而且比从基因型中查找个体更重要。其中一些查询的示例如下：

• 一个人所有基因型的简单查找 从“基因型”内部加入"genotypes_individuals“，在”基因型“.id= "genotypes_individuals".genotype_id中选择* ("genotypes_individuals".individual_id = 2946 )
• 通常情况下，虽然这是有限的，因为有很多基因型。我们通常只对特定染色体上的染色体感兴趣。 从“基因型”内部加入"genotypes_individuals“，在”基因型“.id= "genotypes_individuals".genotype_id中选择* ("genotypes_individuals".individual_id = 2946 )和(”基因型“.ref_variation_id IN (37142,37143，.))
• 我们还需要偶尔走另一条路。 从“个人”内部加入"genotypes_individuals“，在”个人“.id= "genotypes_individuals".individual_id中选择* ("genotypes_individuals".genotype_id = 53430)

每次将一个新的个体添加到db中时，联接表就会增长约300万行。从设计的角度来看，这似乎很糟糕，因为添加新的个人会降低使用现有数据的任何进程的性能。

我知道数据库是为了高效地处理大型表而设计的，但是由于驱动器IO，我们已经遇到了瓶颈。单个查询仍然无关紧要，但1000 s的查询加起来很快。通过将db扩展到多个驱动器，我们可以在一定程度上缓解这个问题。然而，我想看看是否还有其他的选择。我一直在想，是否有可能通过individual_id隔离连接表条目，这可能通过向联接表中添加额外的单个基因型行，从而使从个人到基因型的查找不受影响。还是指数已经这么做了？

Answer 1

你看过表分区吗

Answer 2

我会考虑测试一个使用自然键而不是id号的模式。

你查了一个人的所有基因型

SELECT * 
FROM "genotypes" 
INNER JOIN "genotypes_individuals" 
        ON "genotypes".id = "genotypes_individuals".genotype_id 
WHERE ("genotypes_individuals".individual_id = 2946 )

变成了

SELECT * 
FROM genotypes_individuals
WHERE (individual_id = 2946)

有时候会更快。有时候却不是。

在我们的生产系统上切换到自然键，性能中值提高了10倍。一些查询运行速度比自然键快100倍，因为自然键消除了大量的连接。有些查询也运行得较慢。但中位提速还是令人印象深刻的。