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/博文翻译而来!