select * from A where id in (SELECT id from B);
select * from A where exists (SELECT 1 from B where A.id=B.id);
- in是在内存里遍历比较,而exists需要查询数据库,所以当B表数据量
IN()语句内部工作原理
IN()只执行一次,它查出B表中的所有id字段并缓存起来.之后检查A表的id是否与B表中的id相等,如果相等 则将A表的记录加入结果集中,直到__遍历完A表__的所有记录. 当B表数据较大时不适合使用IN(),因为它会把B表数据全部遍历一次
- 例1:A表有10000条记录,B表有1000000条记录,那么最多可能遍历10000*1000000次,效率很差.
- 例2:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率提升. 所以IN适合B表比A表数据小的情况
EXISTS()语句内部工作原理
exists()会执行A.length次,它不会缓存exists()结果集,因此结果集的内容就不重要了,重要的是其内查询语句 的结果集空或非空,空返回false,非空返回true. 当B表比A表数据大的时候适合使用EXISTS(),因为它没有那么多遍历操作,只需要在执行一次查询操作. 同样的例子
- 例1:A表有10000条数据,B表有1000000条数据,那么EXISTS()会执行10000次去判断A表中的id是否与B表中的id相等
- 例2:A表有10000条数据,B表有100条记录,那么EXISTS()还是执行10000次,还不如使用IN()遍历10000*100次 因为IN()是在内存里面遍历比较,而EXISTS()需要查询数据库.
总结:
- IN语句只执行一次,适合外表大而内表小的情况,执行时会先查询子查询;
- EXISTS语句执行外表length次,适合外表小而内表大的情况,执行时会先查主查询;
拓展:
- 用not exists替代not in 在子查询中,not in子句将执行一个内部的排序合合并. 无论在哪种情况下,not in都是最低效的(因为它对子查询的表执行全表遍历). 为了避免使用not in,可以改写成外连接(outer joins)或not exists.
- 用exists替换distinct 当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct,一般可以考虑使用exists替换. exists使查询更为迅速,因为RDBMS核心模块将在子查询的条件满足后立刻返回结果.
- 用表连接替换exists 通常来说,采用表连接的方式比exists有更高的效率.
