MySQL5.7 IN与EXISTS

文章中的表参考这里的创建http://blog.csdn.net/stevendbaguo/article/details/73467649

数据表的创建

制造数据

DROP TABLE IF EXISTS test; 
CREATE TABLE test( 
    ID INT(10) NOT NULL, 
    `Name` VARCHAR(20) DEFAULT '' NOT NULL, 
    PRIMARY KEY( ID ) 
)ENGINE=INNODB DEFAULT CHARSET utf8;

创建生成测试数据的存储过程

DROP PROCEDURE IF EXISTS pre_test; 
DELIMITER //
CREATE PROCEDURE pre_test() 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET autocommit = 0; 
WHILE i<4000000 DO 
INSERT INTO test ( ID,`Name` ) VALUES( i, CONCAT( 'Carl', i ) ); 
SET i = i+1; 
IF i%10000 = 0 THEN 
COMMIT; 
END IF; 
END WHILE; 
END; //
DELIMITER ;

运行存储过程

创建子表

CREATE TABLE test_bak AS SELECT * FROM test LIMIT 1000;
CREATE INDEX ind_t_id ON test(id);
SHOW CREATE TABLE test;
CREATE INDEX ind_t1_id ON test_bak(id);

SQL测试

[SQL]
SELECT * FROM test a LEFT JOIN test_bak b ON a.id = b.id LIMIT 5000;
时间: 0.017s

[SQL]
SELECT * FROM test a LIMIT 5000;
时间: 0.003s

[SQL]
SELECT * FROM test a WHERE a.ID in (SELECT ID FROM test_bak) LIMIT 5000;
时间: 0.004s

[SQL]
SELECT * FROM test a WHERE EXISTS(SELECT 1 FROM test_bak b WHERE b.id = a.id) LIMIT 5000;
时间: 19.255s

[SQL]
SELECT * FROM test_bak b WHERE b.ID in (SELECT ID FROM test) LIMIT 5000;
时间: 0.003s

[SQL]
SELECT * FROM test_bak b WHERE EXISTS(SELECT 1 FROM test a WHERE b.id = a.id) LIMIT 5000;
时间: 0.006s

[SQL]
SELECT * FROM test a WHERE a.Name in (SELECT Name FROM test_bak) LIMIT 1000;
时间: 0.101s

[SQL]
SELECT * FROM test a WHERE EXISTS(SELECT 1 FROM test_bak b WHERE b.Name = a.Name) LIMIT 1000;
时间: 0.229s

注意：最后两条LIMIT别大于1000

原因

MySQL自某个版本起（不记得了，逃~~），对于in中无与外表的数据做条件判断的查询，会优化为join方式执行。这比EXISTS高效的多
当然，in中内表不能与外表的数据做条件判断，否则请用join