左加入表现优于内联？

我一直在我正在处理的应用程序中分析一些查询,并且我遇到了一个查询,它检索的行数超过了必要的数量,结果集在应用程序代码中被删除了.

将LEFT JOIN更改为INNER JOIN会将结果集修剪为所需的内容,并且可能也会更高效(因为选择的行数较少).实际上,LEFT JOIN'ed查询的表现优于INNER JOIN'ed,花费了一半的时间来完成.

LEFT JOIN :(总共127行,查询耗时0.0011秒)

INNER JOIN :(总共10行,查询耗时0.0024秒)

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

在两者上运行EXPLAIN都没有解释性能差异:

对于INNER JOIN:

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

对于LEFT JOIN:

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

(我正在谈论的条款是'用户'表上的最后一个INNER JOIN)

该查询在MySQL 5.1数据库上运行,如果它有所不同.

有没有人知道为什么LEFT JOIN'ed查询在这种情况下优于INNER JOIN'ed？

更新:由于Tomalak的建议,我正在使用的小表使INNER JOIN变得更复杂,我创建了一个带有一些模拟数据的测试数据库.'users'表是5000行,contacts表是~500,000行.结果是一样的(时间也没有改变,当你考虑到现在表格更大时,这是令人惊讶的).

我还在联系人表上运行了ANALYZE和OPTIMIZE.没有任何明显的区别.

如果您认为LEFT JOIN的实现是INNER JOIN +更多的工作,那么这个结果会令人困惑.如果INNER JOIN的实现是(LEFT JOIN +过滤)怎么办？啊,现在很清楚了.

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

这是一种与where子句中通常使用的不同类型的过滤.在A上创建索引以支持此过滤操作非常简单.

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上的条件.

这可能是由于INNER JOIN必须检查两个表中的每一行,以查看列值(在您的情况下是电子邮件)是否匹配.无论如何,LEFT JOIN都将从一个表中返回.如果它已编入索引,那么它也会更快地知道该做什么.