拥有数千张表的 MySQL 数据库

Question

所以我在 MySQL 中构建了一个数据库，其中包含大约 20,000 个表，每个表对应一个人类基因，其中每个基因的表都有一个列，列出了该基因在文献中发现的替代名称(同义词)，而且经常这些同义词没有逻辑，它们的存在纯粹是出于历史原因。

首先，有没有更好的方法来使用更少的表来设置这个数据库？

问题是每个基因都有不同数量的替代名称，所以我无法制作一个大表格，每一行对应一个基因和一定数量的列。即使每个基因都有相同数量的替代名称，任何特定的列基本上也毫无意义，因为，例如，基因 1 的第 1 列中的同义词与基因 2 的第 1 列中的同义词之间没有关系。

在 MySQL 中拥有数千个表到底有什么不好？

我可能会将数据库分成 23 个数据库(每个染色体一个)，或者类似的东西，然后每个数据库将只有大约 900 个表，这样的东西会更好吗？

我几乎觉得 MySQL(一种关系数据库)可能不适合这项工作。如果是这样，什么是更好的数据库范例？

Answer 1

20,000 张 table 很多。如果您实际上需要 20,000 张 table ，那么拥有 20,000 张 table 不一定是“坏”的。我们使用 innodb_file_per_table 运行，所以这是一大堆文件，我们可能会遇到 MySQL 中的一些限制(innodb_open_files、open_files_limit、table_cache_open) 又受操作系统 ulimit 的限制。

此外还有管理大量相同 表的潜在困难。如果我需要添加一列，我需要将该列添加到 20,000 个表中。那是 20,000 个 ALTER TABLE 语句。如果我错过了一些表格，这些表格将不再相同。如果可以的话，我只是不想去那里。

我会提出并考虑不同的设计。

作为第一次剪辑，像这样:

CREATE TABLE gene_synonym
( gene           VARCHAR(64) 
, synonym        VARCHAR(255)
, PRIMARY KEY (gene, synonym)
) ENGINE=InnoDB
;

要为基因添加同义词，而不是将值插入特定表的单个列:

INSERT INTO gene_synonym (gene, synonym) VALUES ('alzwhatever','iforgot');

查询时，我们不会确定要查询 20,000 个表中的哪个表，而是只查询一个 表并在 gene 列上包含一个条件:

SELECT gs.synonym 
  FROM gene_synonym gs
 WHERE gs.gene = 'alzwhatever'
 ORDER BY gs.synonym

WHERE 子句使我们可以查看一个大表的子集，返回的集合将模拟当前单个表之一。

如果我需要搜索同义词，我可以只查询这个表:

SELECT gs.gene
  FROM gene_synonym gs
 WHERE gs.synonym = 'iforgot'

要对 20,000 个表执行相同的搜索，我需要 20,000 个不同的 SELECT，每个表对应 20,000 个表。

我刚刚了解了数据类型。由于 MySQL 对表名有 64 个字符的限制，我将 gene 列限制为 64 个字符。

我们可以用当前设计中的表名填充基因列。

但是，这个表不能模拟的是一个空表，一个没有任何同义词的基因。 (或者我们的设计可能是基因的名称是它自身的同义词，所以我们会有一行 ('alzwhatever','alzwhatever')

无论哪种情况，我们都可能还想添加一个这样的表:

CREATE TABLE gene
( gene           VARCHAR(64) 
, PRIMARY KEY (gene)
) ENGINE=InnoDB
;

这是包含 20,000 行的表格，当前设计中的每个表格一行。

更进一步，我们可以添加一个外键约束

ALTER TABLE gene_synonym 
ADD CONSTRAINT FK_gene_synonym_gene (gene) REFERENCES gene (gene)
  ON UPDATE CASCADE ON DELETE CASCADE
;

这种设计更符合关系数据库的规范模式。

这并不是说其他​​设计“不好”。只是这种设计会更典型。