为什么LEFT JOIN比INNER JOIN慢？

Question

我有两个查询，第一个（内连接）非常快，而第二个（左连接）非常慢。如何使第二个查询变快？

EXPLAIN SELECT saved.email FROM saved INNER JOIN finished ON finished.email = saved.email;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  finished    index   NULL    email   258 NULL    32168   Using index
1   SIMPLE  saved   ref email   email   383 func    1   Using where; Using index

EXPLAIN SELECT saved.email FROM saved LEFT JOIN finished ON finished.email = saved.email;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  saved   index   NULL    email   383 NULL    40971   Using index
1   SIMPLE  finishedindex   NULL    email   258 NULL    32168   Using index

编辑：我已经在下面为两个表格添加了信息。

CREATE TABLE `saved` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) DEFAULT NULL,
  `email` varchar(127) NOT NULL,
  [omitted fields include varchar, text, longtext, int],
  PRIMARY KEY (`id`),
  KEY `slug` (`slug`),
  KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=56329 DEFAULT CHARSET=utf8;

CREATE TABLE `finished` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) DEFAULT NULL,
  `submitted` int(11) DEFAULT NULL,
  `status` int(1) DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  [omitted fields include varchar, text, longtext, int],
  PRIMARY KEY (`id`),
  KEY `assigned_user_id` (`assigned_user_id`),
  KEY `event_id` (`event_id`),
  KEY `slug` (`slug`),
  KEY `email` (`email`),
  KEY `city_id` (`city_id`),
  KEY `status` (`status`),
  KEY `recommend` (`recommend`),
  KEY `pending_user_id` (`pending_user_id`),
  KEY `submitted` (`submitted`)
) ENGINE=MyISAM AUTO_INCREMENT=33063 DEFAULT CHARSET=latin1;

- allenylzhou

---

我认为这应该属于dba.stackexchange.com。 - Leonardo Herrera

@Marcus Adams：根据解释输出 - 确实存在这样的。 - zerkms

为两个表添加了SHOW CREATE TABLE输出 - allenylzhou

尝试使用 SELECT STRAIGHT_JOIN ... FROM finished LEFT JOIN saved ...。 - Deadooshka

请查看我的更新答案以了解实际问题。 - Marcus Adams

Answer 1

使用INNER JOIN时，MySQL通常会从具有最少行数的表开始。在这种情况下，它从表finished开始，并使用saved.email上的索引查找相应记录。

对于LEFT JOIN，（不包括某些优化）MySQL通常按顺序连接记录（从最左边的表开始）。在这种情况下，MySQL从表saved开始，然后尝试在finished中查找每个相应的记录。由于finished.email上没有可用的索引，因此必须针对每个查找进行全面扫描。

编辑：

现在，您发布了模式，我可以看到MySQL在从utf8到latin1字符集时忽略了索引（finished.email）。您没有发布每列的字符集和排序规则，因此我按照表的默认字符集进行操作。排序规则必须兼容，以便MySQL使用索引。

MySQL可以将一个非常有限的latin1排序规则强制转换（升级）为如unicode_ci这样的utf8排序规则（因此第一个查询可以通过将latin1排序规则升级为utf8来使用saved.email上的索引），但反之则不成立（第二个查询无法使用finished.email上的索引，因为它无法将utf8排序规则降级到latin1）。

解决方案是将两个电子邮件列更改为兼容的排序规则，可能最容易的方法是使它们具有相同的字符集和排序规则。

- Marcus Adams

---

但是在finished.email上有一个索引。 - allenylzhou

很好，你对字符集的区别抓得很准。 - spencer7593

字符集点确实是一个非常好的优化点。它让我的一个查询时间从1小时降到了87毫秒。 - Darwayne

"为了让MySQL使用索引，排序规则必须兼容。" -- 谢谢，我没有考虑到排序规则是问题的一部分。我将表格转换为使用相同的排序规则，查询时间从几分钟降至一秒左右。谢谢！ - Sammy Larbi

哦，天哪，你是救星！我们的查询从 8 小时缩短到了 1 分钟，因为我们在表格中使用了不同的编码格式。非常感谢。 - Christian Saiki

Answer 2

