NOT IN , NOT EXISTS ,LEFT JOIN / IS NULL 比较

要想从数据库中选出在A表中存在而又在B表中不存在的记录时，哪种方法更适合？

第一种:

SELECT  l.*

FROM    t_left l

LEFT JOIN

t_right r

ON      r.value = l.value

WHERE   r.value IS NULL

第二种：

SELECT  l.*

FROM    t_left l

WHERE   l.value NOT IN

(

SELECT  value

FROM    t_right r

)

第三种：

SELECT  l.*

FROM    t_left l

WHERE   NOT EXISTS

(

SELECT  NULL

FROM    t_right r

WHERE   r.value = l.value

)

首先创建两张表和制造数据：

CREATE TABLE filler (

        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT

) ENGINE=Memory;

CREATE TABLE t_left (

        id INT NOT NULL PRIMARY KEY,

        value INT NOT NULL,

        stuffing VARCHAR(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE t_right (

        id INT NOT NULL PRIMARY KEY,

        value INT NOT NULL,

        stuffing VARCHAR(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE INDEX ix_left_value ON t_left (value);

CREATE INDEX ix_right_value ON t_right (value);

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)

BEGIN

        DECLARE _cnt INT;

        SET _cnt = 1;

        WHILE _cnt <= cnt DO

                INSERT

                INTO    filler

                SELECT  _cnt;

                SET _cnt = _cnt + 1;

        END WHILE;

END

$$

DELIMITER ;

START TRANSACTION;

CALL prc_filler(100000);

COMMIT;

INSERT

INTO    t_left

SELECT  id, id % 10000,

        RPAD(CONCAT('Value ', id, ' '), 200, '*')

FROM    filler;

INSERT

INTO    t_right

SELECT  (l.id - 1) * 10 + f.id,

        l.value + 1,

        RPAD(CONCAT('Value ', (l.id - 1) * 10 + f.id, ' '), 200, '*')

FROM    (

        SELECT  id

        FROM    filler

        ORDER BY

                id

        LIMIT 10

        ) f

CROSS JOIN

        t_left l;

T_left表计100，000有10，000记录是不同的。

T_right表计1,000，000有10，000记录是不同的。

在t_left表中有10行与t_right表不同。

两张表都建了索引。

第一种方法：执行时间是0.663s

第二种方法：执行时间是0.679s

第三种方法：执行时间是1.796s

 

总结：第一种方法和第二种方法可取，not exists 性能不咋滴。

ps.文章在http://explainextended.com/博文翻译而来！