MySQL左加入分组 - 索引优化

Question

我试图优化一个涉及两个表的左连接，但是我无法让我的头绕着可能的索引加速事情。 表1包含2171289行：

text_metadata_for_nzcorpus | CREATE TABLE `text_metadata_for_nzcorpus` (
    `text_id` varchar(255) NOT NULL, 
    `newspaper` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `year` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `month` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `day` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `section` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `subsection` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `topics` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 
    `words` int(11) NOT NULL DEFAULT '0', 
    `cqp_begin` bigint(20) unsigned NOT NULL DEFAULT '0', 
    `cqp_end` bigint(20) unsigned NOT NULL DEFAULT '0', 
    PRIMARY KEY (`text_id`), 
    KEY `newspaper` (`newspaper`), 
    KEY `year` (`year`), 
    KEY `month` (`month`), 
    KEY `day` (`day`), 
    KEY `section` (`section`), 
    KEY `subsection` (`subsection`), 
    KEY `topics` (`topics`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

第二个表只包含8584行：

db_dist_fb8ddyk760 | CREATE TABLE `db_dist_fb8ddyk760` (
    `text_id` varchar(255) COLLATE utf8_bin DEFAULT NULL, 
    `beginPosition` int(11) DEFAULT NULL, 
    `endPosition` int(11) DEFAULT NULL, 
    `refnumber` mediumint(9) NOT NULL AUTO_INCREMENT, 
    KEY `refnumber` (`refnumber`), 
    KEY `text_id` (`text_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=utf8 COLLATE=utf8_bin | 

我需要运行以下类型的查询：

SELECT md.day as handle, count(db.text_id) as hits, 
    count(distinct db.text_id) as files FROM text_metadata_for_nzcorpus as md 
    LEFT JOIN db_dist_fb8ddyk760 as db on md.text_id = db.text_id 
    GROUP BY md.day; 

目前这需要更多处理时间超过5秒。由于这是我在网页上显示输出之前需要运行的很多查询中的一种，如果可能的话，我希望加快速度。这里是“解释”的输出：

+----+-------------+-------+-------+---------------+---------+---------+----------------------+---------+--------------------------+ 
| id | select_type | table | type | possible_keys | key  | key_len | ref     | rows | Extra     | 
+----+-------------+-------+-------+---------------+---------+---------+----------------------+---------+--------------------------+ 
| 1 | SIMPLE  | md | index | day   | day  | 768  | NULL     | 2452080 | Using index    | 
| 1 | SIMPLE  | db | ref | text_id  | text_id | 768  | cqpweb_db.md.text_id |  1 | Using where; Using index | 
+----+-------------+-------+-------+---------------+---------+---------+----------------------+---------+--------------------------+ 

任何有帮助的建议，将不胜感激。 （我不是系统的开发人员，我不负责代码本身 - 但如果事情可以改进，我想为程序员提供输入...）

非常感谢！ Sebastian

Answer 1

您的EXPLAIN报告显示您已经在两个表中使用索引，并且您没有为GROUP BY使用临时表，并且两个表都使用覆盖索引（“使用索引”）。

一些其他的事情，你除了可以创建索引做：

• 定义db_dist_fb8ddyk760.text_id为NOT NULL。这可能会消除“使用哪里”笔记，这意味着它必须评估表达式作为搜索的一部分。这可能会稍微更有效率。
• 将db_dist_fb8ddyk760.text_id定义为该表的PRIMARY KEY，如果这样做合理 - 换句话说，如果text_id在该表中是唯一的。这样，“type：ref”将变成“type：eq_ref”，这意味着一个独特的密钥查找，这更有效一些。但是，如果此表需要为每个text_id记录多个匹配，当然会忽略此建议。
• 将您的innodb_buffer_pool_size增加得足够多，以便索引可以缓存在内存中。如果查询只从缓冲池读取索引页，则可以获得更好的性能和更少的磁盘I/O。
• 利用MySQL Query Cache，所以如果您再次运行相同的查询，它将重用先前查询的结果。但是，如果这些表中的数据更改频率比执行查询更频繁，则查询缓存可能没什么用处。
• 考虑将结果缓存在应用程序内存或memcached或其他东西中。

---

回复您的评论：

顺便说一句，表db_dist_fb8ddyk760很可能只有一次或两次，然后丢弃使用。

那你为什么要将它存储在持久数据库中呢？

考虑使用像Redis一样的内存中键/值存储。使每个键对应一天，并且每个值都是包含点击次数和不同text_id集合的结构。这基本上是制作一个汇总表（您也可以在SQL中完成），但Redis是内存中的。

Answer 2

请勿盲目使用VARCHAR(255)。使用对数据有意义的数据类型。其中许多列听起来像数字，而不是字符串。

假设年+日+日只是DATE的一部分，请使用数据类型为DATE的单列。然后，使用DAY(date_col)提取日期。

每个InnoDB表应该有一个PRIMARY KEY。也许组合(text_id, beginPosition)是独一无二的，可能是PK？

每一列都是NULL ??我对此表示怀疑。让他们NOT NULL除非你有一个NULL的原因。

refnumber是AUTO_INCREMENT，但不是PRIMARY KEY？是什么赋予了？

进行上述更改将有助于某些。但是，所述的查询注定要扫描整个2M行表并进入另一个表。事情可以完成。但是他们将涉及构建和维护摘要表。