MySQL5.7 IN与EXISTS

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

数据表的创建

  • 制造数据
    1
    2
    3
    4
    5
    6
    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;
  • 创建生成测试数据的存储过程
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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 ;
    运行存储过程
  • 创建子表
    1
    2
    3
    4
    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