如何提升分组速度

Question

我有一个拥有超过3B行的大表格，我的查询现在需要3天才能执行。

这是我的查询：

insert into frequencywords (word, frequency, filename)
select word, count(*) as frequency, max(filename)
from allwordstemp
group by word

基本上，我的查询是从allwordstemp表格按照word进行分组，并且我想知道当frequency = 1时的filename，这就是为什么我添加了max(filename)，因为filename需要包含在聚合函数中，比如max。如果frequency > 1，我也不需要filename的值。 这两个表格都在word和filename上有2个索引。

allwordstemp表格（filename是一个id文件）：

CREATE TABLE `allwordstemp` (
  `word` varchar(45) DEFAULT NULL,
  `filename` int(11) DEFAULT NULL,
  KEY `idx_allwordstemp_word` (`word`),
  KEY `idx_allwordstemp_filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

frequencywords表格：

CREATE TABLE `frequencywords` (
  `word` varchar(45) DEFAULT NULL,
  `frequency` int(11) DEFAULT NULL,
  `filename` int(11) DEFAULT NULL,
  KEY `idx_frequencywords_word` (`word`),
  KEY `idx_frequencywords_frequency` (`frequency`),
  KEY `idx_frequencywords_filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

解释选择：

+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key                   | key_len | ref  | rows       | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------------+----------+-------------+
|  1 | SIMPLE      | allwordstemp | NULL       | index | NULL          | idx_allwordstemp_word | 138     | NULL | 3487864881 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------------+----------+-------------+

我该如何使我的查询更快？

Answer 1

您的筛选条件似乎是基于频率，而不是单词或文件名。所以我首先会按频率对所有单词进行索引。

然后，假设频率是一个整数，我会添加一个WHERE子句，如下所示：

select word, max(filename)
from allwordstemp
where frequency = 1
group by word

以上将给您提供一个在各种文件名中仅出现一次的所有单词列表。

希望这对您有所帮助，祝一切顺利！