not in,not exists,left join性能对比

    今天注意到，公司的代码里经常用到exists这个用法。也许个人习惯不一样吧，测试了下性能。对比下，这三种效率上的区别。
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表连接取代之。