为什么LEFT JOIN比INNER JOIN慢? [英] Why is LEFT JOIN slower than INNER JOIN?

问题描述

我有两个查询，第一个(内部联接)超级快，第二个(左联接)超级慢.如何快速进行第二个查询?

I have two queries, the first one (inner join) is super fast, and the second one (left join) is super slow. How do I make the second query fast?

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

我在下面添加了两个表的表信息.

I have added table info for both tables down below.

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;

推荐答案

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

With INNER JOIN, MySQL generally will start with the table with the smallest number of rows. In this case, it starts with table finished and does a look up for the corresponding record in saved using the index on saved.email.

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

For a LEFT JOIN, (excluding some optimizations) MySQL generally joins the records in order (starting with the left most table). In this case, MySQL starts with the table saved, then attempts to find each corresponding record in finished. Since there is no usable index on finished.email, it must do a full scan for each look up.

修改

现在您已经发布了架构，我可以看到从utf8到latin1字符集时，MySQL忽略了索引(finished.email).您尚未发布每列的字符集和排序规则，因此我将使用表的默认字符集.这些排序规则必须兼容，以便MySQL使用索引.

Now that you posted your schema, I can see that MySQL is ignoring the index (finished.email) when going from utf8 to latin1 character set. You've not posted the character sets and collations for each column, so I'm going by the default character set for the table. The collations must be compatible in order for MySQL to use the index.

MySQL可以强制(升级)非常有限的latin1归类，直到utf8归类(例如unicode_ci)(因此第一个查询可以通过升级latin1来使用saved.email上的索引)归类为utf8)，但事实并非如此(第二个查询不能使用finished.email上的索引，因为它不能将utf8归类降级为latin1).

MySQL can coerce (upgrade) a latin1 collation, which is very limited, up to a utf8 collation such as unicode_ci (so the first query can use the index on saved.email by upgrading latin1 collation to utf8), but the opposite is not true (the second query can't use the index on finished.email since it can't downgrade a utf8 collation down to latin1).

解决方案是将两个电子邮件列更改为兼容的排序规则，这也许最简单的方法是将它们设置为相同的字符集和排序规则.

The solution is to change both email columns to a compatible collation, perhaps most easily by making them identical character sets and collations.

这篇关于为什么LEFT JOIN比INNER JOIN慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！