文本字段上的 COUNT 和 GROUP BY 似乎很慢

Question

我正在构建一个 MySQL 数据库，其中包含有关酵母物种 DNA 特殊子串的条目。我的表如下所示：

+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| species      | text    | YES  | MUL | NULL    |       |
| region       | text    | YES  | MUL | NULL    |       |
| gene         | text    | YES  | MUL | NULL    |       |
| startPos     | int(11) | YES  |     | NULL    |       |
| repeatLength | int(11) | YES  |     | NULL    |       |
| coreLength   | int(11) | YES  |     | NULL    |       |
| sequence     | text    | YES  | MUL | NULL    |       |
+--------------+---------+------+-----+---------+-------+

大约有 180 万条记录。在一种类型的查询中，我想查看有多少 DNA 子串与每种类型的物种和区域相关，因此我发出此查询：

select species, region, count(*) group by species, region;

物种和区域列只有两个可能的条目（物种的保守/scer，启动子/编码）对于区域），但此查询大约需要 30 秒。

考虑到表的大小，对于此类查询来说，这是正常的预期时间吗？它是否很慢，因为我使用文本字段而不是简单的整数或布尔值（我更喜欢文本字段，因为一些非 CS 研究人员将使用数据库）。任何其他想法和建议将受到欢迎。

如果这是一个愚蠢的问题，请原谅，我是一个 SQL 新手。

PS我也看到了这个问题 但提议的解决方案似乎与我正在做的事情无关。

编辑：将这些字段转换为 VARCHAR 将运行时间减少到约 2.5 秒。请注意，我还针对具有相似计时的 ENUM 进行了计时。

Answer 1

为什么所有基于字符串的列都定义为 TEXT？如果您阅读性能比较，您会发现 TEXT 比使用相同索引的 VARCHAR 列慢约 3 倍： http://forums.mysql.com/read.php?24,105964,105964

Answer 2

如果您的字段只会有 2 个值，那么最好将它们设置为布尔值。您还应该将所有内容设置为NOT NULL，除非有真正的原因需要将其设置为NULL。

另请查看 ENUM 类型< /a> 为列使用有限数量的人类可读值的更好方法。

至于速度慢，首先要尝试的是在列上创建索引。对于您在此处显示的特定查询，物种、地区 上的索引应该会产生巨大的差异：

create index on mytablename (species, region);

应该做到这一点。