Mysql Group by 查询需要很长时间

Question

我在 mysql 数据库中有一个表“Words”。该表包含 2 个字段。 word(VARCHAR(256)) 和 p_id(INTEGER)。表的建表语句:

CREATE TABLE `Words` (
  `word` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `p_id` int(11) NOT NULL DEFAULT '0',
  KEY `word_i` (`word`(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

表中的示例条目是:

+------+------+
| word | p_id |
+------+------+
| a    |    1 |
| a    |    2 |
| b    |    1 |
| a    |    4 |
+------+------+

此表包含 30+ 百万个条目。我正在按查询运行一个组，运行该查询需要 90 多分钟。我正在运行的分组查询是:

SELECT word,group_concat(p_id) FROM Words group by word;

为了优化这个问题，我使用以下查询将表中的所有数据发送到一个文本文件中。

SELECT p_id,word FROM Words INTO OUTFILE "/tmp/word_map.txt";

之后，我编写了一个 Perl 脚本来读取文件中的所有内容并对其进行解析并从中生成散列。与按查询分组(<3 分钟)相比，它花费的时间非常少。最终哈希有 1400 万个键(词)。它占用了很多内存。那么有什么办法可以提高Group BY查询的性能，这样我就不需要经历上面提到的所有步骤了吗？

美国东部时间:我在下面添加 my.cnf 文件条目。

[mysqld]
datadir=/media/data/.mysql_data/mysql
tmpdir=/media/data/.mysql_tmp_data
innodb_log_file_size=5M
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
group_concat_max_len=4M
max_allowed_packet=20M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
tmpdir=/media/data/.mysql_tmp_data/

谢谢，

红酒

Answer 1

我认为你想要的索引是:

create index words_word_pid on words(word, pid)

这有两件事。首先，group by 可以通过索引扫描处理，而不是加载原始表并对结果进行排序。

其次，该索引还消除了加载原始数据的需要。

我的猜测是原始数据不适合内存。因此，处理过程(有效地)遍历索引，找到单词，然后需要加载包含该单词的页面。好吧，最终内存会填满，并且带有单词的页面不在内存中。该页面是从磁盘加载的。下一页可能不在内存中，该页面是从磁盘加载的。等等。

您可以通过增加内存大小来解决此问题。您还可以通过拥有一个涵盖查询中使用的所有列的索引来解决此问题。