数十亿行mysql表/ cassandra模型-(以及一些生物学：)

Question

我在一个生物学实验室工作，我必须设计一个数据库来存储许多DNA微阵列实验结果。

每个实验由多个微阵列组成(平均10个)，每个微阵列包含500多万个探针。每个探针被映射到一个特定的基因id，当然，在所有的实验中，相同的探针匹配相同的gene_id。其目的是存储每个微阵列的强度值，以便能够在特定实验中快速检索特定基因id的探针的强度值。

实际上，一个简单的mysql表就足够了，看起来如下所示：

强度表：|probe_id|experiment_id|microarray_id|gene_id|intensity_value

具有由(probe_id、experiment_id、microarray_id、gene_id)组成的主键

，这是一个问题，：每个实验都有许多微阵列，它有超过500万个探针。通过1000个实验，平均10个微阵列(wich是一个很低的估计值，有些是数百个)，它的1000 * 10 * 5M =500亿行。我想会很慢。我完全不知道如何处理一个数十亿行mysql表。这有可能吗？有小费吗？

我对noSQL数据库也很好奇。我从来没有使用过卡桑德拉，但在我看来，这将是完美的任务，对吗？我能想象出这样的谢马：

{
experiment_id_1:{ <- thats a super collumnFamilly ?
    gene_id_1:{ <- thats a collumnFamilly ?
        probe_id_1:{ value_microarray_1, value_microarray_2, ... }, <- thats a superCollumn ?
        probe_id_2:{ value_microarray_1, value_microarray_2, ... },
        probe_id_3:{ value_microarray_1, value_microarray_2, ... },
        ...
    },
    gene_id_2:{
        probe_id_1:{ value_microarray_1, value_microarray_2, ... },
        probe_id_2:{ value_microarray_1, value_microarray_2, ... },
        probe_id_3:{ value_microarray_1, value_microarray_2, ... },
        ...
    }
}
experiment_id_2{
    ...
}
...
}

我很严厉吗？它符合卡桑德拉模型吗？这样会有效率吗？你认为noSQL古鲁:)

谢谢。

Answer 1

在这种方法中，我还会考虑NoSQL数据库上的关系。如果您做了一些考虑，您将能够检查您是否能够处理数据：

1. 表的预期大小是多少，要得到一个粗略的想法，检查一组数据的大小，并通过将其与预期的数据总数相乘来计算总体大小。
2. 计算索引的大小
3. 现在检查一下，您的服务器是否能够在RAM中处理这些索引，或者更好地处理RAM中的整个表。
4. 这个表上的DML/选择操作之间的比率是多少？
5. 确保您有适当的策略来处理常见的任务，如备份、优化、修改这些表。

如果我必须处理哪种情况，我通常会生成一些测试数据，这些数据与我希望在我的表中拥有的数据非常相似，并且使用不同的服务器参数。此外，我还考虑在本例中使用表的划分 (例如，experiment_id上的分区)。这将导致表分裂为较小的子集，这些子集可以在现有硬件边界上处理。不要胆敢自己做这件事，MySQL可以为您做这件事，并且该表将作为单个表呈现给用户。但是机器只需要处理存储给定experiment_id的数据集的部分。这会导致更快的I/O等。

我已经看过一些机器，这些机器处理的表比预期的行数容易得多，但是您必须仔细规划这些设置，通常需要进行大量的测试/优化/重新设计，然后才能投入生产。但这种努力总是值得的，因为这是一件很有趣的事情。

(在我学习期间，我在处理embl数据时，第一次在这个领域取得了经验，这成为了我的激情；)

Answer 2

考虑到这一点：

每个实验都有一个表，其中有列(probe_id、gene_id、array_of_values)。如果我正确地理解了您，主键将在probe_id上(但是如果您不查询这个列，您就不能有主键)。另外，您需要在gene_id上建立一个索引。

因此，您有1000个表，每个表有可管理的500万行。好还是不好？这符合您的查询模式吗？该方案的一个优点是它便于删除旧数据。

顺便说一句，如果您考虑postgresql而不是mysql，那么它具有本机数组类型。否则，您应该找到一种高效的序列化数组的方法。

无论如何，这应该是很容易测试的。

Answer 3

RDBMS根本不应该被该卷阻塞。你的数据有足够的结构，所以有足够的意义把它们放在关系中。

取决于您的存储空间，MySQL可以处理这个问题。我可能建议将表分区放在单独的表中，而不是存储管理位置。

相关数据库中有多少行太多了？