代码之家 › 专栏 › 技术社区 › Eran Galperin

左连接的性能优于内连接?

join performance database mysql

Eran Galperin · 技术社区 · 16 年前

我一直在分析一个正在处理的应用程序中的一些查询,我发现一个查询检索的行数超过了必需的行数,结果集在应用程序代码中被缩减。

将左连接更改为内部连接会将结果集修剪为所需的内容,而且可能还会更具性能(因为选择的行更少)。实际上,left join'ed查询的性能要优于inner join'ed,只需一半的时间就可以完成。

左联接:(共127行,查询耗时0.0011秒)

内部联接:(共10行,查询耗时0.0024秒)

(我多次运行查询,这些是平均值)。

运行explain on both无法解释性能差异:

对于内部连接:

id  select_type     table   type    possible_keys   key     key_len     ref        rows     Extra
1   SIMPLE  contacts        index       NULL        name        302     NULL         235    Using where
1   SIMPLE  lists           eq_ref      PRIMARY     PRIMARY     4   contacts.list_id     1   
1   SIMPLE  lists_to_users  eq_ref      PRIMARY     PRIMARY     8   lists.id,const  1    
1   SIMPLE  tags            eq_ref      PRIMARY     PRIMARY     4   lists_to_users.tag_id   1    
1   SIMPLE  users           eq_ref      email_2     email_2     302     contacts.email 1    Using where

对于左连接:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          contacts index      NULL        name        302     NULL    235     Using where
1   SIMPLE        lists     eq_ref      PRIMARY     PRIMARY     4   contacts.list_id    1    
1   SIMPLE    lists_to_users eq_ref     PRIMARY     PRIMARY     8   lists.id,const  1    
1   SIMPLE         tags     eq_ref      PRIMARY     PRIMARY     4   lists_to_users.tag_id   1    
1   SIMPLE        users     eq_ref      email_2     email_2     302     contacts.email  1

以及查询本身:

SELECT `contacts`.*, `lists`.`name` AS `group`, `lists`.`id` AS `group_id`, `lists`.`shared_yn`, `tags`.`name` AS `context`, `tags`.`id` AS `context_id`, `tags`.`color` AS `context_color`, `users`.`id` AS `user_id`, `users`.`avatar` 
FROM `contacts`  
LEFT JOIN `lists` ON lists.id=contacts.list_id  
LEFT JOIN `lists_to_users` ON lists_to_users.list_id=lists.id AND lists_to_users.user_id='1' AND lists_to_users.creator='1'  
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id 
INNER JOIN `users` ON users.email=contacts.email 
WHERE (contacts.user_id='1') 
ORDER BY `contacts`.`name` ASC

(我所说的子句是'users'表上的最后一个内部连接)

如果有什么不同的话,查询将在mysql 5.1数据库上运行。

在这种情况下,有人知道为什么左连接查询的性能优于内部连接吗?

更新: 由于tomalak建议我使用的小表使内部连接更加复杂,所以我创建了一个包含模拟数据的测试数据库。“users”表是5000行,contacts表是大约500000行。结果是一样的(而且时间也没有改变,这是令人惊讶的,当你认为现在的表大得多)。

我还在contacts表上运行analyze和optimize。没有明显的区别。

6 回复 | 直到 15 年前

Amy B 16 年前

如果您认为left join的实现是内部join+more work,那么这个结果是令人困惑的。如果内部连接的实现是(左连接+过滤)呢?啊,现在很清楚了。

在查询计划中,唯一的区别是: 用户…额外:使用位置 . 这意味着过滤。有一个 额外过滤步骤 在具有内部连接的查询中。

这是一种不同于通常在where子句中使用的筛选。在上创建索引以支持此筛选操作很简单。

SELECT *
FROM A
WHERE A.ID = 3

请考虑以下查询:

SELECT *
FROM A
  LEFT JOIN B
  ON A.ID = B.ID
WHERE B.ID is not null

此查询等效于内部联接。B上没有索引可帮助执行筛选操作。原因是where子句在联接结果上声明一个条件,而不是在b上声明一个条件。

HAdes 16 年前

这可能是因为内部连接必须检查两个表中的每一行,以查看列值(在您的示例中是电子邮件)是否匹配。不管怎样,左联接都将从一个表返回所有值。如果它被编入索引,那么它也会知道该怎么做才能更快。

Tomalak 16 年前

表基数对查询优化器有影响。我猜小表,因为你已经使内部连接更复杂的操作。一旦您拥有的记录超过了db服务器希望保留在内存中的记录,内部连接就可能开始优于左连接。

Greg Dean 16 年前

在我看来,你陷入了所谓的过早优化的陷阱。查询优化器是非常变化无常的事情。我的建议是,在您确定某个特定的连接有问题之前,继续前进。

Dannie Juge 15 年前

试试这个:

SELECT `contacts`.*, `lists`.`name` AS `group`, `lists`.`id` AS `group_id`, `lists`.`shared_yn`, `tags`.`name` AS `context`, `tags`.`id` AS `context_id`, `tags`.`color` AS `context_color`, `users`.`id` AS `user_id`, `users`.`avatar` 
FROM `contacts`  
INNER JOIN `users` ON contacts.user_id='1' AND users.email=contacts.email
LEFT JOIN `lists` ON lists.id=contacts.list_id  
LEFT JOIN `lists_to_users` ON lists_to_users.user_id='1' AND lists_to_users.creator='1' AND lists_to_users.list_id=lists.id
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id 
ORDER BY `contacts`.`name` ASC

这会给你带来额外的表现,因为:

在出现任何“左”或“右”连接之前,您将放置所有内部连接。这将在应用后续外部联接之前筛选出一些记录
“and”运算符的短路(和“matters”的顺序)。如果列和文本之间的比较为false,则不会对pks和fks表之间的比较执行所需的表扫描

如果您没有发现任何性能改进,则将所有列集替换为“count(*)”,然后执行左侧/内部测试。这样,不管查询是什么,您将只检索一行一列(计数),因此您可以放弃返回字节数是查询缓慢的原因:

SELECT COUNT(*)
FROM `contacts`  
INNER JOIN `users` ON contacts.user_id='1' AND users.email=contacts.email
LEFT JOIN `lists` ON lists.id=contacts.list_id  
LEFT JOIN `lists_to_users` ON lists_to_users.user_id='1' AND lists_to_users.creator='1' AND lists_to_users.list_id=lists.id
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id

祝你好运

-3

mike 16 年前

左连接返回的行比内部连接返回的行多,因为这两个连接不同。
如果left join在它要查找的表中找不到相关条目,它将返回该表的空值。
但是,如果内部连接找不到相关条目,它将不会返回整体全行。

但对于您的问题,您是否启用了查询缓存? 尝试使用运行查询

SELECT SQL_NO_CACHE `contacts`.*, ...

除此之外,我会用更多的数据填充表,运行

ANALYZE TABLE t1, t2;
OPTIMIZE TABLE t1, t2;

看看会发生什么。