左连接优于内连接？ [英] Left Join outperforming Inner Join?

问题描述

我在我正在处理的应用程序中一直在分析一些查询，我遇到了一个查询，检索的行数超过必要，结果集在应用程序代码中被修剪。

I've been profiling some queries in an application I'm working on, and I came across a query that was retrieving more rows than necessary, the result set being trimmed down in the application code.

将LEFT JOIN更改为INNER JOIN将结果集调整为所需的值，并且可能会更加高效（因为选择了更少的行）。实际上，LEFT JOIN的查询超过了INNER JOIN的值，花了一半的时间来完成。

Changing a LEFT JOIN to an INNER JOIN trimmed the result set to just what was needed, and presumably would also be more performant (since less rows are selected). In reality, the LEFT JOIN'ed query was outperforming the INNER JOIN'ed, taking half the time to complete.

LEFT JOIN：（127行，查询占用0.0011 sec）

LEFT JOIN: (127 total rows, Query took 0.0011 sec)

INNER JOIN：（共10行，查询需要0.0024秒）

INNER JOIN: (10 total rows, Query took 0.0024 sec)

运行EXPLAIN时，两者都不能解释性能差异：

Running EXPLAIN on both reveals nothing that explains the performance differences:

对于INNER JOIN：

For the 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

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

（我所说的子句是'users'表上的最后一个INNER JOIN）

(The clause that I'm talking about is the last INNER JOIN on the 'users' table)

任何人都有一个线索为什么LEFT JOIN的查询优于INNER JOIN的在这种情况下？

Does anyone has a clue on why the LEFT JOIN'ed query outperforms the INNER JOIN'ed on in this case?

更新：由于Tomalak的建议，我使用的小表使INNER JOIN更复杂， d使用一些模拟数据创建了一个测试数据库。 users表为5000行，联系人表为〜500,000行。结果是一样的（也是时间没有改变，这是令人惊讶的，当你认为表是更大的现在）。

UPDATE: Due to Tomalak's suggestion that the small tables I'm using were making the INNER JOIN more complex, I'd created a test database with some mock data. The 'users' table is 5000 rows, and the contacts table is ~500,000 rows. The results are the same (also the timings haven't changed which is surprising when you consider that the tables are much bigger now).

我也跑了ANALYZE和OPTIMIZE联系人表。

I also ran ANALYZE and OPTIMIZE on the contacts table. Didn't make any discernible difference.

推荐答案

如果你认为LEFT JOIN的实现是INNER JOIN + more work，这个结果令人困惑。如果INNER JOIN的实现是（LEFT JOIN + filtering）呢？现在很清楚。

If you think that the implementation of LEFT JOIN is INNER JOIN + more work, then this result is confusing. What if the implementation of INNER JOIN is (LEFT JOIN + filtering)? Ah, it is clear now.

在查询计划中，唯一的区别是： users ... extra：using where 。这意味着过滤。

In the query plans, the only difference is this: users... extra: using where . This means filtering. There's an extra filtering step in the query with the inner join.

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

This is a different kind of filtering than is typically used in a where clause. It is simple to create an index on A to support this filtering action.

SELECT *
FROM A
WHERE A.ID = 3

考虑这个查询：

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

此查询相当于inner join。 B上没有索引，将有助于该过滤操作。原因是where子句声明了对连接结果的条件，而不是B上的条件。

This query is equivalent to inner join. There is no index on B that will help that filtering action. The reason is that the where clause is stating a condition on the result of the join, instead of a condition on B.

这篇关于左连接优于内连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！