MySQL 代替in/not in 的sql语句

1.in和exists

in是把外表和内表作hash连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询,一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大;如果两个表中一个较小一个较大,则子查询表大的用exists,子查询表小的用in。

一般情况下,主表中的数据要少,从表的数据要多。

例:table a(小表) 、table b(大表)

select * from a where id in(select in from b)  -->效率低,用到了a表上id列的索引;

select * from a where exists(select id from b where id=a.id)  -->效率高,用到了b表上id列的索引。
与之相反:

select * from b where id in(select id from a)  -->效率高,用到了b表上id列的索引

select * from b where exists(select id from a where id=b.id)  -->效率低,用到了a表上id列的索引。

(1)性能的考虑此时就按子表大主表小用exist,子表小主表大用in的原则就可以.

(2)写法的不同, exist的where条件是: "...... where exist (..... where a.id=b.id)"

in的where条件是: " ...... where id in ( select id from......)"

2.not in和not exists

在做查询时,想要查询有联系的两张表,想得到结果是一张表有而另外一张表没有的数据时,我们通常会用not in:

select * from a where a.id not in (select id from b)

通常,我们会习惯性的使用not in,在数据比较少的时候是可以的,但是一旦数据量大了,not in的效率就会显得差了点。

因为not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。

所以无论那个表大,用not exists都比not in要快

所以推荐使用not exists或者外连接来代替:

select * from a where not exists(select id from b where id=a.id)
或者

select * from  a left join  b on a.id = b.id where b.id is null;




发布了17 篇原创文章 · 获赞 1 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览