MySQL 5.6 not in 和表关联性能测试对比

    对比的结果是加索引的情况下，最好是选择表关联。
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.37-log |

+------------+

##############开始制造数据##############

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.37-log |
+------------+

CREATE TABLE a (c1 INT, c2 VARCHAR(2000));

DELIMITER $
CREATE PROCEDURE pre_test() 
BEGIN 
DECLARE i INT DEFAULT 0;
WHILE i<40000 DO  INSERT INTO a  VALUES( i,LPAD(i, 1200, 0)); 
SET i = i+1; 
END WHILE; 
END$

CALL pre_test();

#多执行几次
INSERT INTO a SELECT * FROM a;
COMMIT;

CREATE TABLE b LIKE a;
SHOW CREATE TABLE b;
INSERT INTO  b SELECT * FROM a  LIMIT 20000;
COMMIT;
SELECT COUNT(1) FROM a;
+----------+
| COUNT(1) |
+----------+
|   160000 |
+----------+
SELECT COUNT(1) FROM b;
+----------+
| COUNT(1) |
+----------+
|    20000 |
+----------+


#第一组测试
SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a ); ##3.092
EXPLAIN  SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY  | key_len | ref  | ROWS   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY     | b     | ALL  | NULL          | NULL | NULL    | NULL |  18348 | USING WHERE |
|  2 | SUBQUERY    | a     | ALL  | NULL          | NULL | NULL    | NULL | 146674 | NULL        |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;#0.018
EXPLAIN SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY  | key_len | ref  | ROWS   | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |  18348 | USING WHERE                                        |
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL | 146674 | USING WHERE; USING JOIN buffer (Block Nested LOOP) |

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+

#第二组测试
SELECT * FROM a WHERE c1 NOT IN(SELECT c1 FROM b ); ##0.081
EXPLAIN  SELECT * FROM a WHERE c1 NOT IN(SELECT c1 FROM b );
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY  | key_len | ref  | ROWS   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY     | a     | ALL  | NULL          | NULL | NULL    | NULL | 146674 | USING WHERE |
|  2 | SUBQUERY    | b     | ALL  | NULL          | NULL | NULL    | NULL |  18348 | NULL        |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
SELECT a.* FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;#出不来
EXPLAIN SELECT a.* FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY  | key_len | ref  | ROWS   | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL | 146674 | NULL                                               |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |  18348 | USING WHERE; USING JOIN buffer (Block Nested LOOP) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+

#加上索引后
CREATE INDEX ind_a_c1 ON a(c1);
CREATE INDEX ind_b_c1 ON b(c1);
#第三组测试
SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a ); ##0.053
EXPLAIN  SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
mysql> EXPLAIN  SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
| id | select_type        | TABLE | TYPE           | possible_keys | KEY      | key_len | ref  | ROWS  | Extra                              |
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
|  1 | PRIMARY            | b     | ALL            | NULL          | NULL     | NULL    | NULL | 18348 | USING WHERE                        |
|  2 | DEPENDENT SUBQUERY | a     | index_subquery | ind_a_c1      | ind_a_c1 | 5       | func |     2 | USING INDEX; FULL scan ON NULL KEY |
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+

SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;#0.001
EXPLAIN SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY      | key_len | ref       | ROWS | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
|  1 | SIMPLE      | b     | ref  | ind_b_c1      | ind_b_c1 | 5       | const     |    1 | USING INDEX CONDITION |
|  1 | SIMPLE      | a     | ref  | ind_a_c1      | ind_a_c1 | 5       | test.b.c1 |    1 | USING INDEX           |
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+

#第四组测试
SELECT COUNT(1) FROM a WHERE c1 NOT IN(SELECT c1 FROM b ); ##0.022
EXPLAIN  SELECT COUNT(1) FROM a WHERE c1 NOT IN(SELECT c1 FROM b );
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | TABLE | TYPE  | possible_keys | KEY      | key_len | ref  | ROWS   | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | PRIMARY     | a     | INDEX | NULL          | ind_a_c1 | 5       | NULL | 146674 | USING WHERE; USING INDEX |
|  2 | SUBQUERY    | b     | INDEX | ind_b_c1      | ind_b_c1 | 5       | NULL |  18348 | USING INDEX              |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+

SELECT COUNT(1)  FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;#0.052
EXPLAIN SELECT COUNT(1)  FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
| id | select_type | TABLE | TYPE  | possible_keys | KEY      | key_len | ref       | ROWS   | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
|  1 | SIMPLE      | a     | INDEX | NULL          | ind_a_c1 | 5       | NULL      | 146674 | USING INDEX              |
|  1 | SIMPLE      | b     | ref   | ind_b_c1      | ind_b_c1 | 5       | test.a.c1 |      1 | USING WHERE; USING INDEX |
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+