如何提升分組速度

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

以上將提供您一個在各種檔案名稱中僅出現一次的所有單字清單。

希望這對您有幫助，祝一切順利！