左联接更快或内联接更快? [英] Left JOIN faster or Inner Join faster?

问题描述

所以...哪一个速度更快(NULl值不成问题)，并被编入索引.

So... which one is faster (NULl value is not an issue), and are indexed.

SELECT * FROM A
  JOIN B b ON b.id = a.id
  JOIN C c ON c.id = b.id 
 WHERE A.id = '12345'

使用左联接:

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

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

推荐答案

这要视情况而定；把他们俩都找出来；然后运行解释选择"进行解释.

It depends; run them both to find out; then run an 'explain select' for an explanation.

实际性能差异可能在几乎不存在"到非常重要"之间，具体取决于A中有多少行id ='12345'的行在B和C中没有匹配的记录.

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.

更新(基于发布的查询计划)

Update (based on posted query plans)

当您使用INNER JOIN时，从哪张表开始并不重要(从结果的角度，而不是从性能的角度)，因此优化程序会尝试选择它认为性能最佳的表.似乎您在所有适当的PK/FK列上都有索引，并且在friend_events.userid上没有索引，或者userid = '13006'的记录太多，因此没有使用它.无论哪种方式，优化器都会选择行数较少的表作为基础"，在这种情况下为zcms_users.

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.userid or 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.

当您使用LEFT JOIN时，确实很重要(以结果为准)，从哪个表开始；因此，选择了friend_events.现在，为什么，用这种方式花费的时间更少了，我不太确定.我猜friend_events.userid条件会有所帮助.如果要在其上添加索引(真的是varchar，btw吗?不是数字吗?)，那么INNER JOIN的行为也可能会有所不同(并且变得更快).

When you use LEFT JOIN it does matter (results-wise) which table to start with; thus friend_events is picked. Now why it takes less time that way I'm not quite sure; I'm guessing friend_events.userid condition 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.

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