mysql - 连接 2 个 MySQL 临时表比连接临时表和普通表并添加 WHERE 子句慢 50 倍？

Question

我有 2 个组，我试图找到它们的交集(需要 2 列匹配)，我发现连接 2 个临时表产生的性能比仅连接一个原始表慢 50 倍临时表。这对我来说毫无意义，所以也许有人可以启发我？

这是我编写 2 个临时表版本的方式:

CREATE TEMPORARY TABLE attendees (
    event_id SMALLINT(5) UNSIGNED,
    person_id INT(10) UNSIGNED NOT NULL,
    KEY(event_id),
    KEY(person_id)
);
INSERT INTO attendees (event_id, person_id) 
    SELECT event_id, person_id
    FROM attendance WHERE year=2013
    GROUP BY event_id, person_id;
CREATE TEMPORARY TABLE invitees (
    event_id SMALLINT(5) UNSIGNED,
    person_id INT(10) UNSIGNED NOT NULL,
    KEY(event_id),
    KEY(person_id)
);
INSERT INTO invitees (event_id, person_id)
    SELECT event_id, person_id
    FROM invitations WHERE year=2013
    GROUP BY event_id, person_id;
SELECT i.event_id, COUNT(DISTINCT i.person_id)
    FROM attendees AS a
    INNER JOIN invitees AS i
        ON a.person_id = i.person_id AND a.event_id = i.event_id
    GROUP BY i.event_id;

这 2 个临时表中的每一个都不到 2,000 行，但这个最终查询在我的笔记本电脑上花费了大约 2.5 秒。我不明白这怎么可能。

另一方面，通过以下实现，最终查询只需要 0.05 秒，即使它访问了完整的邀请表(约 100,000 行):

CREATE TEMPORARY TABLE attendees (
    event_id SMALLINT(5) UNSIGNED,
    person_id INT(10) UNSIGNED NOT NULL,
    KEY(event_id),
    KEY(person_id)
);
INSERT INTO attendees (event_id, person_id) 
    SELECT event_id, person_id
    FROM attendance WHERE year=2013
    GROUP BY event_id, person_id;
SELECT i.event_id, COUNT(DISTINCT i.person_id)
    FROM attendees AS a
    INNER JOIN invitations AS i
        ON a.person_id = i.person_id AND a.event_id = i.event_id
    WHERE i.year=2013
    GROUP BY i.event_id;

就其值(value)而言，两个原始表(attendance 和 invitations)都有关于 event_id、person_id 和 year 的索引。我之所以首先要编写如此复杂的代码，是因为有些事件的参加者没有被邀请，我必须计算这些人(参加和被邀请，出席未受邀、受邀未出席、两者均未出席)。

我想我的问题是，这里发生了什么使第二个版本更快？

以防万一，我的服务器版本是 5.5.36 MySQL Community Server(5.6 有几个奇怪的行为破坏了我的网站)。

Answer 1

一个连接只能对每个表使用一个索引。不是在 person_id 和 event_id 上单独索引，而是在两个表上提供复合索引:

CREATE TEMPORARY TABLE attendees (
    event_id SMALLINT(5) UNSIGNED,
    person_id INT(10) UNSIGNED NOT NULL,
    KEY(event_id, person_id)
);

CREATE TEMPORARY TABLE invitees (
    event_id SMALLINT(5) UNSIGNED,
    person_id INT(10) UNSIGNED NOT NULL,
    KEY(event_id, person_id)
);

我怀疑原始的 attendance 表有这样的索引，这使得与该表的连接更快。