MySQL Left JOIN 更快还是Inner Join 更快？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1810465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left JOIN faster or Inner Join faster?
提问by Murvinlai
So... which one is faster (NULl value is not an issue), and are indexed.
所以......哪个更快（NULl 值不是问题），并且被索引。
SELECT * FROM A
JOIN B b ON b.id = a.id
JOIN C c ON c.id = b.id
WHERE A.id = '12345'
Using Left Joins:
使用左连接：
SELECT * FROM A
LEFT JOIN B ON B.id=A.bid
LEFT JOIN C ON C.id=B.cid
WHERE A.id = '12345'
Here is the actual query Here it is.. both return the same result
这是实际的查询 Here it is.. 两者都返回相同的结果
Query (0.2693sec) :
EXPLAIN EXTENDED SELECT *
FROM friend_events, zcms_users, user_events,
EVENTS WHERE friend_events.userid = '13006'
AND friend_events.state =0
AND UNIX_TIMESTAMP( friend_events.t ) >=1258923485
AND friend_events.xid = user_events.id
AND user_events.eid = events.eid
AND events.active =1
AND zcms_users.id = user_events.userid
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE zcms_users ALL PRIMARY NULL NULL NULL 43082
1 SIMPLE user_events ref PRIMARY,eid,userid userid 4 zcms_users.id 1
1 SIMPLE events eq_ref PRIMARY,active PRIMARY4 user_events.eid 1 Using where
1 SIMPLE friend_events eq_ref PRIMARY PRIMARY 8 user_events.id,const 1 Using where
LEFTJOIN QUERY: (0.0393 sec)
EXPLAIN EXTENDED SELECT *
FROM `friend_events`
LEFT JOIN `user_events` ON user_events.id = friend_events.xid
LEFT JOIN `events` ON user_events.eid = events.eid
LEFT JOIN `zcms_users` ON user_events.userid = zcms_users.id
WHERE (
events.active =1
)
AND (
friend_events.userid = '13006'
)
AND (
friend_events.state =0
)
AND (
UNIX_TIMESTAMP( friend_events.t ) >=1258923485
)
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE friend_events ALL PRIMARY NULL NULL NULL 53113 Using where
1 SIMPLE user_events eq_ref PRIMARY,eid PRIMARY 4 friend_events.xid 1 Using where
1 SIMPLE zcms_users eq_ref PRIMARY PRIMARY 4 user_events.userid 1
1 SIMPLE events eq_ref PRIMARY,active PRIMARY 4 user_events.eid 1 Using where
回答by ChssPly76
It depends; run them both to find out; then run an 'explain select' for an explanation.
这取决于; 运行它们以找出答案；然后运行“解释选择”以获得解释。
The actual performance difference may range from "virtually non-existent" to "pretty significant" depending on how many rows in A with id='12345' have no matching records in B and C.
实际性能差异可能从“几乎不存在”到“相当显着”，这取决于 A 中有多少行 id='12345' 在 B 和 C 中没有匹配的记录。
Update(based on posted query plans)
更新（基于发布的查询计划）
When you use INNER JOIN it doesn't matter (results-wise, not performance-wise) which table to start with, so optimizer tries to pick the one it thinks would perform best. It seems you have indexes on all appropriate PK / FK columns and you either don't have an index on friend_events.useridor there are too many records with userid = '13006'and it's not being used; either way optimizer picks the table with less rows as "base" - in this case it's zcms_users.
当您使用 INNER JOIN 时，从哪个表开始并不重要（结果明智，而不是性能明智），因此优化器会尝试选择它认为性能最好的表。似乎您在所有适当的 PK / FK 列上都有索引，而您要么没有索引，friend_events.userid要么有太多记录userid = '13006'而未使用；无论哪种方式，优化器都会选择行数较少的表作为“基础”——在这种情况下，它是zcms_users.
When you use LEFT JOIN it doesmatter (results-wise) which table to start with; thus friend_eventsis picked. Now whyit takes less time that way I'm not quite sure; I'm guessing friend_events.useridcondition helps. If you were to add an index (is it really varchar, btw? not numeric?) on that, your INNER JOIN might behave differently (and become faster) as well.
当您使用LEFT JOIN它做事情（结果明智）开始与表; 因此friend_events被选中。现在为什么我不太确定这样需要更少的时间；我猜friend_events.userid条件有帮助。如果您要在其上添加索引（它真的是 varchar，顺便说一句？不是数字？），您的 INNER JOIN 也可能表现不同（并且变得更快）。
回答by DJ.
The INNER JOIN has to do an extra check to remove any records from A that don't have matching records in B and C. Depending on the number of records initially returned from A it COULD have an impact.
INNER JOIN 必须做一个额外的检查，以从 A 中删除任何在 B 和 C 中没有匹配记录的记录。根据最初从 A 返回的记录数，它可能会产生影响。
回答by Mark Byers
Use EXPLAINto see the query plan. It's probably the same plan for both cases, so I doubt it makes much difference, assuming there are no rows that don't match. But these are two different queries so it really doesn't make sense to compare them - you should just use the correct one.
使用EXPLAIN查看查询计划。对于这两种情况，这可能是相同的计划，所以我怀疑它有很大的不同，假设没有不匹配的行。但这是两个不同的查询，因此比较它们确实没有意义-您应该只使用正确的查询。
Why not use the "INNER JOIN" keyword instead of "LEFT JOIN"?
为什么不使用“INNER JOIN”关键字而不是“LEFT JOIN”？
回答by symfonian
LEFT JOINshows all data from Aand only shows data from B/Conly if the condition is true. As for INNER JOIN, it has to do some extra checking on both tables. So, I guess that explains why LEFT JOINis faster.
LEFT JOIN显示来自的所有数据，A并且仅B/C在条件为真时才显示来自的数据。至于INNER JOIN，它必须对两者进行一些额外的检查tables。所以，我想这解释了为什么LEFT JOIN更快。
