多连接MySQL查询的低效执行方案

Question

我对MySQL有一个性能问题；我的请求的执行计划似乎还远远不是最优的，但我不知道MySQL为什么选择它，也不知道如何更改它。我在一个最小的环境中复制了这个问题，下面是查询：

SELECT member.id, member_cache.id, section.id, topic.id
FROM topic
INNER JOIN (section
    INNER JOIN (member
        LEFT JOIN (member_cache) ON member_cache.id = member.id
    ) ON member.id = section.last_member
) ON section.id = topic.section
WHERE topic.last_time IS NOT NULL
ORDER BY topic.last_time DESC
LIMIT 0, 1

下面是这个查询中使用的表：

CREATE TABLE `member` (`id` int(10) unsigned NOT NULL)
    ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `member_cache` (`id` int(10) unsigned NOT NULL)
    ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `section` (`id` int(10) unsigned NOT NULL, `last_member` int(10) unsigned NOT NULL DEFAULT '0')
    ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `topic` (`id` int(10) unsigned NOT NULL, `section` int(10) unsigned NOT NULL, `last_time` int(10) unsigned NOT NULL)
    ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `member` ADD PRIMARY KEY (`id`);
ALTER TABLE `member_cache` ADD PRIMARY KEY (`id`);
ALTER TABLE `section`ADD PRIMARY KEY (`id`);
ALTER TABLE `topic` ADD PRIMARY KEY (`id`), ADD KEY `section__last_time` (`section`,`last_time`), ADD KEY `last_time` (`last_time`);

下面是用“解释”获得的执行计划：

+----+-------------+--------------+--------+------------------------------+--------------------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table        | type   | possible_keys                | key                | key_len | ref                           | rows | Extra                           |
+----+-------------+--------------+--------+------------------------------+--------------------+---------+-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | section      | ALL    | PRIMARY                      | NULL               | NULL    | NULL                          | 2188 | Using temporary; Using filesort |
|  1 | SIMPLE      | member       | eq_ref | PRIMARY                      | PRIMARY            | 4       | temporary.section.last_member |    1 | Using index                     |
|  1 | SIMPLE      | member_cache | eq_ref | PRIMARY                      | PRIMARY            | 4       | temporary.section.last_member |    1 | Using index                     |
|  1 | SIMPLE      | topic        | ref    | section__last_time,last_time | section__last_time | 4       | temporary.section.id          |  106 | Using index condition           |
+----+-------------+--------------+--------+------------------------------+--------------------+---------+-------------------------------+------+---------------------------------+

正如您所看到的，它首先扫描整个“区段”表，使用临时表并造成糟糕的性能。我真的不明白为什么会出现这种情况，因为"topic.last_time“(用于WHERE子句)和"section.id”(用于第一个内部联接中)都存在索引。我还做了几次测试，结果非常不稳定：

• 如果我在“主题”表上添加了一个显式的“强制索引”语句，那么显然MySQL正确地使用了索引"topic.last_time“和"section.id"，最终得到的结果要快得多，如下所示(但我无法从我正在使用的查询生成库生成这种特定于MySQL的扩展)。
• 如果我将第一个“内部联接”(针对“节”表)替换为“左联接”，结果可能是相同的，这可能是因为它阻止MySQL逆转联接的操作数(但左联接不是我想表达的)；
• 奇怪的是:如果我从表"section__last_time“中删除索引”“，那么我也会得到相同的结果。我真的不明白为什么这个索引会对执行计划产生影响？(无论如何，我需要它来进行其他查询，所以我不能删除它)

下面是我在应用上述三项更改中的任何一项之后的执行计划：

+----+-------------+--------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+
| id | select_type | table        | type   | possible_keys | key       | key_len | ref                           | rows | Extra       |
+----+-------------+--------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | topic        | index  | last_time     | last_time | 4       | NULL                          |    1 | Using where |
|  1 | SIMPLE      | section      | eq_ref | PRIMARY       | PRIMARY   | 4       | temporary.topic.section       |    1 | NULL        |
|  1 | SIMPLE      | member       | eq_ref | PRIMARY       | PRIMARY   | 4       | temporary.section.last_member |    1 | Using index |
|  1 | SIMPLE      | member_cache | eq_ref | PRIMARY       | PRIMARY   | 4       | temporary.section.last_member |    1 | Using index |
+----+-------------+--------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+

我还试图“优化表”所有表或切换到InnoDB引擎，但这些都没有改变任何东西。问题是在MySQL版本5.5.35和5.6.15上复制的；我还上传了测试环境这里的快照，在这里可以轻松地复制上面的查询。

你知道什么能解释这个执行计划吗？

Answer 1

考虑在section.last_member，section.id上添加一个索引。

ALTER TABLE section ADD KEY(last_member, id);

如果他们是无辜的，你可以省略ID，因为它已经是一个PK。