mysql> select SQL_NO_CACHE count(*) from test1 where id not in(select id from test2);
+----------+
| count(*) |
+----------+
|   215203 |
+----------+
1 row in set (5.81 sec)

mysql> select SQL_NO_CACHE count(*) from test1 where not exists (select * from test2 where test2.id=test1.id);
+----------+
| count(*) |
+----------+
|   215203 |
+----------+
1 row in set (5.25 sec)

mysql> select SQL_NO_CACHE count(*) from test1 left join test2 on test1.id=test2.id where test2.id is null;             
+----------+
| count(*) |
+----------+
|   215203 |
+----------+
1 row in set (4.63 sec)

 

生产环境里，应尽量避免使用子查询，用left join表连接取代之。