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

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表连接取代之。

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/824287