mysql - 使用枚举列替代数据库设计，导致性能不佳

Question

以下关于 my previous question 的评论，我在这里描述了导致我有一个带有枚举列的数据库模式的问题，导致性能不佳。

(有关我的总体结论，请参阅此问题底部的编辑)

我处理基因表达数据。我们捕获其中condition任何gene被表达(例如，说基因 X 在条件[器官 Y - 生命阶段 Z] 中表达)。我有 4 dataType s 可以产生这样的表达数据。因此，例如，我的原始数据存储在不同的表中(这只是一个说明性示例，原始数据要复杂得多):

+--------------------+------------------------------------+------+-----+--------------+-------+
| Field              | Type                               | Null | Key | Default      | Extra |
+--------------------+------------------------------------+------+-----+--------------+-------+
| geneId             | int(10) unsigned                   | NO   | PRI | NULL         |       |
| evidenceId         | varchar(70)                        | NO   | PRI | NULL         |       |
| experimentId       | varchar(70)                        | NO   | MUL | NULL         |       |
| conditionId        | mediumint(8) unsigned              | NO   | MUL | NULL         |       |
| expressionId       | int(10) unsigned                   | NO   | MUL | NULL         |       |
| detectionFlag      | enum('expressed', 'not expressed') | NO   |     | NULL         |       |
| quality            | enum('low quality','high quality') | NO   |     | NULL         |       |
+--------------------+------------------------------------+------+-----+--------------+-------+

每个 dataType 我都有一张这样的表.现在，典型的查询将同时请求数千个基因。因为数据非常大(每个表中有几亿行)，并且包含冗余值(相同 gene 的大量证据，相同证据的 gene 吨)，因此非常慢分别查询每个表。出于这个原因，我们有一个预先计算的“汇总”表，它是根据这 4 个表中的信息计算得出的:

+----------------+-----------------------+------+-----+---------+----------------+
| Field          | Type                  | Null | Key | Default | Extra          |
+----------------+-----------------------+------+-----+---------+----------------+
| expressionId   | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| geneId         | int(10) unsigned      | NO   | MUL | NULL    |                |
| conditionId    | mediumint(8) unsigned | NO   | MUL | NULL    |                |
+----------------+-----------------------+------+-----+---------+----------------+

(请注意，此表中还有其他有用的列)。 expressionId字段允许返回原始数据。

现在我的问题是:

对于每种数据类型，我们计算 summaryQuality ，基于支持表达式行的不同实验的数量，在 condition本身，但也考虑到任何相关的condition (我放出了相关的 condition ，但是，是的，condition 之间的关系可以存储在另一个表中)。

用户应该能够计算“全局”summaryQuality , 通过对支持 dataType 任意组合的表达式行的实验求和s。例如，他们应该能够说“给我从 dataType1 和 dataType2 中的实验总和得到 x 次实验支持的结果”，或者“从 dataType1 和 dataType2 以及 dataType3 和 dataType4 中的实验总和中给我 y 次实验支持的结果” .

所以我最终得到了以下设计:

+--------------------------+-----------------------+------+-----+---------+----------------+
| Field                    | Type                  | Null | Key | Default | Extra          |
+--------------------------+-----------------------+------+-----+---------+----------------+
| expressionId             | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| geneId                   | int(10) unsigned      | NO   | MUL | NULL    |                |
| conditionId              | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| dataType1ExperimentCount | smallint(5) unsigned  | NO   |     | 0       |                |
| dataType2ExperimentCount | smallint(5) unsigned  | NO   |     | 0       |                |
| dataType3ExperimentCount | smallint(5) unsigned  | NO   |     | 0       |                |
| dataType4ExperimentCount | smallint(5) unsigned  | NO   |     | 0       |                |
+--------------------------+-----------------------+------+-----+---------+----------------+

此表中的行是通过考虑所有 dataType 预先计算的。 s 和所有相关的 condition给定 conditionId 的 s .这计算起来非常慢。结果，该表有数亿行。

现在我的查询看起来像:

SELECT * FROM myTable WHERE geneId IN (?, ?, ?, ...) AND (dataType1ExperimentCount + dataType2ExperimentCount + dataType3ExperimentCount + dataType4ExperimentCount) >= ?;
SELECT * FROM myTable WHERE geneId IN (?, ?, ?, ...) AND (dataType1ExperimentCount + dataType2ExperimentCount) >= ?;

根据我上一个问题的答案，性能非常糟糕，因为这样的查询不能使用索引。我需要允许 dataType 的任意组合s。我需要允许添加新的 dataType s 在 future (从而使组合数量达到 32 或 64 非常快)。

我能想出什么更好的设计？

编辑用户 Rick James 的以下请求，显示创建表:

CREATE TABLE `expression` (
  `expressionId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `geneId` mediumint(8) unsigned NOT NULL,
  `conditionId` mediumint(8) unsigned NOT NULL,
  `dataType1ExperimentCount` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dataType2ExperimentCount` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dataType3ExperimentCount` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dataType4ExperimentCount` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`expressionId`),
  UNIQUE KEY `geneId` (`geneId`,`conditionId`),
  KEY `conditionId` (`conditionId`),
  CONSTRAINT `expression_ibfk_1` FOREIGN KEY (`geneId`) REFERENCES `gene` (`geneId`) ON DELETE CASCADE,
  CONSTRAINT `expression_ibfk_2` FOREIGN KEY (`conditionId`) REFERENCES `cond` (`conditionId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(并且，是的，给定的 geneId 在表中的行数少于给定的 conditionId ，因此多个唯一键的顺序正确)。

编辑，总体结论 :

@RickJame 的回答使我的查询运行速度提高了 4 到 5 倍，现在它们在合理的时间内运行。问题暂时解决了。

但是@Strawberry 是对的，我的设计可以改进(参见这个问题的评论)

但是在 MySQL 上，“正确”的设计使我的查询运行速度慢了 10 倍。我认为这是因为 MySQL 是一个基于行的数据库，非常适合检索单行上多列中的所有信息，就像我目前的“枚举”设计一样。

我认为长期的解决方案是使用@Strawberry 提出的正确设计，如@ŁukaszKamiński 的回答中所建议的那样，切换到基于列的数据库。因为那时，要检索的信息将在几行中，但在同一列中。

Answer 1

代替

PRIMARY KEY (`expressionId`),
UNIQUE KEY `geneId` (`geneId`,`conditionId`),

利用

PRIMARY KEY(`geneId`,`conditionId`),
INDEX (`expressionId`),

如果没有其他表引用 expressionId ，摆脱该列及其上的索引。

为什么这有帮助？数据用主键聚类；您正在查找 geneId 的数据，也就是PK的开始；因此可以更有效地获取数据，尤其是当表比 innodb_buffer_pool_size 大很多时(应该是 RAM 的 70% 左右)。