LEFT JOIN 查询比 INNER JOIN 查询慢，因为它要执行更多的操作。从 EXPLAIN 输出可以看出，MySQL 正在执行嵌套循环连接。对于 INNER JOIN 查询，MySQL 使用有效的“ref”（索引查找）操作来定位匹配的行。但是对于 LEFT JOIN 查询，看起来 MySQL 正在执行完整的索引扫描以查找匹配的行。因此，使用嵌套循环连接操作，MySQL 需要为另一个表中的每一行执行全索引扫描。因此，扫描数量大约为数万次，每个扫描都要检查数万行。根据 EXPLAIN 输出的预估行数，这将需要 (40971*32168=) 1,317,955,128 个字符串比较。INNER JOIN 查询避免了大量的工作，所以速度要快得多。它通过使用索引操作来避免所有这些字符串比较。

-- LEFT JOIN
id select table    type   key   key_len ref    rows  Extra
-- ------ -------- -----  ----- ------- ----  -----  ------------------------
1  SIMPLE saved    index  email     383 NULL  40971  Using index
1  SIMPLE finished index  email     258 NULL  32168  Using index

-- INNER JOIN 
id select table    type   key   key_len ref    rows  Extra
-- ------ -------- -----  ----- ------- ----  -----  ------------------------  
1  SIMPLE finished index  email     258 NULL  32168  Using index
1  SIMPLE saved    ref    email     383 func      1  Using where; Using index
                   ^^^^^                ^^^^  ^^^^^  ^^^^^^^^^^^^

注意：Markus Adams注意到您在问题中添加的CREATE TABLE语句中的电子邮件列字符集存在差异。

我认为正是这些字符集的差异阻止了MySQL使用索引来查询。

Q2：如何使左连接查询更快？

A：我认为，如果不进行模式更改（例如将两个电子邮件列的字符集更改为匹配），那么很可能无法使特定的查询运行更快。

唯一看起来产生“重复”行的“外连接”至finished表的影响是在找到多个匹配行时。 我不理解为什么需要外部连接。 为什么不完全取消它，然后执行：

SELECT saved.email FROM saved

- spencer7593

---

我们有获胜者了 :-) 很好的解释，+1 - zerkms

我错过了字符集的差异（Markus注意到了），这可能解释了为什么MySQL没有使用“ref”连接操作。 - spencer7593

我会接受Adam的答案，因为字符集是罪魁祸首，但感谢您详细解释了LEFT JOIN和INNER JOIN之间的区别。 - allenylzhou

Answer 3

我担心需要更多的信息。但是，内连接会消除任何具有空外键（如果您这么说的话，就是没有匹配项）的项目。这意味着需要扫描的行数减少了，以便进行关联。然而，对于左连接，任何不匹配的项都需要给出一个空白行，因此必须扫描所有行，不能消除任何内容。这使数据集变得更大，并且需要更多的资源来处理。此外，在编写select语句时，请不要使用select * - 而是明确指定您想要的列。

- Frank V

---

这里根本没有进行行扫描。您是正确的，LEFT JOIN 返回了更多的行，但是如解释所示，无论哪种情况下都可以使用索引计算连接。 - Mike Brant

@MikeBrant，“使用索引”并不意味着它使用了索引来满足“WHERE”条件。 “Using where; Using index”表示它使用索引来满足“WHERE”条件。 - Marcus Adams

@MarcusAdams 在这个例子中没有 WHERE 子句。我是在谈论索引被用来满足连接条件的情况。 - Mike Brant

@Mike Brant：实际上这并不正确。using index而没有using where意味着索引只是被读取来获取数据（以避免读取实际的数据页）。因此，在第二个EXPLAIN输出中，它基本上是一个索引全扫描，而不是查找。因此它很慢。在第一个查询中有WHERE（在优化期间，ON节点转换为WHERE子句）。 - zerkms

@zerks 是的，同意索引会被扫描并读入内存。我更多是在指出答案提到了所有行都被扫描的问题。似乎在暗示进行全表扫描，但这里并非如此。 - Mike Brant

@Mike Brant: 仍然是完整索引扫描，会导致完全相同的效果：两个集合的笛卡尔积。因此，在我的观点中，这样说是公平的（但需要仔细解释以避免进一步的混淆）。然而，我完全同意问题根源的解释有点含糊不清，在目前的形式下并没有解释任何东西 :-) - zerkms

Answer 4

saved.email和finished.email的数据类型有两个不同之处。首先，它们具有不同的长度。其次，finished.email可以为NULL。因此，您的LEFT JOIN操作无法利用finished.email上的索引。

您能否将finished.email的定义更改为与其连接的字段匹配？

`email` varchar(127) NOT NULL

如果您这样做，可能会加速。

- O. Jones